Lync 2013 SQL Mirroring Witness Gotcha

The IT Leader's Guide to Multicloud Readiness

This guide provides practical key insights and important factors to consider to make informed decisions in your multicloud journey.

Download the Guide

My colleague, Jason Sloan, has a great blog post on some SQL mirroring issues he encountered during a recent Lync 2013 deployment that I highly recommend everyone read.  This post is intended as an addition to that list with an issue that I discovered during my own Lync 2013 SQL mirroring deployment.
Consider the following scenario:

• Lync is installed and the initial topology is published with only mirroring configured (no witness)
• At a later date (this could be weeks, days, or hours – it really does not matter the length of time) administrators decide to add a SQL witness for automatic failover of the SQL mirror
• A Lync administrator adds the witness configuration into the topology and publishes the changes
• Topology builder reports no errors and the Lync administrator assumes that all is properly configured for SQL automatic failover
• Despite no errors encountered in topology builder, SQL Management Studio reports that NO witness configuration has been completed and attempting automatic failover results in a SQL back end outage

I have been able reproduce the scenario above in my lab, thereby removing doubt that it was an “environment issue”, and determined that the root cause is in the overall order of the SQL mirror configuration and how topology builder acts in a given configuration scenario.  The bottom line (and thus, the “gotcha”) is this:
If you want to use SQL mirroring with a SQL witness, have all servers ready and initially publish the SQL mirror and SQL witness at the same time in topology builder to ensure proper activation and configuration.  If you add a SQL witness after SQL mirroring was initially published, topology builder publishes the new SQL server configuration into the topology but it does not actually make any SQL-related changes on the SQL mirror nodes or SQL witness server.  In the latter scenario, the witness can eventually be configured and used, but only by removing the mirroring functionality and then publishing it all again.

If you initially only configure and install SQL mirroring and then attempt to go back and add the mirroring witness, you’ll discover that nothing gets changed on the SQL principal or SQL mirror database properties after you publish your topology changes:

Notice how the Witness field is empty and that the Operating mode is set to High safety without automatic failover.  Examining the Lync Topology, however, indicates that the witness is configured and active:

After a few rounds of testing I determined the following steps were required to actually get the SQL witness configured within SQL in the scenario where the mirror was installed first and a witness is added later:
Open Lync topology builder and disable the SQL witness configuration from pool properties and publish the topology changes.
Note:  Do not disable the SQL mirror configuration.  Only remove the SQL witness configuration.

Run the uninstall-csmirrordatabase cmdlet from the Lync management shell to remove mirroring configuration for all published databases.
Uninstall-csmirrordatabase –databasetype User –sqlserverfqdn pia-sql-be01.widgets.com –dropexistingdatabasesonmirror –verbose
Uninstall-csmirrordatabase –databasetype Application –sqlserverfqdn pia-sql-be01.widgets.com –dropexistingdatabasesonmirror –verbose
Uninstall-csmirrordatabase –databasetype Centralmgmt –sqlserverfqdn pia-sql-be01.widgets.com –dropexistingdatabasesonmirror –verbose
Uninstall-csmirrordatabase –databasetype Archiving –sqlserverfqdn pia-sql-be01.widgets.com –dropexistingdatabasesonmirror –verbose
Uninstall-csmirrordatabase –databasetype Monitoring –sqlserverfqdn pia-sql-be01.widgets.com –dropexistingdatabasesonmirror –verbose
The SQL mirror node databases should now be in an offline state.  Manually remove the databases on the SQL mirror node through Management Studio.  Right-click each offline database and select Delete.
Note:  The database will have a small green arrow to indicate it is offline, so make sure you only remove the correct databases based on your topology.

The SQL mirroring endpoint information does not get removed automatically as part of the uninstall-csmirrordatabase cmdlet.  Manually remove the endpoint information on the principal and mirror through Management Studio.  Navigate to Server Objects>Endpoints>Database Mirroring. Right-click the mirroring_endpoint object and select Delete.

In Lync topology builder re-enable the mirror and witness settings on the pool properties and publish the changes.

Following the topology publish and subsequent database installation on the SQL mirror you should now notice that the witness information has been configured on the principal & mirror servers and that the Operating mode reflects the witness configuration.

Additionally, if you query the SQL witness you will see that it is successfully configured with endpoint configuration for monitoring the mirror:

Again, the bottom line here is that it is easiest to make sure the SQL witness is activated by enabling SQL mirroring and the SQL witness at the same time when you initially publish your Lync topology.  If that is not an option, you will have to follow the steps above to essentially break and remove the SQL mirror, and then add the mirror AND witness configuration back in.  For anyone out there that has attempted to add a SQL witness at a later date, do not assume that your witness configuration succeeded – you should absolutely verify this configuration in your environment and take the steps above if your environment did not actually get configured for SQL witness functionality.