Sample script for configuring SQL Server database mirroring (SharePoint Server 2010)

 

Applies to: SharePoint Server 2010, SharePoint Foundation 2010

This article contains a series of sample scripts that you can use to set up Microsoft SQL Server mirroring for a test Microsoft SharePoint Server 2010 environment. We recommend that a SQL Server database administrator configure mirroring for a production environment.

To set up database mirroring with SharePoint Server 2010, you must work individually with each database that you want to mirror.

In this article:

• Configure database mirroring with certificates and full recovery

• Set up a witness server

• Transfer permissions to the mirror server

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

• One or more front-end Web servers

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

• One configuration database

• Multiple content databases

• One or more service application 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 (<>); replace this with information that is specific to your deployment.

To set up the principal server for outbound connections

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

   --On the 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 a mirroring endpoint for server instance by 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 the 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 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
   

To set up the mirror server for outbound connections

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

   --On the 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 the 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
   

To 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 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
   

To 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 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
   

To 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 the server instance on HOST_B as a 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 the server instance on HOST_A as a 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 (<>); replace this with information that is specific to your deployment.

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

   --On the 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 a mirroring endpoint for server instance by 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 witness server, back up the certificate.

   --Back up the 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. On the witness server, create logins and users for the principal and mirror servers, associate the certificates with the users, and grant the logins connect permissions for the partnership.

   --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 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 a login for the 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 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 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.

   --Set up 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 server. 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/p/?LinkId=122053).

Removing mirroring from a server

To remove mirroring from a server, see How to: Remove Database Mirroring (Transact-SQL) (https://go.microsoft.com/fwlink/p/?LinkId=185070).