Agile Insider Blog

SQL Server Log Shipping Best Practices

Log shipping in SQL Server has been introduced since SQL Server 4.21a. But it is still good to used in replicating database in either another location or same server box. It is best used when you upgrading your database, implementing disaster recovery scenario, doing database maintenance while keeping database window as minimum as possible. I list things you need to consider while preparing log shipping for successful implementation.

  • Make sure database recovery model is full recovery to support transaction log chain recovery.
  • Before you can restore subsequent transaction logs on secondary server (for log shipping target), you need to restore database full back into secondary server with option either WITH NORECOVERY or WITH STANDBY option. If you don’t specify those options, the default will be WITH RECOVERY so that you couldn’t restore subsequent transaction logs.
  • If you plan to implement log shipping into multiple secondary servers, it is better to have backup and restore time same for all of them.
  • Make sure your transaction log backup window period is prudent enough so that avoiding big transaction log applied to secondary server. In this case, size does matter. And make sure no long running transaction such as inserting mass amount of record that would take transaction log recovery longer.
  • It’s better to consider separating network for copying transaction log from other network activities to ensure copy process doesn’t get distracted. And always copy transaction log backup from primary to intermediate location to avoid single point of failure in primary server.
  • Avoid log shipping different SQL Server version between primary and secondary. The good point is that log shipping can tolerate with different hardware specification and for different edition of SQL Servers along with their x64/x86 build.
  • Make sure you have same database collation so that your application will have consistent behaviour such as sorting and searching.
  • Make sure you allow access to folder share for account that is doing backup and copy task and restrict users to access the share log shipping folder. It is security matter.
  • Make sure you have your logins identical between primary and secondary.
  • Script all objects that reside outside database such as SQL Server Agent job, SSIS packages. Test these objects on testing server (act as secondary) to make sure it works fine when secondary server is expected to take over role from primary server.
  • Never change database recovery model to simple/bulk logged and change it back to full recovery for any good reason. Doing so will break transaction log chain to stop log shipping from running.

Please check us out for your Managed Service or Cloud Consulting needs.

Leave a comment

Learn More Today

Have questions or want to learn more about the services and solutions Agile IT has to offer?

Schedule a call with us today!

Schedule a Call

Request a Quote