Fragmentation Manager dramatically improves the manageability and visibility of index fragmentation across your SQL Server enterprise… and via support for multiple concurrent operations, it defrags indexes faster than ever before!
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.
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:
I will provide more details on these databases in the following sections and in future tips.
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.
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.
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.
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 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:
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
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
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.
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.