SQL Server database mirroring based on availability group failover

SQL Server database mirroring based on availability group failover

Microsoft began to introduce database mirroring from SQL Server 2005 and quickly became a popular failover solution. A big problem with database mirroring is that failover is database-level, so if a database fails, mirroring will only switch to that database, but the other databases are still on the primary server. The disadvantage is that more and more applications are built on multiple databases, so if one database fails over and the other databases are still on the primary server, the application will not work. How do I know when this happens? And do all the databases that the application calls together fail over?

In all of the features of SQL Server, there is a way to be alerted when a database mirroring failure occurs or to check for events that occur. The event reminder for database mirroring is not as straightforward as you might think, but it can be implemented.

For database mirroring, you can choose to use trace events, or configure SQL Server alerts to check WMI (Windows Management instrumentation) events for changes to the database mirroring State.

Before we begin, we need some preparatory work:

The mirror database and the msdb database must have Service Broker enabled. You can use the following query to check:

SELECT name, Is_broker_enabledfrom sys.databases

If the value of Service Broker is not 1, you can use the following command for each database to turn on.

ALTER DATABASE msdb SET enable_broker

If SQL Server Agent is running, then this command will not be completed. You need to stop SQL Server Agent, run the above command, and then start SQL Server Agent again.

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

Create alarms

First, we create alarms, and unlike other alarms, we select the WMI event alert type.

Use SSMs to connect to the instance, expand SQL Server Agent, right-click on the alerts, and select "New Alert".

650) this.width=650; "title=" clip_image001 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image001" src= "Http://s3.51cto.com/wyfs02/M01/8E/A3/wKiom1jHlPrThJNDAAAf2S9mxqE042.png" height= "227"/>

Pop up the "New Alert" interface and select "WMI event Alert". Pay attention to the namespace of the query. By default, SQL Server chooses the correct namespace based on the instance you are working on.

650) this.width=650; "title=" clip_image003 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image003" src= "http://s3.51cto.com/wyfs02/M00/8E/A2/wKioL1jHlPuih0R4AAB6arTpFrw515.jpg" height= "227"/>

For query, use the following query:

SELECT * from Database_mirroring_state_change WHERE state = 7 OR state = 8

This data is obtained from WMI, which triggers a job or reminder when the database mirroring State becomes 7 (manual failover) or 8 (automatic failover).

In addition, you can further define the query for each specific database:

SELECT * from Database_mirroring_state_change WHERE state = 8 and DatabaseName = ' Test '

You can read the contents of Database_mirroring_state_change in the online Help.

The following is a list of the different state changes that can be monitored. More content can be found in the 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

• Ten = No Quorum

• one = Synchronizing Mirror

• Principal Running Exposed

• = Synchronizing Principal

In the response interface, you can configure how to handle events when they occur. You can configure a job to be executed when the alarm is triggered, or send a reminder to the operator.

650) this.width=650; "title=" clip_image005 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image005" src= "http://s3.51cto.com/wyfs02/M01/8E/A2/wKioL1jHlPzS62NsAABoTkK81x4539.jpg" height= "194"/>

Finally, you can configure the additional options as shown below.

650) this.width=650; "title=" clip_image007 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image007" src= "http://s3.51cto.com/wyfs02/M02/8E/A2/wKioL1jHlP2iGB3mAABnxx7AjAk157.jpg" height= "234"/>

Configuration examples

For example, if an application has a call to 3 databases (Customer, orders, and log), if one of the databases is automatically switched, you also want to fail over both two databases. Additionally, this image is configured with a witness server that automatically fails over if a failure occurs.

The following shows how to configure.

First, we configure alarms only for these 3 databases.

650) this.width=650; "title=" clip_image009 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "clip_image009" src= "http://s3.51cto.com/wyfs02/M02/8E/A3/wKiom1jHlP6Sq7qkAACKxopoKks836.jpg" height= "226"/>

Then configure which job to run after the alarm is triggered.

650) this.width=650; "title=" clip_image011 "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clip_ image011 "src=" http://s3.51cto.com/wyfs02/M00/8E/A2/wKioL1jHlP_BwEEQAABoPQrka0w830.jpg "height=" 193 "/>

We need to create a "Failover Databases" job to run when the alarm is triggered.

For SQL Server Agent "Failover Databases" jobs, the job steps are as follows:

IF EXISTS (SELECT 1 from sys.database_mirroring WHERE db_name (database_id) = N ' Customer ' and Mirroring_role_desc = ' PRINCI PAL ') ALTER DATABASE Customer SET PARTNER failovergoif EXISTS (SELECT 1 from sys.database_mirroring WHERE db_name (database_  ID) = N ' orders ' and Mirroring_role_desc = ' PRINCIPAL ') ALTER DATABASE Orders SET PARTNER failovergoif EXISTS (SELECT 1 from sys.database_mirroring WHERE db_name (database_id) = N ' Log ' and Mirroring_role_desc = ' PRINCIPAL ') ALTER database Log SET P Artner Failovergo

The ALTER DATABASE command above forces the failover of other databases that do not have automatic transfers. This is the same as when you click "Failover" on the GUI interface.

Reference:

Https://msdn.microsoft.com/en-us/library/ms191502.aspx

Https://msdn.microsoft.com/en-us/library/ms186449.aspx

This article is from the SQL Server deep Dive blog, so be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1906335

SQL Server database mirroring based on availability group failover