I’ve been working with and talking about SQL Server Log Shipping a lot lately!
I’ve gotten a lot of great questions about log shipping, 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?
Interested in learning more about backups and recovery?
Grab my Backup & Recovery Step By Step training for more information on why backups and restores are so important, and how to perform them!