Post reply

Mirroring Failover if Primary Database Server Dies

  • March 2, 2009 at 11:07 pm

    #131482

    I have 22 mirrored databases setup with "high safety without automatic failover."

    How do I get the mirrored databases online in the event of total primary server failure?

    Thanks

  • March 3, 2009 at 12:30 am

    #952242

    The main thing you need to know is to execute the following from the mirror server when this happens...

    ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

    Since that is a royal PITA when you have more than a few databases configured for DBM, you could use something like the following to help make the process a little simpler... this should work, but use at your own risk.

    DECLARE @dbm_dbname nvarchar(128)

    DECLARE @dsql nvarchar(250)

    DECLARE c_DB CURSOR FOR

    SELECT DB_NAME(database_id) FROM master.sys.databasemirroring WHERE mirroring_role_desc='MIRROR'

    OPEN c_DB FETCH NEXT FROM c_DB INTO @dbm_dbname

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @dsql = 'ALTER DATABASE ' + @dbm_dbname + ' SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS'

    EXEC(@dsql)

    FETCH NEXT FROM c_DB INTO @dbm_dbname

    END

    The following article explains the whole process for manual failover in high-safety mode without a witness server:

    SQL 2008 - http://technet.microsoft.com/en-us/library/ms189977.aspx

    SQL 2005 - http://technet.microsoft.com/en-us/library/ms189977(SQL.90).aspx

    [edit]Used mirroring_state_desc instead of mirroring_role_desc accidentally. Corrected now.[/edit]

  • March 3, 2009 at 10:16 am

    #952563

    Thanks. That looks like it will do the trick.

Viewing 3 posts - 1 through 2 (of 2 total)

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