Disaster Recovery Plan using Microsoft SQL Server Logshipping







Disaster Recovery Plan using Microsoft SQL Server Logshipping

The purpose of this post is to give you a quick view of Logshipping with Microsoft SQL Server 2005/2008 for a scenario Disaster Recovery Plan.


Description
The log shipping is a SQL Server to maintain a backup server to date by applying to regular transaction logs of a database on a secondary database An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

The principle is simple:
1. Backup of Transaction Log on the primary server
2. Copy of Transaction Log on the primary to the secondary server
3. Restore Transaction Log on the machine secondary

The method of recovery must be in FULL or Bulk-Logged.

Log Shipping Overview
http://msdn.microsoft.com/en-us/library/ms187103.aspx
 


Benefits

• No need for specialized equipment
• Possibility to have multiple secondary servers
• Ability to access the database in read-only secondary
• Possibility to have a cluster on the production site and a simple machine on the backup site


Constraints

• Operating base by base (not server)
• Need to keep the OS on both machines in parallel
• Need to retain both SQL (options, logins, ...)
• Large network bandwidth to copy the databases and logs.
• More input / output discs due to copy transaction logs from one machine to another. It is recommended to stagger the various backup jobs each database.
• Asynchronous operation can thus shift between primary and secondary server. In the case of failover on the secondary site, may be the last transaction logs were not copied, the data from the backup site would then not be in phase with the production site.
• Both machines must be on the same network to communicate and make copies of files. They will have different names.
• Failover and clients connection string must be manually .
• Plan and monitor the capacity directories backup and restore to ensure that storage space is sufficient for newspapers sent.

If you want a solution without data loss synchronous and automatic failover you must choose SQL Server Mirroring instead of Logshipping


Maintenance

• Maintenance must be duplicated on the main server and all secondary servers .
• Any changes to SQL Server such as a configuration change, adding login ... must be made on different servers

Managing Metadata When Making a Database Available on Another Server Instance
http://msdn.microsoft.com/en-us/library/ms187580.aspx



Applying patch or hotfix
• Applying a service pack or hotfix on a SQL Server must be on each machine.SQL Server logshiping allows for rolling patches an upgrades :
- You have to upgrade secondary first
- Then perform a failover
- And upgrade the original primary (now secondary)


Test server backup
• Possible without affecting the production.
• Need to resynchronize the databases on the server after the backup test
• Manual Switches

Failing Over to a Log Shipping Secondary
http://msdn.microsoft.com/en-us/library/ms191233.aspx

Changing Roles Between Primary and Secondary Servers
http://msdn.microsoft.com/en-us/library/ms178117.aspx











Michel Degremont | Microsoft EMEA
Product Support Services Developer - SQL Server Core Engineer |

Skip to main content