SharePoint 2010 : SQL Server Database Mirroring for SharePoint Farms

3/18/2011 9:10:44 AM
Understanding the Threats to SharePoint Data

A SharePoint document management and collaboration environment is a mission-critical service for many organizations. In many cases, a SharePoint environment contains the physical representation of the intellectual property of an organization, and it is subsequently critical that the data in a SharePoint environment is secure and reliable.

Modern threats to SharePoint data integrity take many forms, but organizations can’t afford to ignore them. Some common issues are as follows:

  • Data redundancy— The ability to have an up-to-date copy of data in more than location is a critical requirement because it provides a data redundancy solution for hardware and site disaster scenarios or outages. This type of requirement is provided for SharePoint 2010 with SQL database mirroring.

  • Data high availability— The ability to eliminate single points of failure in an environment that houses mission-critical data is key. High availability in SharePoint can take multiple forms, but often includes network load balancing used together with either SQL clustering or SQL database mirroring.

  • Data integrity— The ability to control the integrity and security of data, in transit, at rest, and when backed up, is critical. Technologies such as SQL TDE can help to secure data in storage and when backed up, providing better overall data integrity.

  • Data leakage— The ability to control what happens to sensitive data after it has been accessed is becoming more and more critical. Technologies such as AD RMS can be used to provide for this much-needed functionality, as they restrict the ability of users to print, copy/paste, or send data outside of a company.

SQL Server Database Mirroring for SharePoint Farms

Introduced in SQL Server 2005 Service Pack 1, database mirroring is a software solution that delivers high availability/database redundancy of SQL databases, including SharePoint databases. SQL mirroring is highly valuable for SharePoint environments because it provides the promise of having an always-available constant replica of SharePoint databases on a different server and being able to fail over to that server, either automatically or manually, as needed.

There are two primary partners in a database mirroring session: the principal server and the mirror server. Essentially, database mirroring works by maintaining a copy of the same database or databases on both partners. This is accomplished by streaming the active transactional log records from the principal server to the mirror server, which in turn applies the log records to the mirror database. In comparison to failover clustering, database mirroring works on a per-database basis, and provides high availability and data protection for both storage and hardware failures. In addition to the two partners involved in a database mirroring session, a third server instance, known as the witness server, can be added to provide automatic failover by verifying whether the principal server is up and functioning.

Utilizing SQL database mirroring for a SharePoint environment can have the advantage of having a second, fully complete copy of all SharePoint content automatically replicated to a secondary location. This is the equivalent of running a constant backup of SharePoint data, and has the added advantage of allowing for instant failover to a secondary SQL server in the event of a failure. Indeed, this version of SharePoint has been written to be “mirror-aware,” and all databases can be configured with a secondary SQL server instance chosen to allow for mirroring failover scenarios.

Understanding Operating Modes

There are essentially three operating modes for database mirroring, as follows:

  • High-safety mode (synchronous)— After a database mirroring session is initiated under high-safety mode, the mirror server database synchronizes with the principal server database and then writes the logs to disk. After the logs have been written to disk, all transactions are committed on both servers. Although this mode guarantees no data loss between the two servers, it does come with the expense of increased transaction latency.

  • High-availability mode (synchronous)— Also referred to as high-safety mode with automatic failover, this operating mode is made available only with the presence of a witness server. This mode is the recommended operating mode for database mirroring within a SharePoint farm because of its ability to provide increased high availability through automatic failover. Similar to high-safety mode, the mirror server synchronizes the mirror database with the principal database and proceeds by committing transaction logs on both servers after the mirror server has written the logs to disk.

  • High-performance mode (asynchronous)— Running under asynchronous operation, this operating mode provides an increased level of performance by sending logs from the principal server to the mirror server and then immediately committing the transactions on the principal server without waiting on the mirror server to write the logs to disk. By minimizing the latency of committed transactions, this mode increases the overall performance of the principal server. However, it also increases the risk of data loss between the two partners. Asynchronous mirroring is available only with the Enterprise edition of SQL Server, whereas the other two modes are available in either the Standard or Enterprise editions.

Although it is possible to switch between operating modes once a database mirroring session is configured, it is essential that administrators understand each operating mode and their respective advantages and disadvantages to successfully meet the availability requirements of their organization.

Understanding Transaction Safety Levels

Transaction safety levels work hand in hand with operating modes. Based on the configured operating mode, the transaction safety level will either be set to FULL or OFF. In turn, if the database mirroring session is configured using Transact-SQL statements instead of SQL Server Management Studio, the transaction safety level determines the operating mode. Essentially, if the operating mode is running under synchronous transfer mode, the transaction safety level will be FULL, whereas if the transfer mode is running asynchronously, the transaction safety level will be OFF.

When using SQL Server Management Studio to configure a database mirroring session, the transaction safety level will automatically be set to FULL if the partner and mirror servers operate in either high-safety or high-availability mode. If the two partners are configured for high-performance mode, the transaction safety level will automatically be set to OFF. If Transact-SQL statements are used to configure a database mirroring session, the SAFETY property in the ALTER DATABASE statement should be set to either FULL or OFF, depending on the desired operating mode. The following statement is an example of setting the transaction level safety to FULL:


Table 1 lists the different operating modes and their respective transaction safety levels.

Table 1. Operating Modes
Operating ModeTransaction Safety LevelTransfer Mode
High SafetyFULLSynchronous
High PerformanceOFFAsynchronous
High AvailabilityFULLSynchronous

  •  Remote Administration of Exchange Server 2010 Servers : Using the Remote Exchange Management Shell
  •  Remote Administration of Exchange Server 2010 Servers : Certificates, Trust, and Remote Administration
  •  Enabling Presence Information in SharePoint with Microsoft Communications Server 2010
  •  Integrating Exchange 2010 with SharePoint 2010
  •  Documenting an Exchange Server 2010 Environment : Exchange Server 2010 Project Documentation
  •  Documenting an Exchange Server 2010 Environment : Benefits of Documentation
  •  Getting the Most Out of the Microsoft Outlook Client : Using Cached Exchange Mode for Offline Functionality
  •  UML Essentials - UML at a Glance
  •  Understanding Microsoft Exchange Server 2010
  •  Working with Email-Enabled Content in SharePoint 2010
  •  Enabling Incoming Email Functionality in SharePoint
  •  Getting the Most Out of the Microsoft Outlook Client : Using Outlook 2007 (part 3) - Using Group Schedules
  •  Getting the Most Out of the Microsoft Outlook Client : Using Outlook 2007 (part 2) - Sharing Information with Users Outside the Company
  •  Getting the Most Out of the Microsoft Outlook Client : Using Outlook 2007 (part 1)
  •  Implementing and Validating SharePoint 2010 Security : Using IPsec for Internal SharePoint Encryption
  •  Examining Integration Points Between SharePoint and Public Key Infrastructure
  •  Getting the Most Out of the Microsoft Outlook Client : Deploying Outlook 2007
  •  Getting the Most Out of the Microsoft Outlook Client : Implementing Outlook Anywhere
  •  Getting the Most Out of the Microsoft Outlook Client : Security Enhancements in Outlook 2007
  •  Getting the Most Out of the Microsoft Outlook Client : Highlighted Features in Outlook 2007
    Top 10
    Smart, But Not Pricey : LG Optimus L7, Nokia Lumia 610, Sony Xperia U, Micromax A90S, BlackBerry Curve 9320, Nokia Lumia 710
    Acer Iconia W510 - Multi-Faceted Windows Tablet
    The Big Show: LG Optimus Vu
    Edifier E10 Exclaim - Exclamation Mark
    If It Bleeps We Can Mix It (Part 2)
    If It Bleeps We Can Mix It (Part 1)
    Jabra SOLEMATE Bluetooth Portable Speaker
    Projecting Movies From Smartphone – Sharp BD-AMS20S
    Sharkoon X-Tatic SP Plus - Talk About Lots Of Cables
    Audio Technica ATH-FC707 - No Headline, Too Busy Enjoy Music
    Most View
    Java Mobile Edition Security : Development and Security Testing (part 3) - Code Security & Application Packaging and Distribution
    Top Tips For Mountain Lion (Part 3)
    DSLs in Boo : Implementing the Scheduling DSL
    Synchronizing Mobile Data - Using Merge Replication (part 2) - Programming for Merge Replication
    How To Find The BEST DEALS (Part 2)
    Windows Vista : Communicating with Windows Mail - Setting Up Mail Accounts
    Windows Phone 7 Development : Using Rx.NET with Web Services to Asynchronously Retrieve Weather Data
    Windows Azure : Common uses for worker roles (part 1)
    iPhone 3D Programming : Adding Depth and Realism - Filling the Wireframe with Triangles
    CorelDraw 10 : Alternate Printers and Paper Options, Creating a PDF File
    A New Leaf (Part 2)
    Lomond EvoJet Office
    Installing HP-UX : Remote Support (Instant Support Enterprise Edition)
    Asus GTX 660 Ti DirectCU II Top (Part 1)
    Thermaltake Water2.0 Pro - Cool, But Loud
    Macro Marvels (Part 4)
    Programming .NET Framework 3.5 : Placing Text
    Backup And Maintenance Software – Jan 2013
    The End Of The Beginning Or The Beginning Of The End? (Part 1)
    How To – December 2012 (Part 1) : Install Windows 8 from a USB Drive