SQL Server 2005 Database Mirroring and Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007 – Part 1 (Introduction, Overview, and basics)


This will be the first of a multi-part series covering SQL Server 2005 Database Mirroring and Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007.  This post will cover an introduction to SQL Server 2005 Database Mirroring, an overview, and the basics to include considerations and integration with SharePoint Products and Technologies. Part 2 will cover implementing SQL Server 2005 Database Mirroring with SharePoint Products and Technologies using NTLM authentication and dedicated (DAS) storage and failover examples.


SQL Server 2005 Database Mirroring has increased in popularity since its introduction and among the possible applications there is a growing demand to implement SQL Server 2005 Database mirroring for SharePoint Products and Technologies.  This article outlines the considerations and implications of designing SQL Server 2005 Database Mirroring into your SharePoint Products and Technologies database architecture.

Understanding Basic Database Mirroring Concepts

SQL Server 2005 Database Mirroring provides high-availability and rapid failover by continuously sending a databases’s transaction logs from an originating SQL server instance (principal) to a destination SQL server instance (mirror).  Since the granularity of failover is at the database level unlike the server level failover in Microsoft Cluster Server, SQL Server 2005 Database Mirroring failover can provide an increase in failover performance and is more seamless and transparent to the application.  A complete overview of Database Mirroring in SQL Server 2005 is available at http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx.

Considerations and Implications

There are several important factors to consider before implementing SQL Server 2005 Database Mirroring in your SharePoint Products and Technologies infrastructure.  Prior to implementing SQL Server 2005 Database Mirroring, you should understand what problems you are trying to solve whether they are performance, availability, or geographic replication related.


  • Determine the SQL Server 2005 Database Mirroring implementation mode – High Performance vs. High Availability vs. High Protection

    • High performance SQL Server 2005 Database Mirroring is also known as asynchronous mirroring where the transaction safety level is OFF.  In high performance mirroring the transaction is committed as soon as the principal server writes the log record to the local log and sends the log record to the mirror.  The principal does not wait for acknowledge and if needed queues the logs, a failover at this point may result in data loss.
    • High availability SQL Server 2005 Database Mirroring is also known as synchronous mirroring where the transaction safety level is FULL.  Each transaction committed on the principal database is also committed on the mirror server synchronously.  The principal server will only commit a transaction after receiving acknowledgement from the mirror server indicating the transaction log has been hardened.  This process of acknowledgement and receipt results in slower performance versus asynchronous mirroring.
    • High protection SQL Server 2005 Database Mirroring is also known as synchronous mirroring where the transaction safety level is FULL and is similar to high availability with primary difference being that there is no implementation of a witness server which requires manual failover.
    • SQL Server 2005 Standard Edition allows only the FULL transaction safety level.

  • Understand and plan for database limitations

    • master, model, temp, or msdb cannot be mirrored.
    • SQL Server 2005 Database Mirroring requires that the databases use the FULL recovery model, the SIMPLE and BULK-logged recovery models cannot be used.
    • Transactions are played out on two servers, limiting the number of databases will reduce the performance cost.
    • Fewer databases will result in less overhead, by limiting the number of databases in a mirroring session you can maintain optimum performance levels.  This may require the repartioning of smaller content databases to achieve fewer and larger content databases.  Larger databases can leverage multiple data files spanning one or more drives to optimize performance and management.
    • Mirror databases that require redundancy and/or high value databases – content databases typically fall into this category.

  • Determine the appropriate hardware requirements – Can a single SQL server support your infrastructure?

    • Automatic failover requires a witness (polling) server.  The witness server’s role is to enable automatic failover – if the mirror server has confirmation from the witness, it can automatically take on the role of principal and make its database available.
    • SQL Server 2005 Database Mirroring requires two unique SQL server instances, one on the principal server and one on the mirror server.

  • Determine the deployment methodology

    • Decide on a localized or geographically disperse deployment.  A geographically disperse deployment provides a disaster solution, but comes with performance implications as the result of network performance, latency, and type LAN vs. MAN vs. WAN.

  • Security

    • SQL Server 2005 Database Mirroring supports both Windows (NTLM/Kerberos) and Certificate based SQL authentication.
    • SQL Server Database Mirroring supports both AES and RC4 encryption algorithms for transmission encryption.

  • Decide on shared vs. dedicated storage

    • Storage is duplicated in mirroring 1TB of storage on the principal server requires 1TB of storage on the mirror server.  Since each server in a mirroring partnership is a unique SQL server instance, the resources are not shared.

  • Plan for capacity

    • SQL Server 2005 Database Mirroring requires duplication of storage, before designing mirroring into your database architecture you should understand the capacity requirements of the current server farm and future scale.  1TB of storage on an MSCS cluster with shared storage requires 2TB of storage in mirroring, 1TB on the principal and 1TB on the mirror.

  • Change – understand the existing SharePoint Products and Technologies infrastructure’s rate of change (churn).  The performance of the principal server is affected by the transfer of log records to the mirror.

    • Long running and/or intensive transactions can impact performance and failover times and can include creating and/or rebuilding an index on a large table or bulk loading a large amount of data (Search).
    • Log bound workload performance under database mirroring is highly dependent on network performance and log I/O.

  • Handling role changes – understand the implications resulting of the loss of the principal or mirror server on SharePoint Products and Technologies.

    • Understand the manual failover steps to support SharePoint Products and Technologies.
    • Script an automatic failover mechanism to handle client-redirects.

  • Managing failover – SQL Server 2005 Database Mirroring works with a single database at a time. You need to take this into account when designing SQL Server 2005 Database Mirroring into your database architecture.  *See Failover Handling
  • Planning and implementation

    • To provide the best initial performance when implementing SQL Server 2005 Database Mirroring, consider taking an online backup of the principal database(s), copy the database(s) to the mirror server and restore the database(s) with the option to apply further transaction logs.  This will reduce the length of time associated with initial synchronization and minimize the performance implications of the initial synchronization.
    • Ensure the implication of mirroring role changes is understood and planned for and the technology has been thoroughly tested before designing into your database architecture.
    • A one to one mapping of principal to mirror server is recommended to maximize compatibility with SharePoint Products and Technologies.
    • In high availability SQL Server 2005 Database Mirroring the process of determining a failover is based on the network connection. If there is a problem with the network, mirroring will fail over or deny access to the database because of the quorum requirement.  Understand the network implications on database mirroring.
    • As with any product or technology understand the support parameters, limitations of the product or technology, and ensure it has been thoroughly tested prior to a production implementation.

Failover Handling

The introduction of a witness server in your SQL Server 2005 Database Mirroring architecture provides a mechanism for automatic failover; however, since mirroring granularity is at the database level, it is important to consider how to handle both single, multiple database and/or server failure.  Since the principal and mirror server SQL server instances are unique, SharePoint Products and Technologies will need to be made aware of the database server hosting its content.  The following sections details the STSADM operations that can be run to create this awareness in Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007.

If an individual database fails you can set the database server using the SharePoint 3.0 Central Administration user interface or STSADM.

Content Database Failover

To change the principal server for a content database using STSADM run:

stsadm -o deletecontentdb -url “<http:// webapplicationurl>” -databasename “<contentdatabase>” -databaseserver “<failedprincipal>”
stsadm -o addcontentdb -url “<http:// webapplication>” -databasename ” <contentdatabase>” -databaseserver “<newprincial>”

To change the principal server for a content database using the SharePoint 3.0 Central Administration user interface:



  1. In SharePoint 3.0 Central Administration select Application Management, then click Content Databases.
  2. From the Manage Content Databases page, select Remove content database for the failed content database.
  3. From the Manage Content Databases page, select Add a content database to reinstate the content database on the new principal server.

Configuration Database and Administration Content Database Failover


The configuration and administration content database must reside on the same SQL database server, in the event that either of these databases fails, both must be failed over to the new database server.  To failover the configuration and administration content database, run the following STSADM from a web front-end computer:

stsadm.exe -o renameserver -oldservername <failedprincipal> -newservername <newprincipal>


Restart Internet Information Services to commit the change.


Search Database Failover

The following STSADM operation should be run from one web front-end computer for each failed search database.

stsadm –o editssp –title <searchname> –ssplogin <username> –ssppassword <password> -searchdatabaseserver <newprincipal>

Shared Services Database Failover

The following STSADM operation should be run from one web front-end computer for each failed SSP database.


stsadm –o editssp –title <SSPName> –ssplogin <username> –ssppassword <password> -sspdatabaseserver <newprincipal>


The information provided above details the steps necessary to instantiate a manual failover of the various components of a SharePoint Products and Technologies server farm, for additional information on scripting automatic client-side redirect in the event of failover see Alerting on Database Mirroring Events and Database Mirroring in SQL Server 2005 under Resources and Recommended Reading.  While it is possible to mirror the configuration and other databases associated with a SharePoint Products and Technologies server farm through the proper implementation of STSADM operations; support of SQL Server 2005 Database Mirroring for SharePoint Products and Technologies is limited to the content databases.


Resources and Recommended Reading


Database Mirroring in SQL Server 2005


SQL Server: Database Mirroring Best Practices and Performance Considerations


SQL Server 2005 Database Mirroring FAQ


Alerting on Database Mirroring Events


Using Database Mirroring with Office SharePoint Server and Windows SharePoint Services


SQL Server Performance Test Results

Comments (31)

  1. Anonymous says:

    H1 { FONT-SIZE: medium } Many thanks to everyone who attended the MOSS 2007 and SQL 2008 "Better Together"

  2. Anonymous says:

    This is the second part of a multi-part series on using SQL Server 2005 Database Mirroring with SharePoint

  3. Anonymous says:

    Bill Baer has started a series of blogs on the subject of SQL 2005 Mirroring as solution for failover

  4. Anonymous says:

    Database Mirroring Resources: SQL Server: Database Mirroring Best Practices and Performance Considerations

  5. Anonymous says:

    The term replication comes up quite frequently in large deployments. It means a number of things to a

  6. Anonymous says:

    Database mirroring is increasing in popularity and becoming an integral part of high availability and

  7. Anonymous says:

    Body: I just had a client ask about migrating a MOSS/WSS database and found this article and this one

  8. Search name refers to the name assigned to the search database; I should mention that while mirroring the configuration and other databases can be acheived through proper implementation of STSADM operations, support of database mirroring for SharePoint Products and Technologies is limited to the content databases.

  9. Anonymous says:

    The SQL Server 2005 Database Mirroring with SharePoint Products and Technologies whitepaper has been

  10. Anonymous says:

    Here are some resources relating to part 3 of the SharePoint Server 2007 webcast series I’m presenting,

  11. Anonymous says:

    Bill Baer put together some fantastic guidance on how to consider database mirroring as a backup for

  12. Anonymous says:

    Hi Bill

    Great article. Well I have a medium farm set up with two SQL 2005 servers on the backend mirrored. I do the failover and run the stsadm commands to get all of my sites up and running. Everything works like a charm. The only thing that’s not working is search. I run this command

    stsadm -o editssp -title WSS_Portal_search -ssplogin contosospadmin -ssppassword p@ssw0rd -searchdatabaseserver sqlmirror

    and I get an error message

    The following arguments are invalid: searchdatabaseserver

    stsadm -o editssp

           -title <SSP name>

           [-newtitle <new SSP name>]

           [-sspadminsite <administration site url>]

           [-ssplogin <username>]

           [-ssppassword <password>]

           [-indexserver <index server>]

           [-indexlocation <index file path>]

           [-setaccounts <process accounts (domainusername)>]

           [-ssl <yes|no>]

    Any ideas? I was on the phone with Microsoft for eight hours the other day. They couldn’t help.

    Thanks.

  13. I am assuming you are calling the renameserver operation?

  14. Anonymous says:

    SharePoint SP1 и SQL Server 2008 вышли достаточно давно, однако я все же наблюдаю некоторую опаску при

  15. Anonymous says:

    Bill Baer has started a series of blogs on the subject of SQL 2005 Mirroring as one solution for failover

  16. Anonymous says:

    Should I cluster or mirror? A few short months ago, the answer to that question would have been easy,

  17. Anonymous says:

    I came across these great resources for setting up a mirrored failover environment for MOSS: SQL Server

  18. Anonymous says:

    Interesantes Recursos para configuración de entornos de Alta Disponibilidad para SharePoint en Base a

  19. Anonymous says:

    Last week at TechReady I attended Mike Watson&#39;s and James Petrosky&#39;s talk on HA and DR scenarios

  20. Muthu;

    Default database locations are established on the SQL database server – these settings will define where new databases are created.  For a list of sample T-SQL statements that can be used to move data and log files visit http://support.microsoft.com/kb/224071.  If using SQL Server 2005 you can change the location using the Management Studio user inteface.  To simply the data/log file move, you may wish to leave the database attached to Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007.

  21. Anonymous says:

    (Cross-posted from Vantage Point: Bob German’s Weblog ) H1 { FONT-SIZE: medium } Recently Rich Crane

  22. In this scenario I would first use STSADM to set the database server for the SharePoint 3.0 Central Administration content database by running STSADM -o deletecontentdb (see example), assuming the operation completes successfully, run the STSADM operations required to instruct SharePoint Products and Technologies of the new configuration database server or new principal in this case.  Once Central Administration is available from a web front-end computer, you can begin the process of updating the database server hosting the content databases, shared services, etc.

    Example:

    stsadm -o deletecontentdb -url "<http://centraladminwebapplicationurl>&quot; -databasename "<centraladmincontentdatabase>" -databaseserver "<failedprincipal>"

    stsadm -o addcontentdb -url "<http://centraladminwebapplication>&quot; -databasename " <centraladmincontentdatabase>" -databaseserver "<newprincial>"

  23. Marcel Jeanneau says:

    Hi William, I’ve testing SQL 2005 Mirroring with MOSS but it’s not working for me. I have 4 VPCs there I’m trying this: 3 SQL SP1 (principal, mirror and witness) and a MOSS RTM box. I installed MOSS using 7 accounts (Install account, farm account, moss app pool account, ssp app pool account, ssp service account, search account and index account). Well I configured SQL Mirroring successfully, and failover works fine (I tested it pausing the sql principal server and after a while put it back online, and it assumed de mirror role). So SQL is fine. The problem is when I try to reconfigure MOSS to point to the new principal server. According to the whitepaper I do first:

    stsadm.exe -o renameserver -oldservername SQLPRINCIPAL -newservername SQLMIRROR

    and restart MOSS. It SHOULD let me get to the Central Administration site to continue with the process. Well I can’t get to the site. When I open the central admin site it tells me "Can’t connect to the configuration database". If I switch SQL roles back to innitial state and issue stsadm -o renameserver on the reverse direction I get my portal back online. But certainly I can’t take advantage of SQL mirroring since I can’t reconfigure MOSS to point to the new principal database, at least, as I told you, following the whitepaper.

    Any idea?

  24. Marcel Jeanneau says:

    Additional question, when talking about Search Database Failover, what do you mean by <searchname>? Is it the search database name?

  25. Last suggestion not working says:

    William, I tried to remove the central admin content db from the failed principal with the command listed above, but it tells me "Farm not available. Reference to object not stablished as an object instance." The db name is right, dbserver name is right too. Any suggestion?

  26. Pedro says:

    Hi William, if I run this command:

    stsadm –o editssp –title WSS_Serach_MyServer –ssplogin user –ssppassword something -searchdatabaseserver MyNewServer

    I get the following error:

    The following arguments are invalid: searchdatabaseserver

    Can you explain whats wrong?

  27. abi says:

    Hi Bill Baer,

    I am Abi and i aam learning database mirroring set up.  Your article is very good and i learnt so many thing from your article. Also could you send me the article  of clustering. I got lot of articles in clustering but what i am looking for  has not yet been found.

    I have the clustered Environment already set up. I need the  documentation or artilcle that  talks about managing the SQL server  instances in the clustered environment.  If you can, please send me the artilce or documents whatever you have to manage the SQL Server Instance in Clustered Environment.

    I look forward to hearing from you soon.

    Thank you.

  28. Tate says:

    Just got off the phone with MS and the stsadm -o edditssp with the searchdatabaseserver and sspdatabaseserver argumetns are invalid.

    As Bill mentions (I did not catch this until I re-read this page) that mirroring is limited to the content databases only.

  29. vipin says:

    Hi,

    Want to know s there any way on automatic failover i can fire stsadmin command to swith the Sharepoint to point to Mirrored DB, I tried to setup an job for Automatic Failover event alert of type CMD to run STSADMIN command.

    But looks like its not taking affect.

    Any clue how to verify that command is executing on alert or not some log or any thing else, because in job history I am see job execution time and duration which is same when Aautomatic failover event fired, when i change the DB status from ‘ALTER DATABASE <<DB>> SET PARTNER FAILOVER;

    Thanks

    Vipin

  30. Can somebody help "how to transfer WSS v3 database to another drive in same server instead of root drive"

    I really need help on this…

    muthu