SQL Server Reference Guide

One of the fastest-growing, highly adopted Microsoft products (as of this writing) in many organizations is SharePoint. This software acts not only as a product people can use right out of the box, but it also is a full platform that lets developers write code, businesspeople create workflow routings, and there are third-party add-ins that extend this platform even further.

So what does this have to do with the Data Professional? Well, there are two areas of intersection with our role that SharePoint entails. The first is the fact that SharePoint not only runs using data from a SQL Server database, but that it also stores almost all of the data the users work with in SQL Server. That means whenever a user stores a document, drawing or any other file in SharePoint, it ends up in a SQL Server database. When they assign rights to that document, that’s stored as a record in SQL Server as well. So the essence of growth, performance and stability of a SharePoint “farm” (or group of servers) is dependent on the database layer – and that’s our responsibility.

The second area where SharePoint and SQL Server interact is with the reporting and analysis features of SQL Server. SharePoint sites (more on all this terminology in a moment) can easily display SQL Server Reporting Services (SSRS) reports. There are a few things you can do to make that easier for your SharePoint users.

A Few Caveats

I teach an entire course on this subject. It’s a really long, involved process, and there is a lot more information that I (and many others) cover for SharePoint and SQL Server than I will cover in this article series. So there are many references I’ll provide at the end of the series where you can go to learn more. The point is, not everything is here that you need to know about SharePoint and SQL Server, but this should get you started.

I wanted to keep this information to a single article, but it’s just too big a subject to even introduce in one sitting, so I’ll break it up into more. This article will cover a SharePoint Introduction and some parameters around what you can and can’t do to the data layer, and in the next ones I’ll give you some concrete steps to follow for maintenance and monitoring the system. I’ll also provide you with a great many references so you can learn more, and I’ll categorize those for you so that you can get to the information you care about.

You’ll notice that I titled this article with a version number for SharePoint. Whenever I do that here at InformIT, it means that the information applies to that version only – not earlier or later ones. Right now, a much newer version of SharePoint has already been released, but the information in this article applies only to version 2007. I’ll post another article on other versions, but this is the one I see in the field most often.

Here’s the final, most important condition for this article:

You might be surprised to learn that many of the tools and tricks you know to help with system growth and tuning are not supported on a SharePoint system. I’m not saying that it’s not a good idea, I’m saying you shouldn’t do it. “Wait,” you might think, “surely you don’t mean that I can’t just add a simple index or lay out Partitions in SQL Server, right?” That’s exactly what I’m saying. I can’t tell you how many really good DBA’s I met that have trashed the performance of a SharePoint system by “optimizing” it. That isn’t to say changes in structure won’t happen – with an authorized Microsoft person helping you, they might actually suggest an additional index here or there, but they know why they can do this, and you and I don’t.

So wait – if we aren’t allowed to touch the structure of a SharePoint system, what’s there to do? Is it just a self-tuning, hands-off, not my problem kind of thing? No! There’s a lot the Data Professional can do to ensure that the SharePoint system is fast, resilient and safe. You can use this article to begin your information search, and at the end I’ll give you more resources to help you learn more.

Design is Key

Your biggest impact on the SharePoint database system(s) is in the design and planning phase. There are many things that are set into stone once you implement, and just about the only time you have any hope of getting the budget you need is at the outset of putting the system into place, so let’s start there.

Finding SharePoint in your Organization

SharePoint has a way of “growing” into an organization. What that means is that someone turns on a small set of features that are built-in to Windows and then folks start using it. Once they like it, they use it more and more, and only after the fact do the technical professionals think about the design of this organic system.

Wait - what? SharePoint is built into Windows? Well, not entirely, but yes, you can get some of this functionality for free with a Windows Server. It’s an add-on feature called Windows SharePoint Services (WSS), starting in Windows Server 2003. It’s been superseded in later releases with SharePoint Foundation Services, but that’s a later version that I won’t be covering here.

I many cases, someone that owns a server might turn on this feature, people begin to use it, and then over time it becomes mission-critical. It’s important for the DBA to know about this for a few reasons.

The first is that the WSS system installs and uses an MSDB database – which the administrator may not know about and most certainly won’t be doing a lot of maintenance on. So now you have data in your environment without the direct involvement of the DBA or Data Professional. the second is that if you know about the smaller systems, you can work with your technical team to plan a larger environment correctly.

Perhaps your organization decides they want to use SharePoint because of the benefits it gives them. However you find out about the SharePoint effort, make sure you insert yourself as early as possible into the design process. I realize that most of us aren’t looking for more work to do – but if you plan the design with the SharePoint team and help them understand the database impacts, you actually save yourself lots of work later, and you help the organization use it properly. And that’s the shared goal.

There are tools to help you locate a SharePoint database, by the way. You can use various things like Microsoft’s System Center or other software-logging systems to find the main SharePoint Server. Within it’s configuration information (on the main SharePoint administrator’s Page) you’ll find the “Configuration Database”. I’ll explain all these databases later, but once you locate that database, you can run this script to find the other databases SharePoint is using:

/* Find SharePoint Databases */ 
SELECT a.[Name]
FROM [SharePoint_Config].[dbo].[Objects] a
WHERE a.[Properties] LIKE '<object type="Microsoft.SharePoint.Administration.SPContentDatabase%'
OR a.[Properties] LIKE '<object type="Microsoft.SharePoint.Administration.SPConfigurationDatabase%'
OR a.[Properties] LIKE '<object type="Microsoft.Office.Server.Administration.SharedDatabase%'
OR a.[Properties] LIKE '<object type="Microsoft.Office.Server.Search.Administration.SearchSharedDatabase%'
OR a.[Properties] LIKE '<object type="Microsoft.SharePoint.Search.Administration.SPSearchDatabase%'
ORDER BY a.[Name];
GO

It’s important to note that this is just about the only script you should run on a SharePoint system. While there are lots of scripts you can find to run on SharePoint (I have a whole library of them) you shouldn’t run them on a production system. Even this one should be used only to locate those databases – SharePoint is a busy system, and locking any part of it while you explore the system is a bad idea. Even simple “this won’t hurt anything” queries, will in fact take locks you may not be aware of, impacting your production system.

OK, let’s get started digging in to what these databases mean within a SharePoint System.

Understand the design of SharePoint

Working together with other technical professionals is important for a SharePoint environment, since it involves many parts. While you don’t have to become a SharePoint expert, knowing certain broad components is important, so I’ll cover those here.

A SharePoint 2007 system is composed of three main parts:

1. Web Server
2. Worker Services
3. Databases

A "Farm" is one or more servers talking to a single (SQL Server) “Configuration” database. The Configuration database is the "registry" of SharePoint, and the Windows SPTimer Service it creates is one of the communication mechanisms between servers.

Here’s a diagram I created that shows the main components in a SharePoint 2007 Farm and how each of those components is used:

Figure 1

Take your time and look this diagram over – each layer and piece has a distinct function, whether this is all on one server (suitable for only the smallest of SharePoint implementations) or many.

There are many boxes and pieces here – and many of them can live on separate servers. That helps the designers scale the system. While scaling the SharePoint components isn’t necessarily interesting to you, notice the bottom layer – SQL Server running on Windows. That’s where you come into play.

You can place the different databases SharePoint uses on different (or the same) SQL Servers, and it’s best to do that as early as possible. While you can move the databases around later, it’s much easier to design it that way from the outset.

Recall that the Configuration database in SharePoint points to where all of the other databases live. Here are the main databases within a SharePoint 2007 system, with a description of what they do – this will form the basis of the things I’ll explain later, so keep this handy:

There are three primary types of databases in SharePoint 2007:

Configuration (SharePoint_Config – this makes a Farm)

• Show the Sites for SharePoint
• Points to the other Databases
• Points to the other Servers
• References the Web applications in the Farm

Content (WSS_Content* and SharePoint_AdminContent*)

• Windows SharePoint Services site details
• Structure details
• User content (Like Word documents and such)
• Files for the rest of the system

Shared Services Provider (SSP) (SharedServices*_*, WSS_Search*)

• User profiles
• Audience data
• Business application data
• Excel Services functions
• Site usage data
• InfoPath Forms Services session state information
• Search data
• History log
• Search log
• Calculation tables for crawl statistics
• Links tables and statistical tables

Search Databases

• Search Meta Data

Later versions of SharePoint lay out these databases differently, so remember that this is only applicable to SharePoint 2007. And don’t worry if you don’t know what each of those functions are – the other members of the SharePoint team will, and they can help you understand how busy each one of these functions are, or whether they are even implemented in your environment.

Two final bits of architecture are important for you to know. In SharePoint 2007, the “Search” function is actually hosted out in the operating system, with meta-data stored in a database in SQL Server. The process of examining the contents of the databases (and SharePoint itself) is called a “crawl”, and it’s one of the most intensive procedures that happens in SharePoint – it hit everything from SQL Server to the CPU and Memory as well as network. The SharePoint administrator schedules these to happen at the least busy times, which is when you might be doing your database maintenance, so it’s good to share this scheduling information between the two of you.

One other architecture attribute is important for you to be aware of is that instead of storing large binary documents in a SQL Server database, SharePoint 2007 has the ability to use a special “shim” layer that stores them out on the Operating System’s files area. Each one works a little differently, and is outside the scope of what I’ll cover here for sure, but you should know if it is being planned by the SharePoint team. If so, you should find out where they plan to store the files and keep your database files separate from those spindles.

OK, I’ll stop there for now and let you digest this part of the process. In the next article I’ll explain how to plan with the team for scale, what database settings you should use, and what your options for monitoring and management are.