Database Mirroring Automating Failover for Multiple SQL Server Databases With No Witness
March 6th 2009 04:59
Database Mirroring Automating Failover for Multiple SQL Server Databases With No Witness
----------------------------- ----------------------------- -------------
Problem
In a previous tip on Database Mirroring Automating Failover for Multiple SQL Server Databases, you've seen how you can failover all of the databases in a mirroring session if one database fails over. However, what happens if you don't have a witness server and your principal server goes, how do you make your mirror server the principal and bring all your databases online?
Solution
This solution extends the tip by reading the sys.databases and the sys.database_mirroring catalog views from the mirrored server and automatically (or manually, depending on your requirements) failover databases that are in a database mirroring pair. One case would be when you have a database mirroring pair in a high-protection operating mode without a witness requiring you to do a manual failover, whether planned or unplanned. You can use this script in the Failover Databases job as described in this tip or simply run it on the mirror database in case you decide to do a manual failover instead of an automated one.
This script uses the FORCE_SERVICE_ALLOW_DATA_LOSS option as in a real disaster, the primary database will be inaccessible. The process will recover the mirror database by finishing all completed transactions and rolling back unfinished ones. Since the old primary is inaccessible, some data may be lost because the synchronous data transfer may not have completed between the time the primary database went down and the transactions got committed on the mirror database.
--This script does a failover of all the databases in a database mirroring session
--to the Mirror server. This will be used in cases where the PRINCIPAL server is no longer available
--and the mirrored databases have to be brought online.
--NOTE: Run this script in the MIRRORED server instance
SET NOCOUNT OFF
DECLARE @strSQL NVARCHAR(200) --variable for dynamic SQL statement - variable size should change depending on the
DECLARE @strDatabasename NVARCHAR(50) --variable for destination directory
DECLARE MyCursor CURSOR FOR --used for cursor allocation
SELECT name FROM master.sys.databases a
INNER JOIN master.sys.database_mirroring b
ON a.database_id=b.database_id
WHERE NOT mirroring_guid IS NULL
AND mirroring_role_desc='MIRROR'
OPEN MyCursor
FETCH Next FROM MyCursor INTO @strDatabasename
WHILE @@Fetch_Status = 0
BEGIN
---Run the ALTER DATABASE databaseName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
SET @strSQL = 'ALTER DATABASE ' @strDatabaseName ' SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS'
EXEC sp_executesql @strSQL
PRINT 'Bringing ' @strDatabaseName ' ONLINE'
PRINT '============================ ============'
FETCH Next FROM MyCursor INTO @strDatabasename
END
CLOSE MyCursor
DEALLOCATE MyCursor
Once the databases are online, you can now redirect your applications to the new principal server.
----------------------------- ----------------------------- -------------
Problem
In a previous tip on Database Mirroring Automating Failover for Multiple SQL Server Databases, you've seen how you can failover all of the databases in a mirroring session if one database fails over. However, what happens if you don't have a witness server and your principal server goes, how do you make your mirror server the principal and bring all your databases online?
Solution
This solution extends the tip by reading the sys.databases and the sys.database_mirroring catalog views from the mirrored server and automatically (or manually, depending on your requirements) failover databases that are in a database mirroring pair. One case would be when you have a database mirroring pair in a high-protection operating mode without a witness requiring you to do a manual failover, whether planned or unplanned. You can use this script in the Failover Databases job as described in this tip or simply run it on the mirror database in case you decide to do a manual failover instead of an automated one.
This script uses the FORCE_SERVICE_ALLOW_DATA_LOSS option as in a real disaster, the primary database will be inaccessible. The process will recover the mirror database by finishing all completed transactions and rolling back unfinished ones. Since the old primary is inaccessible, some data may be lost because the synchronous data transfer may not have completed between the time the primary database went down and the transactions got committed on the mirror database.
--This script does a failover of all the databases in a database mirroring session
--and the mirrored databases have to be brought online.
--NOTE: Run this script in the MIRRORED server instance
SET NOCOUNT OFF
DECLARE @strSQL NVARCHAR(200) --variable for dynamic SQL statement - variable size should change depending on the
DECLARE @strDatabasename NVARCHAR(50) --variable for destination directory
DECLARE MyCursor CURSOR FOR --used for cursor allocation
SELECT name FROM master.sys.databases a
INNER JOIN master.sys.database_mirroring b
ON a.database_id=b.database_id
WHERE NOT mirroring_guid IS NULL
AND mirroring_role_desc='MIRROR'
OPEN MyCursor
FETCH Next FROM MyCursor INTO @strDatabasename
WHILE @@Fetch_Status = 0
BEGIN
---Run the ALTER DATABASE databaseName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
SET @strSQL = 'ALTER DATABASE ' @strDatabaseName ' SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS'
EXEC sp_executesql @strSQL
PRINT 'Bringing ' @strDatabaseName ' ONLINE'
PRINT '============================ ============'
FETCH Next FROM MyCursor INTO @strDatabasename
END
CLOSE MyCursor
DEALLOCATE MyCursor
Once the databases are online, you can now redirect your applications to the new principal server.
| 36 |
| Vote |
Add To: 
