SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Just one mirror

You can only have one mirror of a SQL Server database in SQL Server 2005/2008. I researched that last year when someone asked the question. It didn’t make sense to me why you couldn’t have more than one, and I struggled to find documentation, but finally Paul Randal pointed me to a reference.

And recently in his myth a day series, he posted this as a note in there as well. His recommendation, which is one that I’ve used as well, is include log shipping as a secondary way of moving data, and introduce a delay.

Since mirroring sends transactions from the log to be replayed on another machine, why couldn’t you have two or more mirrors? I don’t think there’s a theoretical reason, though if you were in high safety mode, you might cause delays on your principle with the two phase commit.

However for now, each database can have at most one mirror. I wonder if that will change (I hope so) in SQL 11.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Posted by Jason Brimhall on 19 April 2010

I too hope for this to be changed.  I remember seeing a presentation where MS presented Mirroring and showed several downlevel mirror partners for a single database.  We know that it can't but MS must be working to that end.

Posted by Steve Jones on 19 April 2010

I'm hoping they are. I think this is a great feature, and it could be nice to have at least two. One onsite, one off.

Posted by Dukagjin Maloku on 19 April 2010

Yep I agree with you Steve "...at least two"!

Leave a Comment

Please register or log in to leave a comment.