Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Introduction to SharePoint 2010 for SQL Servers DBAs

MSSQLTips author Ray Barley By:   |   Read Comments (9)   |   Related Tips: > Sharepoint
Problem

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.

Solution

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. 

Sites

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!


Last Update: 2/2/2012


About the author
MSSQLTips author Ray Barley
Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, February 02, 2012 - 3:17:24 AM - Mohammed Moinudheen Read The Tip

Ray,

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

 


Thursday, February 02, 2012 - 6:13:02 AM - Ray Barley Read The Tip

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 02, 2012 - 8:24:41 AM - Desh Maharaj Read The Tip

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 02, 2012 - 9:14:46 AM - Ray Barley Read The Tip

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 02, 2012 - 9:39:01 AM - Thomas LeBlanc Read The Tip

Excellent introduction. Looking foward to the series!!!

Thanks, Thomas


Thursday, February 02, 2012 - 12:15:20 PM - Bob Chauvin Read The Tip

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 http://blogs.technet.com/b/lukeb/archive/2011/09/08/sharepoint-maxdop-1.aspx

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 http://technet.microsoft.com/en-us/library/ee748617.aspx   and heres a good instructional on automating them (via Powershell) http://imperfectit.blogspot.com/2010/03/automate-sharepoint-2010-farm-backups.html


Thursday, February 02, 2012 - 12:25:40 PM - Ankit Shah Read The Tip

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


Wednesday, February 15, 2012 - 4:42:57 AM - Srinath M Read The Tip
Awesome article....Very nicely written...!!

Tuesday, September 24, 2013 - 5:23:16 PM - Timothy Murphy Read The Tip

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.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.