Jun 25, 2009

SharePoint databases part 1

It is long time since I started to wonder what databases SharePoint is using, and today I finally spend some time on this and get some clarifications:

1) SSP database and SSP search database. These 2 databases are tied with your individual SSP, and they are backup and restored with SSP. For backup and restore SSP, see This KB;

2)Content Database for SSP site and/or MySite: When you create your SSP,if you setup individual web applications for your SSP site and/or MySite , you will have 2 more extra content databases. But they need to be backuped and restored individually. They are content databases.

3) Config database: this is most importanat database, and if moved to a different SQL server, the target sql server must have exact the same version number (SPs and KBs) applied to backup/restore operation, seems Log Shipping can overcome this restriction.

4)Content Database for the Center Admin Site: This database name can't be modified and must be backup and restored with Config database. (Config DB name can be changed in registry: My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Secure\ConfigDB)

5)WSS search database. I am not sure what this datbase is used in MOSS since there is SSP Search database, is this for WSS Search as a legacy from WSS 2.0?
but anyway, this database need to be backuped and restored individually. Actually every time when you restart Search Help in the central Admin, you need to recreate this database.

6)Content database for portal sites. This is the database normally referred as "content databse".

You can pretty much manually move (via SQL backup and restore) all databases but SSP related dbs, which will end with SSP related dbs stay in the old SQL server, and all others live in a new SQL Server. To move all sharepoint database? see this KB http://technet.microsoft.com/en-us/library/cc512725.aspx for SSP backup/restore and SQL Server Connection Alias. Be cautious, SQL Connection Alias can cause a lot confusions in the future.

update: there is another db which draws more and more attentions as performance killer: TempDB. This db is particularly related with sharepoint indexing/crawling. For db related performance recommendation, see this post.

Part 2 will focuse on sharepoint database permission.