Printed 2017/08/20 09:15PM

Just one mirror

By Steve Jones, 2010/04/19

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.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.