In the previous part of this series, I talked about how we fail over our databases. In this part, I’ll cover what happens to Lync after the failover.
The objective of this exercise was to attempt to get our Lync Enterprise Edition Pool to function when we took the primary SQL server offline and pointed Lync to the SQL mirror node where our back end databases had been mirrored to.

Trying to get it to work

I found that after failing over, the SQL Native Client on the Front End servers was Database Mirroring aware and had automatically redirected itself to the mirror node of SQL02. This was confirmed when I saw a successful automatic connection to SQL02 on port 1433 from the Front End server (FE03) that we’d failed over to.

However the Lync Server Front End service is not Database Mirroring aware, and we saw loads of errors and warnings in the Lync Server event log telling us that a connection to the SQL server could not be made as it was consistently attempting to connect to SQL01 (the failed server) that is defined in the Lync topology.

When I attempted to sign in using Lync, all we could get was the “Limited Functionality Mode” experience with no presence or contact list.

At this stage, I started to think about how we could get this to work, and changed the DNS record for SQL01 to resolve to the IP address of SQL02, which didn’t help. I even changed the IP address of SQL02 to that of SQL01, which still didn’t result in a successful connection. Everything I tried to get SQL02 to look like SQL01 wouldn’t work.

How to achieve basic client functionality

Now that we’ve discovered that Lync doesn’t support Database Mirroring natively nor do basic DNS changes help, I worked out that we needed to do to get the Lync FE services to start properly and to make a database connection to the SQL mirror node.

1. Delete the computer account of the principal SQL server (SQL01) from Active Directory.
2. Modify the hostname of the mirror SQL Server (SQL02) to that of the failed, principal SQL Server (SQL01).
3. Restart the mirror SQL server and verify that all SQL services are started.
4. Modify the DNS A record for the failed principal SQL server so that it resolves to the IP address of the mirror SQL server.
5. Restart the Lync Front End server and confirm that the Front End service is started.
6. Verify signing in with the Lync client and that you receive the full client experience and not “Limited Functionality Mode”.

As you can see, this is by no means a “High Availability” solution, as it means computer name and AD changes which rules this out as being an acceptable “quick failover” scenario and even rules it out as a Disaster Recovery solution.

Because Lync Server is inherently secure, it means we cannot connect an FE server to a back end SQL server that isn’t defined in the topology.

Why is it not supported?

One reason why it’s not supported is that there is a highly dependent and integrated relationship between the rtc andrtcdyn databases.

Each database refers to the other to maintain a consistent presence state to the user of their contacts, and of their presence to their contacts. The rtc database is mostly static, holding publications of users, contact lists, privacy relationships etc whereas the rtcdyn database is dynamic, holding state about which front end in the pool holds a SIP endpoint and what active subscriptions are bound to the endpoint.

Because database mirroring is configured and managed at an individual database level, in the event of failure OCS/Lync cannot guarantee both databases are in the consistent state required to maintain the required quality user experience.

Summary

To summarise, when the failover process is executed, the following behaviour was observed:

• Initially, the Lync client signs in but is in “limited functionality mode”. This is because the Lync Front End service cannot communicate with the back-end database.
• Front End Service starts but events are logged advising that a back-end database connection cannot be made.
• It was concluded that due to inherent product design, the Lync Front End service will not connect to a SQL server that is not defined in the Lync Topology.
• It was only after we deleted the computer account for the failed principal SQL server from Active Directory and renamed the mirror SQL server did errors stop appearing in the Application Log and the Lync client function properly.

What about Group Chat?

In the next instalment, I’ll talk more about what happens to Group Chat when we failover the databases to the SQL mirror node. Make sure you subscribe to get updated!

In the first part of this series covering SQL Database Mirroring and Lync Server 2010, I covered a lot of the prerequisites required to establish this deployment scenario. I also ran through getting the Database Mirroring side of things setup.

In this next part, I’ll cover actually simulating a failure of our SQL Server. There are a few T-SQL queries we need to run first, followed by actually failing over the SQL database server node and verifying that the failover was successful.

Failing over to another SQL Server

The failover process was scoped so that it simulated a production data centre failure as close as possible. The process consisted of the following steps for each database mirroring scenario.

Prerequisites to Failover

1. First, open a Command Prompt and navigate to C:\Program Files\Common Files\Microsoft Lync Server 2010\Support. Run the following command to backup all user data (e.g. contact lists, privacy relationships, conference directories) using dbimpexp.exe:dbimpexp.exe /export /hrxmlfile:location of backup file /sqlserver:name of SQL server /restype:all.
2. Next, ensure the mirroring status is healthy and that the Principal server node (SQL01) reports each database as Principal, Synchronized:
   
    and that the mirror server node (SQL02) reports each database as Mirrored, Synchronizing:
3. To track synchronous replication, timestamp tables should be created in each database using the following Transact SQL commands.

CREATE TABLE dbname1.dbo.tblDate (dtDate datetime)
CREATE TABLE dbname2.dbo.tblDate (dtDate datetime)
CREATE TABLE dbname3.dbo.tblDate (dtDate datetime)
CREATE TABLE dbname4.dbo.tblDate (dtDate datetime)
CREATE TABLE dbname5.dbo.tblDate (dtDate datetime)
CREATE TABLE dbname6.dbo.tblDate (dtDate datetime)

4. Next, run the following command to insert timestamp data into the table every second so replication can be tracked:

SET NOCOUNT ON
WHILE 1 <> 2
BEGIN
INSERT INTO dbname1.dbo.tblDate (dtDate) (select GETDATE())
INSERT INTO dbname2.dbo.tblDate (dtDate) (select GETDATE())
INSERT INTO dbname3.dbo.tblDate (dtDate) (select GETDATE())
INSERT INTO dbname4.dbo.tblDate (dtDate) (select GETDATE())
INSERT INTO dbname5.dbo.tblDate (dtDate) (select GETDATE())
INSERT INTO dbname6.dbo.tblDate (dtDate) (select GETDATE())
WAITFOR DELAY ‘00:00:01‘
END

In each T-SQL command, replace the field dbname1 with the name of the database you wish to run the command against. Now we’re ready to failover the databases.

Simulating a Server Failure and Failing Over the Databases

You can do this in two ways – either using the Failover button in the Database Mirroring GUI or use the following method to simulate a complete server failure:

1. Force-power off the Lync Front End and back-end SQL servers, or verify that these servers have failed.
2. After the decision has been made to activate the disaster recovery process, log onto the SQL Server mirror node (SQL02) and run the following SQL queries to force failover of databases.
1. Check that the database is in synchronous mirroring mode:
   ALTER DATABASE dbname SET SAFETY FULL;
   GO
2. Failover the database (this is an unplanned database failover, performed when the Principal is offline, which may result in data loss, hence the command name):
   ALTER DATABASE dbname SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
   GO

Replace the field dbname with the name of each database.

3. After failing over the database, verify that the Lync databases are in “Principal, Synchronized” mode on the mirror node (SQL02).

At this point, we can check the Front End (FE) service on our Front End servers and test signing in with the Lync client.

How Lync Server Behaves

In the next instalment, I’ll talk more about what weird behaviour was observed and how I tried to get Lync to use the SQL mirror node. I encountered loads of problems, but eventually got the Lync Front End service to use the SQL server we’d failed the databases over to. Make sure you subscribe to get updated!

I gave you guys a heads up on this a few weeks ago that I’d looked into this in some fairly significant detail and found some interesting behaviour when you attempt to failover after deploying Lync Server 2010 backend and Group Chat databases using SQL Server Database Mirroring. Now that I’ve completed this work, I can share the results with you in a multi-part blog series.

Questions have always been asked over the years as to why organisations can’t use SQL Database Mirroring as a lower cost alternative to SQL Failover Clustering and the only advice we’ve been able to give is what TechNet documentation provides:  “Lync Server 2010 does not support native database mirroring“.

Based on the work I’ve done, I can tell you what happens when you attempt to failover, what you have to do to actually get to a “recovered” situation and reasons why you won’t want to use SQL Database Mirroring with Lync.

The Supported Scenario Today

Why SQL Database Mirroring?

Database mirroring was a new feature delivered as part of SQL Server 2005. The data replication occurs at an individual database level rather than at an instance level in clustering, providing greater flexibility at the expense of a higher management/configuration overhead. Unlike clustering, the system databases e.g. master db cannot be replicated using Database Mirroring. This means that you need to recreate logins, security etc manually on the mirrored node.

The mirroring process transports logs over its own TCP/IP session on port 5022 and uses compression by default. An important point to note is that SQL Server Standard Edition is a single threaded operation whereas SQL Server Enterprise Edition is a multi-threaded mirroring process.

One of the greatest benefits of database mirroring is its flexibility. An example of this is that an administrator can effortlessly switch principal and mirror roles back and forth, failing over between the two nodes. Plus there is no dependency on identical/near-identical hardware, disk, heartbeat networks etc that make traditionally make clustering a hinderance. Think of it like SCR was to Exchange Server 2007.

Compelling Reasons for Database Mirroring

I can definitely see why an organisation would want to utilise mirroring rather than clustering, especially when attempting to design site resiliency. The top reasons being that it is:

• Cheaper.
• Represents less infrastructure complexity.
• Better for site resiliency as there are no tight network and storage requirements. <— No 1 reason.

Test Environment

As part of this investigation, I deployed the following machines to test this configuration:

• One Lync Server 2010 Enterprise Edition pool consisting of two Front End servers (named FE01 and FE02).
• Two Lync Server 2010 Group Chat servers (GC01 and GC02).
• Two SQL Server 2008 R2 servers fulfilling the principal (named SQL01) and mirror (named SQL02) roles for Database Mirroring.

Lync and SQL Configuration

After deploying my servers, I got them ready to define the topology and install Lync. I’m not going to go into loads of detail on the configuration of SQL Database Mirroring here because it’s well covered on TechNet and it’s all GUI driven. I completed the database parts in the following way:

1. Specified the location for the back end databases for the Front End pool as the principal SQL server (SQL01) in Topology Builder and published.
2. Setup my Group Chat database and permissions on SQL01 and specified this server in the Group Chat Installation Wizard.
3. Ensured the Lync backend databases were successfully deployed onto this server.
4. Verified all sign-in functionality with both the Lync and Group Chat clients.
5. Configured SQL Database Mirroring using the SQL Server 2008 R2 Management Studio GUI to mirror all databases to SQL02.
6. Verified that all databases were in a Principal, Synchronized status on SQL01 and Mirrored, Synchronizing on SQL02.

The Next Chapter

Once we’re at this stage, we’re good to start failing the databases over to the mirror node to see how Lync behaves.

Be sure to subscribe and come back for the next part where it gets interesting. Services started failing over the shop, TCP connections were being automagically redirected to the SQL mirror node and there’s lots of the Lync client being in “limited functionality mode”. Stay tuned. 🙂