Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

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

SharePoint 2010

Published: May 12, 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 Foundation 2010 environment. We recommend that a SQL Server database administrator configure mirroring for a production environment.

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

In this article:

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 (http://go.microsoft.com/fwlink/p/?LinkId=122053&clcid=0x409).

Removing mirroring from a server

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

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.