Home > Articles > Databases > SQL Server

SQL Server Reference Guide

Hosted by

Toggle Open Guide Table of ContentsGuide Contents

Close Table of ContentsGuide Contents

Close Table of Contents

SharePoint 2007 and SQL Server, Part Two

Last updated Mar 28, 2003.

This is the second in a series on SharePoint and SQL Server. In this part of the series, I’ll be building on the information I shared in the first article, so if you haven’t had a chance to look that over, take a moment to do that now.

In this tutorial I’ll dig a little deeper into the practical steps you need to take for working with your SharePoint databases, but I’ll assume that you have the information from the first article, so I won’t repeat the same conditions and caveats here. I’ll have plenty of other ones, though — because based on your needs, layout, and many other factors you’ll get the standard DBA answer: “It depends”. That’s OK — I’ll give you lots of references at the end of the series to go and learn more about each of those areas.

Planning for Scale

As I mentioned earlier, your biggest impact is in the planning phase of your SharePoint Farm. As a refresher, take a look at the parts of the SharePoint “Farm”:

Figure 1

We’ll use this as a discussion point for the planning phase. With this background, the first tool you and your team should investigate is at http://www.sharepointsqlmodeler.com/. This tool will help you select the Edition of SQL Server based on the features you require, and it’s the first decision where you have a big impact. Selecting the wrong Edition might limit everything from performance to safety and recovery features, so this is a good place to start.

Hardware

Your next decision is the hardware. I put this decision after the Edition, since each Edition can use different classes of hardware. Obviously you want the highest architecture you can afford for SharePoint SQL Servers. SharePoint servers deal with two factors — locking, and large inserts and deletes. This means the areas of hardware to focus on are memory and I/O (drives and storage), rather than massive amounts of CPU power or excessive networking capabilities. That doesn’t mean you should “starve” those other two resources, just be aware of where the pressure on the system is.

Note that you haven’t made your choices on how many servers yet — that will come in a moment. Also — I’m discussing the data servers — there are other server types in a SharePoint Farm. You can learn more about those here: http://technet.microsoft.com/en-us/library/cc288751(office.12).aspx

High-Availability and Disaster Recovery (HADR) Planning

Although I can’t cover all of the information for planning for HA and DR, You have many options. I normally propose the following layers of protection:

You do not have the option of SQL Server Replication for SharePoint databases. Yes, I’m sure you can find an article or two out there where people have done it, but you’re in an unsupported environment, and you’re taking even more performance hits when you do — this is typical of SharePoint system articles. There’s always someone out there that has discovered some way to get around the rules, but the unintended consequences always catch up with the work-arounds.

Planning the Databases

I listed all of the databases that SharePoint 2007 uses in the last article, but there are four primary types of those databases:

  1. Configuration
  2. Content
  3. Shared Services Provider (SSP)
  4. Search

The Configuration database makes a Farm, because it stores the locations, paths and so on to not only the other databases, but even other servers in the Farm. So essentially it’s like a “master” system database in SQL Server, or the Registry in Windows. It contains the configuration for everything else.

This database isn’t actually accessed as often as the other databases, but it should be backed up, maintained and so on just like the others — I’ll cover that more in the next couple of sections.

The real focus is on the Content databases. This is where the majority of information and activity is. Keeping them separate, sometimes even onto other servers, can be a good strategy. Keeping them separate helps you maintain, monitor and change the performance at a more granular level. Not only that, there is one other concern that you need to be aware of. It’s best if you keep each Content database below 100 GB in size. That can be quite the challenge, with users wanting to store larger and larger documents in a single site or web area.

This helps you understand where you should work with your SharePoint administration team carefully. They need to be aware of this limit, since they understand what actually creates a Content database. If they aren’t aware of that linkage, check my links at the end of this series to learn more, but an easy way to tell is when they create a new site or area in the SharePoint administration tool, tell them always to look for a field where they enter a database name. If that field exists, that will potentially be a new Content database. They need to help you think through how big that site might get, because splitting them up later is not a trivial task.

As I mentioned, you’ll face the most issues with locking in these Content databases, as users put data and documents in and pull them out. This is why even looking at simple scripts inside a live SharePoint system is exceptionally bad. Running external scripts or processes outside of SharePoint code takes even more locks, slowing the system down even further. This is also the reason even simple or seemingly obvious changes to a SharePoint database, like adding or removing an Index is not supported, and a bad idea. The SharePoint administrators, the users, and system operations change even the structure of the database in ways you can’t predict. So when you change a structure element such as a View or Index, you can bring the system to a crawl. And no one wants that.

So planning the Content database layouts is one of the most critical steps you can take. Not only in how many and where they live, but the growth options and settings you should configure. I’ll cover many of those in a moment, but you can learn more about the sizing aspect here: http://technet.microsoft.com/en-us/library/cc298801(office.12).aspx

Make sure you check out that link, in addition to the options I’ll mention next.

Database Configuration Options

There are certain configuration options and steps that you need to follow for optimum stability and performance for a SharePoint database.

Set the databases to have automatically created and automatically updated statistics. This will cause some further blocking, but the tradeoff for performance is worth the locking it might cause.

Turn off the “Auto Close” setting in database options. That’s a good idea normally anyway, but it’s essential here.

Before you install any SharePoint databases, ensure that you have learned about and set up “Instant File Initialization” — you can read more about that here: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Instant-Initialization-What-Why-and-How.aspx

As far as security options go, install SharePoint with a domain account, as a local admin on each box. The accounts need the following rights in SQL Server, and may even need the sysadmin role. Of course, if it has that role, the rest of these are not necessary to set up individually. This isn’t something we data professionals like very much, which is why I recommend that the SharePoint database be on its own server or group of servers. I just don’t like other folks having that much control in my data systems. At a base, the account that runs SharePoint needs the following roles:

  • SQL Server Logins
  • SQL Security Admin
  • DBCreator

The Server Farm Account (domain\SAFarm) should be separate, and not used for setup. Use it for application pools in IIS. It also needs:

  • SQL Server Logins
  • Admin
  • DBCreators
  • DBO for all DB's

Use at least one account for all Web Applications, and always use a separate account for the "Central Admin" Web app. It also needs:

  • DBO for its Content Database
  • R/W for SSP Content Database
  • Read for Config DB
  • Read for the Central Admin WebApp DB

In the final article in this series I’ll show you how to monitor and maintain your SharePoint data layer, and I’ll give you lots of external links to more information on this topic.

Save To Your Account