Lync 2013 Database Mirror Manager Tool

In Lync 2013 we have been given a new SQL High Availability option for Lync databases to replace the old and more complicated SQL Clustering method from Lync 2010. The Lync 2013 SQL Mirroring method is easy to provision using topology builder, and can be managed using Powershell commands.

Managing Mirrored Databases

The mirror state of the databases can be controlled by Lync Powershell commands (probably best not to go fiddling with the SQL configuration directly). The command used to change the state of databases is shown below:

Invoke-CsDatabaseFailover -DatabaseType <Application | Archiving | Monitoring | User | Provision | CentralAdmin | Lyss | Registrar | Edge | PersistentChat | PersistentChatCompliance | CentralMgmt> -NewPrincipal <Primary | Mirror> -PoolFqdn <Fqdn> [-Confirm [<SwitchParameter>]] [-ExcludeDatabaseList <String[]>] [-Force <SwitchParameter>] [-LocalStore <SwitchParameter>] [-Report <String>] [-WhatIf [<SwitchParameter>]]

The Invoke command is broken down into Database Types. These ‘database types’ do not line up with the actual names of the databases within SQL, but instead, are given function names that relate to their role within Lync. The ‘database types’ can also contain more than one physical database. Here is how the database types are mapped to SQL names:

DatabaseType	Databases
Application	Rgsconfig Rgsdyn Cpsdyn
Archiving	Lcslog
Monitoring	Lcscdr Qoemetrics
User	Rtcab Rtcxds Rtcshared
CentralMgmt	Xds Lis
PersistentChat	Mgc
PersistentChatCompliance	Mgccomp
Provision	Not Implemented
CentralAdmin	Not Implemented
Lyss	Not Implemented
Registrar	Not Implemented Note: When you Invoke a changeover on the Archiving or Monitoring database, Lync will ask you if you want to accept the action to perform changeover on the target "Registrar". Kind of weird that it refers to these databases in that way... At any rate, you can't actually call the invoke command on a 'database type' of "Registrar" so don't try it.
Edge	Not Implemented
Cls	Not Implemented

Note: The Not Implemented database types above are listed on Technet as being database types. However, you can’t actually call the Invoke command on these 'database types' in Powershell.  I’m assuming this is a documentation error…

You can see the physical databases within SQL. Here’s what they look like:

What you might notice in the picture above is that some of the physical databases that are grouped together within the Powershell database type categories are actually in different database states. For example, cpsdyn and rgsdyn are in mirrored state, whilst rgsconfig is in Principal state. This is because SQL manages these databases mirrored state per physical database, and not in the groupings that Microsoft chose to build the Invoke changeover command.

By running a Get-CsDatabaseMirrorState command, we can see that these databases are not in the same state:

PS > Get-CsDatabaseMirrorState

cmdlet Get-CsDatabaseMirrorState at command pipeline position 1

Supply values for the following parameters:

PoolFqdn: pool.domain.com

DatabaseName             : rtcab

StateOnPrimary           : Mirror

StateOnMirror            : Principal

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

DatabaseName             : rtcxds

StateOnPrimary           : Principal

StateOnMirror            : Mirror

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

DatabaseName             : rtcshared

StateOnPrimary           : Principal

StateOnMirror            : Mirror

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

DatabaseName             : lcscdr

StateOnPrimary           : Principal

StateOnMirror            : Mirror

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

DatabaseName             : qoemetrics

StateOnPrimary           : Principal

StateOnMirror            : Mirror

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

DatabaseName             : lcslog

StateOnPrimary           : Mirror

StateOnMirror            : Principal

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

DatabaseName             : rgsconfig

StateOnPrimary           : Principal

StateOnMirror            : Mirror

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

DatabaseName             : rgsdyn

StateOnPrimary           : Mirror

StateOnMirror            : Principal

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

DatabaseName             : cpsdyn

StateOnPrimary           : Mirror

StateOnMirror            : Principal

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

DatabaseName             : xds

StateOnPrimary           : Mirror

StateOnMirror            : Principal

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

DatabaseName             : lis

StateOnPrimary           : Principal

StateOnMirror            : Mirror

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

Note: I refer to this as being ‘out of sync’, which is not to be confused with their synchronisation state…

If I was to run the Invoke command on the User database here, I would end up with the following:

Invoke-CsDatabaseFailover -PoolFqdn pool.domain.com -DatabaseType "User" -NewPrincipal "Primary"

This causes the rtcab to move back to the primary mirror with the other user services:

PS > Invoke-CsDatabaseFailover -PoolFqdn pool.domain.com -DatabaseType "User" -NewPrincipal "Primary"

Confirm

Are you sure you want to perform this action?

Performing operation "Invoke-CsDatabaseFailover" on Target "UserServices".

[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "Y"): y

DatabaseName                                              FailoverResult

------------                                              --------------

rtcab                                                            Success

rtcxds                SucceededAsNewPrincipalAlreadyOwnsThePrincipalRole

rtcshared             SucceededAsNewPrincipalAlreadyOwnsThePrincipalRole

PS > Get-CsDatabaseMirrorState -PoolFqdn pool.domain.com -DatabaseType User

DatabaseName             : rtcab

StateOnPrimary           : Principal

StateOnMirror            : Mirror

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

DatabaseName             : rtcxds

StateOnPrimary           : Principal

StateOnMirror            : Mirror

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

DatabaseName             : rtcshared

StateOnPrimary           : Principal

StateOnMirror            : Mirror

MirroringStatusOnPrimary : synchronized

MirroringStatusOnMirror  : synchronized

Now all the physical databases within the User Database Type are all in the Principal state.

Alternatively the databases can be failed over as a single database. However, you need to run a more complicated powershell command:

Invoke-CsDatabaseFailover -PoolFqdn pool.domain.com –DatabaseType “User” -NewPrincipal "Primary" -ExcludeDatabaseList "rtcxds", "rtcshared"

Note: There’s an error on technet that includes an additional switch “-ExcludeDatabase” which isn’t actually needed (or available, for that matter).

Running the command will look something like this:

PS > Invoke-CsDatabaseFailover -PoolFqdn pool.domain.com -NewPrincipal "Mirror" -DatabaseType "User" -ExcludeDatabaseList "rtcxds", "rtcshared"

Confirm

Are you sure you want to perform this action?

Performing operation "Invoke-CsDatabaseFailover" on Target "UserServices".

[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "Y"): y

DatabaseName                        FailoverResult

------------                        --------------

rtcab                                      Success

It makes sense that Microsoft designed the commands to force functionally associated databases to be running on the one SQL machine. However, it can make it a bit confusing to administer from a Powershell perspective. Which brings me to the real point of this article…

Lync 2013 Database Mirror Manager Tool

Wouldn’t it be much easier to just have GUI interface that shows you the state of the database? And be able to simply click to choose which ones you wanted to fail over? Well, today is your lucky day. I’ve made another GUI based tool that does just that. It looks like this:

Lync Front End Mirroring

Lync Persistent Chat Mirroring

UPDATE (27/5/2015):

1.01 Enhancements: 

• Added enhanced handling of error states so the GUI doesn't display the option to change the mirror state of databases that aren't mirrored. I noticed this when doing CU updates and disconnected mirrors giving "DatabaseInaccessibleOrMirroringNotEnabled" errors that need to be handled in a way that makes sense in the GUI.
• Change the foreground colour of the database name label when the user has selected to change the state. This gives a visual indication of what is going to be changed when the Invoke button is pressed (ie. Any database with a green label will be changed over when the Invoke button is pressed.)

1.02 Update:

• Added a check box for automatically agreeing to changeover without having to explicitly agree to every database change in the Powershell window.
• Script now checks for the location of the central management store for Migration scenarios when it still resides on Lync 2010. This previously resulted in an error.
• Suppressed warnings from Powershell window that would be displayed when getting status of databases that weren't available.
• Write the commands being run to Powershell window for more feedback to the user.
• Database names are now listed in Powershell window with mirror state when refresh is done.
• Script is now signed.
• Updated the form icon.
• Removed the dedicated Close button because it's unnecessary.

1.03 Update

• Added Powershell pre-req checks and Module loading.
• Updated reporting of database status in PS window
• Added Up and Down keys in the pool listbox 
• Updated to work with Skype for Business!

1.04 Update

• There were reports of issues on some versions of Powershell with version 1.03. ErrorVariable flags have been removed in 1.04 this version to fix these issues.

1.05 Update

• Updated Signature

Download Version 1.05: 

DOWNLOAD HERE

It’s pretty straightforward to use (much more than Powershell anyway!). You simply select the pool from the list, and the databases that have mirroring enabled will display check boxes that show you the current Primary/Mirror status of the databases. If you decide you want to change one, or many, of the states, you simply click on the desired state check box for each database type, and then hit the Invoke button.

Note: You will be prompted in the Powershell window for each database that the tool is changing over. The tool will not make any changes without your knowledge.

So, what if databases within the Database groups get out of sync, like I described earlier in the article? Well, the tool is smart enough to realise this, and will inform you that the underlying physical databases within a 'database type' are out of sync. At this point you can choose to leave them out of sync, or invoke a change-over.

Out of Sync Error

Note: If you want to have your databases out of sync (because you just love defying Microsoft’s best practices), then don't press the invoke button. If you do, it will re-sync the databases back to the value selected in the check box for that database type.

Let me know if you find the tool useful or have any ideas on how you would like it improved.

The Wrap Up

The Evil Queen: "Mirror, mirror upon the wall, Who is the fairest of all?"

SQL Mirror: "O Lady Queen, though fair ye be, Lync Mirroring is the fairest of them all."

The Evil Queen: "Fair enough.”

See y’all next time. Enjoy!