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 secondary.
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 secondary for both DB1 and DB3? Or will I need a separate new secondary server (DB4) for DB3? Would this configuration be possible using separate instances on DB2?
Best 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:
But the question is What do you use mirroring for? Primarily it 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?
Yes, but is not necessary.