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.
- 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.
- 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.
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.
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!