In this blog post, we will cover the mirroring of an existing SharePoint content database using SQL Server 2005. We will also discuss some basics of Search Index Servers in a SharePoint farm. The objective is to provide a light-weight cursory knowledge of how to setup and configure these two aspects of the SharePoint farm. This will not be a detailed blog covering endless situations but a DPM Administrator-oriented post in preparation for protecting these data sources using DPM 2007 SP1.
Subsequent blogs in this series will cover Protecting mirrored SharePoint databases, handling failovers of mirrored SharePoint databases, recovering mirrored SharePoint databases and a couple of posts on protecting and recovering Search Index databases.
Periodically, there will also be short video demonstrations provided to round out the blog topic. Additional information and greater detail about the various ways to mirror SharePoint databases can be found online at http://technet.microsoft.com.
Mirroring a SharePoint Content Database
In our demonstration, we will be mirroring a single configuration database that is in a farm with several other content databases. There is no limitation stating that all content databases in a farm must be mirrored. There is flexibility in which content databases you choose to mirror and which will be left un-mirrored. We will take an existing content database and go through the steps of mirroring it across a SQL 2005 Failover cluster and a stand-alone SQL Server.
We created the content database “WSS_Content_8100” for a new “team docs” site we were going to build. You see it highlighted below in the list of databases on this cluster from within “SQL Management Studio” console.
Having seen the database that we are going to mirror, let’s go back to the Central Administration web site for SharePoint and prepare to mirror the content database. Under the Application tab, choose the link Content Database so that the Manage Content Databases page appears as shown below.
Under the Database Name field is a link to the Manage Content Database Settings page for the database whose name appears there. In our example, the WSS_Content_8100 database name appears there and when we click on that link, it will bring us to the screen we see below.
In the Database Information section at the top, note the Database status is likely set to Ready as this is the default setting. Before we mirror this content database, we need to change this to Offline so that no new site collections can be added to this content database while we are setting up the mirror.
This is the only change we are going to make so scroll down to the bottom of the page and click OK to commit the change. When it completes, the Database Status should now appear as Stopped.
Now that the SharePoint side of the configuration has been made, we will proceed to the SQL Management Studio side to begin the process of mirroring the database.
If you have not already seen the blog “SQL Database Mirroring for DPM Administrators”, now would be good time to jump over and review it. Since the steps on mirroring a database are the same as mirroring a SharePoint content database from this perspective, the steps will not be duplicated here.
Once the database has been mirrored and failover has been confirmed to work, you can now return the Database Status to Ready as shown above.
When mirroring has been setup on the database, you will see a configuration like the following in SQL Management Studio.
As long as the Principal server is the same as the server on which the content database originated, you and your users should be able to see the content in any sites within the Web Applications that use that content database.
Testing Failover of the Mirrored Database
Now that the database has been mirrored, it is time to test failover of the mirror to confirm that the data is still accessible to SharePoint users.
From SQL Management Studio, failover the database from the Mirroring page in the database properties. Refresh the SQL Management Studio view to confirm that the Principal role is now held by the other server in the mirroring partnership.
Now navigate to a well-known page in a site whose information is stored in the newly mirrored content database. Refresh the page and the following error should be returned. The URL doesn’t mention anything about the SQL2k5-CLU SQL instance so there is no clue there. The only indication that we get is at the end of the message where it states “…make sure the database server is running.”
It indicates a database issue.
Note: This configuration change coming up only needs to be performed on a single WFE (Web Front-End) server and not every WFE in the farm. Anytime a mirrored content database is failed over, make this change on one WFE server to restore user connectivity to the content.
To correct our user access issue so that they are able to view the previously accessible content, we will need to go back to the Manage Content Databases page and click on the Database Name object for the database that we just failed over.
Scroll straight to the bottom, check the Remove content database and commit the change.
When you click on the Remove content database checkbox, the following message is displayed. Consider this: users are not able to access the content anyway because the database is not accessible on the SQL instance where it is expected to be. Taking this action is required in order to restore user access.
Click on OK knowing that this is the right thing to do.
Tip: At the top of the page is the database name and the SQL server name. Copy the database name to the buffer\clipboard so you avoid any typing mistakes. You may want to copy and paste this and the database server both to Notepad for future reference.
Now click on OK at the bottom of the page and allow SharePoint to remove the entry.
Now that the Manage Content Databases page is empty, we can add the entry back in. Click on the Add a content database link near the top after confirming that the Web Application in the list on the right is the correct one.
When the Add Content Database page appears, enter the name of the SQL Server where the current Principal role for the database mirror resides. Now tab down and enter in the content database name for the mirrored database. Once these are done, scroll down and click on OK to commit these entries.
The WSS_Content_8100 database should now appear as seen in the screen shot below with a Started status. This is what you need to do from the SharePoint side to restore user connectivity. Now it is time to verify user access to the content.
Back to our IE session at this point to verify if the web content is accessible.
In some cases, the addition of the content database will error out. DO NOT PANIC!! The link just above the Error will redirect you back to the Manage Content Databases page. Click on this and confirm that the WSS_Content_8100 entry is still there. Also, check to see that the database is using the SQL2k5-MirrorA server (based on our example) and not the original. If these things check out and the page is accessible by users, this Error can be ignored.
Congratulations! You have successfully mirrored a SharePoint content database using SQL 2005. Having this understanding of a technology that DPM protects, even to such a basic level, will be a great help when working these types of issues.
This blog covered the steps necessary to take a functional SharePoint content database that resides on a SQL 2005 installation and mirror it using SQL Database Mirroring. Also discussed was how to stabilize a SharePoint site when the content database it is a part of is failed over from one server to the other.
The video included with this blog demonstrates the steps outlined here. This is provided to help reinforce the steps discussed and provide quick reference for future configurations.
Support Escalation Engineer
Microsoft Enterprise Platforms Support