Sunday, August 23, 2009

SharePoint: Move Site Collection / Database Resizing

In SharePoint world, content databases can grow to huge numbers, which in-turn result in multiple (performance, archival, consolidation of Sites, etc...) issues. Solution is either to bring automation in place which is smart enough to allocate content db's for Sites, based on the mapped logical architecture or another way would be to re-size your content databases as per your needs on regular bases. I will not be talking on methods to bring automation in place, I'll leave that for some othe day. Today I will talk on how to resize your content db's using a simple stsadm approach fused with some XSL transformations.
To explaining in detail, let me layout a scenario I executed on my local vm's recently. Scenario executed was more of a POC for our production enviornment. I have a solution which contained nearly 200 site collections where few content databases were munning over 100GB's in size which is not recommended by Microsoft(http://technet.microsoft.com/en-us/library/cc298801.aspx). Also, there were other resons like, specific site collections which needed to be archived and backed up regularly where as some site collections needed to be backed up once in six months or so. All the site collections we categorised by defining multiple manage path(http://sharepoint.microsoft.com/blogs/zach/Lists/Posts/Post.aspx?ID=30). Lets name the managed paths:
1) Root :- /

2) Backup (Managed Path) :- /backup
3) Collaboration (Managed Path):- /collaboration
Let's name the databases as well:
1) WSS_Content_MyDB_1
2) WSS_Content_MyDB_2
3) WSS_Content_MyDB_3
4) WSS_Content_MyDB_4
Site collections were speard across and mixed up. Root site collection was mapped correct with WSS_Content_MyDB_1, but site connections under managed paths spread across all databases. Which made impossible to segregate content and define backup schedules based on logical categorization.
I used STSADM.EXE to help resolve the situation. Following are the steps:
1) Use following command to generate details of all sites in XML format:
==================================================
stsadm -o enumsites -url http://webapp_host_header > c:\sites.xml
==================================================
this command would generate xml as shown below:
[code]
<?xml version="1.0" encoding="UTF-8"?>
<Sites Count="7">

<Site Url="http://webapp_host_header" Owner="SANDEEP\srohilla" SecondaryOwner="SANDEEP\fadmin" ContentDatabase="WSS_Content_MyDB_1" StorageUsedMB="118.6" StorageWarningMB="0" StorageMaxMB="0" />

<Site Url="http://webapp_host_header/Collaboration/site1" Owner="SANDEEP\srohilla" SecondaryOwner="SANDEEP\fadmin" ContentDatabase="WSS_Content_MyDB_2" StorageUsedMB="0.5" StorageWarningMB="0" StorageMaxMB="0" />

<Site Url="http://webapp_host_header/Backup/site2" Owner="SANDEEP\srohilla" SecondaryOwner="SANDEEP\fadmin" ContentDatabase="WSS_Content_MyDB_3" StorageUsedMB="0.5" StorageWarningMB="0" StorageMaxMB="0" />

<Site Url="http://webapp_host_header/Collaboration/site3" Owner="SANDEEP\srohilla" SecondaryOwner="SANDEEP\fadmin" ContentDatabase="WSS_Content_MyDB_4" StorageUsedMB="0.5" StorageWarningMB="0" StorageMaxMB="0" />

<Site Url="http://webapp_host_header/Backup/site4" Owner="SANDEEP\srohilla" SecondaryOwner="SANDEEP\fadmin" ContentDatabase="WSS_Content_MyDB_2" StorageUsedMB="0.5" StorageWarningMB="0" StorageMaxMB="0" />

<Site Url="http://webapp_host_header/Collaboration/site5" Owner="SANDEEP\srohilla" SecondaryOwner="SANDEEP\fadmin" ContentDatabase="WSS_Content_MyDB_3" StorageUsedMB="0.5" StorageWarningMB="0" StorageMaxMB="0" />

<Site Url="http://webapp_host_header/Backup/site6" Owner="SANDEEP\srohilla" SecondaryOwner="SANDEEP\fadmin" ContentDatabase="WSS_Content_MyDB_4" StorageUsedMB="0.5" StorageWarningMB="0" StorageMaxMB="0" />

</Sites>
[/code]
With the help of following command we are going to move sitecollections to different database:

====================================
"stsadm -o mergecontentdbs -url http://webapp_host_header -sourcedatabasename WSS_Content_MyDB_3 -destinationdatabasename WSS_Content_MyDB_2 -operation 3 -filename c:\output.xml"
================================

There are couple things to note here:
1) Multiple Sites(I mean site collections) can be moved from one content database to another content database. Cannot moves sites from multiple DB's to one DB in one go. It hase to be 1 DB at a time.
2) c:\output.xml is generated from c:\sites.xml (created as explained above) by using XSLT. Take a look at the xslt code below:

[code]
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:strip-space elements="*"/>

<xsl:template match="Sites">
<xsl:copy><xsl:copy-of select="@*"/>
<xsl:apply-templates/>
</xsl:copy>
</xsl:template>

<xsl:template match="Site[@ContentDatabase='WSS_Content_MyDB_3']">
<xsl:if test="contains(@Url,'Collaboration')">
<xsl:copy>
<xsl:copy-of select="@*"/>
<xsl:apply-templates/>
</xsl:copy>
</xsl:if>
</xsl:template>
</xsl:stylesheet>
[/code]