By: Ray Barley | Updated: 2012-02-02 | Comments (9) | Related: > 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:
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:
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:
- Hardware and Software Requirements (SharePoint Server 2010).
- SQL Server 2008 R2 and SharePoint 2010 Products: Better Together (white paper)
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!
About the author
Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-02-02
