SharePoint 2007. One Content Database per Site Collection

Tuesday, 16 September 2008 20:17 by RanjanBanerji

In SharePoint 2007 when you create an application you specify the name the database to use.  Most people, therefore, conclude that an application will use one database.  This is not necessarily a very scalable idea.  But then you will often hear that an application can have more than one database.  The more commonly heard statement is that a Site Collection can have its own database.

True, very true.  Each site collection under an application can be made to have its own database.  But how?  When you create a site collection you are never asked which database to use or if a new database needs to be created.

Well our friends at Microsoft have made this in a very confusing process.  But here goes:

  • Let's start with creating a new application.  When we create the application we tell it to use the following database: WSS_CONTENT_1
  • Now create a site collection (SC1) under this application and a few sites under the site collection.  All the sites will use the WSS_CONTENT_1 database.
  • Now go to Central Administration, Applications, Content Databases.  Select WSS_CONTENT_1 and change its status to "Offline".
  • Now under Central Administration, Applications, Content Databases create a new content database WSS_CONTENT_2.  Make sure its status is "Ready".
  • Now create a new site collection SC2 and create a few sites for SC2.  They will use WSS_CONTENT_2.
  • Now create yet another site under SC1.  It will use WSS_CONTENT_1.  This is despite the fact that WSS_CONTENT_1 is "Offline."

So what we have achieved through this roundabout way is a content database for each of our site collections SC1 and SC2.  It would have been a lot better if when creating a site collection in Central Admin one was asked if a new database is to be created or if one wants to use an existing database.

In the Central Administration, Application Management, Content Database module setting a database offline means that from this point on no new site collection will be created in this database.  However this database is still usable and new sub-sites under an existing site collection can be created.  One has to be very careful to take each content database offline once a site collection has been created to prevent any other site collection from being created to use it.  This way you can make sure that each site collection has its own database. 

BTW, the same can be achieved via an stsadm command:

stsadm -o createsiteinnewdb -url -owneremail -ownerlogin Application\Owner -sitetemplate sts -title “Title” -databaseserver servername -databasename WSS_Content_Somename

Why would you want a site collection to have its own database?  Well that depends on what you are building and is a subject that is best handled in another post.

Categories:   SharePoint
Actions:   E-mail | Permalink | Comments (5) | Comment RSSRSS comment feed

SQL Server 2005 Backup a Remote Database

Monday, 15 September 2008 23:17 by RanjanBanerji

So you have a remote SQL Server 2005 database running on a different network than your client machine and you want a backup copy of that database.  Most common scenario, the database is on your web hosting server.  Somehow I never thought this would be a big issue.  I am so used to just using the SQL Management Studio, right clicking on a database, selecting Tasks, and then backup.

But when you backup SQL Server, it is to a local drive or a mapped network drive that the server itself has access to.  Implying that if you are accessing SQL Server on a server on another network on a machine that you do not have access to, you cannot backup SQL Server to your drive.  So how do you get a backup copy?

I found it quite frustrating that I can connect to the database using SQL Management Studio, run queries, make changes etc, but I cannot get a copy of the database to my machine.  Under Tasks there is an option to Copy Database but I just could not get that to work.  I kept getting a security Exception.  One day I will have to look into why.  Until then lets proceed to the so called right way to do it.

Remember DTS from all prior versions of SQL Server?  We it kind of vanished with SQL Server 2005.  Not really, it has a new name, SSIS and is installed when you install Integration Services.  Once installed you will see "SQL Server Business Intelligence Development Studio" on your Start Menu under SQL Server 2005.  Click on it and Visual Studio will launch.  At first I thought this has got to be a mistake.  But no, I was at the right spot.  I created a new Integrated Services Project and under that project by right clicking on solution explorer I selected the import export wizard.

Now the familiar DTS Wizard pops up and you can do what you were used to doing in all prior versions of SQL Server 2005.  Connect to the remote database and get all your data to a local database.  Why couldn't this be a single link on Management Studio as a simple Copy Database?  Because that would make it logical and simple.

By the way, my data transfer failed several times before succeeding, so be patient. :-)



Categories:   SQL Server
Actions:   E-mail | Permalink | Comments (11) | Comment RSSRSS comment feed