Subscribe to this blog
Briefcase
Print
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.
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"!