October 3, 2016 at 6:49 am
I want to configure Log shipping for 7 TB database in sql server 2014(EDW environment) and we are using commvault as a backup tool, weekly full backup happens on Friday and backup takes 5 to 6 hours to complete.And restoration of full backup to DR server takes 32 Hours of time.Please correct me if the below process to configure Logshipping is wrong or suggest better way to configure with less down time.
1)I will change the recovery model to FULL.
2)will take Fullbackup(Friday night) and log backup.
3)will start restore Full backup in DR on Saturday mng and expected finish by Sunday afternoon.Then I will restore the initial log backup and current log backup then I will configure log shipping.
To maninatin the logfile size as less as possible can I take in between log backups when full restoration happening on DR server??
Please suggest.
October 3, 2016 at 7:34 am
prem.m38 (10/3/2016)
I want to configure Log shipping for 7 TB database in sql server 2014(EDW environment) and we are using commvault as a backup tool, weekly full backup happens on Friday and backup takes 5 to 6 hours to complete.And restoration of full backup to DR server takes 32 Hours of time.Please correct me if the below process to configure Logshipping is wrong or suggest better way to configure with less down time.1)I will change the recovery model to FULL.
2)will take Fullbackup(Friday night) and log backup.
3)will start restore Full backup in DR on Saturday mng and expected finish by Sunday afternoon.Then I will restore the initial log backup and current log backup then I will configure log shipping.
To maninatin the logfile size as less as possible can I take in between log backups when full restoration happening on DR server??
Please suggest.
You have a variety of ways you can "initialize" log shipping using the built-in stuff. But I don't know if you can use the commvault stuff for the GUI-driven-and-configured log shipping. That process expects to do it's own backups. I note that all of the log shipping stuff is just msdb tables and sprocs which you have full access too and can massage as needed. I have done a variety of custom configurations over the years. The best thing from my perspective is that you can do ONE backup to ONE location and have the primary drop the file there and the secondary read the file from there, assuming both servers have access to the share. This saves on a full pair of reads/writes of the tlog files.
You can and should do tlog backups throughout the long restore. If/when you have backup restores scheduled they will obviously not be successful while the big restore is happening.
Oh, speaking of LONG restore, have you initialized Instant File Initialization? That can GREATLY reduce the time it takes to do big restores because you won't have to zero out every bit of the main database files any longer.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 3, 2016 at 12:49 pm
If you want to get tricky with it, you can leave the primary DB in SIMPLE mode during the FULL backup. I've done this on a 3.5TB and had great success with it (there's extreme latency between primary and secondary so this way is much easier).
1-Leave primary DB in SIMPLE mode
2-Take FULL backup of primary
3-Restore FULL backup to secondary WITH NORECOVERY
4-Switch primary DB to FULL mode
5-Take DIFFERENTIAL of primary
6-Restore DIFFERENTIAL to secondary WITH NORECOVERY
7-Set up log shipping on the primary and mark the DB as 'already initialized'
Catch:
-Make sure there's no third party backup software that is running. This includes FULL and LOG backups. That will hose you up. As an example, Rackspace Managed Backup checks to see if it took the last FULL backup, if it didn't, then it will automatically start taking a FULL backup for you, invalidating your DIFFERENTIAL. While restoring the DIFFERENTIAL and initializing log shipping, you don't want anything messing you up by breaking your differential base.
Here's a link to a DBA.SE question I asked about doing this very same process:
http://dba.stackexchange.com/questions/107655/log-shipping-large-dbs-what-about-the-log
October 4, 2016 at 11:36 pm
Hi Kris,
I want keep my secondary database in standby/read-only mode,will the same process work for standby mode??
Or till differential restore Can I keep my secondary in No Recovery and when configuring LS can I switch it to standby ??? will it work?
If I restore my full backup with standby mode ,it will not allow to restore further differential backups right.
Please suggest how to proceed.
October 6, 2016 at 2:57 am
Hi Kevin,
We have space issues in local server so I have to use Commvault only for backup and restore.And regarding instant file initialization I can just enable this on DR server is fine right?? Not required in both Prod and DR.
Once this is enabled even though I restore thru Commvault tool It will work I guess.And my other question is my Database I have already restored on DR longback and this time I just want to overwrite ...if I do will it take advantage of IFI ? Or better I will delete the DB and do fresh Restore.
Please suggest.
October 6, 2016 at 6:32 am
prem.m38 (10/4/2016)
Hi Kris,I want keep my secondary database in standby/read-only mode,will the same process work for standby mode??
Or till differential restore Can I keep my secondary in No Recovery and when configuring LS can I switch it to standby ??? will it work?
If I restore my full backup with standby mode ,it will not allow to restore further differential backups right.
Please suggest how to proceed.
You can certainly keep it in standby mode, you make that happen when you do the initial set up of log shipping.
During the restore process (before you set up log shipping), just use NORECOVERY. Then, when you go through the log shipping configuration in SSMS, specify that you want the DB to be in standby mode. After the first restore occurs, youll see the secondary switch from 'Restoring...' To 'Standby/Read Only'.
Does that answer your question?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply