Is Your SQL Server Ready for SharePoint 2010?


Alright, if you have been tracking the posts on the upcoming SharePoint release, you may very well say, “Yes, I know…I know. SQL Server needs to be 64-bit.” But I ask you to keep reading because I think there is more to the story.

The Basics

Let me very quickly review the 64-bit story for those who may not seen the posts. In order to install SharePoint 2010, your SQL Server will need to be version 2005 or 2008 and it will need to be 64-bit. You won’t be able to use a 32-bit SQL Server or a SQL Server 2000 instance.

So What Else Is There?

SQL Server is the heart of your SharePoint farm. I continue to repeat this phrase every opportunity I get to discuss SharePoint infrastructure. With the improved functionality of SharePoint 2010 or your current SharePoint 2007 platform, your usage and needs from SharePoint will continue to grow. SharePoint 2010 providing more functionality means you will need more SQL resources. The first place to start to ensure the infrastructure is ready for your needs is SQL Server.

Does your current SharePoint environment’s response time meet your needs? Do you have enough capacity to handle your database growth for the next six months to year? These are important questions to evaluate now. If your answer  to these questions is no or I don’t know, I want to give you some steps to begin finding the root of the problems.

Performance

In evaluating the performance of a SQL Server, review these four components: processor, memory, disk and network.

Disk is normally the primary bottleneck I find when evaluating client deployments. The disk subsystem needs to have enough disk throughput (or IOPs) so that it can stay responsive enough to your needs. For small to mid-size businesses, your disks should probably respond to requests within 20 milliseconds (ms). For most enterprise environments, a 10 ms responsive time is ideal. For high performance needs, you will design for 5 ms. Look at performance counters \Logical Disk\Avg. Disk sec/Read and \Logical Disk\Avg. Disk sec/Write.

Memory bottlenecks on a SQL server are harder to identify so I want to provide some very high level guidelines: small servers need 8GB, medium servers need 16GB, and large servers need 32GB. I will do a future post digging into these sizing recommendations in more detail.

Processor and network on your SQL server are pretty easy to evaluate. Processor should stay below 80% average utilization and not have any long sustained peeks at 100% utilization. Network utilization should stay below 70% average utilization.

When evaluating bottlenecks, you want to evaluate and eliminate bottlenecks in this order: memory, processor, disk and network. There are reviewed in this order because the previous item could be effecting the item after it.

Capacity

As your use of SharePoint grows, you are going to want to manage the growth of your content databases. For collaboration environments, you want to make sure your content databases do not grow above 100GB. If they start to approach this size, you need to break up the content in different site collections and move those site collections to other content databases.

Ensure you check the free space on each of your drive letters (LUNS).  NTFS performance drops after the free space is below 25%. Therefore, no drive letter should not go beyond 75% utilization.

You want to monitor how quickly your databases grow over time. This allows you to predict future storage needs and plan accordingly.

Putting It All Together

These are a series of check you should do in your environment to make sure today that your SQL Server is ready for the SharePoint workload tomorrow.

About J.D. Wade

Senior SharePoint Consultant, husband, active in community theatre, love watching movies, have been described as “quirky”, aka “KB Man”
This entry was posted in Microsoft Office SharePoint Server 2007 (MOSS 2007), Microsoft SharePoint, Microsoft SQL Server, Outdated: Do Not Use, Sharepoint Foundation 2010 (SPF 2010), SharePoint Server 2010 (SP 2010), Windows SharePoint Services 3.0 (WSS 3.0) and tagged . Bookmark the permalink.

2 Responses to Is Your SQL Server Ready for SharePoint 2010?

  1. Someone who knows about SQL Server says:

    What a crock!

    Don’t mindlessly repeat recommendations if you are not an expert in that field.

    You have mindlessly repeated some recommendations from general SQL Server best practices whitepapers…

    The 100GB content database limit is COMPLETE RUBBISH!

    Can you imagine keeping 25% free disk space on your SAN where you are paying $8000/TB? Ouch!

    Perhaps you shold be talking about the need to optimise your tempdb system database as Sharepoint 2010 heavily utilisaed it.

    Or perhaps how you should have 6-8GB of memory per core on your SQL Server instance. (8GB of memory for a small server… What a joke!)

    These kinds of posts perpetuate bad habits and “myths” about Sharepoint/SQL Server which helps no-one!

  2. J.D. Wade says:

    The target of this blog post was just before the release of SharePoint 2010 and to be looking at the capacity of the SQL server. Its target was not the high-end, large scale SQL Server environment that you seem to be describing or configuration optimization of an environment. I was trying to help the part-time SharePoint Administrator who doesn’t really know SQL well and who doesn’t have the time to even check those white papers to inspect some items to make sure they had capacity and were avoiding basic bottlenecks.

    Also, I will completely agree that SharePoint can scale well above the 100GB limit. But for the basic administrator, with a multi-user collaborative environment specifically, it makes sense to follow that recommendation.

Join the discussion by leaving a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s