June 20, 2022 at 2:48 pm
Hi,
I'm looking for a third-party backup and restoration solution because native restore takes roughly 7 hours for a 4TB database.
Some of the object restore solutions really do a full restore in the background then just pull out the tables selected. This does not alleviate the 7-hour time limit.
Is the Lite Speed version of the object restore option functional? Or any other backup software ?
Thank you!
June 20, 2022 at 3:13 pm
Hi,
I'm looking for a third-party backup and restoration solution because native restore takes roughly 7 hours for a 4TB database.
Some of the object restore solutions really do a full restore in the background then just pull out the tables selected. This does not alleviate the 7-hour time limit.
Is the Lite Speed version of the object restore option functional? Or any other backup software ?
Thank you!
You're doing something wrong if it takes that long to restore a 4TB database. For example, are you using compression? Are you using the buffer count and transfer size options at all and, if so, how did you decide which values work the best? Is your "pipe" the right size and speed? Do you have "Instant File Initialization" enabled? Have you been paying attention to the VLF configuration and sizes of your Transaction Log File? What about your hard disk speed/throughput and are you using spinning rust or SSDs?
And why do you think that any third party solution is going to fix your hardware if that's an issue or custom tune the other settings I mentioned? Most 3rd party backups use the same restore command that native backups and restores use. I don't know of a one that has some other form of magic being done.
Between the hardware and the "pipe" my infrastructure folks setup and the tweaking I've done (which can vary from installation to installation), I'm doing compressed backup to NAS at the rate of nearly 3TB per hour and the restores are just a little slower AND I'm doing single threaded backups to boot rather than using multiple files for any given database.
And I know this sounds absolutely silly but if the batteries on the cache have gone bad, you're also in deep Kimchi. Ours went bad and some folks didn't even know that there was a battery backup to ensure crashes don't corrupt data. That normally only affects backups and not restores but my 2.25 hour backups suddenly jumped to 11-12 hours. Yes... the batteries are rechargeable but even rechargeable batteries have a "lifetime".
Even if you believe in none of what I said, I strongly recommend that you get a "trial" copy of whatever you intend to buy and test it! I believe you'll be surprised to see that it doesn't make the difference that you expect.
--Jeff Moden
June 20, 2022 at 8:26 pm
Thank you for your Response Jeff Moden!
June 20, 2022 at 10:15 pm
In addition to what Jeff Moden has outlined - take a look at the BUFFERCOUNT and MAXTRANSFERSIZE parameters for both backup and restore. Adjusting those values can improve performance quite a bit - in one case I was able to modify a backup process that was taking upwards of 6+ hours down to less than 1.5 hours and a restore process in excess of 9 hours to less than 2 hours with just those changes.
If you really want faster backup/restore options - then you really need to work with your SAN vendor. Almost all of them have snapshot capabilities which can perform a 'backup' in seconds, and then a restore from that snapshot can be done in seconds. But - there are limitations and your SAN vendor will be able to help you configure a solution that works for your requirements.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 20, 2022 at 10:23 pm
Third party solutions can use more compression (and CPU) than native compression, so you need CPU to spare, but as Jeff and Jeffrey mentioned above, you ought to be able to restore quicker than 7 hours.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 21, 2022 at 12:46 pm
If you really want faster backup/restore options - then you really need to work with your SAN vendor. Almost all of them have snapshot capabilities which can perform a 'backup' in seconds, and then a restore from that snapshot can be done in seconds.
Are snapshots adequate for SQL database disaster recovery purposes ? Is it considered a FULL backup ? What about point in time recovery ? Applying Differentials or transaction logs ?
June 21, 2022 at 12:48 pm
3rd party tools should have a free trial period, so you can compare to your existing process.
June 21, 2022 at 7:33 pm
Jeffrey Williams wrote:If you really want faster backup/restore options - then you really need to work with your SAN vendor. Almost all of them have snapshot capabilities which can perform a 'backup' in seconds, and then a restore from that snapshot can be done in seconds.
Are snapshots adequate for SQL database disaster recovery purposes ? Is it considered a FULL backup ? What about point in time recovery ? Applying Differentials or transaction logs ?
It depends - entirely on what the SAN vendor provides and the RTO/RPO requirements. Some SAN vendors have combined snapshots and tlog backups to provide point in time recovery.
IBM has a solution where they perform a full backup using a snapshot of the database and incremental (differential) backups. The solution also includes log backups for point in time recovery. As far as SQL Server is concerned, the backup takes seconds to complete - but the actual backup is performed off that server where they can then compare current snapshot to previous snapshot, take the delta, compress and dedupe that data. The data is then replicated to another DC for offsite storage.
So again - it depends...
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
June 21, 2022 at 9:08 pm
In addition to what Jeff Moden has outlined - take a look at the BUFFERCOUNT and MAXTRANSFERSIZE parameters for both backup and restore. Adjusting those values can improve performance quite a bit - in one case I was able to modify a backup process that was taking upwards of 6+ hours down to less than 1.5 hours and a restore process in excess of 9 hours to less than 2 hours with just those changes.
Those are the two settings that I use and mentioned but not by the actual name. I totally agree with the above and had similar results. Through experimentation, I found that 17 and 1,048,576 (commas added for clarity on the binary million) worked the best for me. YMMV.
If you really want faster backup/restore options - then you really need to work with your SAN vendor. Almost all of them have snapshot capabilities which can perform a 'backup' in seconds, and then a restore from that snapshot can be done in seconds. But - there are limitations and your SAN vendor will be able to help you configure a solution that works for your requirements.
Just make damned sure that the SAN you're backing up to isn't the same SAN that your databases live on. 😀 I'm not sure that's possible with the "instant" SAN "backups" though.
They did that on the Dev box at work and when it blew chunks, it was dead because the 2 days of recent backups were also gone. At the time, they quite literally "didn't think" that would ever be a problem nor did they even consider backing those up to tape.
--Jeff Moden
June 22, 2022 at 6:00 pm
My question is do you need to perform frequent restores, and why?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply