Chandima.Net Blog for SharePoint solutions

Some eons ago.. I wrote a blog post about the same topic on SharePoint 2007 which still gets a fair few hits and one of the comments on that post recently was asking about SharePoint 2010 databases and why there was so many of them. And incidentally  I was preparing for a talk  on the same topic at the Wellington SQL Server user group so that comment couldn’t have come at a better time.

If you are just starting out with learning SharePoint the chances are you will not need to know about SharePoint 2007. So I am going to *try* and not mention anything about 2007 but keep this post only on the topic of SharePoint 2010 and why SP2010 has so many databases.

The new and improved architecture of SharePoint 2010 uses a concept called Service Applications. If you are struggling to understand this fundamental concept in SharePoint I suggest you read this article from Spencer Harbar on this topic > In a nutshell : SharePoint 2010 Service Applications. TechNet also has guidance on planning Service applications.

Well how does this affect SQL? It affects SQL in a big way since most of the Service applications require 1 or sometimes more databases for their functional aspects. But before I go into more detail I want to highlight something that needs to be addressed right at your planning stage around this. When I say this I am referring to Production SharePoint 2010 deployments.

Wizardry and Trickery

Basically if you’ve come from SP2007 background (assuming you’ve actually installed the product end to end) you’d know that you used PSCONFIG and STSADM and or PowerShell to do some of the installation and configuration. Which was fundamental to making sure that you can plan and create your SQL databases you required for SharePoint prior to installing and running the configuration scripts. And you definitely did NOT want to use the “Configuration Wizard” option even then for a production configuration.

In SP2010 you must do the same and it’s far more important that you follow the rule of ignoring anything that says “Wizard”. The first “Wizard” you encounter is just after you install the SharePoint binaries.

See the little checkbox that isn’t checked? Yup that’s how it should stay and you close this window at this stage and turn to PowerShell for the rest of the configuration.

Now even if you did miss this the second one is an absolute NO NO in my books for a production farm configuration. Well the first one (above) is too. But let me show you why the below screen or the “Farm Configuration Wizard” is bad for a production deployment.

Now if you are actually going to click on that “Start the Wizard” in a Production server.. well seriously you should not because this is the net effect it will have on SQL.

See what I mean? No naming conventions whatsoever and this all becomes a mess in a Wizard kind of way. So what’s the right way to do this?

You’ve got a few options and they all use PowerShell. If you are going to be looking after and maintaining a SharePoint 2010 deployment then PowerShell is your ally, friend, swiss army knife or whatever else you want it to be. So the basic steps for a core (base) configuration of installing SharePoint in a production capacity are:

Correct user accounts (You basically need 2 to start with just for the core install). Note when I say core I mean just getting your CA web site with NO Service Applications configured. Each Service Application needs to be carefully thought out and planned and they will need separate service accounts to be created in Active Directory. In this example I am only showing the base!

The Setup account – this is the account you run setup with. This account needs to have administrative privileges on the server you are running setup on. On SQL this account needs to have the following SQL roles. Then the Farm Service Account which is used as the CA web application account and also accesses the CA config and content databases.

You can also create the two databases in SQL. The collation setting is Latin1_General_CI_AS_KS_WS if you are creating the DB manually in SQL first.

Then run the following commands by navigating to the SharePoint 2010 PowerShell Management Shell via the Programs > Microsoft SharePoint 2010 Products and run as Administrator.

New-SPConfigurationDatabase -DatabaseName "SP10_ConfigDB" -DatabaseServer "SPSQL.trainsbydave.com" -AdministrationContentDatabaseName "SP10_Admin_ContentDB" -Passphrase (ConvertTo-SecureString "pass@word1" -AsPlaintext -Force) -FarmCredentials (Get-Credential)

The command will prompt you for the Farm credentials which is the domain\svc_SPFarmService account from above.

Once that is done close and re run the PowerShell window then run each of the following commands.

1. Install-SPHelpCollection -All
2. Initialize-SPResourceSecurity
3. Install-SPService
4. Install-SPFeature –AllExistingFeatures
5. Install-SPApplicationContent

Note: Before running the next command ensure that the farm CA will only run as NTLM if the farm requires Kerberos then you will need to follow steps to setup the SPN's and use the value "Kerberos" for –WindowsAuthProvider or configure this later.

The following cmdlets in this document outlines steps for NTLM authentication.

New-SPCentralAdministration -Port 6038 -WindowsAuthProvider "NTLM"

This command will provision the CA web site on the server. In this example the URL for accessing CA will be in the format : http://[servername]:portnumber

Now this is just the beginning and I am not going to show you how to do this from start to finish but rather look at the SQL Databases that you will need for SharePoint 2010 and what you need to plan with regards to these. If you want to know all the required PowerShell cmdlets there are many resources available on TechNet and Gary Lapointe has some great templates to build/extend your own.

• Windows PowerShell for SP2010 reference > http://technet.microsoft.com/en-us/library/ee890108.aspx
• Gary Lapointe’s PowerShell commands > http://stsadm.blogspot.com/2009/02/downloads.html

What are the Databases?

So the databases you *may* need are as follows based on Service Applications.

• Search (3)
• User Profile (3)
• Managed Metadata*
• Content Type Hub* (*In order for MMS to work you need a CT Hub web application where you will create and publish your enterprise content types and managed term sets)
• Secure Store
• State Service (2*) aspnet_sessiondb > Read this post from Todd Carter on this > http://todd-carter.com/post/2010/04/30/A-Session-State-By-Any-Other-Name.aspx
• Business Data Connectivity
• Web Analytics (2)
• Performance Point
• Usage and Health data collection
• Word Automation
• And the Content Databases as per each web applications you create as required in your deployment

So all in all you are looking at over 20 databases for a single SharePoint deployment, which you need to plan accordingly.

Some of the must read resources around this are available on TechNet and you will need to have a look at these based on what your deployment is.

• Capacity Management and Sizing for SharePoint 2010
• Storage and SQL Server capacity planning and configuration (SharePoint Server 2010)

So as you can see with these added features there is quite a bit to plan before you start running Wizards in order to get stuff working properly. I’ll post some more details as time permits based on some real world capacity planning in the coming weeks.