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
- 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.
- 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:
- 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)
- 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
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‘
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:
- Force-power off the Lync Front End and back-end SQL servers, or verify that these servers have failed.
- 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.
- Check that the database is in synchronous mirroring mode:
ALTER DATABASE dbname SET SAFETY FULL;
- 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;
Replace the field dbname with the name of each database.
- 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 see that you are mirroring the CMS Databases as well as the User data databases.
In my scenario I’m mirroring all the databases except for LIS and XDS since they are specific to CMS.
Thanks for commenting John. Yes those databases are specific to the CMS.
In this scenario I wanted to simulate a complete site failure where all services could be restored in the DR site, which meant getting CMS and LIS back up and running also.
Pingback: SQL Database Mirroring with Lync Server 2010 Series – Backend Databases | Justin Morris on UC