How to store each SharePoint Site Collection in its own Database – Part 1

1. Create a database name which is valid and has the url of the site collection it contains as part of the name
2. Call a stored procedure in the master db on the sql server which does the following
   • Creates a database with data and log files in the correct locations
   • Sets appropriate file growth and other parameters
   • Performs a backup so that the log backup’s won’t fail at night
3. Set all databases in SharePoint that have available space in them to offline (disabled). A database which is offline (disabled) means that it won’t accept any new site collections in it but SharePoint will still serve any content that is in that database.
4. Add the newly created database to the web application in SharePoint
   • We set the warning level to 0 and maximum to 1
5. Create a new site collection. Because our newly created db is the only db that is online and has space in it (1 space) then our newly created site collection will be put in our newly created db.
6. Set all databases in SharePoint that have available space in them to online

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_create_sharepoint_database2] (
@databaseName varchar(1000)
)
AS

declare @dataPath varchar(1000)
declare @logPath varchar(1000)
declare @name1 varchar(1000)
declare @name2 varchar(1000)
declare @sql varchar(8000)

set @dataPath = 'd:mssql90data'
set @logPath = 'f:mssql90log'

set @sql = 'create DATABASE ' + @databaseName + '
ON ( NAME = ' + @databaseName + ',
	FILENAME = ''' + @dataPath + @databaseName + '.mdf'',
	SIZE = 3MB, MAXSIZE = UNLIMITED, FILEGROWTH = 250MB )
LOG ON
( NAME = ' + @databaseName + '_log,
   FILENAME = ''' + @logPath + @databaseName + '.ldf'',
   SIZE = 1MB,
   MAXSIZE = UNLIMITED,
   FILEGROWTH = 100MB )
COLLATE Latin1_General_CI_AS_KS_WS'
EXEC (@sql)

select @name1='e:mssql90backup' + @databaseName +  '.BAK', @name2 = @databaseName + '_C'
    BACKUP DATABASE @databaseName TO DISK = @name1 WITH INIT, NOUNLOAD, NAME = @name2, NOSKIP, STATS = 10, NOFORMAT
    select @name1='E:mssql90backup' + @databaseName + '_' + 'LOG' + '.BAK', @name2 = @databaseName + '_L'
    BACKUP LOG @databaseName TO DISK = @name1 WITH INIT, NOUNLOAD, NAME = @name2, NOSKIP, STATS = 10, NOFORMAT

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;
using System.Text.RegularExpressions;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Utilities;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Web.Services;

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
// [System.Web.Script.Services.ScriptService]
public class SiteCreationWebService : WebService
{
    [WebMethod]
    public void CreatePortalSiteCollectionAndDatabase(string basepath, string serverRelativeUrl, string userName, string displayName, string emailAddress, string template, string quota, string title, string description, string portalUrl, string portalName)
    {
        //Make sure web doesn't already exist
        bool webExists = false;
        try
        {
            using (SPSite site = new SPSite(basepath + serverRelativeUrl))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    webExists = web.Exists;
                }
            }
        }
        catch { }
        if (webExists)
        {
            throw new ApplicationException("This Site already Exists");
        }

        //Construct database name to include the url and only use allowed characters
        //Also append some random numbers at the end to pretty much guarantee a unique name
        string WebURL = serverRelativeUrl.Replace("/", "_");
        string databaseName = "wss__" + WebURL + "__" + System.Guid.NewGuid().ToString().Substring(0, 8);
        databaseName = Regex.Replace(databaseName, "[^A-Za-z0-9_]", string.Empty);

        //Make sure the site creation doesn't fail if the template is not specified
        if (string.IsNullOrEmpty(template))
        {
            template = null;
        }

        SPWebApplication webApp = SPWebApplication.Lookup(new Uri(basepath));

        //Take all databases offline if they have space to ensure the created site is put in
        //the database we create
        foreach (SPContentDatabase database in webApp.ContentDatabases)
        {
            if (database.CurrentSiteCount < database.MaximumSiteCount)
            {
                database.Status = SPObjectStatus.Disabled;
                database.Update();
            }
        }

        //Get the default database server's name
        SPWebService service = SPFarm.Local.Services.GetValue();
        SPDatabaseServiceInstance defaultDatabaseInstance = service.DefaultDatabaseInstance;
        string databaseServer = SPHttpUtility.NoEncode(defaultDatabaseInstance.NormalizedDataSource);

        //Build the connection string
        string connectionString = "Data Source=" + databaseServer + ";Initial Catalog=Master;User ID=uid;Password=pwd";

        //Build the sql statement
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = connectionString;

        SqlCommand command = new SqlCommand("usp_create_sharepoint_database", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@databaseName", databaseName);

        //Create the database
        using (connection)
        {
            connection.Open();
            command.ExecuteScalar();
        }

        //Add our newly created database to SharePoint
        webApp.ContentDatabases.Add(databaseServer,
                                    databaseName,
                                    webApp.WebService.DefaultDatabaseUsername,
                                    webApp.WebService.DefaultDatabasePassword,
                                    0,
                                    1,
                                    0);

        //HACK:  Needed when creating a site collection from a web service
        HttpContext.Current.Items["FormDigestValidated"] = true;

        //Create site collection
        using (SPSite site = webApp.Sites.Add(basepath + serverRelativeUrl,
                                              title,
                                              description,
                                              1033,
                                              template,
                                              userName,
                                              displayName,
                                              emailAddress))
        {
            //Add portal connection if available
            if (false == string.IsNullOrEmpty(portalName) &&
                false == string.IsNullOrEmpty(portalUrl))
            {
                site.PortalUrl = portalUrl;
                site.PortalName = portalName;
            }

            //Set quota if available
            if (false == string.IsNullOrEmpty(quota))
            {
                SPQuotaTemplateCollection quotas = webApp.WebService.QuotaTemplates;
                site.Quota = quotas[quota];
            }

            //Take databases back online
            foreach (SPContentDatabase database in webApp.ContentDatabases)
            {
                if (database.Status != SPObjectStatus.Online &&
                    database.CurrentSiteCount != 0)
                {
                    database.Status = SPObjectStatus.Online;
                    database.Update();
                }
            }
        }
    }
}

There are some issues with this implementation. The first one is concurrency. You can’t really create two site collections at the exact same time with this method. If you do, then you might have issues where you get a site collection in a wrong database or one site collection creation fails, etc. The other issue is what happens when someone deletes their site collection? That means there will be a database with 1 space available for a site collection but can’t really be used because it’s name is for the deleted site collection. Step #3 and #6 of the web service partly takes care of this. The full solution is in Part 2.