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.
Here are two TechNet articles that I found useful when planning for and implementing database mirroring for SharePoint 2010:
To support automatic failover, three SQL servers will be required:
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.
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.
Almost all of the SharePoint SQL databases can be mirrored. The following databases should NOT be mirrored:
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.
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:
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.
Once the databases are restored to the mirror server, perform the following steps to configure mirroring:
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).
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:
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:
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:
To verify SharePoint failover configuration, execute the following:
Databases that have been configured with failover instances will appear with SPServer Name="SQL_Mirror_Server_Name" under the failoverserver column.
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!
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
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!