SharePoint and SQL Databases

Hey folks,

This week I am at presenting SharePoint and Hyper-V information to both EMC and Microsoft personnel @ the Microsoft campus, Building 33 in Redmond, WA.  Strangely, its not raining! 
I will be covering topics, such as best practices, Hyper-V virtualization, backup and recovery and DR.  I hope to share these presentations with you once the conference is over, so stay tuned.

On to the real topic…SharePoint & the proliferation of SQL databases.

SharePoint’s main stay of information is in the form of SQL databases. 

In a typical SharePoint SQL Server I would categorize these databases in the following four layers;

===SQL System Databases   (created when SQL is installed)
           -Master, Model, MSDB, TempDB

===SharePoint configuration Databases   (created when SharePoint is installed)
           -SP_Config, etc

===SharePoint content databases   (created at the end of SP install, portal & content creation)
           -WSS_Content_* (Portal), 
            SharePoint_AdminContent_* (Central Admin)
           User-defined content databases (eg ContentDB01, 02, 03)

===SharePoint Shared Services Provider databases   (created with SSP & application configuration)
           -SharedServices_DB  (SSP Configuration database)
           Shared Services applications 
                 -Search
                 -SharedServices_Search_DB   (actual Office Search “Osearch” database)
                 -WSS_Search_{hostname}       (WSS SPsearch DB – per host)

You need to follow standard SQL best practices, including storage BPs to ensure good SharePoint performance, granular backup and recovery and efficient disaster recovery.  Agreed.
(I will go into more best practices for SharePoint SQL storage in a seperate post, let’s stay with this for now)

 

SharePoint does not allow the user to specify where the SQL database data and logs files should reside, and so the default database locations will be used.  The default databases data and log files locations are a part of the SQL instance configuration.

These are recorded in the registry, specific to the SQL instant, e.g.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.$$INSTANCE$$\MSSQLServer\
             \DefaultData               (Default database data file location)
             \DefaultLog                (Default database log file location)

Easier is to use SQL Management Studio
   –  Right click the SQL instance, Properties, Database Settings -> Database default Locations.

Today, we have two choices in ensuring that SharePoint SQL databases are in the right locations…

1) Change the default SQL data file location prior to the SharePoint configuration task (e.g. create a SSP)
      or
2) After the SharePoint configuration task, bring down SharePoint hosts, detach, relocate and re-attach the SQL databases.

I perfer #1 myself 🙂

So, here are the recommended sequence of steps to take

Recommended sequence

1) Install SQL with advanced options
  -ensure that master, model, msdb locations are correctly set.  
  -ensure that tempdb is on different LUNs, ideally filegroup the datafiles

2) Change the default database file locations to your SP Configurations volume
          -then install SharePoint.

3) Change the default database file locations to your “basic content” (or SP Configurations) volume
         -then create your SharePoint portal(s).

4) Change the default database file locations to your SSP & Search Configurations volume
         -then create your SharePoint SSP and add SPsearch roles to hosts

5) Change the default database file locations to your SSP Search Database volumes
         -then create your SharePoint SSP Search application and associate a content source

 

Now, user-level content databases is trickier…
You dont want to have to follow this procedure every time as many content databases will be created in time. 

My recommendation would be;

1) logged in as the SharePoint system account in SQL, manually create content databases in the right storage locations
2) then use either Central Admin or STSADM to attach an existing SQL Content Database

  a) Central Admin way 
    – Central Administration > Application Management > Content Databases
       – specify the name of the existing SQL content database

     or

     b) Stsadm way – stsadm -o addcontentdb -url (URL) -databasename (ContentDB name) -databaseserver (SQL name)

     Example
  stsadm -o addcontentdb -url http://portal.sps.com/site01 -databasename ContentDB01 -databaseserver SQL1

You should not need to specify the username/password as you will use a trusted connection within your domain.

I am putting in an enhancement request to Microsoft to allow SharePoint admins to specific the directory locations from Central Admin/STSADM/Powershell in future.

Every so often, especially with dispersed power users (capable of creating content databases), full audits of SharePoint database files should be carried out.  It is vital to ensure that all databases are protected.

Hope this helps people
James.