Shulou(Shulou.com)06/01 Report--

SQL Server database mirroring based on availability group failover

Microsoft introduced database mirroring from SQL Server 2005 and quickly became a popular failover solution. A big problem with database mirroring is that failover is based on the database level, so if a database fails, mirroring will only switch over for 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 perform a failover of all the databases called by the application?

Of all the features of SQL Server, there is a way to be alerted or checked for events in the event of a database mirroring failure. The event reminder for database mirroring is not as straightforward as you might think, but it does.

For database mirroring, you can choose to use trace events or configure SQL Server alarms to check for WMI (Windows Management Instrumentation) events that change the state of database mirroring.

Before we begin, we need some preparatory work:

Service broker must be enabled for mirror databases and msdb databases. You can check using the following query:

SELECT name, is_broker_enabledFROM sys.databases

If the value of service broker is not 1, you can turn it on for each database using the following command.

ALTER DATABASE msdb SET ENABLE_BROKER

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

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

Create an alarm

First, let's create an alarm, and unlike other alerts, we choose the "WMI event alert" type.

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

Pop up the "New Alert" interface and select "WMI event alert". You need to pay attention to the Namespace of the query. By default, SQL Server will choose the correct namespace based on the instance you are working on.

For Query, use the following query:

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

This data is obtained from WMI, and a job or reminder is triggered when the database mirroring state changes to 7 (manual failover) or 8 (automatic failover).

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

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

You can read DATABASE_MIRRORING_STATE_CHANGE in the online help below.

The following is a list of different state changes that can be monitored. For more information, you can find it at 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

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

Finally, you can configure additional options as shown below.

Configuration example

For example, an application calls three databases (Customer, Orders, and Log), and if one of the databases switches automatically, you also want two other databases to fail over as well. In addition, the image is configured with a witness server that automatically fails over in the event of a failure.

The following shows how to configure.

First of all, we only configure alerts for these three databases.

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

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

For the "Failover Databases" job of the SQL Server agent, the job steps are as follows:

IF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name (database_id) = Noble customer 'AND mirroring_role_desc =' PRINCIPAL') ALTER DATABASE Customer SET PARTNER FAILOVERGOIF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name (database_id) = Noble orders' AND mirroring_role_desc = 'PRINCIPAL') ALTER DATABASE Orders SET PARTNER FAILOVERGOIF EXISTS (SELECT 1 FROM sys.database_mirroring WHERE db_name (database_id) = Noble Log' AND mirroring_role_desc =' PRINCIPAL') ALTER DATABASE Log SET PARTNER FAILOVERGO

The above ALTER DATABASE command forces failover for other databases that do not have automatic transfer. This is the same as clicking "Failover" on the GUI interface.

Reference:

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

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.