Configure availability in a single farm by using SQL Server database mirroring

  • Article

Applies To: Office SharePoint Server 2007

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2016-11-14

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

This article includes the following sections:

  • Database mirroring requirements

  • Security associated with database mirroring

  • Recommended topologies

  • Set up SQL Server connection aliases

  • Set up database mirroring

  • Monitor mirroring state

  • Next steps

Microsoft SQL Server 2005 database mirroring provides availability support by sending transactions directly from a principal database and server to a mirror database and server whenever the principal database’s transaction log buffer is written to disk. We recommend that for availability within an Office SharePoint Server 2007 farm, you use high-availability database mirroring, also referred to 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 farm, mirroring can provide redundancy for all databases — when databases fail over, we assume that your front-end Web servers remain available. The following figure shows how mirroring is configured to provide high availability within a farm.

Diagram of mirroring all databases within a farm

Office SharePoint Server 2007 is not mirroring-aware. To use mirroring as an availability solution, we recommend that you configure your databases to use SQL Server client aliasing, and run scripts to change the SQL Server client alias on failover.

Database mirroring requirements

Ensure that your databases and system meet the following requirements before you configure database mirroring:

  • We recommend that your system have latency no more than 1 millisecond.

  • System bandwidth should preferably be 1 gigabyte 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 be running the same edition of Microsoft SQL Server 2005 with Service Pack 1 (SP1). Database mirroring is available only in the Standard, Developer, and Enterprise editions. The witness server can run any version of SQL Server 2005, including SQL Server 2005 Express Edition.

  • Mirroring works only with databases that use the full recovery model.

    By default, Office SharePoint Server 2007 SSP 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/?LinkId=132075&clcid=0x409).

  • If you plan to mirror SSP databases, consider that the transaction log size of these databases may become very large. To work around this, consider having 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/?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, as more databases are added to a session, performance can get progressively worse.

    In the hosting environment at Microsoft, we have found that the practical maximum for mirroring databases from a single instance of SQL Server that is hosting SharePoint Products and Technologies is 50 databases. This maximum is based on the resource intensiveness of mirroring (each principal instance and mirror instance requires dedicated threads), and testing. Your results may vary, based on the following factors:

    • Memory of the principal and mirror instances

    • Processing power of the principal and mirror instances

    • Bandwidth of the I/O subsystems on the principal and mirror instances

    • Amount of transaction logs that the workload on each database generates

    • The network bandwidth between the principal and the mirror instances

For more information about performance and scale for database mirroring, see Database Mirroring in SQL Server 2005 (https://go.microsoft.com/fwlink/?LinkId=83566&clcid=0x409).

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.

  • Windows authentication (NTLM or Kerberos)

  • Certificates

This document describes how to use database mirroring with certificates. For information about how to use Windows authentication with database mirroring, see Example: Setting Up Database Mirroring Using Windows Authentication (Transact-SQL) Example: Setting Up Database Mirroring Using Windows Authentication (Transact-SQL) (https://go.microsoft.com/fwlink/?LinkId=83567&clcid=0x409).

Unless the network is secure, the data transmitted during the session should be encrypted. This document outlines how to set up encryption on the data transmitted over the wire by using RC4, but database mirroring supports both 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/?LinkId=83569&clcid=0x409).

SharePoint 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. 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 and the search services and default content access accounts should have SQL Server logins, although they are not 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 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/?LinkId=122053&clcid=0x409). For more general 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/?LinkId=122055&clcid=0x409).

We recommend that you maintain a one-to-one mapping of principal server to mirror server to ensure compatibility with SharePoint Products and Technologies.

The supported topologies include mirroring all content databases, the configuration database, and the Central Administration content database. Additionally, for Office SharePoint Server you can mirror the SSP database, SSP search database, and SSP content databases.

The following illustrations demonstrate some supported topologies.

Supported topologies for mirroring

Avoid topologies that do not have matching principal and mirror servers. Also, keep the configuration database and the administration content database on the same server. The following diagram illustrates an unsupported topology.

Unsupported topologies for mirroring

Set up SQL Server connection aliases

A SQL Server connection alias is a defined alternate name that can be used to connect to an instance of SQL Server. In the case of SharePoint Products and Technologies, you can create a SQL Server alias that is used by all servers (including the front-end Web servers) to connect to the instance of SQL Server. When a mirrored database server fails over, you adjust the alias on the front-end Web server to point to the mirror server instead of the principal server. We recommend that you set SQL Server connection aliases before you implement mirroring.

Note

If you are setting up aliasing for an existing farm, use an alias that is the same name as the principal server so that no changes will have to be made to the front-end Web servers to start to use the alias.

Complete the following steps on every front-end Web server, and every server that connects to SQL Server.

  1. Start the SQL Server Native Client Network Utility (%SYSTEM%\cliconfg.exe).

  2. Click the Alias tab, and then click Add.

    The Add Network Library Configuration dialog box appears.

  3. Select TCP/IP, type an alias, type the server name to associate with the alias, and then click OK.

  4. Repeat for all servers that connect to SQL Server.

Set up database mirroring

This section describes how to use Transact-SQL to set up high-availability mode database mirroring for a SQL Server database.

To set up database mirroring with SharePoint Products and Technologies, you must work individually with each database that you want to mirror.

The steps in the following section pertain to the following server farm topology:

  • One or more front-end Web servers

  • Three servers that are running SQL Server 2005: principal server, mirror server, and witness server

  • One configuration database

  • Multiple content databases

  • One or more SSP databases

Configure database mirroring with certificates and full recovery

Each step lists the server on which it should be performed. Use Transact-SQL to send these commands to SQL Server. Placeholder information is denoted by angle brackets (<>) and should be replaced with information specific to your deployment.

Set up the principal server for outbound connections

  1. On the principal server, create a certificate and open a port for mirroring.

    --On master database, create the database Master Key, if needed
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<test1234->';
GO
-- Make a certificate for this server instance.
USE master;
CREATE CERTIFICATE <MASTER_HostA_cert> 
   WITH SUBJECT = '<Master_HostA certificate>';
GO
--Create mirroring endpoint for server instance using the certificate
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5024
      , LISTENER_IP = ALL
   ) 
   FOR DATABASE_MIRRORING ( 
      AUTHENTICATION = CERTIFICATE <MASTER_HostA_cert>
      , ENCRYPTION = REQUIRED ALGORITHM RC4
      , ROLE = ALL
   );
GO

  2. On the principal server, back up the certificate.

    --Back up HOST_A certificate.
BACKUP CERTIFICATE MASTER_HostA_cert TO FILE = '<c:\MASTER_HostA_cert.cer>';
GO

  3. On the principal server, back up the database. This example uses the configuration database. Repeat for all databases.

    USE master;
--Ensure that SharePoint_Config uses the full recovery model.
ALTER DATABASE SharePoint_Config
   SET RECOVERY FULL;
GO
USE SharePoint_Config
BACKUP DATABASE SharePoint_Config 
    TO DISK = '<c:\SharePoint_Config.bak>' 
    WITH FORMAT
GO
BACKUP Log SharePoint_Config 
    TO DISK = '<c:\SharePoint_Config_log.bak>' 
    WITH FORMAT
GO

  4. Copy the backup file to the mirror server. Repeat for all databases.

  5. By using any secure copy method, copy the backup certificate file (C:\HOST_HostA_cert.cer, for example) to the mirror server.

  6. On the principal server, create a login and user for the mirror server, associate the certificate with the user, and grant the login connect permissions for the partnership.

    --Create a login on HOST_A for HOST_B
USE master;
CREATE LOGIN <HOST_HostB_login> WITH PASSWORD = '<1234-test>';
GO
--Create a user for that login.
CREATE USER <HOST_HostB_user> FOR LOGIN <HOST_HostB_login>;
GO
--Associate the certificate with the user
CREATE CERTIFICATE <HOST_HostB_cert>
   AUTHORIZATION <HOST_HostB_user>
   FROM FILE = '<c:\HOST_HostB_cert.cer>' --do not try to use a network path, SQL Server will give an error about the key not being valid
GO
--Grant CONNECT permission on the login for the remote mirroring endpoint.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<HOST_HostB_login>];
GO

Set up the mirror server for outbound connections

  1. On the mirror server, create a certificate and open a port for mirroring.

    --On master database, create the database Master Key, if needed.
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1234-test>';
GO
-- Make a certificate on the HOST_B server instance.
CREATE CERTIFICATE <HOST_HostB>
   WITH SUBJECT = '<HOST_HostB certificate for database mirroring>';
GO
--Create a mirroring endpoint for the server instance on HOST_B.
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5024
      , LISTENER_IP = ALL
   ) 
   FOR DATABASE_MIRRORING ( 
      AUTHENTICATION = CERTIFICATE <HOST_HostB>
      , ENCRYPTION = REQUIRED ALGORITHM RC4
      , ROLE = ALL
   );
GO

  2. On the mirror server, back up the certificate.

    --Back up HOST_B certificate.
BACKUP CERTIFICATE <HOST_HostB> TO FILE = '<C:\HOST_HostB_cert.cer>';
GO

  3. By using any secure copy method, copy the backup certificate file (C:\HOST_HostB_cert.cer, for example) to the principal server.

  4. On the mirror server, restore the database from the backup files. This example uses the configuration database. Repeat for all databases.

    RESTORE DATABASE SharePoint_Config 
    FROM DISK = '<c:\SharePoint_Config.bak>' 
    WITH NORECOVERY
GO
RESTORE log SharePoint_Config 
    FROM DISK = '<c:\SharePoint_Config_log.bak>' 
    WITH NORECOVERY
GO

Set up the mirror server for inbound connections

  1. On the mirror server, create a login and user for the principal server, associate the certificate with the user, and grant the login connect permissions for the partnership.

    --Create a login on HOST_B for HOST_A
USE master;
CREATE LOGIN <MASTER_HostA_login> WITH PASSWORD = '<test1234->';
GO
--Create a user for that login.
CREATE USER <MASTER_HostA_user> FOR LOGIN <MASTER_HostA_login>;
GO
--Associate the certificate with the user
CREATE CERTIFICATE <MASTER_HostA_cert>
   AUTHORIZATION <MASTER_HostA_user>
   FROM FILE = '<c:\MASTER_HostA_cert.cer>' --do not try to use a network path, SQL Server will give an error about the key not being valid
GO
--Grant CONNECT permission on the login for the remote mirroring endpoint.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<MASTER_HostA_login>];
GO

Set up the principal server for inbound connections

  1. On the principal server, create a login and user for the mirror server, associate the certificate with the user, and grant the login connect permissions for the partnership.

    --Create a login on HOST_A for HOST_B
USE master;
CREATE LOGIN <HOST_HostB_login> WITH PASSWORD = '<1234-test>';
GO
--Create a user for that login.
CREATE USER <HOST_HostB_user> FOR LOGIN <HOST_HostB_login>;
GO
--Associate the certificate with the user
CREATE CERTIFICATE <HOST_HostB_cert>
   AUTHORIZATION <HOST_HostB_user>
   FROM FILE = '<c:\HOST_HostB_cert.cer>' --do not try to use a network path, SQL Server will give an error about the key not being valid
GO
--Grant CONNECT permission on the login for the remote mirroring endpoint.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<HOST_HostB_login>];
GO

Set up the mirroring partners

  1. On the principal server, set up the mirroring partnership. This example uses the configuration database. Repeat for all databases.

    --At HOST_A, set server instance on HOST_B as partner (mirror server).
ALTER DATABASE SharePoint_Config
    SET PARTNER = '<TCP://databasemirror.adatum.com:5024>';
GO

  2. On the mirror server, set up the mirroring partnership. This example uses the configuration database. Repeat for all databases.

    --At HOST_B, set server instance on HOST_A as partner (principal server):
ALTER DATABASE SharePoint_Config 
    SET PARTNER = '<TCP://databasemaster.adatum.com:5024>';
GO

Set up a witness server

Each step lists the server on which it should be performed. Use Transact-SQL to send these commands to SQL Server. Placeholder information is denoted by angle brackets (<>) and should be replaced with information specific to your deployment.

  1. On the witness server, set up the certificate and open the port.

    --On master database, create the database Master Key, if needed
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1234test->';
GO
-- Make a certificate for this server instance.
USE master;
CREATE CERTIFICATE <WITNESS_HostC_cert> 
   WITH SUBJECT = '<Witness_HostC certificate>';
GO
--Create mirroring endpoint for server instance using the certificate
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
      LISTENER_PORT=5024
      , LISTENER_IP = ALL
   ) 
   FOR DATABASE_MIRRORING ( 
      AUTHENTICATION = CERTIFICATE <WITNESS_HostC_cert
      , ENCRYPTION = REQUIRED ALGORITHM RC4
      , ROLE = ALL
   );
GO

  2. On the principal server, back up the certificate.

    --Back up HOST_C certificate 
BACKUP CERTIFICATE <WITNESS_HostC_cert> TO FILE = '<c:\ WITNESS_HostC_cert.cer>';
GO

  3. By using any secure copy method, copy the backup certificate file (C:\WITNESS_HOSTC_cert.cer, for example) to the principal server and the mirror server.

  4. By using any secure copy method, copy the backup certificate file (C:\WITNESS_HOSTC_cert.cer, for example) to the principal server and the mirror server.

    --Create a login on Witness HOST_C for Principal HOST_A
USE master;
CREATE LOGIN <MASTER_HostA_login> WITH PASSWORD = '<test1234->';
GO
--Create a user for that login.
CREATE USER <MASTER_HostA_user> FOR LOGIN <MASTER_HostA_login>;
GO
--Associate the certificate with the user
CREATE CERTIFICATE <MASTER_HostA_cert>
   AUTHORIZATION <MASTER_HostA_user>
   FROM FILE = '<c:\MASTER_HostA_cert.cer>' --do not try to use a network path, SQL Server will give an error about the key not being valid
GO
--Grant CONNECT permission on the login for the remote mirroring endpoint.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<MASTER_HostA_login>];
GO
--Create Login for Mirror Host B
CREATE LOGIN <HOST_HostB_login> WITH PASSWORD = '<1234-test>';
GO
--Create a user for that login.
CREATE USER <HOST_HostB_user> FOR LOGIN <HOST_HostB_login>;
GO
--Associate the certificate with the user
CREATE CERTIFICATE <HOST_HostB_cert>
   AUTHORIZATION <HOST_HostB_user>
   FROM FILE = '<c:\HOST_HostB_cert.cer>' --do not try to use a network path, SQL Server will give an error about the key not being valid
GO
--Grant CONNECT permission on the login for the remote mirroring endpoint.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<HOST_HostB_login>];
GO

  5. On the principal server, create a login and user for the witness server, associate the certificate with the user, and grant the login connect permissions for the partnership. Repeat for the mirror server.

    --Create a login on Master HostA for Witness HostC
USE master;
CREATE LOGIN <WITNESS_HostC_login> WITH PASSWORD = '<1234test->';
GO
--Create a user for that login.
CREATE USER <WITNESS_HostC_user> FOR LOGIN <WITNESS_HostC_login>;
GO
--Associate the certificate with the user
CREATE CERTIFICATE <WITNESS_HostC_cert>
   AUTHORIZATION <WITNESS_HostC_user>
   FROM FILE = '<c:\WITNESS_HostC_cert.cer>' --do not try to use a network path, SQL Server will give an error about the key not being valid
GO
--Grant CONNECT permission on the login for the remote mirroring endpoint.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [<WITNESS_HostC_login>];
GO

  6. On the principal server, attach the witness server. This example uses the configuration database. Repeat for all databases.

    --Setup the Witness Server
ALTER DATABASE SharePoint_Config
    SET WITNESS = 
    '<TCP://databasewitness.adatum.com:5024>'
GO

Transfer permissions to the mirror server

When you set up a mirrored database, the SQL Server logins and permissions for the database that will be used with a SharePoint farm are not automatically configured in the master and msdb databases on the mirror. Instead, you must configure the permissions for the required logins.

We recommend that you transfer your logins and permissions from the principal server to the mirror server by running a script. The script that we recommend that you use 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/?LinkId=122053&clcid=0x409).

Monitor mirroring state

On the witness server, you can use the following Transact-SQL statement to monitor the current mirroring state.

SELECT mirroring_role FROM sys.database_mirroring WHERE mirroring_role is not 
null --If returns a 1, principal, if 2: mirror

You may want to create a series of SQL Server jobs and alerts to determine which server is principal, or create a Windows service that runs this command to determine which server is principal.

Next steps

After you have configured mirroring, you must create the scripts that enable you to fail over the databases in your farm. For more information, see Fail over and recover mirrored databases in a single farm.

SELECT mirroring_role FROM sys.database_mirroring WHERE mirroring_role is not 
null --If returns a 1, principal, if 2: mirror

You may want to create a series of SQL Server jobs and alerts to determine which server is principal, or create a Windows service that runs this command to determine which server is principal.

See Also

Other Resources

Database Mirroring in SQL Server 2005