I’ve been working with and talking about SQL Server Log Shipping a lot lately! If you haven’t yet, go ahead and watch my webcasts – Part 1: Preparing for Disaster and Part 2: When Disaster Strikes.
One way to ship logs…
I’ve gotten a lot of great questions about log shipping through these webcasts, so I’ve put together an FAQ.
- What editions of SQL Server is log shipping available in?
- 2012 – Enterprise, Business Intelligence, Standard, and Web
- 2008R2 – Datacenter, Enterprise, Standard, Web, and Workgroup
- 2008 – Enterprise, Standard, Web, and Workgroup
- 2005 – Enterprise, Standard, and Workgroup
- Does the secondary need to be licensed?
- I am not the licensing police, and I am not Microsoft – check with your licensing representative to clarify your exact situation. Generally, you can have one warm standby server. However, the second someone starts using it for reporting, testing, or anything else, you need to license it like any other server.
- Log shipping is compatible with backup compression. What edition of SQL Server do I need to take advantage of compression?
- 2012 – Enterprise, Business Intelligence, or Standard
- 2008R2 – Datacenter, Enterprise, or Standard
- 2008 – Enterprise
- 2005 – Not available
- When log shipping is set up, Agent jobs are created to alert me if a backup, copy, or restore fails. How do I get notified?
- You need to go into the Agent job, pull up Notifications, and choose your method – email an operator, or write to the event log, for example.
- Are my logins shipped from the primary to the secondary?
- No, they are not. You’ll need to set up a separate method to sync the logins.
- Does this replace, or can it be combined with, our existing daily full and log backups?
- TL; DR – no.
- You’ll still want to take regular full and/or differential backups. Log shipping only takes one full backup – at the beginning – and that’s only if you specify that it does so. It can also be initialized from an existing full backup.
- Taking two log backups in separate jobs will break the log chain, however. If you implement log shipping, it will replace your current transaction log backup job.
- What’s the difference between the secondary being in “Restoring” vs. “Standby”?
-
- Restoring means the database is not accessible. Standby means it is read-only. You make this decision when you set up the log shipping.
- If the database is in Standby mode, users can query it – except when a log backup is being restored. You need to decide if a restore job will disconnect users, or if the restore is delayed until after the users are disconnected.
What other questions do you have?
Robert L Davis March 21, 2013 | 6:38 pm
Great FAQ, Jes!
One small disagreement, taking a log backup in a separate job won’t break the log chain. Failing to restore that log backup on the secondary will cause a gap in the log chain on the secondary. You can manually restore that log backup (or even a differential that spans the gap) on the secondary to get log shipping to continues.
Jes Schultz Borland March 22, 2013 | 9:27 am
Robert, thanks for the clarification. You can take as many log backups as you want – it’s the order they are restored in that matters.
Patrick B March 22, 2013 | 6:59 am
Jes,
What are the pros and cons of using backup compression with log shipping?
Thanks,
Patrick
Jes Schultz Borland March 22, 2013 | 9:31 am
Patrick, I’d ask, “What are the pros and cons of backup compression?” Enabling backup compression will use extra CPU, but the backups will take up less space on disk, and the files that need to be copied across the network are smaller. Don’t use it on a system constrained by CPU, however.
Pingback: Something for the Weekend - SQL Server Links 22/03/13 • John Sansom
SMinar March 22, 2013 | 9:02 am
I haven’t gotten to work with this yet, but it surprises me that there are log chain issues. Can you not just set the log shipping job to copy_only and leave your existing log backups untouched? Though I guess you would then need to backup the secondary’s log to keep it under control.
Jes Schultz Borland March 22, 2013 | 9:34 am
If you leave an existing log backup job in place, and implement log shipping, you would have to figure out a way to restore both log backups to the secondary, in order, so the log shipping chain was never out of order.
Robert L Davis March 23, 2013 | 12:51 am
No, you cannot set the log shipping job to do copy only log backups. There’s no logical reason you would want it to do that. You would then need a second log backup job to maintain the log file.Why woul you want to do twice as many log backups?
The secondary’s log cannot be backed up. The database is not online. It is maintained by replaying the records of the primary. As long as you maintain the log on the primary, it will be maintained on any secondaries.
Damian March 22, 2013 | 6:36 pm
•Taking two log backups in separate jobs will break the log chain, however. If you implement log shipping, it will replace your current transaction log backup job.
Jes – if you do a “copy only” backup – that will not break a log chain IMO
Alejandro March 22, 2013 | 9:21 pm
HI Jes. I am a big fan, congrats.
Jes. You really lost me in the log shipping job (second). If i have a log shipping set up and in the same time i have a recovery model. (full every Sunday- Differentials every day and logs every hour) it will make a conflict.
Please can you clarified this foe me you put me aout track.
Thanks and once again thanks for your hard work on this.
Jes Schultz Borland March 25, 2013 | 8:17 am
The only type of backup that log shipping controls is log backups. Full (and differential, if you take them) backups are not part of the automatic log shipping jobs. Full and differential backups still need to be run regularly, in separate jobs.
Sam Greene April 4, 2013 | 5:37 pm
Jes,
Once you’ve failed from Primary to Secondary and them from ‘Secondary’ back to ‘Primary’, do you need to remove the log shipping from Secondary to Primary?
I’m getting alerts from the LSAgent on both servers that sync hasn’t happened from S to P. So a final step for the planned failover would be to remove the shipping that was set up to do the second failure?
Thanks for the screencast, it was well done.
Jes Schultz Borland April 5, 2013 | 6:23 am
Sam, thanks for watching. Yes, after you’ve failed back from your original secondary to your original primary, you’ll want to disable the LSBackup on the secondary and the LSCopy and LSRestore on the primary. They serve no purpose at that point.
Sam Greene April 8, 2013 | 11:56 am
I’ve disabled them, but LSAlert raises errors since the databases being shipped from B to A are now out of sync and past the threshold. Seems like that shipping setup needs to be removed.
Sam Greene April 8, 2013 | 3:19 pm
Looks like the shipping must be removed: http://social.msdn.microsoft.com/Forums/en-US/sqldisasterrecovery/thread/3a0aa7bf-cbaa-40e2-9bd5-ecb64c7c550e/
Derek Steinmetz April 18, 2013 | 3:56 pm
If my SQL 2012 agent is running as NT Service\SQLSERVERAgent how do I go about giving the file share permissions with that account or should I have not used the SQL Server 2012 default installation accounts to run SQL Server? Thanks.
Jes Schultz Borland April 22, 2013 | 3:28 pm
Derek, best practice from Microsoft is to use individual domain accounts for SQL Server services – and I agree. Here’s a link on how to configure security for Log Shipping: http://support.microsoft.com/kb/321247.
Miguel April 28, 2013 | 3:16 pm
Just to confirm you can not do log shipping from SQL 2012 to SQL 2008 R2 right?
Brent Ozar April 28, 2013 | 3:23 pm
Correct. You can’t restore a database to an earlier version of SQL Server.