locked
MS SQL 2008 R2 Database Mirroring. RRS feed

  • Question

  • If you have a MS SQL 2008 R2 server with one MS SQL instance, with 4 different databases, can each of the 4 different databases be mirrored to another MS SQL 2008 R2 server, single SQL instance on my network?

    • Edited by Stephanie Lv Tuesday, June 21, 2011 12:32 AM edit
    Monday, June 20, 2011 6:06 PM

Answers

  • Hi Antoine,

    Yes, you can mirror multi databases to a standby server because Database mirroring is scoped at the database level.  It is also possible that one of them fails over to the standby server but the others remain on the original server. In that case, you might need one connection per database that you are querying, so that you do not attempt cross-database queries on a standby server where only one database is a principal and the remaining are mirrors.


    Best Regards,
    Stephanie Lv

    • Proposed as answer by PrinceLucifer Wednesday, June 22, 2011 4:44 AM
    • Marked as answer by WeiLin Qiao Tuesday, June 28, 2011 8:37 AM
    Tuesday, June 21, 2011 2:25 AM

All replies

  • There shouldn't be any problem in doing so as long as you run same version on both your SQL servers
    Thanks, Leks
    • Edited by Lekss Tuesday, June 21, 2011 8:18 AM
    • Proposed as answer by PrinceLucifer Wednesday, June 22, 2011 4:44 AM
    Monday, June 20, 2011 9:01 PM
  • Hi Antoine,

    Yes, you can mirror multi databases to a standby server because Database mirroring is scoped at the database level.  It is also possible that one of them fails over to the standby server but the others remain on the original server. In that case, you might need one connection per database that you are querying, so that you do not attempt cross-database queries on a standby server where only one database is a principal and the remaining are mirrors.


    Best Regards,
    Stephanie Lv

    • Proposed as answer by PrinceLucifer Wednesday, June 22, 2011 4:44 AM
    • Marked as answer by WeiLin Qiao Tuesday, June 28, 2011 8:37 AM
    Tuesday, June 21, 2011 2:25 AM
  • >>possible that one of them fails over to the standby server but the others >>>remain on the original server

    Yep, that may or may not cause a problem. If your application depends on more than one database and only one database got mirrored so,it could break  down the application and users start getting errors 

     


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, June 21, 2011 6:51 AM