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

Multiple Mirrors

Up through SQL Server 2008 R2 database mirroring has been limited to a single mirror for each database. While that does work well for many companies, as you become larger and more dependent on your computer systems, there is a need to have multiple mirrors.

Often someone wants to failover to another database on the same network for capacity reasons if there are minor issues. Moving all clients to a secondary data center is not something you always want to do, especially as the secondary data center might not include the same level of bandwidth and may cost more.


In SQL 11, there is work underway to implement HADR, high availability and disaster recovery, which is an enhanced database mirroring. In this new technology, multiple mirrors are possible, allowing you to have multiple up to date copies of your data in separate locations. These are now called “replicas” and you get up to 4, according to Brent Ozar (HADRON Rocks). I didn’t see that listed, but I’m sure Brent knows more about this stuff than I do.

The other nice things is that you can failover multiple databases together, which you put in an “availability group” and configure together. This is good since many applications might require multiple databases, and it could be important that all databases move to the same server at the same time.

Overall this is cool, and I am looking forward to this technology being deployed. Only one replica works in CTP1, so we can’t really see this working, but it could be an amazing edition to your DR strategy in the future. One note is that this is built on Windows clustering, which no longer requires shared storage, but might require Enterprise Edition, so that will limit how widely this is deployed.

I’ll write more on this later, but the ability to have multiple mirrors, and have multiple databases failover as a group, is something that DBAs should look forward to.

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 30 November 2010

I was really looking forward to playing with HADR with multiple replicas.  I can't wait for it to be fully functional.

Posted by Dukagjin Maloku on 30 November 2010

Yep, nice enhancements in the new incoming SQL Server version.

Posted by DBATAG on 4 December 2010


I just implemented HADR (Availability Groups) in my test virtual Setup.

I used the following Infrastructure To implement HADR / High Availability Group

1) Domain Controller for Authentication

2) Two Virtual Machines with Windows Server 2008 R2 with Failover Clustering installed on each Node.

3) Local Instance of SQL Server (Not Clustered Instance) installed on Virtual Machines with Windows Server 2008 R2 (which are mentioned in point 2)

More Details and Step by Step Guide is posted here


Suggest me some good scenarios to test this HADR (Availability Groups) now.

Posted by Hugo Shebbeare on 9 December 2010

Great post, cannot wait.

Leave a Comment

Please register or log in to leave a comment.