locked
My structure contains mutli databases in one server, will that affect mirroring ? RRS feed

  • Question

  •  

    With the Synonym, I was encouraged to separate my db to several smaller dbs, like base,dynamic,static and security. Now I am trying to use mirroring, I see it may cause problem, I think I need mirror all them to another server. My question is when the server is down, will all db switch to mirror server in the same time? And one can manually set which db is the principal db, but in my case, it will not work if principal server of all four dbs are not the same.

     

    Any thought?

    thanks

    Wednesday, March 19, 2008 2:46 PM

All replies

  • Database mirroring is per database level. You can configure mirroring as shown below,
    Code Snippet

    Server A           Server B
     DB1                   DB1
     DB2                   DB2
     DB3                   DB3

    The database name should be the same for principal and mirror database as shown in the above example. Automatic failover is only available in High Availability mode of database mirroring where you need to have witness server to facilitate the same. Refer this White Paper which explains database mirroring in detail.

    - Deepak
     
    Wednesday, March 19, 2008 6:07 PM
  • If i understand your questions correctly

     

    "My question is when the server is down, will all db switch to mirror server in the same time?"

     

    If the mirroring for these databases are configured in Synchornous Mode with Witness , the witness server will take care of failover to the mirror server

     

    A witness can coexist with high-performance/Asynchrnous mode, but the witness provides no benefit and introduces risk , so its not a recommended configuration

     

    Other option is to configure without using Witness Server either in Async/Sync mode. so that you can control the failover.

     

    You could have database spread out on different servers and still have single mirror for all the principals , and control the fail over process manually

     

    HTH

     

    Vishal

    Monday, March 24, 2008 7:33 AM
  • Hope this helps

    Refer section Mirroring Multiple Databases

    http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx#EEMAE

     

     

    Monday, March 24, 2008 9:25 AM