Saturday, April 21, 2012

Setup database mirroring and log shipping together

SQL Server has multiple high availability solutions. Comparing with cluster, database mirroring and logshipping are easier to setup, and they don't need special hardware.

here is a great article about how to setup the database mirroring and log shipping together.
http://technet.microsoft.com/en-us/library/ms187016.aspx

In our system, we setup data mirroring with high-safety mode in a fast network domain, and setup log shipping with the secondary server in remote data center, which provide off-site disaster-tolerant for our system.

All system diagram and setup steps are in the artive upper, but there is less comments about log shipping monitor setup. Based on my setup, if you want to keep log shipping monitoring as seamless as possible, you'd better use a standalone server as log shipping monitor server. By this way, after database mirroring failover from primary server to mirror server, log shipping monitor can still work.

However in my current system, when I setup log shipping after database mirroring setup complete, I use the current primary server as monitor server, which will cause problem after database mirroring failover.

because the secondary server only look current primary server as monitor server, so after restore job complete, it only update the log_shipping_monitor_secondary on primary, not on mirror. if primary server is down, and failover happen. the original mirror server will be primary server, however, it will not be the monitor server, and there is no log shipping monitor server any more.


When setup database mirroring and log shipping together, I recommend using a standalone server as witness server for database mirroring and monitor server for log shipping. This server can also be the file share server for transaction log backup file of log shipping. Or you can setup log shipping alert job on all primary, mirror and seconary server, it is also a simple way to solve the monitor problem.





4 comments:

  1. Hi James, what about using the the log shipping server as witness instead of primary instance?

    ReplyDelete
    Replies
    1. yes, you can, however, if secondary is down, or sql agent is stopped on secondary, you will not get alert.

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. hi james,Can u tell me specific no. of secondary servers we can add in logshipping with proof....

      Delete