 |
|
Introduction to SharePoint 2010 for SQL Servers DBAs
|
By: Ray Barley | Read Comments (8) | 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:
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!
Last Update: 2/2/2012
About the author
PrintComments 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...!! | |
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |