Radi Atanassov

SharePoint MCM, MVP, MCT and owner of OneBit Software

Step-by-Step Guide to Configuring Database Mirroring in SharePoint 2010

I was looking up some material on database mirroring, and I could find a good step-by-step post on setting up such functionality. So here it is!

Read these posts before you begin - you should really understand the concepts if you are to undertake this, especially in a production environment.

http://technet.microsoft.com/en-us/library/dd207314.aspx

http://blogs.technet.com/b/wbaer/archive/2010/05/03/database-mirroring-in-sharepoint-2010.aspx

http://www.sharepointbandit.com/2010/08/configure-database-mirroring-on.html

My SQL instances are based on the SQL Server 2008 R2 Standard SKU, but they we're upgraded from SQL Express 2008 R2, hence the sqlexpress names:

Mcm-server-1\sqlexpress (Primary) & Mcm-server-2\sqlexpress (Mirror)

SQL Service Account: mcm\svc_sql

Database to sync: WSS_Content_ProductionDB

For mirroring to work, the versions of SQL must be the same on both servers.

1. Configure permissions on the mirror server according to the details from the technet article:

  • The Central Administration application pool account should be a member of the dbcreator and securityadmin fixed server roles.
  • All application pool accounts, the default content access accounts, and any accounts required for service applications should have SQL Server logins, although they should not be assigned to SQL Server fixed server or fixed database roles.
  • Members of the Farm Administrators SharePoint group should also have SQL Server logins and should be members of the same SQL Server roles as the Central Administration application pool account.

2. Ensure that no firewall is blocking connectivity between the servers. In particular, you will create a TCP connection over a port, by default 5022.

3. Make sure that the database is in the Full recovery model

clip_image001

4. Perform a FULL backup of the database you are going to mirror:

clip_image002

5. Place the backup to a place where the mirror server can access it. I dropped it on a network share:

clip_image003

6. Restore the database on the mirror SQL Server. Notice how I have referenced the UNC path - SQL's interface here is not the best, you have to type in the location and file name for it to find the backup. See the next point before you proceed.

clip_image004

clip_image005

7. On the restore page you MUST select a RESTORE WITH NORECOVERY under the Options menu. This is required for DB mirroring to work:

clip_image006

8. Assuming your restore was successful, the database will always show as "Restoring…":

clip_image007

At this stage you could proceed with setting up mirroring.

9. Go to the Mirroring settings page on the Primary server's database in the Database Properties section. Go ahead and click Configure Security…

clip_image008

10. Run through the wizard. Select to set up a witness server if the situation requires one. The wizard will ask you to configure the endpoints for communication on all instances. Make sure the port is open over TCP and that you select to encrypt the data.

clip_image009

clip_image010

11. The next screen lets you specify database mirroring service accounts. In my case I left them blank as all SQL instances are running under the same service account. Read this article for more info on service accounts: http://msdn.microsoft.com/en-us/library/ms189434.aspx

clip_image011

12. Continue the wizard and your endpoints will get created:

clip_image012

13. Click close and you will get the next message with some information. Click the Start Mirroring button and hopefully it works (:

clip_image013

14. You should get no errors and your Mirroring page should look similar to mine. (I postponed the configuration of my witness):

clip_image014

15. Your databases will indicate that mirroring is configured:

clip_image015

clip_image016

(ignore the ArchiveDB in the screenshot, I had to set it up twice...)

16. To check if it is all working you could use the "Launch Database Mirroring Monitor" tool:

clip_image017

This will provide you with information, which is near real time. I uploaded a large document on the Primary server in SharePoint and refreshed the tool a few times:

clip_image018

Now it is time to tell SharePoint that we have a failover database set up. SharePoint must know this to change its connection string when the mirrors swap. To do this we must use PowerShell:

$db = get-spdatabase | where {$_.Name -eq "WSS_Content_ProductionDB"}

$db.AddFailoverServiceInstance("MCM-SERVER-2\sqlexpress")

$db.Update()

In Central Administration you will see the setting populated. Read Bill Baer's post on how exactly this setting is used.

clip_image019

As a final test, go back in SQL, go on the mirroring page and click the Failover button. This will ultimately swap the roles of your two databases.

clip_image020

Click it and make sure SharePoint is still working for you! Hope this helps!

Missing the “Mirroring” page in SQL Server Database Properties

I was working on setting up database mirroring on a SharePoint 2010 installation, and my “Mirroring” tab was missing in “Database Properties” in SQL Management Studio.

Configuring it through scripts was possible, but I still wanted the interface.

Here is a screenshot with the option missing:

clip_image001

My installation of SQL Server 2008 R2 Standard was upgraded from SQL Express 2008 R2. The catch here is that the “Upgrade SKU” options don’t install the "Complete” set of SQL Management Tools. So you must go through the installer and explicitly install this toolset.

clip_image001[5]

 

Now the mirroring option will be available:

clip_image001[7]

SharePoint 2010: Backup and Restore Farm Configurations

I often get called out to do SharePoint health checks - both environment and customisations. The people I've worked with know I'm an absolute nazi - if it's not in the books then it should be pointed out. Although some tasks are individual to the case, I have a really long checklist that I always go through. One of the first items on that list is to backup everything I could. A responsible consultant should do so. I also like to be sure that there is a restore process in place, and that the client has tested it. I hate the feeling of going into a company knowing that their restore process is not good enough.

SharePoint 2010 has new feature that makes backing up SharePoint configuration settings much easier than what it was in 2007. Through Central Administration or Powershell you could "backup" the configuration of the farm without any content. In 2007 backing up the configuration database was supported, but restoring it was not. Only certain situations allowed it.

Central Administration

To backup your farm configuration, go to "Backup and Restore" in Central Administration, then click "Perform a backup".

clip_image001

You will be taken to a screen which allows you to select the components to back up. Since we want the configuration, we must select the whole farm. Click "Next".

clip_image002

The next screen is where it all gets interesting. In SP 2010 you get a new option, "Back up only configuration settings". Select it, and specify a UNC file path where the timer service has rights to write to disk. Notice the required disk space and make sure you have enough. Don't be worried if the backup is less than that, mine ended up 1MB.

clip_image003

Click "Start backup" and off you go. You can monitor the progress on the Timer Job Status page, or the Backup and Restore page:

clip_image004

clip_image005

Given that it is configuration only, it didn't take long. Under 5 minutes.

Once complete, you could be enthusiastic and check out how SP backs things up. The folder will contain .BAK files, which are XML, and represent the objects and their properties as they lived within the configuration database.

clip_image006

The XML will be very familiar to developers.

Now, restoring is also interesting. You get the option to choose to restore to the "Same" server, or a new environment. If you select new, you get the option to input new names - databases, database servers, etc.

Go to "Backup and Restore" in Central Administration, then click "Restore from a backup". Enter the folder where you placed your backup, and choose the latest one (there could be more than 1 backup in that folder).

clip_image007

Select the whole farm:

clip_image008

Notice the Restore Options on the same screen. "Same configuration" is for restoring to the same farm, "New configuration" is if you have different computer names, databases and other settings.

clip_image009

You can fire it off and monitor the progress:

clip_image010

And that's pretty much it.

Powershell

To achieve the above with Powershell:

PS C:\Users\Administrator> Backup-SPConfigurationDatabase -Directory c:\spbackup -DatabaseServer devbox -DatabaseName SharePoint_Config -Verbose

Note, this will not get executed by the Timer service, so you will not find a timer job. You will, however, see the backup job in the Backup and Restore job status page. Also, Verbose is pretty nasty on the Powershell screen.

To restore from Powershell:

PS C:\Users\Administrator> Restore-SPFarm -Directory c:\spbackup -RestoreMethod Overwrite -ConfigurationOnly

The command will ask you to confirm.

Hope this helps!

Recreating the SiteMap in your configuration database

When having a disaster recovery farm facilitated by Log Shipping, you would set up content databases in your secondary farm to process logs from your primary farm. For that to work successfully, you need to configure and set up the secondary farm with it's own configuration database, and some services also can't be configured as targets for log shipping or asynchronous mirroring. This KB article has more info on that: http://technet.microsoft.com/en-us/library/ff628971.aspx

As you create sites in your primary environment, the Configuration Database in the secondary environment(s) don't get updated - they end up not knowing about these sites. The configuration database stores a "SiteMap" of all sites, and that is used in various places, such as Search, the Central Administration UI, and I also believe the constructor of an SPSite object uses that to identify the exact site you are instantiating.

In SharePoint 2007, the only way to refresh the SiteMap in the configuration DB is to detach and re-attach the content database. Apart from being inconvenient and not well known this was also adding to precious minutes in downtime.

In SharePoint 2010 you can also detach and re-attach the database, but we now have a managed method that does the job for us. The SPContentDatabase object has a RefreshSitesInConfigurationDatabase() method that we could call from code or PowerShell:

To list all databases in the farm:

Get-SPDatabase

To get a specific database in a variable:

$contentDB = Get-SPDatabase | where {$_.Name -eq "WSS_Content_Contoso"}

To execute the method on the database:

$contentDB.RefreshSitesInConfigurationDatabase()

You will have to do this on each disaster recovery farm that you have.

Opening up this method with Reflector, we could see its implementation.

clip_image001

It first calls the CleanUpSiteMap() method, which deletes all entries that do not exist anymore. It runs the following SQL query:

"SELECT SiteMap.Id, SiteMap.Path FROM SiteMap WHERE SiteMap.ApplicationId=@ApplicationId AND SiteMap.DatabaseId=@DatabaseId"

And then runs a check for each SiteMap.Id (the GUID of the site), deleting entries where the site is not found.

After that it calls RestoreSiteMap() , which internally gets all sites and runs the SPConfigurationDatabase.CreateSite() method. And that’s how the configuration database gets updated.

The key thing here to point out is that having a HOT standby environment requires planning and testing. If you haven’t tested your recovery strategy you might as well say you don’t really have one.

Other resources:

http://msdn.microsoft.com/en-us/library/ms191233.aspx

http://technet.microsoft.com/en-us/library/cc748824.aspx

http://blogs.technet.com/b/mahesm/archive/2010/04/30/configure-disaster-recovery-farm-with-sharepoint-2010.aspx