I’ve already admitted I’m a fan of pop music. In particular, Eighties pop music holds a dear spot in my heart. I’ve spent the last week working with database mirroring and I’ve had this Human League song going through my head the whole time. Ah, the 80s.. Such music..

Moving along… Last week, I wrote about setting up database mirroring. This week, I’m going to talk about alerts you can use in monitoring your mirroring topologies and how failovers affect clients accessing the database. Let’s start with the latter.

Client Access

If you have used clustering as a high availability solution previously, you are aware of what happens when a clustering failover occurs: The SQL Server service is switched from running on one machine to the other (assuming an active / passive cluster). Behind the scenes, Windows also handles redirecting incoming requests to the cluster name and / or IP  address  from the original machine to the newly active machine. From a client point-of-view, there is no change. The client still tries to connect to the same IP address or same server name (which would be the name of the cluster).

But in database mirroring, things are a bit different. Only a database fails over and the original SQL Server may still be up and running and hosting other databases. The mirrored database is suddenly active on a new SQL Server with a new server name and IP address. So how does the client know to connect to the mirror instance (which has become the new principal) instead of the old principal (where the database has gone offline for some reason, triggering the failover)? If you are using older ODBC drivers that don’t support mirroring, it doesn’t. You’ll have to manually reconfigure the ODBC driver or change the database connection properties in your program to point to the new server. This is obviously not ideal. If the goal of mirroring is to reduce downtime, having to manually reconfigure all your clients after a failover does the opposite – it creates downtime!

Luckily, Microsoft has released an ODBC driver that supports mirroring failovers. Go to this site and look for the paragraph titled Microsoft SQL Server 2008 Native Client. There are versions there for 32 bit, 64 bit, and IA64 processors. In addition to the ODBC driver, this download also contains OLE DB providers and support for ADO. This driver allows your client to access the new features of SQL 2008, such as mirroring.

Once you install this package, when you go to add a new ODBC source, you’ll see a new option when you add a System DSN: SQL Server Native Client 10.0.

If you select this and go through the standard configuration steps, you’ll notice a new screen:

This is where you enter the name of the mirror server (and, optionally, it’s SPN). Now, when the database fails over, the client will know where to go to connect to the mirrored database.

Be sure to check the System Requirements (listed at the top of the download page) before trying to install and use this new driver.

Monitoring Mirroring

I want to be notified when a mirroring failover occurs. If the failover was due to something internal to SQL Server, such as a media failure, it is likely you will get an alert for this because this is a severity 24 error (and you do have alerts set up for severity 17 through 25 errors, don’t you?). But what if SQL Server is running fine and it was a network outage that caused the mirroring failover? That won’t generate a SQL Server alert. So I want to be notified anytime a failover occurs.

This page, which I linked to last week, describes how to set up notifications for SQL mirroring events. The article is about how to initiate a failover of other databases via a job when one fails over (in case your client application uses multiple databases), but the alert concept works just as well for sending an email.

In this case, we set up an alert for a Windows WMI event. Database mirroring has several states you can monitor for. See the link for a list of them. In this case, I am looking for failovers – both manual and automatic failovers. This corresponds to states 7 and 8. I also want a specific alert for each database that is being mirrored. You’ll see why in a minute. So I set up my alert with a condition like this

You see in the Query field, I specify the database name this alert monitors. (Don’t worry about the Namespace field – it defaults to the current instance you are working with, which will be fine). Click the Response page and choose to have an email sent when the alert is triggered. I then like to go to the Options page and include some additional text to be included with the alert:

This is why I like a separate alert for each database – I configure my alerts to include the database name that is affected, as well as which server it was on and which server it failed over to. As I’ve mentioned before, I like my alert emails to provide me with as much information as I need to determine exactly what happened.

The other alert I set up is to watch for a paused mirroring session. You can pausing mirroring if, for example, you are doing something that you know will generate a ton of transaction log entries – rebuilding all the indexes perhaps. If you have a slow network connection, you could experience performance issues on the principal server as it waits for the transactions to be sent to the mirror server (assuming you are in High Availability mode – High Performance mode does not have this issue). To get around this, you can pause the mirroring session, which lets the principal server run at full speed. When the session is un-paused, the transaction log entries then get sent to the mirror. However, in high availability mode, transactions in the transaction log remain active and cannot be cleared by a checkpoint until they are sent to the mirror database. Therefore, the transaction log cannot be shrunk, even if it has been backed up, and you can potentially run out of disk space if a mirroring session remains paused for too long. See this Microsoft article for more details on this situation. So I want to be notified anytime a mirroring session is paused so I can make sure to un-paused it quickly.

This alert is set up that same as the previous alert, except this time you will be monitoring for “State = 9” in your Query statement and my additional text to include on the Options page reads “Database mirroring of the SJSTest database on <servername> has been paused.”

We’ve just defined an alert that tells us when the mirroring session has been paused. In real life, I’ve seen mirroring automatically get switched into the paused state during periods of high disk I/O, like during the aforementioned maintenance job that is rebuilding indexes on the mirrored database. In most cases, as soon as the job is over and the disk I/O drops off, mirroring can be resumed with no problem. Now suppose I’m driving in my car on the way to the local Indian casino to see Rick Springfield perform when my phone dings and I see this alert come in. I know this is about the time a maintenance job runs and that this is what is likely causing the problem. Mirroring should be able to be resumed with no problem in a few minutes. I have a couple of choices. I can turn around and head home, login to the server remotely and resume the mirroring session myself, like a working class dog, or I can call up another member of the IT department and walk them through un-pausing the mirroring session – after all, it’s just a couple of mouse clicks – and continue my evening with Jessie’s girl. Either way, I still want to be sure that mirroring has resumed.

So another alert can be set up. Two actually. Again, the setup is the same as before, but this time we will be monitoring for State = 11 and State = 13. State 11 corresponds to a “synchronizing mirror” state and state 13 corresponds to a “synchronizing principal” state. These are the states the mirrored database will transition to when it is resuming from being paused. (It is also a state they will go through during a failover.) By setting these alerts up, we can get notified when a mirroring session transitions out of the paused state and I will have confirmation that my IT co-worker has successfully followed my instructions. If you want to be very paranoid, you can also create alerts for when the database enters the “synchronized” state, indicating all the transactions have been caught up. However, if you look at the various states in the state chart, you’ll see there are several state IDs for this status, depending on the state of the witness server. That’s just a few too many for me to bother setting up alerts for. I’m content to assume that, once the database enters the “synchronizing” state, it will make it to the “synchronized” state. At least, until experience proves otherwise.

Now we’ve set up all these alerts on the principal server. They also need to be set up on the mirror server – because if a failover occurs, that server will become the new principal. So create all the alerts on that server as well. Be sure to change the text of the messages to reflect the change in server roles (i.e., the “server X has failed over to server Y” part).

I should note that these alerts are not instant. During my (admittedly brief) testing, I noticed it took about 30 seconds after a mirror session had been paused for me to get the alert email and 10 seconds after a manual failover to get that alert email. But this is a minor and acceptable delay.

If you have any questions, leave a comment and I’ll try to answer your every wish. Your every dream, hope, desire.