SharePoint Database Information

Note: This post relates to SharePoint 2007.

I’ve recently worked on a project that required direct access to the SharePoint content and Shared Service databases for reporting purposes.  Although accessing the SharePoint databases directly is generally a no-no and is not supported, the types of reports required were not possible, or would have a serious impact on performance if the object model was used.

Also, as the code reports would be generated with SQL scripts any possible changes to the database structure with future service packs would be easy to resolve and not affect the code controlling the report generation.

The main reason for this post is to show how to retrieve the database details for a web application’s shared service database, as this is not easily accessible with the object model.  But to start with, I’ll describe how to retrieve the database details for the content databases.

As a web application can have multiple content databases, it was important to execute the desired SQL queries against all databases for a web application and merge the results to product data that can be displayed in a scorecard for example. As most people who’ve developed with SharePoint will know, the content databases associated with a web application are provided through the ContentDatabases property for the web application:

using (SPSite site = new SPSite("http://server"))
{
  foreach (SPContentDatabase db in site.WebApplication.ContentDatabases)
  {
    ...
  }
}

Within each SPContentDatabase object are a number of useful properties. Interestingly if SQL is the connection type for the content database, the Password property is stored in clear text.

Regardless of the configured authentication type, you can retrieve the connection string through the DatabaseConnectionString property. If you have a requirement to report on multiple web applications and don’t want to store the connection details in a configuration file, or have to encrypt\decrypt the data, a good solution is to store the URLs of the sites to report on in a configuration file and retrieve the connection details using the object model.

After this it’s simply a matter of creating a connecting to the database and executing the stored procedures or SQL queries. Obviously using SQL queries is the better option here as you don’t want to create stored procedures in each content database, which would be a bigger sin than connecting to the databases in the first place.

Retrieving the content database connection details is the easy part. It’s slightly more tricky to get the shared service database details. This is not the shared service’s content database, but the configuration database. Accessing this was required to be able to generate custom statistical reports.

This solution uses Reflection as the desired properties are internal to the SharePoint object model so are not accessible through normal means.

Firstly we need to get the SPFarm object associated with the site we are reporting on:

SPFarm farm = SPContext.Current.Site.WebApplication.Farm;

Using the farm object, we now need to load the ServerFarm assembly:

Type serverType = Assembly.GetAssembly(ServerContext.GetContext(site).GetType()).GetType("Microsoft.Office.Server.Administration.ServerFarm");
object serverFarm = farm.GetObject(string.Empty, farm.Id, serverType);

Next, we need to get the SharedResourceProviders property from our farm object and use it to retrieve all of the shared resource providers associated with the farm:

PropertyInfo providersProperty = serverFarm.GetType().GetProperty("SharedResourceProviders");
IEnumerable sharedResourceProviders = (IEnumerable) providersProperty.GetValue(serverFarm, null);

Before we iterate through each shared resource provider, we first need to load the SharedResourceProvider assembly and use it to retrieve the web applications enumerator. This will allow us to iterate through the web applications associated for each shared resource provider until we find the web application that matches the root web application for the site to be reported on. Once we have this, we know the shared resource provider that contains the web application and can retrieve the correct database connection details.

Type sharedResourceProviderType = Assembly.GetAssembly(ServerContext.GetContext(site).GetType()).GetType("Microsoft.Office.Server.Administration.SharedResourceProvider");
PropertyInfo webApplicationsProperty = sharedResourceProviderType.GetProperty("WebApplications");

Now we have all the objects required to identify the shared resource provider:

foreach (SPPersistedObject sharedResourceProvider in sharedResourceProviders)
{
  IEnumerable webApplications = (IEnumerable) webApplicationsProperty.GetValue(sharedResourceProvider, null);
  // Iterate through collection of Web Applications and check if any match the Web Application for the site.
  foreach (SPWebApplication webApp in webApplications)
  {
    if (webApp.Id == site.WebApplication.Id)
    {
      ...
    }
  }
}

We’re are now close to the last piece of code, which will allow us to retrieve the connection string for the shared resource provider’s database. All that is now required is to get the ServiceDatabase property from the current shared resource provider object and then we are able to access the DatabaseConnectionString property using Reflection:

object serviceDB = sharedResourceProvider.GetType().GetProperty("ServiceDatabase").GetValue(sharedResourceProvider, null);
string connectionString = serviceDB.GetType().GetProperty("DatabaseConnectionString").GetValue(serviceDB, null).ToString();

Another useful property of the serviceDB object is FullName:

string[] sspDetails = serviceDB.GetType().GetProperty("FullName").GetValue(serviceDB, null).ToString().Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
string serverName = sspDetails[0];
string databaseName = sspDetails[1];

As with the content databases, it is also possible to get the SQL username and clear text password by retrieving the Username and Password properties respectively.

The full code for this post should now look something like the following:

using System;
using System.Collections;
using System.Reflection;
using Microsoft.Office.Server;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
 
namespace SPDatabaseInfo
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SPSite site = new SPSite("http://server"))
            {
                foreach (SPContentDatabase db in site.WebApplication.ContentDatabases)
                {
                    SPFarm farm = site.WebApplication.Farm;
 
                    Type serverType = Assembly.GetAssembly(ServerContext.GetContext(site).GetType()).GetType("Microsoft.Office.Server.Administration.ServerFarm");
                    object serverFarm = farm.GetObject(string.Empty, farm.Id, serverType);
 
                    PropertyInfo providersProperty = serverFarm.GetType().GetProperty("SharedResourceProviders");
                    IEnumerable sharedResourceProviders = (IEnumerable)providersProperty.GetValue(serverFarm, null);
 
                    Type sharedResourceProviderType = Assembly.GetAssembly(ServerContext.GetContext(site).GetType()).GetType("Microsoft.Office.Server.Administration.SharedResourceProvider");
                    PropertyInfo webApplicationsProperty = sharedResourceProviderType.GetProperty("WebApplications");
 
                    foreach (SPPersistedObject sharedResourceProvider in sharedResourceProviders)
                    {
                        IEnumerable webApplications = (IEnumerable)webApplicationsProperty.GetValue(sharedResourceProvider, null);
                        // Iterate through collection of Web Applications and check if any match the Web Application for the site.
                        foreach (SPWebApplication webApp in webApplications)
                        {
                            if (webApp.Id == site.WebApplication.Id)
                            {
                                object serviceDB = sharedResourceProvider.GetType().GetProperty("ServiceDatabase").GetValue(sharedResourceProvider, null);
                                string connectionString = serviceDB.GetType().GetProperty("DatabaseConnectionString").GetValue(serviceDB, null).ToString();
 
                                //
                                // Retrieve values from connectionString variable
                                // OR
 
                                string[] sspDetails = serviceDB.GetType().GetProperty("FullName").GetValue(serviceDB, null).ToString().Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
                                string serverName = sspDetails[0];
                                string databaseName = sspDetails[1];
 
                                // Retrieve values from sspDetails such as serverName and databaseName
                            }
                        }
                    }
                }
            }
        }
    }
}

Note: This post relates to SharePoint 2007.

2 comments

  1. Doreen says:

    This page is buggy and I’m having difficulty copying the code to give it a try. Do you have the code in a console application perhaps so the information can be easily viewed?

Leave a Reply

Your email address will not be published. Required fields are marked *

Solve the maths problem shown below before posting: *

Follow

Get every new post delivered to your Inbox

Join other followers: