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!