SharePoint and SQL Databases

November 15, 2009

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.

Advertisements

Fancy having fully-automated site Disaster Recovery?

November 4, 2009

A critical application…
…nearly as much $$$ spent on your DR site as your Production site…
…hours of time spent on defining & refining your disaster recovery procedure…
…never mind the training & updates from testing…
…varying bandwidth between sites, sometimes not enough…
…want to be able to recovery your SQL server back to 10:22:16…
…and that was 2 days ago…
…need that data back within minutes…

Well you can…

EMC RecoverPoint, EMC’s CDP (Continous Data Protection) product provides near instantanous roll-back capability.

 The majority of Operating Systems and applications are supported.  Data reduction* and WAN bandwidth compression – native.  Non EMC storage arrays – supported.  Long distances – how about 2000km? – supported.

Combine RecoverPoint and EMC Cluster Enabler together (called RP/CE for short) and the solution gives you just this.

RP/CE is what is called a geographically dispersed clustering solution, but alot more…
…RP/CE allows one or many clustered applications or Hyper-V VMs to be failed over in a minimal amount of time to a disaster recovery cluster node(s)   – in my Proven Solution – try 3 production / 3 DR nodes. 

I could even run some VMs on the DR cluster nodes (if your network supports this of course) and some on Production.  RecoverPoint/CE doesn’t mind, it supports bi-directional replication. 
EMC understands nowadays DR sites are too costly to just leave idle. 
RecoverPoint natively supports bi-directional replication in the same RecoverPoint installation.

The slick part of RecoverPoint/CE is that once operational, all the user needs to know is how to use normal Microsoft clustering and cluster administrator console.  RP/CE adheres to all Microsoft failover clustering requirements.  It is installed as a clustered resource and is added to each cluster group which needs RP/CE’s protection.

Say, you have a 2-node (1 active / 1 passive) Hyper-V server running your virtualized SharePoint farm.  Down goes your production site.  Within a few minutes, all your SharePoint virtual machines are up and running on the DR side again, with the latest image of your data.  It’s that simple.

I was at the EMC booth as SharePoint Conference 2009 ( SPC09 ) in Las Vegas in October showcasing this and I must say…visitors were very impressed…most especially the folk who have been through the pain of recent DR planning..

* as a classic example, in something like an OLTP environment, if an 8k SQL data page is changed by say 200 bytes, the entire 64k block is written back down to filesystem.  Without RecoverPoint’s data reduction, all 64k of data is shipped across the wire to the DR site.  With data reduction, only the 200 bytes plus some checksum data is sent across the wire – AND compressed! clever.

For a recorded demonstration of the Proven Solution I am working on please see below;

In this demo, I would like to show you the power of EMC RecoverPoint and Cluster Enabler (RP/CE) in providing fully automated Disaster Recovery in your environment. In this use case, a busy enterprise SharePoint farm hosting 240,000+ busy users will expect a full site disaster and RP/CE automates disaster recovery of the farm in minutes.

The environment consists of a 6-node Hyper-V cluster, (3 Active/3 Passive) using iSCSI connectivity to an EMC CLARiiON CX4-240 storage array

I will share more information on this solution as it evolves.

Some more doc resources 

EMC RecoverPoint/Cluster Enabler – a detailed review
Disaster Recovery for Windows using EMC RecoverPoint/CE
EMC RecoverPoint/SE for CLARiiON Cx4
DR in a geographically dispersed cross-site virtual environment

I would love to get some feedback on what people think of something like this, not necessarily this EMC solution, but geo-clustering in general…
If anyone would like some serious detail into how RecoverPoint/CE works, I can gladly provide same as a blog post.

Thanks, James.