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
