Automate SQL Server Database Mirroring Failover for Multiple Databases With No Witness

By:   |   Comments (8)   |   Related: > Database Mirroring


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.

Next Steps
  • Review your disaster recovery process and include this automation procedure as part of your plan
  • Take a look at other database mirroring tips


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, May 13, 2013 - 2:45:10 PM - DNg Back To Top (23926)

Great tip from both Edwin and Robert!

One thing I'd like to point out with Robert's comment though is that 

And mirroring_state = 2 -- Disconnected

Should be mirroring_state = 1 (1 is Disconnected / 2 is Synchronizing)

Thursday, February 28, 2013 - 5:46:57 PM - bass_player Back To Top (22493)

Hi Camille,

Not much, really. However, it is important to know what the script is doing and the side effects it has when you run it. Since it uses the FORCE_SERVICE_ALLOW_DATA_LOSS option, you risk losing transactions on the principal database when you run this script. The assumption here is to run this script only when you encounter a disaster and there is no way to recover your principal database. Request assistance from your DBA to simulate running this script in a test environment and see the potential data loss that may be incurred as a side effect of running this

Thursday, February 28, 2013 - 3:02:22 PM - Camille Landry Back To Top (22488)

Question (from a non-DBA):  How much tweaking would this script require in order to keep it updated after patches, server reconfigurations, etc.?

 

Thanks!

Thursday, July 19, 2012 - 6:33:30 PM - Robert L Davis Back To Top (18671)

Yes, you do say that. I see that at the top now. My apologies for jumping to conclusions.

I do see some potential issues with the script.

  1. Database names van be up to 128 characters nvarchar so using an nvarchar(50) variable could cause issues.
  2. When building the string to execute, you should use quotename() or hard-coded square brackets around @strDatabaseName to ensure that a nonstandard name does not cause the script to fail.
  3. Also, I think we should consider that a dedicated mirror may host mirrors of databases from more than just a single principal. We don't want to force service on databases in good standing, You should verify that the principal is actually down. At the very least, verify that the mirror is disconnected or do a quick query against the principal across a linked server.

This doesn't have any potential for problems, but just perseverating to get the most efficient code possible:

  1. You don't need to join sys.database_mirroring to sys.databases. Use the DB_NAME() function on the database_id column to get the database name.
  2. The mirroring_guid column cannot be null and the mirroring_role_desc column return a value. You don't need to also check mirroring_guid.
  3. If you are going to use a cursor, at least use an optimized cursor (i.e., LOCAL STATIC FORWARD_ONLY READ_ONLY cursor)

This is the way I'd write it:

DECLARE MyCursor CURSOR 
 LOCAL STATIC FORWARD_ONLY READ_ONLY 
 FOR --used for cursor allocation 
 SELECT DB_NAME(database_id)
 FROM sys.database_mirroring
 WHERE mirroring_role = 2 -- Mirror
 And mirroring_state = 2 -- Disconnected
 And mirroring_witness_state Is Null -- no witness

 

Thursday, July 19, 2012 - 6:14:54 PM - bass_player Back To Top (18670)

Imagine the case where your primary server is down and you have to rebuild it. Once that's back online, you need to re-configure the database mirroring from the new primary to the old primary so you can swithch it back to it's original configuration. Now, even if you didn't have to rebuild the original primary server, you would still have to restore a backup of the new primary to the old primary because  after running this script, the databases on both instances are now online. In a database mirroring session, one database is online while the partner is in a constant recovery mode

Thursday, July 19, 2012 - 4:26:38 PM - klavan Back To Top (18667)

So the original Primary server is back on line.  How do you fail back?  Thanks.

Monday, March 23, 2009 - 4:41:19 PM - bass_player Back To Top (3062)

I totally agree with you on this, Robert. This solution is not for everybody and as was mentioned in the tip, it is for cases where you don't have a witness and your principal server goes down. This tip also highlighted the risks of doing this as there will definitely some data loss in the process. It's a "quick-and-dirty" way to do a failover when a real disaster strikes. I've had customers who actually requested for this solution because of their constraints and I have also explained the risks involved. But thanks for highlighting this so that the readers would understand the context on how to use it

Monday, March 16, 2009 - 9:07:25 AM - RobertLDavis Back To Top (3016)

This is a bad suggestion, in my opinion. If you simply need to set up a process to fail over all of the databases, a much better approach would be to first set safety to Full, allow the databses to get in sync, and then issue the partner failover command. Forcing service should only be done if this fails.

Personally, I don't think forcing service should be done automatically unless you are trully not worried about data loss.














get free sql tips
agree to terms