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

As I mentioned in Part 1, I decided to store each site collection in its own database for better managability on both of our SharePoint farms. This post is Part 2 and hopes to answer the question: “What happens when someone deletes a site collection?” In a normal sharepoint environment that only has a few content databases, this is not a big deal. But in our environment it’s a much bigger deal because each site collection has its own database and that database name includes the site collection url. Our web service from Part 1 helps make sure we don’t put a site into a database that is left over from a deleted site collection, but it’s our event receivers that do the real clean up.

We have two event receivers for this. One for SiteDeleting (Before the site collection is deleted) and the other for SiteDeleted (After the site collection is deleted). SiteDeleting will check if the site collection being deleted is in a database which will be empty after the site collection is deleted. If this is the case, then we set the database status in sharepoint to offline (disabled). SiteDeleted will get a list of all content databases that are empty and remove them from SharePoint. It also sets the database to single user and then drops it.

Below is the code or click this link to download:

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

public class DeleteDatabaseOnSiteDeleted : SPWebEventReceiver
{
    public override void SiteDeleting(SPWebEventProperties properties)
    {
        base.SiteDeleting(properties);
        using (SPSite site = properties.Web.Site)
        {
            //Before the site collection is deleted
            //set status to disabled if the currently being deleted site collection
            //is the last one in this database
            if (site.ContentDatabase.CurrentSiteCount == 1)
            {
                site.ContentDatabase.Status = SPObjectStatus.Disabled;
                site.ContentDatabase.Update();
            }
        }
    }

    public override void SiteDeleted(SPWebEventProperties properties)
    {
        base.SiteDeleted(properties);

        Uri url = new Uri(properties.FullUrl);
        string currUrl = properties.FullUrl;
        string basepath = currUrl.Substring(0, currUrl.IndexOf(url.Host) + url.Host.Length);

        SPWebApplication webApp = SPWebApplication.Lookup(new Uri(basepath));
        List<SPContentDatabase> emtpyDbs = new List<SPContentDatabase>();

        //After the site collection is deleted find all databases that 
        //don't have any sites in them
        foreach (SPContentDatabase database in webApp.ContentDatabases)
        {
            if (database.CurrentSiteCount <= 0)
            {
                emtpyDbs.Add(database);
            }
        }

        foreach (SPContentDatabase database in emtpyDbs)
        {
            RemoveAndDeleteDb(database);
        }
    }

    private void RemoveAndDeleteDb(SPContentDatabase db)
    {
        string dbName = db.Name;

        if (db.CurrentSiteCount != 0) return;

        //Remove database from SharePoint
        db.WebApplication.ContentDatabases.Delete(db.Id);

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

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

        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = connectionString;
        string sql;

        //Force database to close other connections
        sql = "ALTER DATABASE [" + dbName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
        SqlCommand command1 = new SqlCommand(sql, connection);
        command1.CommandType = CommandType.Text;

        //Remove database from sql server
        sql = "DROP DATABASE [" + dbName + "]";
        SqlCommand command2 = new SqlCommand(sql, connection);
        command2.CommandType = CommandType.Text;

        using (connection)
        {
            connection.Open();
            command1.ExecuteScalar();
            command2.ExecuteScalar();
        }
    }
}

As with Part 1, the main issue with this implementation is concurrency. You probably shouldn’t be creating and deleting a site collection at the same time. Since the chances in our environment for this are very slim, we’ve never had any issues. Also, since the database is dropped, any changes made since the last backup are lost. It might be a better solution to call a stored procedure during SiteDeleted which backs up the database first, and then sets it to single user and drops it.

Of course for this solution to work, you’ll need to write a feature that adds the eventreceivers to all existing site collections and any new site collections. I haven’t included that code here because it’s pretty straightforward, but if anyone is interested, leave a comment and I’ll see if I can get it posted.

————————————
Update 11/06/2009: I’ve created a Part 3 which includes the feature code.