Configure availability by using SQL Server database mirroring (SharePoint Server 2010)

Article
07/22/2014

Applies to: SharePoint Server 2010, SharePoint Foundation 2010

This article describes how to use high-availability database mirroring to configure availability within a farm for Microsoft SharePoint Server 2010. The article assumes that you are familiar with the concepts and terms presented in Plan for availability (SharePoint Server 2010).

Microsoft SQL Server database mirroring provides availability support by sending transactions directly from a principal database and server to a mirror database and server when the transaction log buffer for the principal database is written to disk. For availability within a Microsoft SharePoint Server 2010 farm, you use high-availability database mirroring, also known as high-safety mode with automatic failover. High-availability database mirroring involves three server instances: a principal, a mirror, and a witness. The witness server enables SQL Server to automatically fail over from the principal server to the mirror server. Failover from the principal database to the mirror database typically takes several seconds.

Within a SharePoint Server 2010 farm, mirroring can provide redundancy for the content and configuration databases, and for many service databases. Even if your databases are mirrored to the same server, each database fails over individually. The following figure shows how mirroring is configured to provide availability within a SharePoint Server 2010 farm.

Diagram of mirroring all databases within a farm

SharePoint Server 2010 is mirroring-aware. To use mirroring in your environment, first configure mirroring, and then set the failover database value in SharePoint Server.

In this article:

Before you begin
Configure high-availability database mirroring
Configure SharePoint 2010 Products to be aware of mirrored databases
User experience during a failover

Before you begin

Before you begin to configure mirroring, make sure that your database administrator is aware of the following requirements and supported topologies.

Database mirroring requirements

Become familiar with the recommendations in the following list, and ensure that your databases and system meet any requirements before you configure database mirroring for a SharePoint Server environment:

We recommend that your system have latency no more than 1 millisecond.
System bandwidth should preferably be 1 gigabyte (GB) per second.
Logs are copied in real time between the principal and the mirror servers, and copying can affect performance. Make sure that you have sufficient memory and bandwidth on both the principal and mirror server.
The principal server and mirror server must run the same version and edition of SQL Server, and they must run in the same language. Database mirroring is available only in the Standard, Developer, and Enterprise editions. The witness server can run any version of SQL Server, including SQL Server 2008 Express.
Mirroring works only with databases that use the full recovery model.

By default, SharePoint Server 2010 databases are configured to use the simple recovery model. To configure database mirroring, the recovery model of the database must be set to Full. For information about how to set the recovery model for a database, see How to: View or Change the Recovery Model of a Database (SQL Server Management Studio) (https://go.microsoft.com/fwlink/p/?LinkId=132075&clcid=0x409).
If you plan to mirror databases, consider that the size of the transaction logs for these databases may become very large. To work around this, you can establish a recovery plan that truncates transaction logs as necessary. For more information, see the following article in the Microsoft Knowledge Base: How to stop the transaction log of a SQL Server database from growing unexpectedly (https://go.microsoft.com/fwlink/p/?LinkId=111458&clcid=0x409).
Every database mirroring session creates at least two threads for each database. Ensure that your database server has enough threads to allocate for mirroring all the supported databases. If you have insufficient threads, performance can decrease as more databases are added to a session.

For more information about performance for database mirroring, see Database mirroring best practices and performance considerations (https://go.microsoft.com/fwlink/p/?LinkId=185119).

https://technet.microsoft.com/en-us/library/ff872145(v=office.14) If you will be configuring mirroring for Microsoft Project Server 2010 databases, see Configure availability by using SQL Server database mirroring (Project Server 2010) for information specific to Project Server.

Security associated with database mirroring

Database mirroring uses TCP sessions to transport the transaction log from one server to another and to monitor the current health of the system for automatic failovers. Authentication is performed at the session level when a port is opened for connection. Database mirroring supports both Windows authentication (NTLM or Kerberos) and certificates.

Unless the network is secure, the data transmitted during the session should be encrypted. Database mirroring supports both Advanced Encryption Standard (AES) and RC4 encryption algorithms. For more information about the security associated with database mirroring, see Database Mirroring Transport Security (https://go.microsoft.com/fwlink/p/?LinkId=83569&clcid=0x409).

SharePoint 2010 Products security and mirrored servers

When you set up a mirrored database, the SQL Server logins and permissions for the database to be used with a SharePoint farm are not automatically configured in the master and msdb databases on the mirror server. Instead, you must configure the permissions for the required logins. These include, but are not limited to, the following:

The Central Administration application pool account should be a member of the dbcreator and securityadmin fixed server roles.
All application pool accounts, the default content access accounts, and any accounts required for service applications should have SQL Server logins, although they should not be assigned to SQL Server fixed server or fixed database roles.
Members of the Farm Administrators SharePoint group should also have SQL Server logins and should be members of the same SQL Server roles as the Central Administration application pool account.

We recommend that you transfer your logins and permissions from the principal server to the mirror server by running a script. An example script is available in Knowledge Base article 918992 How to transfer the logins and the passwords between instances of SQL Server 2005 (https://go.microsoft.com/fwlink/p/?LinkId=122053&clcid=0x409). For more information about how to transfer SQL Server metadata between instances, see the SQL Server Books Online article Managing Metadata When Making a Database Available on Another Server Instance (https://go.microsoft.com/fwlink/p/?LinkId=122055&clcid=0x409).

Supported topologies

We recommend that you maintain a one-to-one mapping of principal server and database instance to mirror server and database instance to ensure compatibility with SharePoint Server 2010.

The supported topologies include mirroring all content databases, the configuration database, the Central Administration content database, and the service application databases except for the Web Analytics Staging database and the User Profile Synchronization database.

Note

We do not recommend that you mirror the Usage and Health Data Collection Logging database. A SharePoint environment can continue to run if this database fails, and this data can be quickly regenerated.

Avoid topologies that do not have matching principal server and database instances and mirror server and database instances. Also, keep the configuration database and the administration content database on the same server.

Configure high-availability database mirroring

We recommend that a SQL Server database administrator configure high-availability mirroring for a production environment. For a test environment, we have provided Transact-SQL scripts that you can use to configure your environment. For more information, see Sample script for configuring SQL Server mirroring (SharePoint Foundation 2010).

Configure SharePoint 2010 Products to be aware of mirrored databases

To make SharePoint Server 2010 aware that failover mirrored databases exist, perform the following procedure for all configuration and content databases.

Note

We recommend that you use Windows PowerShell cmdlets to set failover database values. Although you can use the Central Administration Web site to set some failover database values, you cannot use it for all databases.

To configure SharePoint 2010 Products to be aware of mirrored databases by using Windows PowerShell

Verify that you meet the following minimum requirements: See Add-SPShellAdmin.
On the Start menu, click All Programs.
Click Microsoft SharePoint 2010 Products.
Click SharePoint 2010 Management Shell.
At the Windows PowerShell command prompt, type the following commands, and then press ENTER:

$db = get-spdatabase | where {$_.Name -eq "database name"}

$db.AddFailoverServiceInstance("mirrored database name")

$db.Update()

For more information, see Get-SPDatabase.

User experience during a failover

While SQL Server is switching to using a mirrored database, users of a SharePoint site that runs against the database may experience brief connectivity issues and data loss.

Monitoring and troubleshooting mirroring

To monitor the status and performance of mirroring within a farm, database administrators can use the Database Mirroring Monitor. Monitoring enables you to determine whether and how well data is flowing in the database mirroring session. Database Mirroring Monitor is also useful for troubleshooting the cause of reduced data flow. For more information, see Database Mirroring Monitor Overview (https://go.microsoft.com/fwlink/p/?LinkId=185068). Another resource to use in troubleshooting is the SQL Server Books Online article Troubleshooting Database Mirroring Setup (https://go.microsoft.com/fwlink/p/?LinkId=185069).