Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Convert Mirroring to Log Shipping

By Jeremy Egbert,

There are certain occasions when you would rather have a high availability (HA) solution be log shipping as opposed to database mirroring in SQL 2005/2008. A few examples include; having multiple secondary databases, delay restoring the log to protect against human error, or to have a quick backout plan for a code upgrade to a VLDB. This article will use the last example as a reason for converting from mirroring to log shipping.

Creating a quick backout plan

Converting a mirrored database to a log shipped database can provide a fast way to rollback a failed code upgrade. The traditional method of rolling back the code upgrade of a mirrored database would be to restore from a backup taken prior to the upgrade. With a VLDB restoring from backup might take several hours and cause a longer downtime than necessary.

Once we convert the mirrored database to a log shipped database we can disable the restore job on the secondary and have the secondary available as a hot standby in case of an upgrade failure. This allows us to quickly backout of the upgrade by simply bringing the secondary online and pointing the application servers at it. Once the system is back online we can reestablish mirroring back to the original principal. If the upgrade is successful, we enable the restore job on the secondary, allow the secondary to catch up, and then reestablish mirroring.

The Setup

For this article, I will assume the following: There are 2 SQL 2008 Enterprise Edition (EE) servers with default instances installed and a witness server as a default instance of SQL 2008 Standard. All 3 servers are running on the same domain and I am a sysadmin on each server. The mirrored database is DemoDB.

  • Server1 - Principal/Primary server with SQL Agent running under domain\server1service
  • Server2 - Mirror/Secondary server with SQL Agent running under domain\server2service
  • Witness1 - Witness/Log Ship monitor with SQL Agent running under domain\witness1service

The conversion steps

1. Setup file shares for the backup files on each server. Create a share called \\Server1\LogShip.

2. Grant domain\server1service read/write access to the folder and share. Grant domain\server2service read access to the folder and share.

3. Create \\Server2\LogShip and grant domain\server2service read/write access to the folder and share.

4. Stop the backup jobs on Server1(Principal) for DemoDB.

5. Remove mirroring by executing the following on Server1(Principal):

ALTER DATABASE DemoDB SET PARTNER OFF


6. In SSMS right click on the DemoDB database on Server1 and select properties.

7. Select the "Transaction Log Shipping" page on the left hand side and then check the "Enable this as a primary database in a log shipping configuration".

8. Click "Backup settings" and enter the share path created in step 1, \\Server1\LogShip, and the local path to the share C:\LogShip. You can also set the backup file retention, alerting, and backup schedule here but we will use the defaults.

9. Click on the "Add" button under Secondary databases

10. Click "Connect" and enter Server2, ensure DemoDB is selected as the secondary database

11. On the "Initialize Secondary Database" tab select the "No, the secondary database is initialized" radio button.

12. Under the "Copy Files" tab enter the share location \\Server2\LogShip. You can also set the backup file retention and schedule here but we will use the defaults.

13. Under the "Restore Transaction Log" tab, ensure the radio button next to "No recovery mode" is selected and disable the restore job since we will use the secondary as a quick backout in the event of an upgrade failure. Here you can set in a delay for the restore of the transaction log backups, alert timing, and the schedule for restoring the transaction log backups to the secondary.

14. Check the "Use a monitor server instance" box and then click the "Settings" button. Click "Connect" and enter Witness1. Select the appropriate login info and history retention and click OK. Again, we will use the defaults.

15. Finally click OK on database properties and voila! You've just converted a mirrored database to a log shipped database.

Conclusion

By converting from database mirroring to log shipping we have created a faster backout plan in the event of a code upgrade failure. If the database upgrade is successful then we just need to enable the restore job on the secondary (Server2), allow it to catch up, and then reestablish mirroring. The SQL CAT team has written a great white paper about mirroring and log shipping which includes converting a log shipped database to a mirrored database. I urge you to check it out here:
http://sqlcat.com/whitepapers/archive/2008/01/21/database-mirroring-and-log-shipping-working-together.aspx


Total article views: 5520 | Views in the last 30 days: 7
 
Related Articles
FORUM

Backup FileGroup and Restore on Secondary Database

Backup FileGroup and Restore on Secondary Database with same filegroups

FORUM
FORUM

mirrored backups

restoring from a mirrored backup

FORUM

After breaking mirror, the mirrored database is in restoring state

After breaking mirror, the mirrored database is in restoring state

FORUM

Backup/Restores for Database Mirroring Session

Cannot Restore Transaction Log after the Full Backup Restore

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones