Post reply

Database Mirroring with multiple databases

  • January 30, 2007 at 7:29 am

    #194118

    Hello

    I am looking into database mirroring but I am a little confused about how this works if you have a server with multiple databases on it which are all mirrored to another server.

    If one database goes down on the primary server will the mirror server take over for every database or just the one that went down?  I am hoping that the roles will change just for the database in question not for every database.

    Any help would be appreciated.

    Regards

    Graham Macfarlane

  • January 30, 2007 at 8:45 am

    #686473

    Microsoft have a length yet friendly article on the topic as well as a FAQ.

    See

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

    http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirfaq.mspx

    Neither of the above are the article that I remember reading back when mirroring came out but they do an adequate job of explaining how the mirroring works in straightforward terms.

    Cheers

  • January 30, 2007 at 8:46 am

    #686475

    Found it

    http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

  • January 30, 2007 at 10:36 am

    #686531

    Thanks, very helpful. Still can't see anything on what triggers a failover and if one database on a server fails, will the whole server fail over (all databases).

  • January 31, 2007 at 1:30 am

    #686744

    It seems that if you have multiple database on an instance, each database can be mirrored (even to different servers if desired).  It is possible for one database to failover and another to stay on the principal server.  Apparently, this can cause problems if you have an application that uses 2 database and one fails over but the other does not.

    Still not totally sure exactly what events would trigger a failover, but I will experiment and see.  I wonder if a database has several blocking locks would this trigger a failover?

    Thanks again for the article links.

  • January 31, 2007 at 12:16 pm

    #686932

    The databases are essentially separate regarding mirroring and fail-over.  There could be many conditions triggering a fail-over of one or more of the primary databases.  It also depends on whether the mirrored databases have a witness. 

    As mentioned in one of the above responses, it can be undesirable to have a single database fail-over to the alternate server/mirrored database if an application is addressing addtional databases on the primary server.  For that reason, we have written jobs which cause a cascade of all the production databases to fail-over whenever one of them does.  This has worked very well for us towards keeping the business application pointed to a single server in the event of a failure.

    Elliott

  • February 5, 2007 at 9:35 am

    #687815

    Hi there, is it possible for you to give an example of the jobs you have written - much appreciated.

  • September 8, 2011 at 9:38 am

    #1378665

    Also interested in the job to cascade the failovers is possible

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply