11

Question ported from Serverfault as it is specific to database maintenance for SharePoint administrators:

For SharePoint administrators, SQL Server is often a black box that is installed as a prerequisite during the SharePoint installation process. As a result, there is very little (if any) planning that goes into the SQL side of the installation, leading to problems that surface somewhere further upstream.

  • Transaction Logs that fill drives
  • No maintenance plans (or uninformed ones, such as plans that both reorganize and rebuild the indexes)
  • Unmanaged autogrowth
  • Databases and logs on the same spindles
  • Poorly chosen RAID levels
  • No backup (or recovery plan)

So... what types of problems do SharePoint administrators without any SQL/DBA experience tend to hit, and what resources would best help an "accidental DBA" get up to speed on SQL planning, administration and performance tuning basics?

Improve this question

3 Answers 3

Reset to default
9

The TechNet Article "Storage and SQL Server capacity planning and configuration (SharePoint Server 2010)" should be your first port of call:

http://technet.microsoft.com/en-us/library/cc298801.aspx

Improve this answer
5

Something to be aware of along with SQL aliasing is the SQL instance. There are instances where if patching hasn't been applied, features and services within SharePoint will not configure properly on named instances. This you can also bypass using SQL aliasing.

Personally, I am a proponent of SQL Aliasing, but you should always make sure that you configure it correctly and that if the alias needs to be updated that it is made on each server in the farm.

Another thing to look out for is alternate ports. These are used often, especially for external facing sites for "security" but there are so many other issues that can arise from this not being configured properly at both ends as well.

Improve this answer
3

I would like to add a biggie to your list of common problems admins hit: The need to move the SharePoint farm to a different database server. SharePoint 2007 (not sure about 2010) does not like it when you change database servers.

There are numerous articles out there about how to move your farm to a new server, but the big gotcha in doing it is that you need to configure the permissions properly for all of the SharePoint service accounts in the new database server. If you don't you will spend hours beating your head against either the ever so helpful 'permission denied' or 'login failed' errors.

If you are setting up a new farm, be sure to set it up using a SQL Alias as the name of the database server. It will make life a lot easier down the road. the instructions for this are in the link that James Love posted above.

Improve this answer
2
  • 3
    Good hint here. 3 rules of this basically are: plan, plan, plan. If you're fixing a mess that wasn't planned: scrap it, plan and start again. Don't get into analysis paralysis, but it can probably be cheaper to plan + migrate content rather than fix a horrendous 800GB content database that you've moved to another server and can't attach to a site collection.
    – James Love
    Commented Apr 8, 2011 at 20:09
  • I could not agree more. Creating a new farm based on a proper plan will lead to a far more stable, secure and maintainable SharePoint environment. It's a little extra work up front but pays for itself 100 times over later on.
    – Dave Wise
    Commented Apr 8, 2011 at 22:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.