As most of the MOSS 2007 developer/administrator knows, you may have multiple content databases per MOSS web application but MOSS site collection can be hosted only on one content database.
If you have one large content database with one large site collection, unfortunately only way to migrate content database components is re-architect the SharePoint environment and migrate sub sites to the another site collection or web application.
If you are lucky enough to have web application architected with multiple site collections, MOSS offers STSADM mergecontentdbs command that you can use to migrate specific site collections from one content database to another. It is also important to remember that mergecontentdbs command was introduced as part of SP1 service pack release (more specifically October 2007 Cumulative Update). If your MOSS farm is running the RTM version, you must upgrade the SharePoint environment to use this command. To use the mergecontentdbs command to move the site collection from one content database to another, the source and destination databases must be in the same Microsoft SQL Server instance and must be attached to the same Web application.
In order to move a site collection from one database to another, you must be a member of both the Farm Administrators group and the Local Administrators group, and must have the Full Control permission granted for any site collection that needs to be moved. To grant this permission, in Central Administration, click Application management, and then Application Security, and then Policy for Web application. The account that you use to perform this procedure must be a member of the db_owner fixed database role in SQL Server. If you do not have the correct permissions to perform the operation, you will receive the following error message: “Moving sites… Another site already exists at /sites/test. Delete this site before attempting to create a new site with the same URL, choose a new URL, or create a new inclusion at the path you originally specified.”
Let’s say scenario is MOSS farm My Sites web application currently hosts more than 6000 personal sites on the single large content database. All the personal sites need to be hosted on the multiple content databases to improve scalability. My Sites content database will be partitioned into 6 content databases to host equal number of personal sites with maximum limit of 1500 site collections per content database and initially all content databases will have approximately 1000 site collections after partition.
Here is the step by step process of partitioning the content database associated with the My Site web application. The process of moving a site collection between databases consists of exporting the site collection URLs to a file and then moving the site collection URLs to a new database.
- Let’s say Current My Site Web Application is http://mysite/ and current My Site content database is MySite_Content
- Back up the My Site content database using SQL Server Management Studio.
- Log in the MOSS Server running the Central Administration Site using the Farm Administrator account.
- Start Central Administration Site on the MOSS Server.
- Under Application Management on Applications tab, click Content Databases]
- From the Manage Content Databases page, add 6 content databases for the http://mysite/ web application – WSS_MySites_Content_01, WSS_MySites_Content_02, WSS_MySites_Content_03, WSS_MySites_Content_04, WSS_MySites_Content_05, and WSS_MySites_Content_06. Make sure maximum number of sites that can be created in this database – 1500. Adding content databases through the central administration site will create content databases on the database server.
- After creating the new content databases to host the My Sites, it is important to log in to the SQL Server box and verify there is enough disk space allocated to the new content database.
If you run out of the space during the site collection migration from one content database to another, you will see following error message. Unfortunately STSADM doesn’t record any process in the log files and it’s hard to track down where things went wrong. In a nutshell, it is always good practice to verify the storage allocations for the database and hard disk space available.
- To list the personal sites you want to move into 6 specific content databases, first step will be to download the personal sites URLs to the XML file. Use the STSADM enumsites command to download the personal sites.
Syntax for the command is – stsadm -o enumsites -url > .xml, where url is the address of the Web application that contains the site collection that you want to move, and path\file name is the name of the XML file that you want to create with the site collection data.
For more information about the STSADM enumsites command –
stsadm -o enumsites -url http://mysite > C:\MySiteURLs.xml
- The previous step will create a MySiteURLs.xml file on the C drive that contains all of the site collection URLs for the My Site content database.
- Open the MySiteURLs.xml file and split the XML file into 6 XML files – MySiteURLs1.xml, MySiteURLs2.xml, MySiteURLs3.xml, MySiteURLs4.xml, MySiteURLs5.xml, and MySiteURLs6.xml. Each file will be used to import the site collections into specific content databases. Keep the approx. 1000 unique URLs in each file to move the site collections into new database. The only URLs that should remain in the XML file should be for the site collections that you want to move. Please note that there is no need to change the site count or any of the other site collection information in the file. Only the URLs are relevant to this procedure.
- To move the site collections from one content database to another, use the STSADM mergecontentdbs command. It is important to remember that IIS must be reset before mergecontentdbs change will take effect.Syntax for the command is – stsadm -o mergecontentdbs -url -sourcedatabasename -destinationdatabasename -operation 3 -filename where url is the address of the Web application that contains the site collection that you want; sourcedatabasename is the name of the database that you want to move the site collection from; destinationdatabasename is the name of the database that you want to move the site collection to; operation 3 is the “Read from file” operation; and file name is the name of the file that contains the site collection URLs that you want to move.For more information about the STSADM mergecontentdbs command –
Stsadm -o mergecontentdbs –url http://mysite/ –sourcedatabasename MySite_Content -destinationdatabasename WSS_MySites_Content_01 –operation 3 –filename C:\MySiteURLs1.xml
- After the move is complete, you must run the iisreset /noforce command to restart the IIS on each of the front-end Web servers in your farm.
- Whenever you move the site collections from one content database to another database, it is best practice to sync the content databases with the web applications. Typically you need to run the preparetomove the command prior to detach/attach the content database to the web application. Run the following three commands in order to sync WSS_MySites_Content_01 database with the http://mysite/ URL.//Prepare To Move the WSS_MySites_Content_01
stsadm -o preparetomove -contentDB SQLServer01:WSS_MySites_Content_01//Detach the WSS_MySites_Content_01 from the http://mysite/
stsadm -o deletecontentdb -url http://mysite -databasename WSS_MySites_Content_01 -databaseserver SQLServer01
//Re-Attach the WSS_MySites_Content_01 to the http://mysite/ . It will re-sync the web application to the content databases.
stsadm -o addcontentdb -url http://mysite -databasename WSS_MySites_Content_01 -databaseserver SQLServer01
If you don’t run above commands, you might see lots of Profile Sync Errors – 5554 and 5555 on the application event log on the WFE servers as shown below along with DB errors like – 3355, 5215, 5586, 6398, 6482, 7888, and 27745 stating unable to connect the database.
5554 – Failure during sweep synch. Exception was The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Named Pipes Provider, error: 0 – No process is on the other end of the pipe.)
5555 – Failure trying to synch web application a703c314-9ed1-4ce9-8bc4-309b7efbc30d, ContentDB 531e9ef1-c601-49d4-9283-1a038e7bbc6e Exception message was The client was unable to establish a connection because of an error during connection initialization process before login. Possible causes include the following: the client tried to connect to an unsupported version of SQL Server; the server was too busy to accept new connections; or there was a resource limitation (insufficient memory or maximum allowed connections) on the server. (provider: Named Pipes Provider, error: 0 – No process is on the other end of the pipe.)
- Repeat the last three steps for all other content databases.
- To completely avoid the profile sync issues, it is important to delete “Unsync” entries from the sync database and restart the timer service. Run the following commands from the command prompt to complete the site collection move from one content database to another.//List the “Unsync” databases which are not marked “Unsync”
stsadm -o sync -listolddatabases 0//Mark the “Unsync” databases as “Unsync”
stsadm -0 sync -deleteolddatabases 0
//Stop the Windows SharePoint Timer Service
net stop “Windows SharePoint Services Timer”
// Start the Windows SharePoint Timer Service
net start “Windows SharePoint Services Timer”
- Refresh the Mange Content Databases page on the central administration site and current number of sites count should be refreshed. Verify that total number of site collections for all the new content databases should remain the same as number of site collections in the original content database.
- Verify the personal My Sites hosted in the different content databases are accessible.
- Detach the original content database – MySite_Content from the http://mysite/ using the Manage Content Databases page. It is best practice to detach the original content database and delete the database from the SQL Server Management Studio.