SQL Mirroring 2005 Management Pack

Recently Microsoft released a new SQL mirroring management pack for SQL 2008. This was a great release, as it adds a lot of additional insight into the relationship and status between a principal database and its mirror. However, SQL 2005 was left out for some reason.

A customer wanted to get the same for its older database-servers. At first glance it appeared that a simple rename-all from 2008 to 2005 would do the trick.

This actually worked…partially. The following event started popping up on all 2005-servers:

clip_image001

A quick check on the script revealed that the following query was run for the mirroring witness discovery:

SELECT database_name,

mirroring_guid,

principal_server_name,

mirror_server_name,

partner_sync_state,

safety_level

FROM sys.database_mirroring_witnesses

When performing the query manually on a random 2005-server, it produced the same error. The ‘partner_sync_state’-table doesn’t exist in a SQL2005-environment.

However, upon further checkup of the script, it became clear that all fields where collected in a discovery propertybag somehow, except the problematic field!

Do While Not oResults.EOF

sDBName = oResults(0) ‘DATABASE_NAME

If Not(IsExcluded(sDBName, ExcludeList)) Then

sMirroringGUID = oResults(1) ‘MIRRORING_GUID

sPrincipalInstance = oResults(2) ‘PRINCIPAL_SERVER_NAME

sMirrorInstance = oResults(3) ‘MIRROR_SERVER_NAME

nState = oResults(4) ‘PARTNER_SYNC_STATE (the problem)

nSafetyLevel = oResults(5) ‘SAFETY_LEVEL

Set oWitnessInstance = oDiscoveryData.CreateClassInstance(“$MPElement[Name=’Microsoft.SQLServer.2005.Mirroring.Witness’]$”)

With oWitnessInstance

.AddProperty “$MPElement[Name=’System!System.Entity’]/DisplayName$”, “Witness for ‘” & sDBName & “‘ database”

.AddProperty “$MPElement[Name=’Windows!Microsoft.Windows.Computer’]/PrincipalName$”, TargetComputer

.AddProperty “$MPElement[Name=’SQL!Microsoft.SQLServer.ServerRole’]/InstanceName$”, InstanceName

.AddProperty “$MPElement[Name=’Microsoft.SQLServer.2005.Mirroring.Witness’]/MirroringGUID$”, sMirroringGUID

.AddProperty “$MPElement[Name=’Microsoft.SQLServer.2005.Mirroring.Witness’]/DatabaseName$”, sDBName

.AddProperty “$MPElement[Name=’Microsoft.SQLServer.2005.Mirroring.Witness’]/Principal$”, ExtractHostNameFromEndpoint(sPrincipalInstance)

.AddProperty “$MPElement[Name=’Microsoft.SQLServer.2005.Mirroring.Witness’]/Mirror$”, ExtractHostNameFromEndpoint(sMirrorInstance)

.AddProperty “$MPElement[Name=’Microsoft.SQLServer.2005.Mirroring.Witness’]/Level$”, GetMirroringLevelName(nSafetyLevel, ConnectionString)

End With

Call oDiscoveryData.AddInstance(oWitnessInstance)

Where is the nState processed?

I altered the query and value-assignments so that the partner_sync_state table doesn’t get queried or processed:

SELECT database_name,

mirroring_guid,

principal_server_name,

mirror_server_name,

safety_level

FROM sys.database_mirroring_witnesses

Do While Not oResults.EOF

sDBName = oResults(0) ‘DATABASE_NAME

If Not(IsExcluded(sDBName, ExcludeList)) Then

sMirroringGUID = oResults(1) ‘MIRRORING_GUID

sPrincipalInstance = oResults(2) ‘PRINCIPAL_SERVER_NAME

sMirrorInstance = oResults(3) ‘MIRROR_SERVER_NAME

nSafetyLevel = oResults(4) ‘SAFETY_LEVEL (with fixed ID-value)

After I sealed and imported the altered management packs, the discovery successfully added some witnesses to the management group!

Included a link to the unsealed versions of these add-ons to the SQL Server management packs, sealed version coming soon:  http://sdrv.ms/MO31xr

Advertisements
This entry was posted in #scom, #sysctr, Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s