locked
Can a database mirror be synchronous and asynchronous? RRS feed

  • Question

  • We are planning a mirror setup with a witness. We want to run synchronous between two local servers for protection and availability. However we are also thinking that it would be nice if in the future we could setup the only database we have to also run asynchronous and send transactions to a server at a different physical location. (More for disaster recovery, cold site). Obviously synchronous wouldn't be good for long distances across the net.. So the question is, can you create a local synchronous mirror between two servers and a witness and then also configure that database for asynchronous mirroring to an sql server at a different location?

    Thanks for your time!

    Friday, July 16, 2010 1:20 AM

Answers

  • Can't be done (yet).

    You can only have one partner with a mirror, so you'll have to use logshipping for your async mirror instead.

    • Proposed as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:24 AM
    • Marked as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:28 AM
    Friday, July 16, 2010 2:51 AM
  • Asyncronous operating mode is available in SQL Server 2005. But automatic failover is not supported via witness server. BUT It can be done by scripts by checking the Mirroring status (Connection Loss, etc.).

    http://technet.microsoft.com/en-us/library/cc917680.aspx

    To go for automatic failover without witness server,

    http://www.mssqltips.com/tip.asp?tip=1701.

    I have implemented the automatic failover in my office without witness server and its working fine.

    • Proposed as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:24 AM
    • Marked as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:28 AM
    Friday, July 16, 2010 12:05 PM

All replies

  • Can't be done (yet).

    You can only have one partner with a mirror, so you'll have to use logshipping for your async mirror instead.

    • Proposed as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:24 AM
    • Marked as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:28 AM
    Friday, July 16, 2010 2:51 AM
  • Asyncronous operating mode is available in SQL Server 2005. But automatic failover is not supported via witness server. BUT It can be done by scripts by checking the Mirroring status (Connection Loss, etc.).

    http://technet.microsoft.com/en-us/library/cc917680.aspx

    To go for automatic failover without witness server,

    http://www.mssqltips.com/tip.asp?tip=1701.

    I have implemented the automatic failover in my office without witness server and its working fine.

    • Proposed as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:24 AM
    • Marked as answer by Tom Li - MSFT Wednesday, July 21, 2010 1:28 AM
    Friday, July 16, 2010 12:05 PM