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:

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:

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:

 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:

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:

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.

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!

 

So, my current place of employment has recently upgraded to SharePoint 2010.  Seriously, don't laugh.  In any case, our environment is fairly InfoPath heavy.  All existing forms were created with InfoPath 2007 as client based forms.  Some of the issues with client based forms include:

I decided to upgrade the forms to SharePoint 2010 web based forms.  The first step was to find all the forms we had on our portal.

The following script will crawl a portal and return this information:

# Title:   ListInfoPathForms.ps1
# Version: 1.0, 22MAR14
# Author:  James Sanders
# Purpose: Find and list all InfoPath forms
 
# NOTE: To export to CSV, run ListInfoPathForms.ps1|Export-Csv <filename>.txt
 
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
 
$WebApp = Get-SPWebApplication -Identity http://your_web_app
$Count = 0
 
ForEach ($SPSite in $WebApp.Sites) {
  ForEach ($Web in $SPSite.AllWebs) {
    For ($i = 0;$i -ne $Web.lists.count;$i++) {
      $List = $Web.Lists[$i]
      If ($List.BaseTemplate -eq "XMLForm" -and $list.BaseType -eq "DocumentLibrary") {
        $O = New-Object PSObject
        $O | Add-Member NoteProperty Site $($List.ParentWeb.Title)
        $O | Add-Member NoteProperty URL $($List.ParentWeb.URL)
        $O | Add-Member NoteProperty Title $($List.Title)
        $Count++
        $O
      }
    }
  }
}

 

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