SharePoint 2010 Database Mirroring with Automatic Failover

By James|04/21/2014|, ,

Introduction

There are lots of resources available online that discuss SharePoint database mirroring.  In this article I will mention a few of these resources, toss in my own personal experience and leave you with a few T-SQL scripts that may prove useful in your mirroring efforts.

Resources

Here are two TechNet articles that I found useful when planning for and implementing database mirroring for SharePoint 2010:

Hardware

To support automatic failover, three SQL servers will be required:

  • Principal - The SQL server that will be providing data to the SharePoint farm on a daily basis
  • Mirror - The SQL server that will provide data to the SharePoint farm when disaster strikes
  • Witness - The SQL server that enables the mirror server to recognize when to initiate an automatic failover.  Automatic failover is not possible without a witness server.

The principal and mirror servers should either be identical or closely resemble each other with regards to processor speed, number of cores, network connectivity, hard drive space and type, etc.  The witness server does not actually store or serve data.  As such, the witness server does not need to closely resemble the principal/mirror server.

Software

The operating system and SQL server version should be the same on all three SQL servers.  For this article, the environment is running Windows Server 2008 R2 and SQL Server 2008 R2.  All systems are running with the same operating system and SQL Server 2008 R2 service packs and patches.

Configure SharePoint Mirroring

Select Databases for Mirroring

Almost all of the SharePoint SQL databases can be mirrored.  The following databases should NOT be mirrored:

  • Web Analytics Staging database
  • User Profile Synchronization database
  • Usage and Health Data Collection Logging database

If disaster strikes, the User Profile service application can be re-provisioned and configured to use the data stored in the profile and social databases (which should be mirrored).  A new User Profile Synchronization database will be created.  The Web Analytics service application will need to be re-provisioned.  The Usage and Health service application will need to be re-provisioned.  A SharePoint farm will continue to function without these databases, so re-provision at your convenience after a disaster.

Prepare SQL Principal Server

By default, SharePoint 2010 databases run in SIMPLE recovery mode.  For mirroring to work properly, the databases must be changed to FULL recovery mode.  This can be checked/changed as follows:

  • Open the SQL Server Management Studio (SSMS) on the principal server and connect to the database engine
  • Expand Databases
  • Right click on a SharePoint database and select Properties
  • Select Options and look at the "Recovery model" setting
  • If necessary, change the recovery model to 'Full'
  • Click OK to save changes and close the properties dialog

 Prepare SQL Mirror Server

On the principal server, create a full backup of the database and transaction log of each database that will be mirrored.  Restore each database and log to the mirror server.  When restoring the database and log, change the restore options to restore with NORECOVERY.

Start Mirroring

Once the databases are restored to the mirror server, perform the following steps to configure mirroring:

  • Open SQL Server Management Studio (SSMS) on the principal SQL server
  • Expand databases, find the database to mirror, right click and select Tasks -> Mirror
  • Click "Configure Security" to launch the mirroring setup wizard
  • For the "Include Witness Server" section, ensure "YES" is selected to include a witness server instance and click Next
  • For the "Choose Servers to Configure" section, ensure all instances are checked (Principal, Mirror, Witness) and click Next
  • For the "Principal Server Instance" section, select the appropriate server and click Next
  • For the "Mirror Server Instance" section, select the appropriate server and click Next
  • For the "Witness Server Instance" section, select the appropriate server and click Next
  • For the "Service Accounts" section, specify applicable service accounts for each server (Principal, Witness, Mirror).  I use the same domain service account on all three servers, so for me each entry was the same.
  • For the "Complete the Wizard" section, click Finish.  If necessary, mirroring endpoints will be created on the SQL servers
  • For the "Configuring Endpoints" section, review the results and click Close
  • Click "Start Mirroring" to start mirroring the database
  • Click "OK" to close the "Database Properties" dialog

Repeat the same steps for each database being mirrored.  Mirrored databases will display on the principal SQL server as Database_Name (Principal, Synchronized).  They will display on the mirror SQL server as Database_Name (Mirror, Synchronized / Restoring).

Copy SQL Logins

All logins and passwords on the principal SQL server should be copied to the mirror server.  Microsoft Support has an article here that shows how to do this.  Basically:

  1. Copy the T-SQL script from the article
  2. Execute the script on the PRINCIPAL SQL server
  3. The script will generate another script in the query output window
  4. Execute the generated script on the mirror server

Make SharePoint 2010 Mirroring Aware

In SharePoint 2010, the mirror server is called a "failover instance".  The content databases can be configured for failover by using Central Administration or PowerShell.  To configure the content databases for failover using Central Administration:

  • Start Central Administration
  • Select Application Management -> Manage content databases
  • Click the content database name
  • Fill in the "Failover Database Server" with the name of the mirror server
  • Click OK
  • Repeat for each content database

PowerShell can also be used to configure failover for SharePoint 2010 content databases.  Further, PowerShell MUST be used for non-content databases such as the configuration database, service application databases, etc.  To configure SharePoint 2010 databases for failover using PowerShell:

  • Start the SharePoint 2010 Management Shell
  • To get a list of all SharePoint 2010 databases, execute "Get-SPDatabase|select name"
  • To get a list of all SharePoint 2010 databases including database type, execute "Get-SPDatabase|select name, type"
  • To assign a failover instance to a database:
    • $db = Get-SPDatabase | Where {$_.Name -eq "Name_Of_Database"}
    • $db.AddFailoverServerInstance("SQL_Mirror_Server_Name")
    • $db.Update()
  • For example, if your SQL mirror server is named "CompanySQL20" and your configuration database is named "SharePointConfig":
    • $db = Get-SPDatabase | Where {$_.Name -eq "SharePointConfig"}
    • $db.AddFailoverServerInstance("CompanySQL20")
    • $db.Update()
  • Repeat these steps for all databases that are being mirrored

To verify SharePoint failover configuration, execute the following:

  • Get-SPDatabase|select name, failoverserver

Databases that have been configured with failover instances will appear with SPServer Name="SQL_Mirror_Server_Name" under the failoverserver column.

Test Mirroring

Mirroring is active, SharePoint 2010 knows all about it, so all is well, right?!  Don't wait for an actual disaster to test your recovery plan.  The easiest way to find out if mirroring and automatic failover is actually working is to stop the SQL services on the principal SQL server.  If it's a good day, SQL services will fail over to the mirror server and the SharePoint farm will remain up.  Before attempting this feat of IT prowess, please read the next section for some handy T-SQL scripts that will assist with mirroring!

Mirroring Scripts

When mirroring with automatic failover works, it works very well.  There is a fairly short delay (90 seconds I believe) between the time the principal server goes down and the mirror server takes over.  Keep this in mind when performing maintenance on the principal server.  You probably don't want automatic failover to kick in while patching or rebooting your principal server.  While failover is automatic, fail back is not.  Databases must be manually failed back from the mirror server to the principal server.

I have created three T-SQL scripts to assist with mirroring.  Here they are:

SuspendMirroring.sql - Place this script on your principal SQL server.  The script will find all mirrored databases and suspend mirroring.  Execute this script before performing maintenance on your server to prevent automatic failover.

/*
NAME:    SuspendMirroring.sql
TITLE:   Suspend Database Mirroring
PURPOSE: This script suspends mirroring for all mirrored databases
VERSION: 1.0, 12APR14
AUTHOR:  James Sanders

Variable declarations

@db_name	Database name
@db_mirror	Database mirroring state
@cmd		Statement to be executed

*/

DECLARE @db_name NVARCHAR(128),
@db_mirror NVARCHAR(60),
@cmd NVARCHAR(2000) 

-- Find all mirrored databases
DECLARE db_cursor INSENSITIVE CURSOR
FOR SELECT d.name,mirroring_state_desc
FROM sys.database_mirroring M
inner join SYS.DATABASES d on m.database_id = d.database_id
where mirroring_state_desc is not null

-- Open the cursor and fetch the first record
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @db_name, @db_mirror

-- Loop through the databases
WHILE @@fetch_status = 0
BEGIN
    -- Suspend Mirroring
    SELECT @CMD = 'ALTER DATABASE ' + @db_name + ' SET PARTNER SUSPEND'
    EXEC(@CMD)

     -- Fetch next record from cursor
    FETCH NEXT FROM db_cursor
    INTO @db_name, @db_mirror
END

-- Close and deallocate cursor
CLOSE db_cursor
DEALLOCATE db_cursor

ResumeMirroring.sql - Place this script on your principal SQL server.  The script will find all mirrored databases and resume mirroring.  Execute this script after your principal SQL server is back up and running.

/*
NAME:    ResumeMirroring.sql
TITLE:   Resume Database Mirroring
PURPOSE: This script resumes mirroring for all mirrored databases
VERSION: 1.0, 12APR14
AUTHOR:  James Sanders

Variable declarations

@db_name	Database name
@db_mirror	Database mirroring state
@cmd		Statement to be executed

*/

DECLARE @db_name NVARCHAR(128),
@db_mirror NVARCHAR(60),
@cmd NVARCHAR(2000) 

-- Find all mirrored databases
DECLARE db_cursor INSENSITIVE CURSOR
FOR SELECT d.name,mirroring_state_desc
FROM sys.database_mirroring M
inner join SYS.DATABASES d on m.database_id = d.database_id
where mirroring_state_desc is not null

-- Open the cursor and fetch the first record
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @db_name, @db_mirror

-- Loop through the databases
WHILE @@fetch_status = 0
BEGIN
    -- Resume Mirroring
    SELECT @CMD = 'ALTER DATABASE ' + @db_name + ' SET PARTNER RESUME'
    EXEC(@CMD)

     -- Fetch next record from cursor
    FETCH NEXT FROM db_cursor
    INTO @db_name, @db_mirror
END

-- Close and deallocate cursor
CLOSE db_cursor
DEALLOCATE db_cursor

FailbackMirroring.sql - Place this script on your mirror SQL server.  The script will find all mirrored databases and fail them back to the original principal server.

/*
NAME:    FailbackMirroring.sql
TITLE:   Failback Database Mirroring
PURPOSE: Fail databases back to principal SQL server
VERSION: 1.0, 12APR14
AUTHOR:  James Sanders

Variable declarations

@db_name	Database name
@db_state	Database mirroring state
@db_role	Database mirroring role
@cmd		Statement to be executed

*/

DECLARE @db_name NVARCHAR(128),
@db_state NVARCHAR(60),
@db_role NVARCHAR(60),
@cmd NVARCHAR(2000) 

-- Find all mirrored databases
DECLARE db_cursor INSENSITIVE CURSOR
FOR SELECT d.name,mirroring_state_desc, mirroring_role_desc
FROM sys.database_mirroring M
inner join SYS.DATABASES d on m.database_id = d.database_id
where mirroring_state_desc is not null and mirroring_role_desc = 'PRINCIPAL'

-- Open the cursor and fetch the first record
OPEN db_cursor
FETCH NEXT FROM db_cursor
INTO @db_name, @db_state, @db_role

-- Loop through the databases
WHILE @@fetch_status = 0
BEGIN
    -- Resume Mirroring
    SELECT @CMD = 'ALTER DATABASE ' + @db_name + ' SET PARTNER FAILOVER'
    EXEC(@CMD)

     -- Fetch next record from cursor
    FETCH NEXT FROM db_cursor
    INTO @db_name, @db_state, @db_role
END

-- Close and deallocate cursor
CLOSE db_cursor
DEALLOCATE db_cursor

Conclusion

Hopefully this article will help you set up SharePoint 2010 mirroring with automatic failover.  If you have any questions, leave a comment and I'll get back to you.  Happy Mirroring!

 

Copyright 2011 - 2024 The Lazy IT Admin | All Rights Reserved
menu-circlecross-circle linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram