Friday, May 3, 2013

Lync 2013 Database Mirror Manager Tool

So it seems there has been quite a bit of interest in my Lync Centralised Logging Tool since I released it to the world. So I figured I might work on another tool to help with managing database mirroring within Lync 2013…

Database Mirroring Overview


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: 



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!


4 comments:

  1. Simple and nice tool to help us Lync Administrators

    Thank you my friend :)

    ReplyDelete
  2. Simple and nice tool to help lync administrators

    Thank you my friend

    ReplyDelete
  3. Thank you - makes life so much easier!

    ReplyDelete