Database Mirroring Automating Failover for Multiple SQL Server Databases

By:   |   Updated: 2008-08-18   |   Comments (24)   |   Related: > Database Mirroring


Problem

Database Mirroring was released with SQL Server 2005 and is becoming a popular choice for a failover solution.  One of the big issues with Database Mirroring is that the failover is tied to one database, so if there is a database failure the mirrored server takes over for this one database, but all other databases remain functional on the primary server.  The drawback is that more and more applications are being built where multiple databases make up the application, so if only one of the databases fails over the other database will still be functional on the principal server, but the application won't work.  How can I be notified when this happens and make all of the databases failover?

Solution

As with just about all functions in SQL Server, there is a way to get alerts or check for events that occur when a database mirroring failure occurs.  Unfortunately this event notification for Database Mirroring is not as straight forward as you would think, but it can be done.

For database mirroring you have the choice of using trace events or you can setup a SQL Server Alert to check for a WMI (Windows Management Instrumentation) event for Database Mirroring state changes.  Before we get started there are a few things you need to do:

Preliminary Steps

The mirrored databases and the msdb database must have service broker enabled.  This can be checked with this query

SELECT name, is_broker_enabled FROM sys.databases

If the service broker value is not set to 1 you can turn this on by issuing this command for each database.

ALTER DATABASE msdb SET ENABLE_BROKER

If SQL Server Agent is running this command will not complete, so you will need to stop SQL Server Agent, run the above command and then start SQL Server Agent again.

Lastly, if SQL Server Agent is not running you will need to start it.

Creating the Alert

To setup the alert we are going to do this just like any other alert, but we are going to be using the "WMI event alert" type. 

To create the alert expand the SQL Server Agent tree and right click Alerts and select "New Alert".

new alert

On the New Alert screen we are going to change the type to "WMI event alert". Another thing that you will notice is the Namespace that is being queried.; By default SQL Server will select the correct namespace based on the instance of SQL Server you are working with.

new alert

For the query we are using the following:

SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 OR State = 8

This data is read from WMI and whenever the database mirroring state changes to either 7 (manual failover) or 8 (automatic failover) the job or notification will be fired.

In addition, you can further define the query for a particular database such as:

SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 8 AND DatabaseName = 'Test'

For additional information you can read about DATABASE_MIRRORING_STATE_CHANGE in books online.

Below is a list of the different state changes that can be monitored. Additional information can be found here Database Mirroring State Change Event Class.

  • 0 = Null Notification
  • 1 = Synchronized Principal with Witness
  • 2 = Synchronized Principal without Witness
  • 3 = Synchronized Mirror with Witness
  • 4 = Synchronized Mirror without Witness
  • 5 = Connection with Principal Lost
  • 6 = Connection with Mirror Lost
  • 7 = Manual Failover
  • 8 = Automatic Failover
  • 9 = Mirroring Suspended
  • 10 = No Quorum
  • 11 = Synchronizing Mirror
  • 12 = Principal Running Exposed
  • 13 = Synchronizing Principal

On the Response screen we can setup how to handle this event when it occurs.  You can either setup a job that runs when the Alert is fired and/or a notification to an operator that has been setup.

response

Lastly, you can setup additional options for the alert as shown below.

options

Example Setup

So let's say you have three databases (Customer, Orders, Log) that are part of an application and if one of the databases automatically fails over you want the other two databases to failover as well.  In addition, this mirroring setup includes a Witness server so if a failure occurs the failover is automatic.

Here is how this could be setup.

First we setup the Alert to look at just these three databases.

failover databases

Then we tell the alert which job to run.

new alert

Then we need to create the job "Failover Databases" which will be run when this alert is fired.

For the SQL Agent job "Failover Databases" the jobs steps would look something like the following:

IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name(database_id) = N'Customer' AND mirroring_role_desc = 'PRINCIPAL')
     ALTER DATABASE Customer SET PARTNER FAILOVER
GO

IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name(database_id) = N'Orders' AND mirroring_role_desc = 'PRINCIPAL')
     ALTER DATABASE Orders SET PARTNER FAILOVER
GO

IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name(database_id) = N'Log' AND mirroring_role_desc = 'PRINCIPAL')
     ALTER DATABASE Log SET PARTNER FAILOVER
GO

The ALTER DATABASE command above forces the failover to the mirrored server for the other databases that did not automatically get flipped.  This is the same as if you clicked on the "Failover" button in the GUI.

Next Steps
  • Now that you have an idea how you can use the WMI events to check for mirror state changes do some testing on your servers to see if this is something you could use.
  • Each time a database fails over it will kick off the alert, so if there are multiple failovers it will attempt to run the "Failover Databases" job multiple times.
  • You also would need to think about how you would want this to run.  You may want an automated failover from the primary server to the secondary server, but you may only want to be notified if the failure goes from the secondary to the primary server.
  • Although this may not be an ideal solution, it does allow you to get all of your databases on the same server.  Since database mirroring is still at the database level there is no guarantee that all or your transactions will stay in synch across multiple databases whether you use this approach or not.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

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

View all my tips


Article Last Updated: 2008-08-18

Comments For This Article




Thursday, August 6, 2015 - 2:23:55 PM - Steve Back To Top (38397)

  There is no need to involve activex, powershell, or anything other than sql server.

  Link the servers to eachother.

  Make a job on both servers to failover all dbs (so you can fail in either direction).

  Make two more jobs...

       one catches state 7 and does msdb.dbo.sp_start_job @job_name = 'Failover All Mirrors' which will run on the local server (manual/local failover)

       one catches state 8 and does msdb.dbo.sp_start_job @job_name = 'Failover All Mirrors',  @server_name = 'DB14-1' which will run the failover from the other server.

 

 

 

 

Wednesday, December 17, 2014 - 1:45:48 PM - jimbobmcgee Back To Top (35643)
I've been using this technique for a while now but realised recently that it does not actually work in any automatic failover scenario. This is because `ALTER DATABASE foo SET PARTNER FAILOVER` can only be run from the PRINCIPAL server, while 'Automatic Failover' alerts are only fired by the MIRROR server (after the mirror server has accepted the responsibility). When failover is manually performed, there is no problem, because the 'Manual Failover' alert fires from the original principal (after it has stopped being responsible for the database), so the job runs and all the steps that can failover will failover. In auto-failover cases, the job runs but the steps will raise error 1470 ("The alter database for this partner config values may only be initiated on the current principal server for database"). If the auto-failover occurs because the entire instance is lost (e.g. stopped service, server shutdown, network outage), you probably will not notice, because the mirror server will already assume responsibility for all databases. However, in auto-failover cases where only one of the databases is lost, but the server remains (e.g. a storage volume dedicated to that database dies), the 'group' will remain split across both servers. In order for this approach to work, there need to be two jobs. One handles the manual failover event, and is as per the article. The other handles the auto-failover event and runs an ActiveX script, PowerShell script or Operating System command that tries to connect to the master database of the remote instance (using ADO or ADO.NET) and issues the `ALTER DATABASE` command from there. This will also require that the account running the local SQL Agent will need suitable administrative permissions to the remote instance, or that the second job runs under a proxy account that has these permissions.

Tuesday, November 25, 2014 - 8:57:15 AM - Greg Robidoux Back To Top (35396)

Hi Mike,

it looks like you need to enable Service Broker before setting up the mirror.  From what I read it looks like you have to break the mirror, enable Service Broker and then setup the mirror again.

Wednesday, November 5, 2014 - 3:33:44 PM - Mike Back To Top (35192)

 

As no one else mentioned it, I get the following:

 

 Msg 9778, Level 16, State 1, Line 1

 

Cannot create a new Service Broker in a mirrored database "xxx".

 

Msg 5069, Level 16, State 1, Line 1

 

ALTER DATABASE statement failed.

Monday, January 27, 2014 - 8:29:36 AM - Kevin Back To Top (29231)

Ok, thank you Paul-Andre for these explanations!

Actually, I am practicing on SQL Server high availability solutions, and the "crash simulations" I made with mirrored databases are at server or network level. I did not know how to simulate a database level crash; even if a crash disk occur with database files on it, the database connection is still up and so the failover does not occur.

Anyway, the mirroring solution is not recommend by Microsoft as it will not be implemented in the next versions of SQL Server. I am trying the AlwaysOn Availability Group solution now.

Thanks again for your help.

Friday, January 24, 2014 - 8:35:15 PM - Paul-Andre Panon Back To Top (28223)
Kevin: "Could you please describe to me cases where only one database failover will occur and not the others?"

Separate monitoring packets are exchanged for each mirrored database between the engines running primary, mirror and witness roles for that database. If a monitoring packet gets dropped between the primary and the witness, then the witness will treat it as a time-out and trigger a fail-over for the database associated with that packet.

A packet could get dropped for many reasons, but the most likely is a saturated switch/router backbone or interface, or a full virtual switch queue on a virtualization host (if one or more of the database servers are running virtualized). The best way to avoid timeouts from the former is standard network monitoring and capacity planning activities to make sure the network infrastructure can handle the load (although that won't help you if you're subject to a DOS attack or some other uncharacteristically heavy network I/O spike). The best way to avoid timeouts from the latter is to create individual resource reservations for each database server to ensure they have enough resources to be able to respond to incoming network data in a timely manner.

Also make sure that, if you are doing network-based backups, that they don't swamp the interface handling SQL network I/O. One typical way is to use a separate NIC, but it's even better if you have also provide some sort of separation/resource guarantee on the network intermediate systems as well.

Friday, January 17, 2014 - 3:05:46 PM - Paul-Andre Panon Back To Top (28127)

Once more with formatting.

I have to agree with Adrian Sims. As far as I can tell, this would only ever work correctly when performing a test by triggering a manual failover of one of the databases. It should not work in the case of an actual automatic failover. This is because the WMI event with state 7 (manual failover) happens on the server hosting the primary database, whereas WMI events with state 8 (automatic failover) are created on the mirror (See the descriptions of the states in < href="http://technet.microsoft.com/en-us/library/cc966392.aspx">http://technet.microsoft.com/en-us/library/cc966392.aspx). The SET PARTNER FAILOVER commands only work on the primary, so when a manual test is performed, the failover task will work on the primary.

However on an automatic failover, the failover task will run on the mirror and do (almost always) nothing. The only way to accurately test this setup is to force an automatic failover by placing a firewall between the primary and witness, and use it to temporarily block heartbeat packets between the primary and witness thereby causing a timeout on the witness.

Faking/having a witness timeout might seem to work if you weren't paying attention to which server was supposed to have all entries PRIMARY, and only checked whether your application(s) work(s). If it took long enough for the WMI alert on the "mirror" server to get triggered and start the failover task, so that the (lightly loaded) single database had managed to completely fail over to the second server, then the failover task might fail that single database back to the server running the other databases in the Primary role. In a loaded production environment however, any queued transactions requiring mirror synchronization would probably delay completion of the switch long enough that the database wouldn't be in the right role to allow the failover task to make it fall back.


Friday, January 17, 2014 - 5:01:49 AM - Kevin Back To Top (28111)

Hello,

Could you please describe me cases where only one database failover will occur and not the others?

Assuming I have 3 servers : 1 principal with 3 mirrored databases, 1 mirror and 1 witness. An automatic failover will occur if the principal lose its connexion with mirror and witness, and so all the 3 databases will failover.

I am wondering in which case only one of the databases could automatically failover ?

Are we agree that only a connexion loss between principal and mirror+witness can cause an automatic failover ?

Thank you per advance.

Wednesday, March 6, 2013 - 5:55:59 PM - Brian Jacobs Back To Top (22610)

Thank you Greg for the reply. That seems to have cleared the issue.  I have just one more question:  Is there a way to customize the e-mail alert message you receive to show the database name?  Mine looks like this:  SQL Server Alert System: 'Database Mirroring Check - Auto Failover' occurred on \\ ...and I would like to show the database name at the end.  Thank you again for your expertise.

  

Wednesday, March 6, 2013 - 8:30:20 AM - Greg Robidoux Back To Top (22590)

Sorry, here is the link: http://social.technet.microsoft.com/Forums/en-SG/operationsmanagergeneral/thread/0f2c24a8-2252-4219-acd7-a526e1935f1f

Wednesday, March 6, 2013 - 8:29:51 AM - Greg Robidoux Back To Top (22589)

Hi Brian, to access the WMI information the account being used would need to have permissions to read the WMI data. 

See if this post helps with your permission issue.

Tuesday, March 5, 2013 - 7:36:13 PM - Brian Jacobs Back To Top (22580)

My apologies, I left the error code off in my previous post:

Cannot create the event notification 'SQLWEP_9F541C30_EF29_4B93_B9B3_21C09E57D610', because you do not have permission.
SQLErrorInfo: 42000
HResult: 0x80040e14
Source: Microsoft SQL Server Native Client 10.0

Thank you again.

Tuesday, March 5, 2013 - 7:28:50 PM - Brian Jacobs Back To Top (22578)

Greg, I am getting a permissions error when trying to create an alert for the recommended database failover job.  I have read that you need to assign permissions to the WMI but to which user - the SQL Agent?  Thank you for your answer in advance.

Friday, March 1, 2013 - 1:37:19 PM - Greg Robidoux Back To Top (22514)

@Patel - 100 databases will be too many to use database mirroring. 

Your best bet would be to use log shipping for that many databases.  Database Mirroring will put too much overhead on your servers.

Friday, March 1, 2013 - 12:05:24 PM - Patel Back To Top (22509)

Greg, please help me on Database Mirroring and Log shipping. How many database we can Configure Mirroing or log shipping per instance we having around 100 + databases on one instance and we are runnig two instance on One server in Prod.

 

Thanks In advance ......

Wednesday, November 14, 2012 - 1:05:11 PM - Hector Back To Top (20338)

I've got this job setup for about 12 databases,  the job triggers fine but roughly only 9 of the DBs move accross,  the other 3 DBs don't make it accross.  I've double checked and they all have service broker enabled.  It's random, it could be any 3 dbs that don't make it.  Some DBs even go into suspend mode.  Could the job triggering 12 times be too much?

Tuesday, August 28, 2012 - 10:45:28 AM - Lee Back To Top (19254)

I have created this alert but when I get the email there is no information included about what database failed over.  Is there anyway to see that in the email without having to have the alert trigger a job?

Tuesday, July 28, 2009 - 8:45:54 AM - AdrianSims Back To Top (3790)

This without doubt is a very good article, and I used this to good effect for some time.

Despite having automatical failover work for this period will no problems. To date the majority of failovers have been due to network glitches .

However the other week, I encountered a problem when the WMI Alert did NOT fire on an automatic failover. I am confused Why it worked in the past !!!!

From http://msdn.microsoft.com/en-us/library/cc966392.aspx 

7

Manual Failover

This state occurs on the principal server instance when the user fails over manually from the principal, or on the mirror server instance when a force service is executed at the mirror.

 

8

Automatic Failover

This state occurs on the mirror server instance when the operating mode is high safety with automatic failover (synchronous) and the mirror and witness server instances cannot connect to the principal server instance.

 

 

From http://msdn.microsoft.com/en-us/library/ms366349(SQL.90).aspx

On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session.

We had grown to over 40 DB's without problems ( they are mainly read only DB's ), although the -g startup switch solved the problems  

 

Adrian

Monday, March 16, 2009 - 3:39:24 PM - Dinga Back To Top (3018)

Thanks, this is a great article.  I am trying to just get notfications but they are not coming through.  Service broker is enabled and I know the operator is set correctly and works.  I cannot query the wmi directly using scriptomatic.  Do the databases need to be in a particular compatibility mode as mine need to be set to 80.  Looking at the history of the job it is incrementing when I fail over the databses.  Any help is appreciated

Sunday, September 14, 2008 - 2:22:29 PM - Imran Mohammed Back To Top (1811)

Man... You are too good...

 I feel like I am doing clustering. This is really nice.

Monday, September 8, 2008 - 9:50:47 AM - ASiddiqui Back To Top (1767)

I got it working. Had a problem with the query, can not all TSQL commands.

<<<

I had a question regarding object DATABASE_MIRRORING_STATE_CHANGE. I have broken enabled on msdb, along with the mirrored dbs.

But I can not query the above object. I assume I am missing a setting, but reading through the article, it does not specify any thing additional to broker.

>>>>

Wednesday, August 20, 2008 - 8:59:20 AM - yaarrabba Back To Top (1668)

Makes sense. Thanks a lot for clarification and this nice article.

Wednesday, August 20, 2008 - 7:05:31 AM - grobido Back To Top (1667)

For Automatic failover to occur you need to have a Witness Server.

In order to use the WMI events you have to have Service Broker enabled on the msdb database regardless if you have a Witness Server or not.

If you do not have a Witness Server you could still take advantage of this process, so if you do a manual failover, the event will fire and failover all of your other databases as well.

 

Wednesday, August 20, 2008 - 7:02:04 AM - yaarrabba Back To Top (1666)

Good article. You didn't mention about the role of witness server. Are you saying that witness server is not needed if service brooker is enabled?

 Thanks, 














get free sql tips
agree to terms