Lync 2013: Monitoring Mirrored SQL Databases With PowerShell

2013-11-25 3 min read Lync Microsoft Powershell System Administration Zachary Loeber

In Lync 2013 you are given a powerful new backend redundancy option for your important databases in the form of SQL mirroring. In this article I’ll discuss which services are able to be mirrored, the databases they encompass, and provide a PowerShell script to generate a report on the database mirror status. I also threw in Lync CMS replication and service status sections because it is the civil thing to do…

In picking through the backend changes between Lync 2010 and 2013 I found myself looking quite a bit more deeply into the new SQL mirroring support than I originally intended. The quick rundown of Lync database mirroring is that there are several Lync service types that are able to be setup for SQL mirroring. These services, in turn, consist of one or more databases which are actually mirrored between two sql servers. You can look at the status of your backend SQL database mirrors within the SQL management tool. Or you can also look at the status within the Lync server control panel by going to Topology -> Status, then selecting the properties of a server.

Unfortunately, there is a slight disconnect between the individual databases which are mirrored, the services that they map to, and the actual database servers which the primary (aka. principal) and mirrored databases reside. The services that a particular database map to is important to know as that is how database fail overs are manually managed within Lync (using Invoke-CsDatabaseFailover -PoolFqdn <_pool.domain.com>_ -DatabaseType __).

As multiple databases can map to a single database type, it is also entirely possible to unknowingly be in a state where some databases are failed over to the mirror copy while others are still on the primary copy. A great blog post by James Cussen has a list of Lync 2013 databases and their associated service types (aka. Database Types). He also covers the above mentioned SQL mirroring scenario quite well and provides an excellent GUI script for managing them

Partly driven by James’s contribution to the community and partly from frustration because of the lack of built in powershell cmdlet functionality, I’ve created a PowerShell function to better report on the lync pools, services, their database mirror state, and associated SQL servers. I then use this information to create a health report based on the state of the primary and mirror databases.

The actual function is quite simple. First we gather a list of Lync pools which are not on the edge and which require replication. This should get me all lync front-end/chat servers. Using these pool Fqdns I loop through and get the results of Get-CsDatabaseMirrorState. For each pool which returns any results I use a hash table of all the database names and their associated service to determine which command I’ll be running to get the primary and mirror database server information from (I could have just integrated this database name checking into the switch statement directly but this should be a more forward moving solution). I have to do this as, for whatever reason, Lync stores all the database server primary/mirror information with different property names in the Get-CSService output. This effectively gives me all the database mirror information and the pool, service, and associated SQL servers.

Here is the function:

I’ve used this function along with a couple others I concocted to create a small Lync 2013 health report for your convenience that you can download from the Microsoft Technet Gallery. Here is a small part of the output (not shown are the associated pools and sql servers to the left and right):