Safeguarding Confidential Data in SharePoint 2010 : Enabling SQL Database Mirroring

3/25/2011 9:03:46 AM
To enable SQL Database mirroring for a SharePoint environment, follow the steps outlined in this section.

Exploring the High-Level Steps Involved with Setting Up a Database Mirror

The steps to set up a simple mirror relationship between principal and mirror instance are relatively straightforward. In essence, you just need to back up and restore the databases to the mirror server and configure the server:

Set the transaction level to FULL for all databases that will be mirrored (if not already set).

Perform a full backup of the databases.

Perform a logs backup of the databases.

Restore the full backup onto the mirror server with the NO RECOVERY option.

Restore the logs backup onto the mirror server with NO RECOVERY option.

Configure security between principal and mirror using the wizard. If necessary, configure the witness server.

Enable mirroring between databases.

Each step is explored in more detail in subsequent sections.

Backing Up the Databases to Be Mirrored

SQL mirroring works by creating an exact copy of a database on a separate server and then keeping that copy in sync with the original copy. To set up mirroring, the secondary, mirror server subsequently requires a restore of the original database to set up the initial mirror copy. That restore does not have to be an exact copy of the database as it is today, but it should be a relatively recent restore. After the mirror has been set up, the database will automatically replicate any changes made to the database since it has been backed up.

Both a full backup and a logs backup will need to be performed. These can be performed directly from the GUI, such as what is shown in Figure 1, or a TSQL script can be created, such as the example shown here:

TO DISK = 'C:\Backup\WSS_Content.bak'
TO DISK = 'C:\Backup\WSS_Content_Log.bak'

Figure 1. Backing up a content database to be mirrored.

Replace WSS_Content with the database to be backed up. Repeat for additional databases. After being backed up, the databases will need to be moved, either across the network or physically using tape or other medium.

Restoring the Databases onto the Mirror Server

After the database backups have been transferred to the mirror server, they should be restored onto that SQL instance. What is critical is that both the full backup and the logs backup must be restored with the NORECOVERY option chosen. If this is not chosen during the restore process, the mirror won’t be able to be created. If restoring using the GUI, choose the Restore with NoRecovery radio button in the Options tab, and be sure to do it for both database and logs restores, as shown in Figure 2.

Figure 2. Restoring the logs with No Recovery to set up a mirror copy.

You can also choose to use a TSQL script, similar to the following, to restore the databases:

FROM DISK = 'C:\Restore\WSS_Content.bak'
FROM DISK = 'C:\Restore\WSS_Content_Log.bak'

Replace the file location and the database name to match your environment and repeat for additional databases. After both the full backup and the logs backups have been restored with NORECOVERY, you can configure the mirroring.

Configuring Security and Enabling Mirroring

There are two steps to the mirroring process. The first is to set up a security relationship between the principal server and the mirror server. The second step is to actually configure the mirror itself.

By right-clicking the source database (on the principal SQL instance) and choosing properties, and then selecting the Mirroring node in the navigation pane, you get the dialog box shown in Figure 3. Note that the mirroring options are grayed out. Click Configure Security to set up the relationship between the principal instance and the mirrored instance. This wizard also allows you to choose whether there will be a witness server.

Figure 3. Beginning the process to configure security for the mirror.

During the wizard, accept the default port of 5022 (be sure this is open in the Windows Firewall) and the default endpoint name of Mirroring. Ensure that service accounts are the same on both sides, or specify the various service accounts. After running the wizard, it should show a successful endpoint configuration on both principal and mirror, as shown in Figure 4.

Figure 4. Configuring security between principal and mirror.

After the wizard runs, you will have the option of immediately configuring mirroring or waiting and turning on mirroring later. Once enabled, the dialog box should look similar to what is shown in Figure 5 and the database should show up in the SQL Management Console with a (Mirror, Synchronized) tag after the name of the database. Repeat this process for each remaining databases.

Figure 5. Enabling SQL database mirroring.
  •  Safeguarding Confidential Data in SharePoint 2010 : Outlining Database Mirroring Requirements
  •  Remote Administration of Exchange Server 2010 Servers : RDP with Exchange Server 2010 (part 2)
  •  Remote Administration of Exchange Server 2010 Servers : RDP with Exchange Server 2010 (part 1) - Planning and Using Remote Desktop for Administration
  •  Remote Administration of Exchange Server 2010 Servers : Using the ECP Remotely
  •  Safeguarding Confidential Data in SharePoint 2010 : Examining Supported Topologies
  •  SharePoint 2010 : SQL Server Database Mirroring for SharePoint Farms
  •  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)
    Top 10
    Nikon 1 J2 With Stylish Design And Dependable Image And Video Quality
    Canon Powershot D20 - Super-Durable Waterproof Camera
    Fujifilm Finepix F800EXR – Another Excellent EXR
    Sony NEX-6 – The Best Compact Camera
    Teufel Cubycon 2 – An Excellent All-In-One For Films
    Dell S2740L - A Beautifully Crafted 27-inch IPS Monitor
    Philips 55PFL6007T With Fantastic Picture Quality
    Philips Gioco 278G4 – An Excellent 27-inch Screen
    Sony VPL-HW50ES – Sony’s Best Home Cinema Projector
    Windows Vista : Installing and Running Applications - Launching Applications
    Most View
    Bamboo Splash - Powerful Specs And Friendly Interface
    Powered By Windows (Part 2) - Toshiba Satellite U840 Series, Philips E248C3 MODA Lightframe Monitor & HP Envy Spectre 14
    MSI X79A-GD65 8D - Power without the Cost
    Canon EOS M With Wonderful Touchscreen Interface (Part 1)
    Windows Server 2003 : Building an Active Directory Structure (part 1) - The First Domain
    Personalize Your iPhone Case
    Speed ​​up browsing with a faster DNS
    Using and Configuring Public Folder Sharing
    Extending the Real-Time Communications Functionality of Exchange Server 2007 : Installing OCS 2007 (part 1)
    Google, privacy & you (Part 1)
    iPhone Application Development : Making Multivalue Choices with Pickers - Understanding Pickers
    Microsoft Surface With Windows RT - Truly A Unique Tablet
    Network Configuration & Troubleshooting (Part 1)
    Panasonic Lumix GH3 – The Fastest Touchscreen-Camera (Part 2)
    Programming Microsoft SQL Server 2005 : FOR XML Commands (part 3) - OPENXML Enhancements in SQL Server 2005
    Exchange Server 2010 : Track Exchange Performance (part 2) - Test the Performance Limitations in a Lab
    Extra Network Hardware Round-Up (Part 2) - NAS Drives, Media Center Extenders & Games Consoles
    Windows Server 2003 : Planning a Host Name Resolution Strategy - Understanding Name Resolution Requirements
    Google’s Data Liberation Front (Part 2)
    Datacolor SpyderLensCal (Part 1)