October 21, 2010 at 3:08 pm
I'm pretty new at this, so bear with me...
I'm trying to set up logshipping from one primary server to two standby servers. One standby server physically located right next to the primary server, the other standby server located several miles away. Both standby servers should be updated in 15 min intervals. Naturally, there will be some lag time associated with transferring the log file across the wires to the standby server located miles away, so I'm not sure what I need to account for that in this scenario. I'm assuming in a situation like this both standby servers would probably have to use the same restore interval (15 mins) but if not, that would be good to know. I'd like to do this via scripts/native SQL if possible, as opposed to third-party software. I haven't been too successful in finding anything useful in google so i thought I'd try here.
Anyone out there have any experience with doing this? Any scripts or instructions I might be able to get a hold of?
Any help is much appreciated!
Thanks
October 21, 2010 at 3:17 pm
If you use the SSMS Transaction Log setup wizard, you will see that you can log ship to multiple secondary destinations. Each can have their own copy/restore schedule. I've log shipped to two secondaries before, without problem. Both secondaries were many miles apart from each other (one 400, the other ~1000).
Depending on the frequency of the transaction log backups, those files can be relatively small, so they would be quick to transfer. Until you do something like rebuild indexes...
I work with another system that is log shipping to 3 secondaries: 1 in the same office, 1 in the same city (~10 miles away), and the third across the country, ~ 2500 miles away. Again, all are working fine.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 21, 2010 at 3:54 pm
Ok, so I feel a little dense not realizing SQL already gives you two instances to restore to. Thanks for the reply. I'm a little nervous to set it up and get it going without having a full understanding of what to do/how to fix it when it breaks (or when we need to do an actual recovery!). Guess I'll just have to dive in...
Thank you again!
October 21, 2010 at 4:07 pm
I suggest you set up a test environment. You can actually do all of this on one server if you want...
Set up a database.
Set up log shipping to another database on the same server.
Play around with adding data to the database.
Also, work with manually restoring transaction logs on the log-shipped database, and with performing a "tail log backup" and restoring it on the log-shipped database.
The more you play around with it, the more comfortable you'll be with it. After all, log-shipping is one of the "high-availability" options... if your primary database goes down, you NEED to be able to bring it up as fast (but properly) as possible. This is one of those areas that you need to do frequently just so that which it's crunch time, you do things right. There is nothing worse in this case than messing up the restore.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 22, 2010 at 2:14 pm
Thank you, Wayne. I do have a test server (two actually) that I can play around with so I'll definitely be using those to see how it works out. I just finished up a test for logshipping one db to one standby server and it worked so that's good. I've not had experience manually restoring the transaction logs, so that will be next on my list.
Another question on the process though,...I can't seem to find any info on this specific part of the logshipping process. I was always under the impression that I should be doing a backup (ours happens at 1am) and restore that db every night to the standby server. Then the log shipping would be in place (apart from when the restore is occurring) to keep the standby server as updated as possible (as opposed to having to rely on the nightly backup for recovery if we go down in the middle of the day). Then someone recently told me that I'm not supposed to be doing the nightly restores to that standby server, that the log shipping will just keep continuing to keep the standby server updated. Am I just doing an unnecessary restore to that standby server each night? This restore to the other (2nd) standby server that's miles away would take quite a while to copy over (once I put it in place), so if it's not actually necessary, that would help a lot.
Thanks for the input!
October 22, 2010 at 2:21 pm
tacy.highland (10/22/2010)
Am I just doing an unnecessary restore to that standby server each night?
Yes you are.
The log restores will keep the database up to date. There is never a need to restore a full backup to the standby server unless there is either a break in the log chain (setting the primary db to simple, corrupt log, etc).
October 22, 2010 at 2:23 pm
Thank you for the confirmation, Derick! Makes this process a bit easier then!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply