Recently we deployed database mirroring in an environment with an Active Directory. The process is usually quite simple and straight-forward, but this time was different.

The mirroring should have been scripted, so it could be embedded in an application’s installation and deployed worldwide. As in any other sensitive infrastructure solution, monitoring is essential. Microsoft provides the sp_dbmmonitor stored procedures to configure and review monitoring metrics. Even though are not user-friendly, they are manageable.

There are several options to watch what’s going on with your mirroring:

1. Database Mirroring Monitor You can launch the database mirroring monitor, a nice tool for watching your mirroring status and performance online. It provides important information on the status of the mirroring, but it is limited to the current activity and can’t send notifications, so it wasn’t useful in this case. This monitor is also used by the replication monitor (you can look for the application name in the task manager). Finally, if you get the critical icon on a server, although it is online, open the “Register Mirrored Database” window (displayed as link) and set the connection string.
2. Database Mirroring System Views Views such as sys.database_mirroring and sys.dm_db_mirroring_connections can provide information on the current status of mirroring and its configuration, but they cannot be used for monitoring if you are looking for a push mechanism unless you want to query these views constantly, looking for the principal in each server and comparing it to its last state.
3. Extended Events There’s an extended event object named “database_mirroring_state_change” which, according to Microsoft, “occurs when the state of a database mirror has changed. Use this event to monitor the condition of a database mirror.” Using Extended Events for monitoring requires a knowledge of Service Broker and event notification, which unfortunately aren’t very common skills.
4. Alerts This was the solution approved for this project. The alert would fire in the case of a failover and would notify the application and the operational staff. It executes the following WQL query (reading WMI information) looking for a failover:

SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE DatabaseName = '{database_name}' AND (State = 8 OR State = 7)

We turned to Microsoft’s TechNet looking for more detailed information, and there it was, in the Alerting on Database Mirroring Events page. Everything you need to establish database mirroring monitoring is in reach and accessible to the common DBA. There are also scripts at the bottom of the page for a simple copy-paste.

@wmi_namespace=N'\.rootMicrosoftSqlServerServerEvents'

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: WMI error: 0x8004100e.
Msg 14511, Level 16, State 1, Procedure sp_verify_alert, Line 304
The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax.

When we copied and executed the scripts, the alerts were created. However, testing a specific alert (DB Mirroring: State Changes) by executing a database failover repeatedly did not raise it. Other alerts (such as CPU and I/O) ran perfectly, so we thought it might have been caused by some adjustments we made to the alert before executing its creation script . Indeed, we did add notification to an operator and tasked the alert with a job to execute, but even after these were removed the alert did not rise.

Back to square one, we copied the script from the web site and executed it (copy-paste):

-- The namespace must include the instance name from which the 
-- WMI events will originate. For example, if the instance is 
-- the default instance, use 'MSSQLSERVER'. If the instance is 
-- SVR1INSTANCE, use 'INSTANCE'
DECLARE @namespace NVARCHAR(200);
IF (SERVERPROPERTY('InstanceName') IS NOT null)
BEGIN
   SELECT @namespace = N'\.rootMicrosoftSqlServerServerEvents'
      + CONVERT(NCHAR(128), SERVERPROPERTY('InstanceName'));
END;
ELSE
BEGIN
   SELECT @namespace = N'\.rootMicrosoftSqlServerServerEventsMSSQLSERVER';
END;
EXEC msdb.dbo.sp_add_alert @name=N'DB Mirroring: State Changes', 
   @message_id=0, 
   @severity=0, 
   @enabled=1, 
   @delay_between_responses=0, 
   @include_event_description_in=0, 
   @category_name=N'Database Mirroring', 
   @wmi_namespace=@namespace,
   @wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE ', 
   @job_id=@jobId;

We executed the script, failing to raise the alert with the following error:

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: WMI error: 0x8004100e.
Msg 14511, Level 16, State 1, Procedure sp_verify_alert, Line 304
The @wmi_query could not be executed in the @wmi_namespace provided. Verify that an event class selected in the query exists in the namespace and that the query has the correct syntax.

We Googled for some insights, but all referred to the WMI query as the cause. I wrote out the script to ask the SQL Server community for assistance, and I decided to use the alert’s script, rather than the one in the web site. This was the “Generate Script” output:

/****** Object:  Alert [DB Mirroring: State Changes]    Script Date: 1/28/2014 12:40:59 PM ******/EXEC msdb.dbo.sp_add_alert @name=N'DB Mirroring: State Changes', 
@message_id=0, 
@severity=0, 
@enabled=1, 
@delay_between_responses=0, 
@include_event_description_in=0, 
@category_name=N'Database Mirroring', 
@wmi_namespace=N'\.rootMicrosoftSqlServerServerEventsBA                                                                                    ', 
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE ', 
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

As you can see, the @wmi_namespace value spreads over multiple lines. Having a closer look, you can see the namespace string is long and full with spaces. These spaces were the reason for the alert not to function as expected. Executing the script in other SQL Server servers with varying configurations will raise the alert in case of failover; however, it wasn’t raising the alert in my case.

What caused the script to run successfully on one server and fail on the other? In the script Microsoft provided, the parameter @namespace is of data type NVARCHAR(200), while it concatenates the instance name as NCHAR(128). Once concatenated, the @namespace parameter is filled with the instance name and additional spaces, preventing the identification of the instance name and preventing the WMI query from raising the alert. By the way, the script to create the alert is described earlier and seems very simple, but it is not re-usable the way we wanted the script to be:

@wmi_namespace=N'\.rootMicrosoftSqlServerServerEvents'

Changing the script by replacing the

CONVERT (NCHAR (128), SERVERPROPERTY ('InstanceName'))

with

CONVERT (NVARCHAR (128), SERVERPROPERTY ('InstanceName')) will fix the alert.

Even though I tried to reproduce the error in several SQL Server installations with different server collations, it looks as if it is not a problem with the SQL Server collation. It made me wonder how many database administrators faced such an issue before, because the script in the TechNet page and the white-paper are dated 2006.

My last insight is the way I used to copy-paste administrative level scripts. I always studied the execution of the scripts and tested them before implemented in testing/integration and production environments, but it never occurred to me to look for minor issues such as blank spaces or variable-length data-types conversions in a long-lasting scripts in Microsoft websites.

Image by Steve Bowbrick “Mind the Gap“

The post Mind the Gap appeared first on Madeira Data Solutions.