donderdag 6 januari 2011

SharePoint 2010 add Site Collection to specific content database

This blog is for those who like to manage the content databases of the SharePoint 2010 environment en make sure that Site Collections are added to specific content databases.

This is not complex to do, I just blog about these kind of things because it could be possible it can help someone.

All actions are done in my SharePoint BI demo environment that I blogged about earlier. Actions are executed using the SharePoint farm administrator account corp\shpFarmAdmin

First we need to manage our content databases. Go to Central Administration > Application Management > Manage Content Databases.

The first thing we see is that I current have one content database in my farm. This content database hosts one site collection “Business Intelligence Portal”.

image

This would not be an issue when I create a new site collection, it will end up in this content database. What I like to do is determine in which content database my site collection is created. To that we first create a second content database.

Click Add a content database

image

I will add the content database Farm1_Content_Financials and leave the rest as default. Click OK to create the database.

image

I now have 2 content databases.

The next to do is change the settings of the content databases to make sure that the next Site Collection I will create ends up in the desired content database. In my case I want to create a site collection that ends up in the Financials content database. If we don’t change anything SharePoint will use a round-robin algorithm to choose the content database where the site collection will be created.

First click the Farm1_Content_BIPortal content database in the Manage Content Databases window. Change the value of the Database Capacity Settings as follow:

image

This makes sure that no site collection are created in this content database click OK. The warning number should always be less then the maximum number of sites that can be created. For the Farm1_Content_BIPortal content database I set the maximum to 1, equal to the current number of site collections and the warning threshold to 0.

This would be enough to make sure that the next site collection I create will end up in the Farm1_Content_Financials database. But what if there was a third or even multiple other content databases? To make it more clear I will add an third content database Farm1_Content_HRM.

image

When I no would create my site collection it still can end up in the financials or hrm content database. I want it to end up in the Farm1_Content_Financials database so need to change some more settings first.

First I change the settings on the Farm1_Content_HRM content database. Click the content database and change the Database Capacity Settings as follow:

image

When you now click OK you will get a warning:

image

It says that the warning threshold should be less than the maximum number and that the maximum number should be greater or equal to the current number of site collections in the content database and you are not returned to the Manage Content Database Settings page. However when you return there your self you will see that the settings are granted.

image

This is enough to make sure that the next site collection I create will end up in the Farm1_Content_Financials content database. To make sure that when other site collections are created by other administrators or myself do not end up in the Financials content database I will also change those default settings. Click the Farm1_Content_Financials content database and change the Database Capacity Settings as follow and click OK:

image

This makes sure that only 1 site collection can be created in the Financials content database. You need to be sure that while doing this none of the other administrators is creating a site collection :-). image

Now I should be able to create a site collection that I ‘m certain of to end up in the Farm1_Content_Financials content database. Go to Central Administration > Application Management > Create Site Collections

image

I create a Audit site collection with the Business Intelligence Center template and click OK. This could be a site collection for the financial audit department.

image

The site collection is created. Return to the Manage Content Database Settings page again to make sure the site collection is created where I want it it be :-).

image

It did, lucky me :-).

I always want to be able to determine in which content database the next site collection will end up so I leave the settings for now. In this case this means that when an administrator wants to add a new site collection he first needs to change the content database settings to determine in which one the site collection will be created.

To complete this story lets add an other site collection Budget that I want to end up in the Financials content database, without changing settings for the content databases. Go to Central Administration > Application Management > Create Site Collections

image

When I now click OK I would expect an error, there is no room left in my content database settings on any of the databases to except a new site collection.

image

This is correct and the hoped for error message! Even the not allowed setting of 0 – 0  on the HRM database is really effective. To be able to add the site collection I will change the settings of the Farm1_Content_Financials database to:
image

And try to add the Budget site collection again.

image

This time it is successfully created and did end up in the Farm1_Content_Financials database.

image image

NO IT DID NOT!!! Why not? when changing the settings of the Financials Content database it changed the not really allowed 0 – 0 settings of the HRM content database back to the defaults. First delete the Budget site collection and set the HRM content database settings to 0 – 0  again.

image

Again I will create the Budget site collection.

This time it is successfully created and did end up in the Farm1_Content_Financials database.

image

So one last tip, when adding a new content database direct add a site collection to it when possible, avoiding to need to set the 0 – 0 threshold settings on the content database. Because when adding a new content database and setting the thresholds all content databases with the 0 – 0  threshold will be set back to the default 9000 – 15000 thresholds again.

I hope you enjoyed this simple blog about a simple need to now SharePoint 2010 management setting. Please let me know when you have any comment, other experience or just enjoyed this blog.

3 opmerkingen:

Vikram zei

Why are taking so much pain to acieve this?.
Just set all other content database status to offline and keep required content database status as ready.
Now create the site collection, it will go to required content database.
Now made other content database status as ready.

Or Just create the site collection, and move it to the deired content database using powershell cmdlet.

Move-SPSite "http://ServerName/Sites/SiteName" -DestinationDatabase "DestinationContentDb"

Arjan Fraaij zei

Hi Vikram,

Thanks for the reply, alto it's not always possible to take content databases offline....

Also moving the site collection might be an issue when your current content database is that full or even that busy you realy like to add the new collection direct on the correct database.

The pain to achieve this is to show ppl what is possible even when it's harder to do...

So thanks for the add-on and other possible solutions.

Darren zei

Are you serious? Both of you are making life way more difficult than it needs to be.

New-SPSite cmdlet does exactly this: http://technet.microsoft.com/en-us/library/ff607937.aspx

Simply stipulate the -ContentDatabase parameter and job done!!!