Finding The Active Sharepoint Databases

This was a task from last week. There was a server where we wanted to make some adjustments to security including changing the database owner to SA. To do that we needed to make sure that we included all the Sharepoint databases. If you’ve ever looked at a SQL instance supporting Sharepoint it’s not one database – for the server I was working on there were more than 60! Some that were clearly Sharepont, some that you might guess, maybe some you wouldn’t guess.

We tried looking in the Sharepoint Config database, but we weren’t sure we were in the right place, so we did some searching and found a blog post that offered two methods, one using Powershell and one using the Sharepoint admin tools. We asked someone from the server team to try both, both worked, and both returned a list of 21 databases – not 60 (actually 63). Making the assumption that 21 was correct, we could go back to the Objects table and write this query (against a Sharepoint 2010 install):

SELECT

[Name], [Properties], convert(xml, properties).value(‘(//object/@type)[1]’, ‘varchar(255)’)

FROM [SharePoint_Config].[dbo].[Objects]

where

properties like ‘%database,%’ and convert(xml, properties).value(‘(//object/@type)[1]’, ‘varchar(255)’) like ‘%database%’

That returned the same list of 21 databases. We think the others are from old/trial/abandoned installs, so it goes on the list to take the others offline and see what breaks. All of them have the ‘ugly’ guid’s in the name – knowing where the db names are stored just makes me itch to try cleaning them up, but it’s not a big win and even suggesting that we touch Sharepoint sets off a ‘oh, that’s a bad idea’ vibe with everyone.

Perhaps a pause here is in order to contemplate an application that needs 21 databases. I’m guessing this is to support sharing the load across multiple servers if needed, but it also feels like it was partitioned by feature (and maybe that’s ok). Still, 21 databases?

It also turns out that Sharepoint doesn’t require the ugly db names, it just tilts that way if you do a default install. Here is post that shows how to set cleaner names. Fight the good fight when it’s time to install Sharepoint!

One thought on “Finding The Active Sharepoint Databases

  1. Having to support a Sharepoint SQL back-end with 56 DBs all used by Sharepoint in some way, I can understand why MS went with as many DBs as they did. If each DB is attached to a different “site collection” in SP, and some user manages to break their site, you don’t need to either jump through hundreds of hoops, tell said user “have fun re-building your site,” or telling the other users “sorry, everything you did from XX:XX to now is about to be wiped out because userA made a mistake.”
    Our SP admins have named the DBs rationally, and split the various depts up, each to their own site collection DB. This was a godsend recently when one sites’ admin managed to wipe out thier access settings, which required restoring a copy of thier DB from 2hrs before.

    Like

Comments are closed.