Automatic mirror failover setting in multi-database system

Problem

With the release of SQL Server 2005, Database mirroring technology has become a very popular way to solve fault problems. A big problem with database mirroring, however, is that failover (failover) only works on the failed main library, and when a database fails, the standby database in the system takes its place immediately, but all other databases remain on the original server. The disadvantage of this technology is that in multiple database systems will continue to build more and more applications, once the main library is invalidated, while the repository can continue to play a role on the original server, but the application based on the failure of the main library will be invalidated. When this happens, how can we be notified and immediately failover all the databases?

Expert answers

As with all features of SQL Server, when database mirroring fails, there is a way to get a warning and detect what's happening. Unfortunately, this event alert mechanism for mirroring is not as straightforward as you might think, but it can still be implemented.

For database mirroring, you can choose to use the event tracking feature or set up SQL Server alert (Alert Manager) to detect WMI (Windows Management instrumentation) that occurs when the database mirroring state value changes. is the Windows Management Specification) event. Before we begin, there are a few things you need to do:

Preparation steps

You must first start the Service Broker feature for the standby and msdb (directory management) databases. This feature can be detected by the following code:

SELECT name, is_broker_enabled
FROM sys.databases

If the value of Service Broker is not set to 1, you can start this feature by setting the following command for each database:

ALTER DATABASE msdb SET ENABLE_BROKER

If the SQL Server Agent is running, the command cannot be executed, you need to terminate the SQL Server Agent, run the command, and then restart it.

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

Create Alert Manager

It is created in the same way as other alert managers, except that we need to select the WMI Event Alert Manager (WMI Events alert) type.

First expand the SQL Server Agent tree directory, right-click Alerts and select New Alert

Figure 1: Select New Alert

On the new alert (Alert Manager) page, we will change the type to WMI event Alert. Another point you should be aware of is that the system also needs a column of namespace information, and the SQL Server system has selected the correct information by default based on the version of SQL Server that you are using.

Figure 2:new Alert page