Mysterious config database: you can back it up but not restore?

March 20, 2009

When you start planning for disaster recovery of your SharePoint farm you inevitably face the challenge of how to restore the farm configuration.

What is a configuration database? A configuration database in SharePoint is what defines your farm. It keeps all the information about other databases, servers and services that comprise the farm. It also stores info about “all Internet Information Services (IIS) Web sites or Web applications, solutions, Web Part packages, site templates, and Web application and farm settings specific to SharePoint technologies, such as default quota, blocked file types, and configuration” (from Database types and descriptions). If you want to drill into this in more detail, take a look at the database structure explained in Nidhi’s blog.

The coolest thing about configuration database in my opinion is how it serves as a central piece in WSS 3.0 architecture to allow administrators easily scale a SharePoint farm. All the global farm settings, most of IIS configuration (some details in Joel’s post here), and even solution binaries are stored in the config database and will be automatically propagated from there when you join more servers to the farm.

What’s the problem with backup/recovery of configuration database? Restoration of SharePoint configuration database is not supported by Microsoft if all you do is just take farm backup via Central Administration or with STSADM. In case of disaster recovery, you would have to manually re-create all farm settings, re-deploy any solutions and customizations, etc. A related article on TechNet gives a little insight on what causes this support limitation:

Although the configuration database and the SharePoint Central Administration Web site content database can be backed up, restoring backups of the configuration database and Central Administration content database from a farm by using the tools built in to SharePoint Products and Technologies is not supported.

This is because data in these databases may not be synchronized with data in other Microsoft Office SharePoint Server 2007 databases. Therefore, the tools built in to SharePoint Products and Technologies do not recover these databases during a farm-level recovery.

If this data is not synchronized, users might experience various random errors.

So, is there any way I can back up and restore the config database? We were discussing this with a well-known SQL expert Charley Hanania a while ago, and came to a simple thought – if something can be backed up, it must be possible to restore it. It’s the question of understanding the risks and choosing the right tools to mitigate them. If the risk is that SharePoint farm structure information in config database would be not synchronized with other databases, you need a way to make a point-in-time backup of all databases within the farm, ensuring that none of these databases changes during the backup process. How is this possible?

  • Obviously, snapshot technologies and products that use them can achieve this functionality. For example, this allows Microsoft Data Protection Manager to back up and restore all SharePoint databases, including configuration db.
  • Another supported way to restore all databases suggested on TechNet is to take a backup when the SharePoint farm is offline. Essentially, this means all SharePoint services are stopped across all front end and application servers, and no changes are being made during the backup process. Does not seem feasible for live SharePoint environment? This is where SQL Server’s features such as log shipping or database mirroring can help. Both techniques allow you to create and maintain a copy of SQL database that is not accessed by SharePoint. So working closely with your SQL DBA you can have a “point-in-time” copy of all the farm databases and back them up.

Config db is not the only reason why you can utilize log shipping and/or database mirroring in SharePoint environment. For example, see Mike Watson’s recent presentation decks from Best Practices and SPTech conferences, where he discusses how both options apply to SharePoint environment for high availability and disaster recovery.

Technorati tags:

Advertisements

7 Responses to “Mysterious config database: you can back it up but not restore?”

  1. David Says:

    Hi – Do I need to worry that I am not able to restore the SharePoint_Config DB on my new server?

    I can successfully move the content and AdminContent DB’s.

    Everything seems to be ok with SharePoint_Config?

    Thanks


  2. David,

    Do you mean you want to move all SharePoint databases to a new SQL Server? There is a procedure for this published at http://technet.microsoft.com/en-us/library/cc512723.aspx.

    Hope this helps.

    Thanks,
    Ilia


  3. Nicely written! Thank you!


    • Natalia, glad if this writeup was helpful to you.

      It is somewhat a disappointment for me that nothing has really changed in SharePoint 2010 and you still have to jump thru the hoops to restore a SharePoint farm, even when you have a proper farm backup. I guess Microsoft sees that as a low priority for SharePoint team, leaving the space to DPM and partners.

  4. amy Says:

    If I have a VM snapshot of the front-end server and full SQL database backups of all databases with services offline during the backup… how would I restore to the same server? I know that I can connect a new content db and set the config db, but what about the others? Could I just restore everything with SQL with the same names to replace?


    • This is a great question and I wish I had a good answer for you. As far as I know, currently Microsoft does not officially support restore of a SharePoint farm from VM snapshots, unless ALL snapshots were made when the farm was shut down.

      If you have all of your SQL databases backed up and all of your configuration settings documented, the restore process would be (on the high level):
      1. Create a new SharePoint farm with a new config database
      2. Re-create configuration settings, web apps, service apps, etc.
      3. Re-deploy any additional front-end, index and search, application servers as necessary. Run PSCONFIG to add these to the new farm
      4. Re-deploy any customization solutions you had
      5. Attach your content databases from backup to the new farm

      Lots of manual work. For a smaller farm, SharePoint built-in backup and restore allows to save some time and efforts and protects more configuration that SQL backups. For larger farms consider 3rd party tools and/or scripted farm deployment.

      Some additional information can be found here http://technet.microsoft.com/en-us/library/cc261687.aspx (and following the links from this article).

      Hope this helps!

  5. Judith Says:

    Hi Ilia,
    Thank you for the article above. But I still have some unanswered questions. If you have a chance, do you mind helping me with this question?

    We are running MOSS 2007 SP2. I need to run security scan on our site. Based on test scan ran in the test environment, no content is changed. But as a precaution, before running the scan on Production, I would like to have a good working backup that can be used to restore the farm in case we need to restore all 9 databases (Config, CA, content, MySite, SSP, etc).

    I read in Microsoft Technet (http://technet.microsoft.com/en-us/library/cc262704(v=office.12).aspx) that I have to stop the farm when using the SQL Server backups to restore farm (including the config and CA content databases). The article mentions about possible synchronization issue.

    If I know that noone will be adding / deleting new sites, no change to the farm topology or configuration and here we don’t really use My Sites, users may just be adding/modifying documents instead on libraries, can I rely on the backups for all of the databases in the farm that are taken while the farm is running? So if there is a need to restore, I would restore all of the databases from the backups taken at for e.g. 8 PM to ensure they are all on the same state. I think the synchronization issue may happen if someone is adding/deleting sites and this information is not yet synched to the config database, right?

    Based on your experience, what is the backup strategy that you have in your implementation? Since I have limited resource on the SP server, I am only relying on the SQL Server database backups run daily (while the farm is running) and I don’t run SP backups (run from CA).
    I would appreciate if you could shed some light on this.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: