; Configuring Database Mirroring for SharePoint Products _ Technologies
Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out
Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

Configuring Database Mirroring for SharePoint Products _ Technologies

VIEWS: 25 PAGES: 25

  • pg 1
									         Using SQL Server Database Mirroring with
Office SharePoint® Server and Windows SharePoint Services




Published March 2007

Updated February 2008
The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of
publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of
Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE
INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this
document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic,
mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this
document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you
any license to these patents, trademarks, copyrights, or other intellectual property.

 2007 Microsoft Corporation. All rights reserved.

Microsoft, SharePoint, SQL Server, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States
and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
Table of Contents
Using SQL Server Database Mirroring with Office SharePoint Server and Windows SharePoint Services .. 1

Introduction to Database Mirroring ............................................................................................................. 1

   Database Mirroring Modes ....................................................................................................................... 1

   Security Associated with Database Mirroring........................................................................................... 2

   Recommended Topologies ....................................................................................................................... 6

   Recommended Database Limits ............................................................................................................... 8

   Performance and Scale ............................................................................................................................. 8

How to Set Up High-Availability Mode Database Mirroring for SharePoint Products and Technologies .... 9

   Prerequisites ............................................................................................................................................. 9

   Setup Steps ............................................................................................................................................... 9

       Configure Database Mirroring with Certificates and Full Recovery ................................................... 10

       Set up a Witness Server ...................................................................................................................... 13

Recovering from a Failure ........................................................................................................................... 15

   Types of Failover ..................................................................................................................................... 15

       Automatic Failover .............................................................................................................................. 15

       Manual Failover .................................................................................................................................. 16

   Reconnect SharePoint Products and Technologies front-end Web servers to the active database server
   ................................................................................................................................................................ 17

       Monitor mirroring ............................................................................................................................... 17

       Use SQL Server aliasing ....................................................................................................................... 18

       Use the Stsadm renameserver operation .......................................... Error! Bookmark not defined.19

       Failing over the configuration database and Central Administration content database ............. Error!
       Bookmark not defined.20

       Failing over a single database when using either aliasing or Stsadm ................................................. 20
Additional References ................................................................................................................................. 21
Most businesses require consistent availability from their SharePoint sites. Database mirroring is one of
the technologies that you can use to increase the availability of your site.

Introduction to Database Mirroring
Database mirroring is a technology in Microsoft® SQL Server™ 2005 Service Pack 1 (SP 1) database
software that can deliver high availability and high performance solutions for database redundancy. In
database mirroring, transactions are sent 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. This
technique can keep the mirror database nearly up to date with the principal database. You can
optionally use a third server, a witness server, to enable automatic failover from the principal server to
the mirror server.

Database mirroring is a primarily software solution for increasing database availability. Mirroring is
implemented on a per-database basis and works only with databases that use the full recovery model.
The simple and bulk-logged recovery models do not support database mirroring.

Database mirroring offers a substantial improvement in availability over the level previously possible by
using SQL Server and provides an easy-to-manage alternative or supplement to failover clustering or log
shipping. When a database mirroring session is synchronized, database mirroring provides a hot standby
server that supports rapid failover with no loss of data from committed transactions. During a typical
mirroring session, after a production server fails, client applications can recover quickly by reconnecting
to the standby server.

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

Administrators of servers running Microsoft Office SharePoint® Server 2007 or Microsoft Windows®
SharePoint Services can use database mirroring and notify Office SharePoint Server or Windows
SharePoint Services if the database server changes to recover quickly from database failures within a
farm.

Database Mirroring Modes
Database mirroring offers three operating modes. The choice you select depends on how you want to
handle failover processing.

   High-safety or high-protection This operating mode allows you to synchronize writing transaction
    logs to both servers. Transactions are marked complete after the mirror server confirms that the log
    has been written. Failover is manual.

   High-performance This operating mode does not synchronize writing transaction logs to the
    servers, and therefore offers some performance gains, and increases the possibility of data loss.


       1
    Transactions are marked complete without waiting for the mirror server to confirm that the
    transaction log has been written. This option only allows manual failover.

   High-availability This operating mode enables you to run in high-safety mode with automatic
    failover. For this option to work, you must have a witness server.

Within a SharePoint farm, we expect most administrators to use high-availability mode to take
advantage of automatic failover. This paper only describes how to use high-availability mode. High-
safety and high-performance modes can be used with SharePoint Products and Technologies within a
farm. For more information about running in high-safety mode, see Synchronous Database Mirroring
(High-Safety Mode) (http://go.microsoft.com/fwlink/?LinkId=98724). For more information about
running in high-performance mode, see Asynchronous Database Mirroring (High-Performance Mode)
(http://go.microsoft.com/fwlink/?LinkId=83579&clcid=0x409).

What you can mirror
You can use mirroring within a farm, or across farm environments. Between farms, fewer databases can
be mirrored and restored.

Mirroring within a farm
Within a farm, mirroring can provide redundancy for all databases. The following figure shows how
mirroring is configured to provide a high availability within a farm.

Note: Mirroring within a farm provides database availability only—that is, when databases fail over, we
assume that your front-end Web servers remain available.




      2
Web, query and application server




                                                             Witness server (Optional)



Web, query and application server          SQL Server 1                                    SQL Server 2


                                                                    High
                                                                  availabilty
                                                                  mirroring
                                        Principal instance                                   Mirror instance
  Index and application server

                                           Content                                              Content

                                        WSS search                                           WSS search

                                         SSP search                                           SSP search

                                             SSP                                                 SSP

                                        SSP content                                          SSP content

                                    Central administration                               Central administration

                                        Configuration                                        Configuration




      3
Mirroring across farms
Across farms, mirroring can provide redundancy for content databases and Shared Services Provider
(SSP) databases, with the following exceptions: configuration database, Central Administration content
database, and search databases. The configuration database and the Central Administration content
database cannot be restored or moved to another location because they contain location-specific
references, and must be rebuilt. The search database cannot function if it is not completely
synchronized with the index file. The index file cannot be mirrored. In the cross-farm scenario, we
recommend that you perform one of the following to recover Search:

   Back up search by using the SharePoint backup and recovery tools, and restore it to the secondary
    environment on failover. A full crawl will be started.

   Rebuild search.

We recommend that you use the tools in SharePoint Products and Technologies to back up and restore
search databases. The following figure shows how mirroring can be configured to provide high
availability between farms.




      4
                      Primary Server Farm                                                 Secondary Server Farm




     Web, query and application server                                                                  Web, query and application server




     Web, query and application server                                                                  Web, query and application server




                                                SQL Server 1                SQL Server 2

                                                                    High safety
                                                                        or
                                                                 High performance
                                                                     mirroring
       Index and application server         Principal instance                      Mirror instance        Index and application server
                                                Content                               Content

                                                  SSP                                   SSP

                                             SSP content                            SSP content

                                             WSS Search                             WSS Search

                                              SSP search                            SSP search

                                         Central administration                Central administration

                                             Configuration                          Configuration




Important: This white paper focuses on how to use high availability database mirroring within a farm.
For more information about overall availability for SharePoint Products and Technologies, see Plan for
availability (Office SharePoint Server) (http://go.microsoft.com/fwlink/?LinkId=98720) on the Microsoft
TechNet Web site.

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 done at the session
level when a port is opened for connection.


       5
Database mirroring supports two types of SQL authentication.

   Windows authentication (NTLM or Kerberos)

   Certificates

This document describes how to use database mirroring with certificates. For information about using
Windows authentication with database mirroring, see Example: Setting Up Database Mirroring Using
Windows Authentication (Transact-SQL) (http://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
(http://go.microsoft.com/fwlink/?LinkId=83569&clcid=0x409.

Recommended Topologies
It is recommended that you maintain a one-to-one mapping of principal server to mirror server to
ensure compatibility with SharePoint Products and Technologies. The following illustrations
demonstrate some supported topologies.

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.

Supported SQL Server topologies in a SharePoint Products and Technologies server farm




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




      7
Unsupported SQL Server topology in a SharePoint Products and Technologies server farm




Recommended Database Limits
Every database mirroring session creates at least two threads for each database. As more databases are
added to the session, performance can get progressively worse.

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

Performance and Scale
The performance and scale of database mirroring are beyond the scope of this paper. Performance and
scale depend on many different factors, including the following:

   The latency of the network is the largest factor for performance. We recommend that your system
    have latency no greater than 1 millisecond.

   System bandwidth should preferably be 1 gigabyte per second.

   Having more than one mirror server does not affect scalability.

   Logs are copied in real time between the principal and the mirror servers, and copying can impact
    performance.



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

How to Set Up High-Availability Mode Database Mirroring for
SharePoint Products and Technologies
Prerequisites
In this document, we assume that you have a server farm deployment and that the topology of the
deployment contains at least one front-end Web server and at least one database server. If you are not
using a server farm, database mirroring will not be useful for you.

Ensure that the principal server and mirror server are running the same edition of SQL Server 2005 SP 1.
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 Express.

        Important Office SharePoint Server SSP databases are by default configured with the Simple
        recovery model. To configure database mirroring, the recovery model of the database must be
        set to Full. For more 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)
        http://msdn2.microsoft.com/en-us/library/ms189272.aspx.

If you plan to mirror SSP databases, consider that the transaction log size of these databases may
become quite large. To remedy 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
(http://go.microsoft.com/fwlink/?LinkId=111458&clcid=0x409).

Setup Steps
The steps in this section give the instructions for using 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 following steps show how to set up database mirroring in high-availability mode. It is recommended
that you use high-availability mode with full recovery to keep the databases synchronized and reduce
the chances for data loss.

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

   One or more front end Web servers


       9
   Three servers running Microsoft SQL Server 2005: principal server, mirror server, and witness server

   One configuration database

   Multiple content databases

   One or more SSP databases

This configuration helps ensure high availability by transferring SQL Server operations to the mirror
server if the principal server fails.

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 that content,
especially the sample passwords, with information specific to your deployment.

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.


     10
    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 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 certiifcate 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
7. 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

8. By using any secure copy method, copy the backup certificate file (C:\HOST_HostB_cert.cer, for
   example) to the principal server.
     11
9. 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


10. 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


11. 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

     12
12. 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


13. 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 (<>). Replace that content,
especially the sample passwords, 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.


     13
    --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. 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 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>;

     14
     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


Recovering from a Failure
Types of Failover
When a failure occurs with the principal server, the mirror server can take over by using one of three
possible failover mechanisms:
 Automatic failover

    Manual failover

    Forced failover

The following table lists the types of failover available in each mirroring mode.
Note: This paper describes only how to recover from an automatic failover or a manual failover.

                  High-availability mode with High-safety mode without High-performance
                  a witness                   a witness                mode

Automatic         Yes                          No                           No
failover

Manual failover   Yes                          Yes                          No

Forced failover   No                           Yes                          Yes




Automatic Failover
Automatic failover occurs when the principal server has lost communication with the rest of the
database mirroring configuration, while the mirror and witness retain communication.

      15
Note: If all server instances lose communication automatic failover does not occur, even if the witness
and mirror server later regain communication.
The following process occurs when automatic failover is triggered:
1. If the principal server is still running, it changes the state of the principal database to
    DISCONNECTED and disconnects all clients from the principal database.

2. The witness and mirror servers register that the principal server is unavailable.

3. If any logs are waiting in the redo queue, the mirror server finishes rolling forward the mirror
   database. The amount of time required to apply the log depends on the speed of the system, the
   recent work load, and the size of the logs in the redo queue.

4. The former mirror database moves online as the new principal database, and recovery cleans up all
   uncommitted transactions by rolling them back as quickly as possible. Locks isolate those
   transactions.

5. When the former principal server rejoins the session, it recognizes that its failover partner now
   owns the principal role. The former principal server takes on the role of mirror server, making its
   database the mirror database. The new mirror server synchronizes the new mirror database with
   the principal database as quickly as possible. As soon as the new mirror server has resynchronized
   the databases, failover is again possible, but in the reverse direction.

For a more detailed description of automatic failover, see Automatic Failover
(http://go.microsoft.com/fwlink/?LinkId=83690&clcid=0x409).

Manual Failover
Manual failover typically occurs when both the principal and mirror servers are still running. The
administrator makes a conscious decision to switch the active database from the principal to the mirror.
This might be done for a variety of reasons, such as upgrading software on the principal server.
The following process occurs during a manual failover:
1. The administrator connects to the principal server and issues the following Transact-SQL commands
    for each database:

    USE master;

    ALTER DATABASE <database_name> SET PARTNER FAILOVER; -- where
    database_name is the mirrored database.

    This initiates an immediate transition of the mirror server to the principal role.

    The following example provides a Transact-SQL script that you can use to failover all mirrored
    databases:

    USE master;


     16
    DECLARE i CURSOR
    READ_ONLY
    FOR
    SELECT name FROM sys.databases WHERE database_id IN
    (SELECT database_id FROM sys.database_mirroring WHERE mirroring_state=4)

    DECLARE @name varchar(255)
    DECLARE @cmd varchar(1000)
    OPEN i

    FETCH NEXT FROM i INTO @name
    WHILE (@@fetch_status <> -1)
    BEGIN
       IF (@@fetch_status <> -2)
       BEGIN
                set @cmd = 'ALTER Database [' + @name + '] SET PARTNER FAILOVER;'
                exec (@cmd)

                DECLARE @message varchar(100)
                SELECT @message = 'Failover for : ' + @name
                PRINT @message
       END
       FETCH NEXT FROM i INTO @name
    END

   CLOSE i
   DEALLOCATE i
   GO
2. On the former principal server, clients are disconnected from the database and uncommitted
   transactions are rolled back.

If you run manual failover with an automatic failover setup that includes a witness server, the principal
and mirror automatically change roles.

Reconnect SharePoint Products and Technologies front-end Web servers
to the active database server
SharePoint Products and Technologies are not mirroring aware, so you must monitor mirroring activity,
and then manually connect the SharePoint Products and Technologies front-end Web servers to the
active database server.

We have tested using SQL Server aliasing to connect SharePoint Products and Technologies to the
mirrored database after failover.

The following methods can be used for disaster recovery failover, but are not covered in this document:

     17
   Stsadm operation: renameserver

   Host file edits

   WINS/DNS edits

   Windows Load Balancing

Monitor mirroring
On the 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.

Use SQL Server aliasing
A connection alias is an alternate name that can be used to make a connection to a SQL Server instance.
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 SQL Server instance. 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.

If you plan to use aliasing, we recommend that you set it up before you install SharePoint Products and
Technologies, although you can set up aliasing after installation. We recommend that you set up
aliasing before you implement mirroring.

Create an 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).




     18
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.




     19
    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 need to be made to the front-end Web servers to start using
    the alias.

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

Fail over using an alias
When you are using an alias, you must reset the value for the alias at each front-end Web server. Note:
The reference within each Web application does not change, so no work is required within SharePoint
Products and Technologies after failover.

Complete the following steps on every front-end Web server and server that connects to SQL Server.
1. On a server, start the SQL Server Native Client Network Utility (%SYSTEM%\cliconfg.exe).

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

3. In the Edit Network Library Configuration dialog box, in the Server field, type the name of the
   mirror server, and then click OK.

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

Failing over a single database
If a single database fails over (and the configuration database and other content databases remain live
on the initial server), complete the following steps on only one front-end Web server.
1. Click Start, point to Programs, point to Administrative Tools, and then click SharePoint 3.0 Central
     Administration.

2. On the Central Administration site, click Application Management.

3. On the Application Management page, in the SharePoint Web Application Management section,
   click Content databases.

4. On the Manage Content Databases page, click the name of the content database that has failed
   over.

5. On the Manage Content Database Settings page, in the Remove Content Database section, select
   the Remove content database check box, and then click OK.

6. On the Manage Content Databases page, click Add a content database.

7. Enter the information for the database you just removed, but replace the information in Database
   Server with the name of the new principal server.

8. Repeat Steps 4-7 for each database that has failed over


     20
You can also use the command line to accomplish the same thing.
1. To delete the failed database, enter the following command:

   stsadm -o deletecontentdb -url "<http:// websitename:port>" -databasename "<ContentDBName>"
   -databaseserver "<OldPrincipalServer>"

2. To add the database back, enter the following command:

   stsadm -o addcontentdb -url "<http:// backupservername:port>" -databasename "
   <ContentDBName>" -databaseserver "<NewPrincipalServer>"

Additional References
SQL Server Database Mirroring Documentation
(http://go.microsoft.com/fwlink/?LinkId=93763&clcid=0x409)

Managing Logins after a Mirroring failover (http://go.microsoft.com/fwlink/?LinkId=93761&clcid=0x409)

How to Transfer Logins between instances of SQL Server
(http://go.microsoft.com/fwlink/?LinkId=93747&clcid=0x409)




    21

								
To top