Questions on SharePoint and SQL Server
I got asked the following questions by a DBA (database administrator). Whilst I could give some answers to these questions I am looking for answers from all of you out there who maybe are better at answering SharePoint and SQL Server related questions.
These are related to SQL server and how backup and maintenance should be handled from a DBA perspective. I have to admit I didn’t know some of the answers to these questions. So I had to do a bit of reading before I could answer all of them. Most of these answers are sourced from TechNet and related blog posts and my own observations apart from the most obvious answers.
So here are the questions I got and my answers that I compiled. Feel free to correct or add new comments.
1. Where is content stored?
Answer: All content is stored in SQL Server. Also note that configuration information is also stored in the database.
2. Where is metadata stored?
Answer: Meta data related to document properties are stored in the properties of the document. The document is stored as a binary file in SQL server. Sites and Lists including all your master pages, page layouts, CSS and XSL apart from managed code and assemblies are all stored in the database.
**3. What are the workflow components and where are they stored? **
Answer: Workflow is provided via a core services layer. The workflow engine is part of Framework 3.0. When a workflow is created these are then associated with a SharePoint list (A document library for example). So the workflow is also content and is stored in the database.** **
**4. What causes a new database to be created? Why are the names so cryptic and can they be changed? **
Answer: (What causes a new database to be created?) Installation of SharePoint causes database(s) to be created. There are detailed installation instructions and a how to guide for evaluating WSS V3 and MOSS.** **
Basically when you install SharePoint you have these options.
A standalone SharePoint installation is done by installing everything on one single server. I.E. Database services and Web server on same box. Ideally you would want a dedicated SQL server for SharePoint depending on your deployment scenario.
Detailed deployment planning articles and guidelines are on TechNet.
From an architectural point of view SharePoint is built using a framework which allows separation of its various service components. This means that it leverages the following services.
Then SharePoint adds a provider model framework which takes care of the rendering and data access layer. This is built on .Net 2.0
As part of its core services the “Storage services” takes care of storing and retrieving information effectively and quickly from the database. Storage services provide context to the raw data stored in the database. This allows the data to be more easily indexed, interpreted and managed.
When a new web application is provisioned a database is created. This holds content for that web application. Then the web application will have a site collection associated with it. All the data related to this site collection is stored in the content database.
Example: when you provision an “Intranet” you create a web application. When you create a web application in SharePoint it requires a content database to store content for the “Intranet”. The web application creation steps will ask you for a DB name. This is where you give a meaningful name such as “SharePoint_Intranet_PRD”.
As part of Shared Services a SSP content database and a Search database is created.
The following blog post gives a good overview of Shared services and what Shared Services provide in a MOSS installation.
http://blogs.msdn.com/martinkearn/archive/2006/06/06/MOSS-Architecture-2600-Shared-Services.aspx
As a guide these are the databases that was created as part of our internal Provoke intranet (MOJO) using SharePoint
Web Application
Database Name
Purpose
SharePoint Central Administration
SharePoint_AdminContent_2313aaf4-abee-4f76-8e06-2bf226e06be6 (See below how to avoid GUID name)
This is the content database for the Central Administration host web site.
Shared Services Provider
WSS_Content_MOJO_PRD_SSP
Shared Services App Content Database
MOJO Production Web
WSS_Content_MOJO_PRD
MOJO Content Database
MySites Content Database (You can choose to have a separate content DB for your mysites in our case we choose not to)
Database
Purpose
Scope
SharedServices_MOJO_PRD_DB
Shared Services Configuration database
Farm
SharedServices_MOJO_PRD_Search_DB
Search settings and search configuration information for SSP
Farm
MOJO_PRD_SharePoint_Config
MOJO Configuration Database
Farm
WSS_Search_MOJO_PRD
MOJO Search Index database
Web App
Answer: (Why are the names so cryptic (GUID based) and can they be changed?)
In a single installation of SharePoint the “Central Administration” application needs a database to hold its content. This one is the cryptic one.
Ex: This database will have a name like: “SharePoint_AdminContent_5d495fff-ed7d-4896-9375-f423867c4e2d”
To avoid a GUID being appended to the end of the database name you have to follow the below steps prior to the install. This is task for someone who is doing the MOSS install the first time around.
Using PSCONFIG rather than the GUI based configuration wizard you can name your “SharePoint_AdminContent” database explicitly.
So with that in mind, consider the following topology
SP-SQL is the name of my SQL 2005 database server.
SP-WEB1 is a web server running as a Web Front End.
DOMAIN\SPServiceAct is my SharePoint farm account
DOMAIN\SQLServiceAct is my SQL service account used by SharePoint
After installing SharePoint, do not select the “Run the configuration wizard” check box and close the dialog box
Open a command prompt and change the directory to the BIN folder, “C:\program files\common files\Microsoft Shared\web server extensions\12\bin”
Run the PSCONFIG command (substitute with your server names)
psconfig -cmd configdb -create -server SP-SQL -database NAME_WSS_configdb -user DOMAIN\SPServiceAct -password “SPServiceActPassword” -admincontentdatabase NAME_WSS_admin_content -dbuser DOMAIN\SQLServiceAct -dbpassword “SQLServiceActPassword”
This will run through the post setup configuration wizard and use the database names that is provided above. Now the databases used for Admin_Content is not cryptic.
Once this is done don’t forget to provision the admin site itself.
psconfig -cmd adminvs -provision
You can find how to instructions for using PSCONFIG here:
For changing the GUID name after and installation follow these steps.
Before performing this operation make sure all your databases are backed up. You also need to log on to the server with an account which has full access (dbo) rights to the database server. Ideally you will need to give the setup account access to the database server.
“stsadm -o addcontentdb -url http://servername:42000 -databasename SharePoint_AdminContent_INTRANET_PRD -databaseserver databaseservername”
**5. What should the recovery model be? What data could be lost if database in Simple recovery mode. **
Answer: This was an interesting question. After several hours of research I found the following. This blog post in particular from Bill English of Mindsharp has the following points. I have taken points from his post to answer the question. Original post is here. http://mindsharpblogs.com/bill/archive/2005/01/14/181.aspx (Again refers to SPS 2003 but is valid for MOSS 2007)
**SQL Full recovery mode **
Full Recovery model logs all transactions so the installation can be recovered to the point of failure (and not just to the last backup).
Basically if a content database is about 4GB in size (MDF) the log file (LDF) size is about twice as that. This is because unless you specifically setup back and maintenance on SQL server the transaction log keeps growing. You can setup maintenance plans to shrink the log file or restrict the growth of the log file.
According to Bill’s post there is no hard and fast rule on the size of the log file. This is up to the DBA and the SharePoint admin to create a data backup and maintenance plan that best meets their environment.
**SQL Simple recovery mode **
The simple recovery model minimizes administrative overhead for the transaction log, because the transaction log is not backed up. Generally, for a user database, the simple recovery model is useful for test and development databases or for databases containing mostly read-only data, such as a data warehouse. The simple recovery model is inappropriate for production systems where loss of recent changes is unacceptable. In such cases, the recommendation is to use the full recovery model.
So basically you need to setup your backup and restore jobs using SQL management studio in SQL server. Joel Oleson also has the following guidelines on High Availability, Backup and Restore as well as Disaster Recovery.
Read more about recovery models of SQL server here:
http://msdn2.microsoft.com/en-us/library/ms189275.aspx
Deploying SharePoint using DBA-created databases:
**6. What is the impact on the transaction log file size of having everything in a database, and say making a one-character change to a 2MB Word doc, and keeping 3 or 4 versions? **
I think the above question no 5 answers this. If there are specific answers please leave a comment and I will incorporate this into this post.
This post from Joel Oleson sums up the file improvements (size) in Microsoft Office System.
Basically if you have a 2MB document and store 2 versions then the file size will equal 4MB. It’s not different from having the same on a file share.
**7. What is different for SharePoint database when it comes to optimising and reindexing? **
I believe that it is not different from any other database. I hope that someone from the community will help me here.
**8. What does the data model look like? What kind of datatypes are being used? What indexes/ What FKs? **
Microsoft has not published these and I believe for a good reason. This post from Mike Fitz sums this up nicely.
**Please stay out of the database **
http://blogs.msdn.com/mikefitz/archive/2005/04/01/404802.aspx
Suffice to say that in terms of a DAL (Data access layer) you must and should use the API and stick to it. If something can’t be done using the API then there is probably a very good reason that it’s not exposed via the API.
On a second note if you are really keen to find out what the indexes and FK relationships are you can pretty much look at a SharePoint development database schema via SQL management tools.
**9. How many tables (ball park), what sizes for a small, medium and large MOSS site? **
Again I would like to hear from anyone on a specific answer.
A quick look shows there are 91 Tables including language conversion tables. You can view the no of Tables on SQL Management Console.
There is no difference in terms of tables for any type of deployment.
**10. What causes performance problems? How do you monitor for these? **
For capacity planning and performance articles please go to Joel’s blog here.
http://blogs.msdn.com/joelo/archive/tags/Performance+2600_amp_3B00+Scale/default.aspx
I hope that this post is helpful for DBA’s and SharePoint administrators who are concerned about database growth with SharePoint. I would like to kindly point out that this is not any different from a file share growth. If an audit was held today on the amount of disk space that has been used up by file shares and duplicate files it would stack up very high. Not to mention the lack of relevance in a document in a file share.
If there are any comments around these answers please leave a comment. Comments are moderated and I will answer them when time permits.
**WSS Evaluation guide: **
http://www.microsoft.com/technet/windowsserver/sharepoint/techref/techguide.mspx
**MOSS Evaluation guide: **
http://office.microsoft.com/en-us/help/HA101680161033.aspx
TechNet IT pro related information
http://www.microsoft.com/technet/prodtechnol/office/sharepoint/default.mspx