SQL and SharePoint

by sharepointbitme

I have mentioned in previous blog entries that SQL is a pretty important part of SharePoint performance and stability. This is important because there are a few things about adjusting SQL for SharePoint that go counter to what I like to call “DBA Lore”. Database Administrators are professional specialists who have a spent a lot of time creating an unwritten set of recommendations for keeping servers running at peak efficiency. Much like wizards DBAs, are subtle and quick to anger when you try and meddle with their servers. SharePoint can be a little frustrating for these highly trained professionals because it requires a little special treatment and may break some of those unwritten rules.So let’s talk about some of those special needs.

SharePoint should have its own SQL server instance.

Most DBAs don’t argue against this in theory,  but in practice they don’t have a budget any larger than the rest of us. Economic concerns have made it a common practice to put as many databases as possible on a single instance, because each instance has a certain amount of overhead.Enter reason number one why SharePoint is a special case; it’s called the “Maxium Degree of Parallelism” or the MAXDOP option.The MAXDOP setting limits the number of processors that are used for the execution of a query with a parallel execution plan. In a system like SharePoint that tends to have a large number of queries, relative to the number of processors, running at the same time, a low MAXDOP setting is desirable. Essentially the value of 1 suppresses parallel plan generation making each query stay on the processor it starts with.  This SQL setting has been recommended with SharePoint for sometime now. In SharePoint 2013 it has gone from being a recommendation to a requirement. The MAXDOP setting can have a very negative impact on database queries that assume they will be allowed to run in parallel, so if they are hosted on the same instance as SharePoint performance will suffer.    If the MAXDOP setting isn’t a good reason for isolating SharePoint databases, then consider the fact that a SharePoint Server 2013 installation by default (basically just clicking through all the wizard options) creates 20 databases. These databases can be quite demanding on a server, and if they have to share with unrelated SQL data for resources in the same instance everything suffers.

SharePoint Databases Don’t Want Your Help

The rules are a little slippery in a few cases, but here is the general advice: Don’t run any SQL commands that query or manipulate data directly against SharePoint databases. That’s it. Don’t reorg, don’t reindex, don’t SHRINK DB’s, don’t do all the stuff normally associated with DB maintenance. Backups and DB checks are as far as you should go.

This goes double for changing the default permissions of the SQL service and admin accounts. Don’t take the db_creator and Security admin roles away from the account used to install SharePoint, don’t make the DBA’s account owner of all the databases and take the service accounts out of db_owner. DB checks and back it up, then walk away.

Finally, if your DBA’s have questions about the db types in SharePoint, how fast they grow, what recovery model they should use, all that stuff, then direct them to this link http://technet.microsoft.com/en-us/library/cc678868.aspx . This technet article is full of details they will hopefully find useful.

Remember SQL does all the hard work so SharePoint can make everything else look easy. It is a big part of your SharePoint equation so treat it right.