Lync Server 2013 SQL Mirroring

Redundancy in Microsoft Lync Server 2013 has evolved from just a mere SQL clustering configuration to SQL mirroring. SQL mirroring has been around for a while in the Microsoft SQL Server realm, but it's new to the Lync infrastructure. The ability to provide redundancy in the back-end SQL Server infrastructure is key.

Setting up and working with SQL mirroring in Lync isn't complicated. Let's take a look at what is involved with setting up the mirror, how to handle routine server maintenance when SQL mirroring is configured, and what to do when an outage occurs in a SQL Server node.

Lync Server 2013 SQL Mirroring Configuration

Lync Server 2013 SQL mirroring is a process that provides resiliency in the back-end Lync infrastructure in case one of the SQL Server nodes goes offline for any reason. It requires that you have two Back End servers running SQL Server 2008 R2 or later. One server functions as the primary server. The other server contains the same databases as the primary server, so it's essentially a mirror of the primary server. The idea is that, if the primary server goes offline, a failover to the databases on the mirror server occurs so that database operations can continue. If you want the failover to be automatic, you need a SQL witness as the decision maker. The SQL witness isn't needed for scenarios in which manual intervention is acceptable.

The TechNet article "Deploying SQL mirroring for Back End Server high availability in Lync Server 2013" provides step-by-step instructions on how to set up SQL mirroring in Lync's Topology Builder. If you want automatic failover, be sure to select the Use SQL mirroring witness to enable automatic failover option and specify the store you want to use as the SQL witness, as shown in Figure 1.

Figure 1: Configuring a SQL Witness

SQL Mirroring and Maintenance

When you need to perform scheduled maintenance on your SQL Server nodes, you need to manually change the state of the databases on the primary server, even if you have a SQL witness. To do so, you can run Windows PowerShell commands in the Lync Server 2013 Management Shell. First, you can use the Get-CsDatabaseMirrorState cmdlet to find out the location of the primary and secondary databases on each SQL Server node and the mirroring status of those databases. The Get-CsDatabaseMirrorState command to run is:

Get-CsDatabaseMirrorState –PoolFqdn YourPoolFQDN `  –DatabaseType User

where YourPoolFQDN is the Fully Qualified Domain Name (FQDN) of the pool containing the databases you want to check. Figure 2 shows sample output. In this case, the rtcab database located on the primary SQL Server node named StateOnPrimary is the principal database and the rtcab database located on the secondary SQL Server node named StateOnMirror is the mirror database. The principal and mirror databases are synchronized.

Figure 2: Checking the Location and Status of the Principal and Mirror Databases

Once the location is established and the status is checked, you can use the Invoke-CsDatabaseFailover cmdlet to perform a manual failover so that the mirror database becomes the principal database. The command to run is:

Invoke-CsDatabaseFailover –PoolFqdn YourPoolFQDN `  –DatabaseType User –NewPrincipal Mirror

As you can see in Figure 3, you'll be asked to confirm the failover. If you proceed, you'll see the results. In this case, the failover to the mirror databases on the StateOnSecondary node was successful.

Figure 3: Performing a Manual Failover So That the Databases on the StateOnSecondary Node Are the Principal Databases

After the failover has completed, you can run the Get-CsDatabaseMirrorState command again to check the database location and status. As you can see in Figure 4, the rtcab database has changed from being hosted on the primary node (StateOnPrimary) to being hosted on the mirror node (StateOnMirror), evident by StateOnPrimary now displaying Mirror and StateOnMirror now displaying Principal.

Figure 4: Checking the Location and Status of the Principal and Mirror Databases After the Failover

After your maintenance work is complete, you need to failover your databases back to their original locations. Once again, you can use the Invoke-CsDatabaseFailover cmdlet:

Invoke-CsDatabaseFailover –PoolFqdn YourPoolFQDN `  –DatabaseType User –NewPrincipal Primary

Based on the results shown in Figure 5, the failover was successful.

Figure 5: Performing a Manual Failover So That the Databases on the StateOnPrimary Node Are the Principal Databases

If you run the Get-CsDatabaseMirrorState command at this point, you'll see that the databases are back their original locations.

SQL Mirroring and Outages

Administrators are most comfortable when operations are running smoothly. However, sometimes operations go awry or something happens that's out of the ordinary. By knowing what to expect and what to do, administrators can improve their comfort level and competency when problems arise.

For example, suppose that the rtcab, rtcxds, and rtcshared databases on the StateOnPrimary node are the principal databases. Suddenly, you discover that the StateOnPrimary node has gone offline. You have a SQL witness, so an automatic failover to the StateOnSecondary node occurs. At this point, you should run the Get-CsDatabaseMirrorState command shown previously to confirm that a failover occurred. As Figure 6 shows, you'll see that:

Figure 6: Confirming a Failover Occurred After the StateOnPrimary Node Went Offline

After doing some more analysis on the outage, you determine that the SQL Server services for the primary node had stopped for whatever reason, which explains the StatusUnavailable and disconnected states. After you restart the SQL Server services, you can run the Get-CsDatabaseMirrorState command again to make sure the StateOnPrimary node is back online and its rtcab, rtcxds, and rtcshared databases are listed as mirror databases. As you can see in Figure 7, the StateOnPrimary node is no longer in a StatusUnavailable state but rather a Mirror state. You can also see that the primary and mirror databases are now synchronized.

Figure 7: Making Sure the StateOnPrimary Node Is Back Online

At this point, you need to decide whether to keep the rtcab, rtcxds, and rtcshared databases on the StateOnSecondary node as the primary databases or perform a manual failover so that the three databases the StateOnPrimary node become the primary databases. To do the latter, you can run the command:

Invoke-CsDatabaseFailover –PoolFqdn YourPoolFQDN `  –DatabaseType User –NewPrincipal Primary

After the manual failover, you should run the Get-CsDatabaseMirrorState command one last time to verify that the rtcab, rtcxds, and rtcshared databases on the StateOnPrimary node are the primary databases, which was the original setup before the outage.

SQL Mirroring Makes Sense

Configuring SQL mirroring isn't difficult, but it's important that you remember to include a SQL witness. Without it, automatic failover isn't possible. Even with automatic failover, you need to perform certain actions if a node goes offline. The process for handling an outage is similar to the process for handling maintenance, which is helpful when operations go awry because you'll already be familiar with some of the steps.

All in all, I've been quite pleased with the concept and practical sense of how SQL mirroring applies to Lync Server 2013. I hope this back-end architecture will be kept in future Lync Server releases.