Home Forums SQL Server 2008 SQL Server 2008 Administration Manually failing over a mirror without access to Primary Server RE: Manually failing over a mirror without access to Primary Server

  • August 25, 2014 at 6:52 am

    #1740689

    SQLisAwE5OmE (8/23/2014)

    Hi Brandie,

    "I've created a job to initiate failover of the mirrors in case of database failure"

    Do you mind sharing how you setup the job?

    The job I set up is for when I have access to the Data Center and the primary server. It's very simple (with a lot of notes in the job's General tab).

    USE master;

    GO

    ALTER DATABASE DB1 SET PARTNER FAILOVER;

    GO

    ALTER DATABASE DB2 SET PARTNER FAILOVER;

    GO

    ALTER DATABASE DB3 SET PARTNER FAILOVER;

    GO

    ALTER DATABASE DB4 SET PARTNER FAILOVER;

    GO

    ALTER DATABASE DB5 SET PARTNER FAILOVER;

    GO

    Then I disabled the job so everyone would know that it shouldn't be run on a schedule and named it "DBA_VendorX_DR_MirrorFailover_Job".

    EDIT: I forgot to mention... This vendor uses a table to store path names for reports, files, server and instance names (YAY! Data driven stuff!). So on the DR (secondary) server, I have a job that updates all those keys to point to the DR locations once the mirrors are failed over.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.