Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Scenario is that I have two servers running SQL Server 2008 R2 (64-bit), let's call them DB1 and DB2. I have set up database mirroring for all the databases on these servers so that DB1 is the principal and DB2 is the slave.

Now the question is, can I set up database mirroring on a new server DB3 running it as principal and have DB2 work as a slave for both DB1 and DB3? Or will I need a separate new slave server (DB4) for DB3? Would this configuration be possible using separate instances on DB2?

share|improve this question
add comment

migrated from stackoverflow.com Mar 18 '13 at 21:00

This question came from our site for professional and enthusiast programmers.

1 Answer

A SQL Server instance can host multiple mirroring partnerships. Mirroring is between databases and databases are hosted on SQL Server instances, so choosing a name like 'DBx' for servers is a rather poor choice. Lets call the instances SRV1, SRV2 and SRV3 and the databases DB1 and DB2. What is possible is:

  • to have the database DB1 mirrored by SRV1 (primary) and SRV2 (secondary)
  • to have the database DB2 mirrored by SRV3 (primary) and SRV2 (secondary)

But the question is Why do you use mirroring for?. Primarily is a High Availability feature. The question is: will the SRV2 instance be able to take over the primary role for both DB1 and DB2 in case of failover? Nothing prevents both SRV1 and SRV3 from suffering a problem at the same time. Also, if SRV2 suffers a disaster, you will have to come up with a backup plan to restore the HA for both DB1 and DB2. Are you prepared to handle all these cases?

Would this configuration be possible using separate instances on DB2?

Yes, but is not necessary.

share|improve this answer
    
I tried it out and it works fine, thanks for your answer. Of course SRV2 needs to be able to handle all databases it mirrors, I think the biggest problem would be that it would run out of working threads if the amount of databases mirrored is big (150+). –  user57792 Mar 26 '13 at 13:59
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.