Introduction to SharePoint 2010 for SQL Servers DBAs

By:   |   Comments (9)   |   Related: > SharePoint


My company is getting ready to rollout SharePoint 2010.  As the only DBA on staff, I need a quick introduction to the product that is geared to a DBA.  Can you help me out?  Check out this tip to learn more.


From the standpoint of a DBA, you can think of SharePoint 2010 as just an application that stores its data in SQL Server databases.  However, it is really useful to have a high level understanding of SharePoint concepts such as the SharePoint farm, server roles, service applications, web applications, and sites.    

In this tip I will provide an overview of the SharePoint 2010 concepts and discuss what matters to the DBA.  In addition, I will highlight what the DBA needs to know about the installation and configuration of SharePoint.  In future tips I will focus on what the DBA needs to know about administering the SharePoint databases.   

In the this tip, I'm referencing the SharePoint 2010 platform.  

SharePoint 101

When you mention the word SharePoint, most people think of a browser-based application that you use for collaboration.  Your company, department, group, etc. has a site that contains lists used for announcements, events, contacts, calendars, documents, and so on.  From the standpoint of the DBA, these lists (and other data) are stored in SQL Server databases.

From a technical standpoint, there are several distinct products that we refer to as "SharePoint 2010".  SharePoint Foundation 2010 provides the basic collaboration functions.  SharePoint Server 2010 provides additional capabilities above and beyond SharePoint Foundation and includes the following versions:

  • SharePoint Server 2010 Enterprise Client Access License features
  • SharePoint Server 2010 for Internet Sites, Enterprise
  • SharePoint Server 2010 Standard Client Access License features
  • SharePoint Server 2010 for Internet Sites, Standard

From the standpoint of the DBA, the version of SharePoint that your company chooses is largely a business decision and maybe somewhat influenced by the difference in the licensing costs.  For the DBA it's all about databases and a lot of them!

There are three specific types of database used by SharePoint:

  • Farm configuration
  • Content
  • Service application

I will provide more details on these databases in the following sections and in future tips.  

SharePoint Farm

The SharePoint farm is comprised of one or more servers.  In your organization you have one or more farms; e.g. intranet, development, and testing.  From the standpoint for the DBA, a farm is represented by a single SQL Server database which by default is named SharePoint_Config (i.e. the farm configuration database).  Each server in a farm is "connected" to a single configuration database.  If you have multiple farms, each one has its own configuration database.

The following diagram is taken from Topologies for SharePoint Server 2010 on the Microsoft TechNet site and shows some examples of small SharePoint farms:

Topologies for SharePoint Server 2010

Note that you can have a SharePoint farm that is made up of only a single server; you can have much larger farms than the above diagram shows as well.

Server Roles

There are three server roles in a SharePoint farm:

  • A web front end serves up web pages for end users; i.e. it runs web applications
  • An application server provides services to the web applications; e.g. Excel Services, User Profile, Performance Point, etc.
  • A database server runs instance(s) of SQL Server that store data for web applications and services

Keep in mind that a single server could provide one or more of these roles.  From the standpoint of the DBA, the database server is obviously the focal point.

Service Applications

SharePoint provides many service applications that are consumed by the web applications.  The following is a screenshot from the SharePoint Central Administration web site showing the complete list of services provided out of the box with SharePoint Server Enterprise Edition:

SharePoint Central Administration complete list of services

From the standpoint of the DBA, the important thing about application services is that some of them have one or more databases associated with them and they have their own unique considerations.  As a general rule the service application databases tend to not be that big.  I will cover the details of creating and managing service application databases in a future tip.

Web Applications

Web applications serve up the web pages that provide the SharePoint user experience.  Each web application is provided by an Internet Information Services (IIS) web site.  The web application's code is executed in an IIS application pool process.  From the standpoint of the DBA, there are two key points about the web application:

  • A web application is associated with one or more content databases; the content database is where the data in the SharePoint is stored.
  • The IIS application pool identity (i.e. the Windows account that is running the process) automatically gets specific permissions in the content database

The default behavior in SharePoint is that when you create a web application, you also create a content database. 


The word sites is somewhat ambiguous in SharePoint.  The connotation of a site is a "web" site.  From the technical standpoint of the SharePoint object model, a site is really a site collection; each item in the collection is a web.  From the standpoint of the DBA, the important thing here is that an entire site collection is contained in a single content database; a content database can hold one or more site collections.  From a planning perspective, you want to give careful consideration to what site collections are stored in a particular content database.  Content databases can become quite large so deciding the content database for each site collection is one of the most important things for the DBA.  Multiple, smaller content databases can be much easier to manage than a single large content database. 

The default behavior in SharePoint is that when you create a site collection, SharePoint puts the site collection in one of the content databases associated with the web application.  You will want to designate which content database will hold a particular site collection.  I will cover creating and managing content databases in a future tip.

SharePoint Installation and Configuration

A SharePoint installation can be broken down into the following steps:

  • Install SharePoint prerequisites (there are a number of items that SharePoint requires)
  • Install SharePoint software on every web front end and application server
  • Perform SharePoint configuration on each web front end and application server

From the standpoint of the DBA, here are the things that you need to know:

  • SharePoint 2010 only works with the following 64 bit editions of SQL Server: SQL Server 2008 R2, SQL Server 2008 SP1 CU2, and SQL Server 2005 SP3 CU3
  • To install SharePoint requires a Windows account (i.e. the SharePoint "setup" or "admin" account) with the following attributes:
    • Should be a Windows domain account
    • Member of the Local Administrators group on each SharePoint server
    • SQL Server login and a member of the dbcreator and securityadmin SQL Server server roles
  • SharePoint configuration is where you create a farm (i.e. create the SharePoint configuration database) and connect the SharePoint server to the newly created farm or an existing farm
  • SharePoint configuration requires a "farm" account (i.e. a farm administrator account); this account should be a Windows domain account and must already exist; the configuration process will:
    • Create a SQL Server login for this account
    • Add it to the dbcreator and securityadmin SQL Server server roles
    • Add it to the db_owner database role for all SharePoint databases

For additional details about the SQL Server database requirements take a look at:

Next Steps
  • This tip provides the foundation knowledge that I think a DBA needs to begin administering SharePoint databases. 
  • Stay tuned for future tips that will cover database administration topics related to SharePoint databases.
  • Remember that SharePoint creates some SQL Server logins and databases; if you see databases and logins that you're not familiar with, find out if they were created by SharePoint before deleting them!

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, September 24, 2013 - 5:23:16 PM - Timothy Murphy Back To Top (26923)

Just a helpful tip:

If you are a DBA and start having to deal with SharePoint databases.  I highly recommend to allocate time to test backup and restore within a test environment and includes someone accessing the SharePoint app to verify functionality.  Look for good backup software that will let you drill into and recover single files as well.  Depending on the setup, the backup and recover process will be different but there is useful information out there that will help you compile a procedure list for your environment.

Be proactive!!!  This will assist in avoiding undo stress and long hours in case the scenario ever arises.

Wednesday, February 15, 2012 - 4:42:57 AM - Srinath M Back To Top (16023)
Awesome article....Very nicely written...!!

Thursday, February 2, 2012 - 12:25:40 PM - Ankit Shah Back To Top (15885)

nice tip..looking forward to learn more in share point admin

Thursday, February 2, 2012 - 12:15:20 PM - Bob Chauvin Back To Top (15884)

My top tips for the DBA provisioning a new SP farm:

1) Keep SP databases in their own instance, separate from other LOB databases.  The reason are:  SP databases perform best with some special configuration settings that can have a negative impact on lob applications, for example MAXDOP=1 for SP;  Second, for performance monitoring and recovery a separate instance has some admin benefits.   Heres a link re MAXDOP

2) Use powershell to provision your farm and sites, as this will allow you to impose a naming convention on your SP databases.  Otherwise you get the old service prefix with a guid.  Once you have more than one site, determining what database goes to which can become a nightmare... especially in 2010 where the number of databases baloons.  A secondary and valuable side benefit is that your farm config is documented, and the build out is repeatable.

3) Script your backups via the SP powershell , or find a good 3rd party tool to help manage the Backup/recovery of your mission critical farm.  Heres a good MSFT ref   and heres a good instructional on automating them (via Powershell)

Thursday, February 2, 2012 - 9:39:01 AM - Thomas LeBlanc Back To Top (15883)

Excellent introduction. Looking foward to the series!!!

Thanks, Thomas

Thursday, February 2, 2012 - 9:14:46 AM - Ray Barley Back To Top (15880)

SharePoint could work but without knowing the details of what you've done it could likely be a signifcant level of effort to migrate your application to SharePoint.  SharePoint can be leveraged as a platform to build applications provided the application needs the kinds of things that SharePoint does well, like forms, workflows, etc.  You have to design the application knowing that you want it to leverage SharePoint else it could be alot of work.

Thursday, February 2, 2012 - 8:24:41 AM - Desh Maharaj Back To Top (15879)

Hi. I have a development team and we have developed web based ERP system with full financials and medicical business practice collatral. We are  not complete there are 400+ tables in SQL 2007 R2 and about 500 pages in ASP. I am very doubtfull that this project will work well in Share Point Server. I need the share point server fro inregraated security and perimeter security enhancements. Also waht about the creation of multiple Private cloud instances. Regards Desh

Thursday, February 2, 2012 - 6:13:02 AM - Ray Barley Back To Top (15876)

Here are two books that I use and would highly recommend:

Microsoft SharePoint 2010 Administrators Companion by Bill English, Brian Alderman and Mark Ferraz

Automating Microsoft SharePoint 2010 Administration with Windows PowerShell 2.0 by Gary Lapointe and Shannon Bray

Thursday, February 2, 2012 - 3:17:24 AM - Mohammed Moinudheen Back To Top (15874)


Thank you for a nice introduction which is very useful for DBA's. Also, could you please recommend a good book for Sharepoint administration specifically from DBA standpoint.

Thanks, Mohammed Moinudheen


get free sql tips
agree to terms