I wrote a while ago about setting up alerts for database mirroring events – when mirroring is suspended and resumed. It’s been a month or so since I’ve implemented those alerts and I’ve discovered I need to make some minor adjustments to my maintenance plans to accommodate them.

My standard maintenance plans are set to reorganize fragmented indexes nightly and to rebuild fragmented indexes once a week. These are both fully logged operations, so when these steps are performed on the mirrored databases, all that activity is also sent to the mirror server for replay on those copies of the databases. My maintenance plans are also all set to run at the same time on all my servers. This means that when the mirror server is performing the index work on its databases, it also suddenly starts getting all the index maintenance commands from the principal server for the mirrored databases and has to execute those transactions as well. My mirror server was getting hammered. The SAN could not keep up with the load and I was seeing errors left and right. The Windows application log was logging errors about disk access taking longer than 15 seconds and my mirroring sessions eventually got so far behind, they suspended themselves. Clearly, something had to change.

My plan was to disable mirroring during the maintenance jobs, letting the transactions pile up at the principal. Once the index maintenance was complete, I re-enabled mirroring. By this time, the maintenance jobs on the mirror server had also completed and it was able to handle the backlog of transactions that the principal started sending it as mirroring was synchronized. The only problem – alerts. Because I had alerts set up on both the principal and mirror for each of the five databases I was mirroring, both for when mirroring was suspended and re-enabled, I was getting bombarded with 20 alerts each night.

I needed to make my maintenance plans a little more sophisticated. I changed them to first, disable all alerts relating to mirroring on both the principal and the mirror, then suspend the mirroring sessions. Then the standard maintenance steps took place, followed by re-enabling mirroring and the mirroring alerts. I also took the extra steps of setting up some On Failure precedents so that if the tasks to disable the alerts failed, I would get an email telling me it failed and I could ignore the barrage of alerts that would be coming. (I included a caveat as a reminder to expect additional alerts about mirroring coming back online shortly thereafter. If I didn’t get those, something went wrong and would require my attention.) Similarly, if the alerts were successfully disabled, but NOT successfully re-enabled, I sent out an email telling me to make sure they got enabled again.

Here is a shot of part of my modified maintenance plan:

Notice the precedence constraints. In some tasks, I used On Completion constraints instead of On Success. If the alerts could not be disabled, I still want the maintenance job to continue. Likewise, if the mirroring sessions could not be resumed, I still want the mirror alerts enabled – to remind me mirroring is still down. There are a couple steps that aren’t shown, but they are just more of the same.

The Code Behind The Tasks

That’s a nice picture, but it doesn’t really show you the code that actually does the work. Here is the code to suspend mirroring:

ALTER DATABASE <database name> SET PARTNER SUSPEND

I have five databases mirrored, so my task has this command five times, once for each database. The task to re-enable mirroring is:

ALTER DATABASE <database name> SET PARTNER RESUME

Remember, the suspend command can be issued on either principal or the mirror, but the resume command can only be issued on the principal. In my case, this is all running on the principal.

To disable all my mirroring alerts, I use the sp_update_alert command. My alerts relating to mirroring are all named starting with “Mirroring”, so I made a cursor to select those alerts and disabled them:

USE msdb

DECLARE	@AlertName varchar(100)
DECLARE 	MirroringAlerts CURSOR FOR
	SELECT name FROM sysalerts WHERE name like 'mirroring%'

OPEN MirroringAlerts
FETCH NEXT FROM MirroringAlerts INTO @AlertName

WHILE @@fetch_status = 0
BEGIN
	EXECUTE sp_update_alert @name=@AlertName, @Enabled=0
	FETCH NEXT FROM MirroringAlerts INTO @AlertName
END

CLOSE MirroringAlerts
DEALLOCATE MirroringAlerts

The code to enable them again is the same except I set @Enabled=1 on line 12.

Your email notification tasks can say whatever you want, but mine say:

The mirroring alerts on <servername> were not able to be disabled as part of the maintenance job. The maintenance job pauses mirroring temporarily, which will cause these alerts to fire. If you subsequently receive alerts that mirroring has started synchronizing, you can ignore these “mirroring paused” alerts.

and

The mirroring alerts on <servername> were not able to be re-enabled as part of the maintenance job. The maintenance job disables the alerts and pauses mirroring temporarily, which will cause these alerts to fire. The alerts were not able to be re-enabled and you should manually enable them as soon as possible.

Now I’ve got some sophisticated maintenance plans that handle both mirroring and mirroring alerts!