Partager via

Configure and manage SQL Server availability groups for SharePoint Server

  • Article

 

Dernière rubrique modifiée : 2016-11-30

This article describes how to configure and manage AlwaysOn Availability Groups to enable high availability for a SharePoint Server 2010 farm.

The article assumes the following:

  • You are familiar with the concepts and terms presented in Planifier la disponibilité (SharePoint Server 2010),

  • You have a working knowledge of Windows Server Failover Clustering.

  • You have a working knowledge of the Microsoft SQL Server 2012 high availability features.

In this article:

  • About AlwaysOn and availability groups

  • Requirements and prerequisites

  • Installation and configuration overview

  • Detailed configuration steps

  • Failover testing

  • Monitoring an AlwaysOn environment

About AlwaysOn and availability groups

SQL Server 2012 AlwaysOn provides a new enterprise-level high availability and disaster recovery solution. This solution supports the following two configurations for high availability:

  • AlwaysOn Availability Group

    Enables failover for multiple databases

  • AlwaysOn Failover Cluster instance

    Enables failover at the instance level

From a SharePoint Server perspective, AlwaysOn Availability Groups provide the optimum solution for high availability and disaster recovery.

AlwaysOn is not just a combination of database mirroring and database clustering. It is a completely new feature that co-exists with existing high availability and disaster recover options such as mirroring and log shipping. For an overview of this feature, see the AlwaysOn FAQ for SQL Server 2012 (https://go.microsoft.com/fwlink/p/?LinkId=245659). For a more details about the benefits of AlwaysOn Availability Groups and an overview of AlwaysOn Availability Groups terminology, see AlwaysOn Availability Groups (SQL Server) (https://go.microsoft.com/fwlink/p/?LinkId=245660).

An availability group enables you to specify a set of databases that you want to fail over together. When an availability group fails over to a target, all the databases in the group fail over as well. Because SQL Server 2012 can host multiple availability groups on a single server, you can configure AlwaysOn to fail over to SQL Server instances on different servers, which reduces the need to have idle standby servers to handle the full load of the primary server. The next illustration shows the main parts of an AlwaysOn Availability Group environment.

SQL Server AlwaysOn with Availability Group

Looking at the previous illustration, you can see the following:

  • Each availability group consists of a collection of read-write primary databases and supports one to four secondary databases that are a corresponding copy of the primary databases.

  • The primary databases and secondary databases are maintained by a primary replica and one to four secondary replica.

  • Each replica is hosted on a specific instance of SQL Server and maintains a local copy of each availability database that belongs to the availability group.

  • An availability group fails over at the level of an availability replica.

The primary replica makes the primary databases available for read-write connections from clients and sends transaction log records for each primary database to every secondary replica. Each secondary replica applies transaction log records to its secondary databases.

An availability group supports a failover environment for a discrete set of user databases, which are known as availability databases, which fail over together as a single unit. An availability group supports a set of primary databases and one to four sets of corresponding secondary databases. An availability group fails over at the level of an availability replica.

Notes

Database issues, such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log do not cause failovers.

The primary replica makes the primary databases available for read-write connections from clients and, also, sends transaction log records for each primary database to every secondary replica. Each secondary replica maintains a set of secondary databases. Every secondary replica applies transaction log records to its own set of secondary databases and serves as a potential failover target for the availability group. Optionally, you can configure one or more secondary replicas to support read-only access to secondary databases. You can configure any secondary replica to permit backups on secondary databases.

Deploying AlwaysOn Availability Groups requires a Windows Server Failover Clustering (WSFC) cluster. Each availability replica of a given availability group must reside on a different node of the same Windows Server Failover Clustering (WSFC) cluster.

Benefits

AlwaysOn provides many benefits and the following are noteworthy for a SharePoint environment:

  • Provides more granularities in managing databases as a group.

  • Supports alternative availability modes (asynchronous and synchronous) on group replicas.

  • Supports automatic, manual, and forced client failover.

  • Provides better utilization of server hardware by offloading read workloads to a secondary instance (additionally, offloading workloads and backups improves performance on the primary replica).

For a more details about the benefits of AlwaysOn Availability Groups, see AlwaysOn Availability Groups (SQL Server) (https://go.microsoft.com/fwlink/p/?LinkId=245660).

Requirements and prerequisites

The following minimum requirements and prerequisites are needed to deploy a SharePoint farm in an AlwaysOn environment.

SQL Server 2012

To create and use AlwaysOn Availability Groups you have to install SQL Server 2012 on a Windows Server Failover Clustering (WSFC) cluster.

Although configuring a WSFC cluster is out of the scope for this article, you should be aware of the following if you plan to set up a cluster:

  • All the cluster nodes must be in the same Active Directory Domain Services (AD DS) domain.

  • Each availability replica in an availability group must reside on a different node of the same Windows Server Failover Clustering (WSFC) cluster.

  • The cluster creator must have the following capabilities:

A very important aspect of configuring failover clustering and AlwaysOn is determining the quorum votes that are needed for the cluster nodes.

For more information, see WSFC Quorum Modes and Voting Configuration (SQL Server) ( https://go.microsoft.com/fwlink/p/?LinkId=245665) and Configure Cluster Quorum NodeWeight Settings ( https://go.microsoft.com/fwlink/p/?LinkId=245666).

We recommend that you fully understand the prerequisites and restrictions for configuring an AlwaysOn environment. For more information, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) (https://go.microsoft.com/fwlink/p/?LinkId=245668). This article provides the following information:

  • Windows System Requirements and Recommendations

  • Server Instance Prerequisites and Restrictions

  • Prerequisites and Restrictions for Using a SQL Server Failover Cluster Instance (FCI) to Host an Availability Replica

  • Availability Group Prerequisites and Restrictions

  • Availability Database Prerequisites and Restrictions

Notes

You can install SQL Server 2012 on Windows Server core, which has a reduced attack surface and requires fewer hot fixes and security updates. However, SharePoint Server cannot be installed on Windows Server core. For more information, see Server Core for Windows Server 2008 R2 [Server Core for Windows Server 2008 R2 (https://go.microsoft.com/fwlink/p/?LinkId=245669).

SharePoint Server 2010

In addition to the documented minimum hardware and software requirements, you must install Microsoft SharePoint Server 2010 avec Service Pack 1 (SP1) to deploy SharePoint Server on SQL Server 2012. We also recommend that you install the December cumulative update to take advantage of the performance gains that this update provides. For more information, see Description of the SharePoint Server 2010 cumulative update package (SharePoint server-package): December 13, 2011 (https://go.microsoft.com/fwlink/p/?LinkID=237662).

Installation and configuration overview

The following steps provide the recommended installation sequence and identify key configurations for deploying AlwaysOn for a SharePoint farm. Detailed procedures are provided in Detailed configuration steps.

Notes

This article assumes that the farm that you configure for high availability is a new deployment. This includes Windows Server 2008 R2 Enterprise or Windows Server 2008 R2 Datacenter, SQL Server 2012, and Microsoft SharePoint Server 2010.

Create an AlwaysOn environment for a farm

  1. Select or create a Windows Server failover cluster. For more information, see Failover Clusters (https://go.microsoft.com/fwlink/p/?LinkID=142423)

  2. Install the prerequisites for SQL Server 2012 on each cluster node. For more information, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) (https://go.microsoft.com/fwlink/p/?LinkID=245668)

  3. Install SQL Server 2012 on each cluster node.

  4. Configure SQL Server 2012 for using AlwaysOn Availability Groups.

  5. Create a temporary user database or select an existing database.

  6. Create a network share for synchronization.

  7. Create a new availability group.

  8. Create an availability group listener.

  9. Install Microsoft SharePoint Server 2010 avec Service Pack 1 (SP1).

    For more information, see the “Service Pack 1 (SP1)” section of Updates for SharePoint 2010 Products (https://go.microsoft.com/fwlink/p/?LinkID=209614).

  10. Create the SharePoint farm.

  11. Add the SharePoint databases to the availability group.

  12. Conduct failover tests

Detailed configuration steps

The following illustration shows a SharePoint Server 2010 farm (SPHA_farm) that uses an availability group named SP_AG1. We'll use this farm as an example for configuring AlwaysOn.

Availability group and replicas for farm

For ease of use, we divide the detailed configuration steps to deploy the test farm in an AlwaysOn environment into the following major phases:

  • Create a Windows Server failover cluster.

  • Install and configure SQL Server 2012.

  • Create an availability group.

  • Create an availability group listener.

    Notes

    For information about connecting to databases, refer to the Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server) article.

  • Install and configure SharePoint Server 2010.

  • Add the SharePoint databases to the availability group.

  • Conduct failover tests

Set up the Windows Server cluster environment

Obtain access to, or create a three node Windows Server Failover Clustering (WSFC) cluster that you can use for installing SQL Server 2012 on each node.

Install and configure SQL Server 2012

Install SQL Server 2012. For installation instructions see, Installation for SQL Server 2012 (https://go.microsoft.com/fwlink/p/?LinkId=245717).

You must take into account the SharePoint Server 2010 database requirements. Refer to the following articles:

Important

You must deploy and configure SQL Server 2012 the same on each server that will be in the availability group.

Install SQL Server prerequisites

Install SQL Server 2012 prerequisites on each cluster node. For more information, see Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server) (https://go.microsoft.com/fwlink/p/?LinkID=245668)

Enable Named Pipes and AlwaysOn High Availability

Use the following procedures to enable Named Pipes and AlwaysOn High Availability. These configurations have to be done on each server that will be part of the availability group.

Create an availability group

The following procedures are required to prepare the SQL Server environment and then create an availability group.

To create the availability group, at least one user database must exist. For more information, see Creation and Configuration of Availability Groups (SQL Server) (https://go.microsoft.com/fwlink/p/?LinkId=245720).

Use the following procedure to create a temporary user database that will be the primary replica for the group.

Create a temporary user database

  1. Log on to the server that will host the primary replica, which is SPVNEXT-SRV1 in our example. You require one of the following permissions in the master database in order to create the new database: CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE.

  2. Start SQL Server Management Studio.

  3. In Object Explorer, right-click Databases and then click New Database.

  4. In the New Database dialog box, type the Database name:, which is "TemporaryUserDB" in our example. Because this is a temporary database that gets deleted after we create the availability group, we can use the default settings. Click OK.

    Because the New Availability Group Wizard will not create an availability group unless the user database has been backed up, you have to back up the temporary database.

  5. In Object Explorer expand Databases and right-click the temporary database that you just created. Pick Tasks and then pick Back Up.

  6. In the Back Up Database dialog box, click OK to accept all the default settings and create the back up.

  7. Repeat steps 1 through 6 and specify the Database name: as "TemporaryUserDB2" for the second temporary database.

A network share must exist and must be accessible by all the nodes in the AlwaysOn configuration to do the initial data synchronization between all the cluster nodes that host a replica. For more information, see Network Shares Extension (https://go.microsoft.com/fwlink/p/?LinkId=245722) and File Services (https://go.microsoft.com/fwlink/p/?LinkId=163091).

About replicas

Every availability replica is assigned an initial role—either the primary role or the secondary role, which the availability databases of that replica inherit. The role of a given replica determines whether it hosts read-write databases or read-only databases as well as the type of failover and the type of commit.

The following table shows the information that you have to provide for each replica, either when you first create the availability group, or later when you are adding secondary replicas.

Replica information Description

Server Instance

Displays the name of the server instance that will host the availability replica.

Initial Role

Indicates the role that the new replica will initially perform: Primary or Secondary.

Automatic Failover (Up to 2)

Indicates the type of failover that the replica uses: automatic or manual.

Synchronous Commit (Up to 3)

Indicates the type of commit that is used for the replica.

Readable Secondary

Indicates whether or not a secondary replica can be read.

The configuration options are unavailable for read access, read-only, and read-only intent. For more information, see Readable Secondary Replicas (AlwaysOn Availability Groups) (https://go.microsoft.com/fwlink/p/?LinkId=245736).

Important

Readable secondaries are not currently supported for SharePoint Server 2010 runtime usage.

Notes

When you add replicas to a group, you will also provide the endpoint for each replica and configure backup preferences. For more information, see Specify the Endpoint URL When Adding or Modifying an Availability Replica (SQL Server) (https://go.microsoft.com/fwlink/p/?LinkId=245737) and Backup on Secondary Replicas (AlwaysOn Availability Groups) (https://go.microsoft.com/fwlink/p/?LinkId=208213).

Data synchronization

As part of the availability group creation process, you will have to synchronize the data on the primary replica with the data on the secondary replicas. The following restrictions exist for using the New Availability Group wizard to start data synchronization:

  • If the file paths on the secondary replica location are different from the file paths on the primary location, you have to start data synchronization manually.

  • If any secondary database exists on a secondary replica, you have to manually delete the secondary databases before you start data synchronization in the New Availability Group. However, if you want to use existing secondary databases, exit the New Availability Group wizard and start data synchronization manually.

  • To use the availability group wizard to synchronize data, you have to have a backup share that all the replicas can write to. You can specify the share by browsing to it or by entering its fully qualified universal naming convention (UNC) path name, \\Systemname\ShareName\Path\, in the Specify a shared network location accessible by all replicas box.

For each database in the availability group, the Start Data Synchronization page displays the progress of the following operations:

  • Creating a full database backup of the primary database on the network share.

  • Creating a full database backup of the primary database on the network share.

  • Restoring these backups onto the secondary replica location. These restore operations both use RESTORE WITH NORECOVERY option and leave the new secondary database in the RESTORING state.

  • Joining the secondary database to the availability group. This step puts the secondary database in the ONLINE state and starts data synchronization for this database.

Use the following procedure to create an availability group on the primary replica, which is SPVNEXT-SRV1 in our example.

Login replication

Microsoft SQL Server 2008 R2 stores logins in the MasterDB database, which is not replicated in Availability Group. Although the farm accounts are created when replicas are synchronized, login information is not available after a failover. The work around is to manually copy the logins to the secondary replicas of an Availability Group.

Microsoft SQL Server 2012 introduces the concept of Users with Passwords for Contained Databases. The database itself stores all the database metadata and user information, and a user who is defined in this database does not need to have a corresponding login. The information in this database is replicated by Availability Group and is available after a failover. For more information, see Contained Databases (https://go.microsoft.com/fwlink/p/?LinkId=262794).

Important

If you have already created an Availability Group and synchronized the primary and secondary replicas, the work around is to manually copy the logins from the primary replica to the secondary replicas.

Create the availability group

  1. Log on to the server that will host the primary replica and start SQL Server Management Studio.

    Permission to create an availability group requires membership in the db_owner fixed database role and either CREATE AVAILABILITY GROUP server permission, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

  2. To start the New Availability Group Wizard, right-click AlwaysOn High Availability and then click New Availability Group Wizard.

  3. Click Next to advance to the Specify Name page. Enter SP-AG1 as the name of the new availability group in the Availability group name: box. This name must be: a valid SQL Server identifier, unique on the Windows Server Failover Clustering cluster and unique on the domain.

  4. On the Select Databases page, all user databases that are eligible to become the primary database for the new availability group are listed on the User databases on this instance of SQL Server grid. Pick TemporaryUserDB, and then click Next.

  5. On Specify Replicas page, use the following tabs to configure the replicas for SP-AG1: Replicas, Endpoints, and Backup Preferences.

  6. On the Listener tab, configure an availability group listener for our example. An availability group listener is a server name to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. Availability group listeners direct incoming connections to the primary replica or to a read-only secondary replica. The listener provides fast application failover after an availability group fails over. For more information, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

  7. Select the desired configuration for each instance in the Selected instances grid. For the “SP-AG1” HA only Availability Group, select Synchronous Replication and Automatic Failover on both nodes. Click Next.when finished.

  8. Click Finish to create the Availability Group.

  9. The Select Initial Data Synchronization page lets you select a synchronization preference and specify the shared network location that all replicas can access. For our environment we'll accept the default, Full, which performs full database and log backups. Click Next.

  10. The Validation page of the wizard displays the results of six checks before letting you continue with availability group creation. If all checks pass, you can click Next to continue. If any tests fail, the wizard prevents you from continuing until you correct the error and run the validation tests again by clicking Re-run Validation. When all the tests pass, click Next to continue.

  11. On the Summary page, verify the configuration of the replica that you are adding and click Finish if you are satisfied with the configuration and want to keep it. If not, you can click Previous to return to previous wizard pages.

Install and configure SharePoint Server 2010

At this point in the process, you can install SharePoint Server 2010 and create the farm. Use the steps in the following general procedure to install and configure SharePoint Server.

Install and configure SharePoint Server 2010

  1. Download and install Microsoft SharePoint Server 2010 avec Service Pack 1 (SP1) and the June 2011 cumulative update refresh. For more information, see the “Service Pack 1 (SP1)” section of Updates for SharePoint 2010 Products (https://go.microsoft.com/fwlink/p/?LinkID=209614). To install the service pack you need the same permissions that are required to install SharePoint Server 2010. For more information, see Comptes d’administration et de service (SharePoint Server 2010).

  2. Run the Outil de préparation des produits Microsoft SharePoint, which installs all required prerequisites to use SharePoint Server.

  3. Run Setup, which installs binaries, configures security permissions, and edits registry settings for SharePoint Server 2010.

  4. Run Assistant Configuration des produits SharePoint, which installs and configures the configuration database, the content database, and installs the SharePoint Central Administration Web site.

    When you run the wizard, you have to identify the server that will host the SharePoint databases. On the Specify Configuration Database Settings page, in the Database server box, type SP-AG1AP as the name of the computer that is running SQL Server.

  5. Configure browser settings.

  6. Run the Farm Configuration Wizard, which configures the farm, creates the first site collection, and selects the services that you want to use in the farm.

  7. Perform post-installation steps.

Add the SharePoint databases to the availability group

The final phase of setting up AlwaysOn for a SharePoint Server 2010 farm entails adding the SharePoint databases to the availability group and synchronizing secondary replicas to the primary replica.

Notes

SharePoint 2010 Web application content databases are the only databases that are supported for Asynchronous replication to a DR location. Therefore they are the only databases that can be added to the SP-AG2 availability group. Configuration and Service application databases in SharePoint 2010 only support Synchronous replication for High Availability so these must be added to SP-AG1.

On the server that hosts the primary replica, you have to run the Add Databases to Availability Group wizard to add all the SharePoint databases to the availability group. The procedure is the same as the procedure for creating an availability group.

Add databases to the availability group

  1. Log on to the server that will host the primary replica and start SQL Server Management Studio. The account you use must be a member of the Local Administrators group for each server where SharePoint Server 2010 will be installed as well as ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission. In addition, joining a database to an availability group requires membership in the db_owner fixed database role.

  2. In Object Explorer, navigate to, and if necessary expand the Availability Groups.

  3. Right-click the example group, SP-AG1, and then click Add Database.

  4. On the Select Databases page, all user databases that are eligible to become the primary database for the new availability group are listed on the User databases on this instance of SQL Server grid. Use the checkboxes to identify all the databases that you want to add to the group, and then click Next.

  5. The Select Initial Data Synchronization page lets you select a synchronization preference and specify the shared network location that all replicas can access. For our environment we'll accept the default, Full, which performs full database and log backups. Click Next.

  6. The Validation page of the wizard displays the results of six checks before it lets you continue with availability group creation. If any tests fail, the wizard prevents you from continuing until you correct the error and run the validation tests again by clicking Re-run Validation. When all the tests pass, click Next to continue.

  7. On the Summary page, verify the configuration of the replica that you are adding, and then click Finish if you are satisfied with the configuration and want to keep it. If you want to change the configuration, click Previous to return to previous wizard pages.

  8. Repeat steps 1 through 7 for any content databases that are required to be in a Highly Available and Disaster Recovery (HADRON) availability group. SP-AG2, in this case is the HADRON capable availability group.

Failover testing

Conduct extensive failover tests to ensure that the behavior of the AlwaysOn environment is as expected and that you completely understood the process . These tests include and are not limited to: verifying that all the farm services and features are completely functional and that SharePoint Server 2010 data is preserved and not corrupted.

You can test an AlwaysOn failover by using either the planned manual failover described in Perform a Planned Manual Failover of an Availability Group (SQL Server) (https://go.microsoft.com/fwlink/p/?LinkId=262795) or the forced manual failover described in Perform a Forced Manual Failover of an Availability Group (SQL Server) (https://go.microsoft.com/fwlink/p/?LinkId=262796). You can perform either failover on an AlwaysOn availability group by using the Failover Wizard in SQL Server Management Studio, Transact-SQL, or Windows PowerShell in SQL Server 2012.

Monitoring an AlwaysOn environment

You have to monitor an AlwaysOn environment for performance, health, and capacity.

Performance

New performance objects are provided monitoring the AlwaysOn environment. They are SQLServer:Database Replica (https://go.microsoft.com/fwlink/p/?LinkId=245745) and SQLServer:Availability Replica (https://go.microsoft.com/fwlink/p/?LinkId=245746).

Health and capacity

For general health monitoring you can use the Availability Groups Dashboard to obtain the health of the availability groups in the system. We recommend that you refer to the following posts on the official SQL Server AlwaysOn team blog to fully understand AlwaysOn health monitoring.

You can also use Transact-SQL to monitor availability groups by using the set of catalog and dynamic management views that are provided for AlwaysOn Availability Groups. For more information, see Monitor Availability Groups (Transact-SQL) (https://go.microsoft.com/fwlink/p/?LinkId=245747).