The Third Day of Lync’mas: SQL DB Mirroring

This is part three of a twelve post series, to see an index of all twelve posts click here.
On the third day of Lync’mas my UC Team gave to me:  three SQL Servers

Everyone that’s heard of Microsoft SQL Server database mirroring, please raise your hand…. alrighty, now for those of you that haven’t (or maybe need a little refresher) here’s a quick overview, courtesy of MSDN:

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. Starting database mirroring on a database, initiates a relationship, known as a database mirroring session, between these server instances. One server instance serves the database to clients (the principal server). The other instance acts as a hot or warm standby server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).

 

Why are we talking about SQL in a Lync blog?  Because, in Lync Server 2013 high availability for the back-end SQL Databases is provided by SQL Server database mirroring.  Gone are the days of failover clustering.  You heard (or read) right, failover clustering is no longer supported for high availability of the database back-end.  From TechNet:

Lync Server 2013 supports SQL Server mirroring, including the use of a witness, for all databases. This is the only high availability SQL Server solution supported in Lync Server 2013.

Now, with all of the “fine print” out of the way, the astute reader may have noticed that this is the third day of Lync’mas and our UC Team has given us three SQL servers.  Thus far, however, we’ve only seen mention of two: a primary server and replica server.  The third server is what is known as a witness server.  The witness server provides a function similar to a quorum resource in failover clustering.  From TechNet:

High-safety mode with automatic failover requires a third server instance, known as a witness. Unlike the two partners, the witness does not serve the database. The witness supports automatic failover by verifying whether the principal server is up and functioning. The mirror server initiates automatic failover only if the mirror and the witness remain connected to each other after both have been disconnected from the principal server.

 

As you can see in the illustration above, a witness server is an optional role.  Lync Server 2013 does not require a Witness server to operate.  However, in my opinion, any Lync Server 2013 high availability architecture should include a witness server for the following reasons:

• High Transaction Safety – With a witness server database transaction is synchronous, ensuring that transactions are written to both the principal and mirror.
• Automatic Failover – A witness server supports automatic failover by verifying whether the principal is up and functioning.

For me, high availability should be as safe, easy and automatic as I can make it.  When a situation arises and high availability is needed, one of the last things I want to worry about is manually invoking a failover routine.  If I have a mechanism that can make failover safe and automatic, I want to build that into my solution.  Thus, my reasoning for requiring three SQL Servers and including a witness SQL server in my Lync Server 2013 high availability designs.