SQL Server – What Every SharePoint Administrator Should Know – Part 1

March 15 2010 15 comments

During the time I have been working with SharePoint, I have noticed that there are quite a lot of admins that don’t know much about one of the most important part of any SharePoint-farm – the database.

My purpose is to clarify some of the very basics of SQL Server running in SharePoint-farms. These writings will cover subjects such as defining a backup and restore strategies, as well as some other database maintenance routines.

Content is in Database
First thing that everybody should know, is that SharePoint saves all content (configurations, sites, pages, documents, list-items etc.) to a database. This means that if the database-server is misconfigured, performance will suffer.

A SharePoint solution consists of web applications which are split into site collections.  There can be several site collections in one content database, but one site collection is always stored into just one database.  When you are planning your SharePoint system, you have to estimate how much data you are going to store per site collection, and use that information when defining how many content databases you are going to need.

Keep them under 100GB
There is one big difference with SharePoint compared to other systems that you should know; a recommendation is that one content database should be under 100GB.  This is due mainly to manageability reasons like backup & restore. You can have hundreds of them, so feel free to create them as many you need.

Word of Warning
I have seen a content database that was 450GB, which was running just fine in everyday operations. However, it is almost certain that serious problems will occur at latest when upgrading that kind of  system with cumulative updates or service packs. Most likely upgrade-process would fail, which means that in worst case your whole farm could be in an inconsistent state, and you would have to recover using different farm. I you have big content databases, it is better to split them before it is too late.

Content Database – don’t touch
Microsoft has one very easy rule concerning SharePoint 2007 databases; dont’ touch them. Principally nothing is supported, not even plain SELECT-queries. They are still normal databases, so nothing of course prevents you from manipulating them in the way you wan’t, but MS won’t help you in the case of trouble.

Database Naming Conventions
I want to remark the importance of naming conventions. Please use them. Nothing is more annoying than trying to figure out which database belongs to which site collection. Names like WSS_Content and WSS_Content_1 doesn’t help much when you have several web-applications with tens of site collections running in your farm. This becomes especially important if you share your SQL Server with other applications.

SQL Server in General

Resources
SQL Server can be very resource intensive, and thus it should not be installed on just any hardware available. This is also why SQL Server shouldn’t be installed on the same machine as other server products.

Check SQL Server hardware and software requirements , but remember that minimum requirements are way too underestimated for SharePoint environments.

Memory
Memory is perhaps the most important part. SQL Server uses memory to store data in memory once it has read it from the disk. This will ensure the best possible performance, as next read to the same data is done directly from memory instead of relatively slow disks. Updates to data will also happen first in memory, because usually there is no need to write data back to disk synchronously. An active database might be read totally into memory, so SQL Server can really use a lot of memory if it is available.

Drawback of this is that if there is not enough memory in your server, swapping to disk will occur, and that is something to be avoided at all costs.

You can finetune how SQL Server uses memory, but memory settings coming by default are well enough in most cases. SQL Server determines by itself how much memory it will use in each situation, and quite often it uses all that is available, which is perfectly normal. Perhaps an advice for memory settings? If you don’t know what you are doing, your best choice is to do nothing.

Memory should be the last thing to save when investing to SQL Server. A good rule of thumb: It is often more expensive to discuss the amount of RAM than to just acquire it. Minimum amount of memory in SharePoint-environment is 4GB, but 8GB or 16GB is far better even in small environments. Of course the total amount has to be decided according to size and usage of your databases.

Disks
A database consists of two types of files, the data-files (mdb) and log-files (ldf).  Data-files are used to store the data itself, as the log-files holds the data that has been updated. More information about these can be found from article Files and Filegroups Architecture. Usage and importance of these files will be covered in more detail later with backup strategies.

If the amount of memory was important, the placing of these files should be taken as seriously. There usually are several physical disks (local or from SAN) in your database system, and using them correctly can significantly give a big boost to performance.  If possible, configure disk arrays to RAID 5 and/or RAID 10 to get the possibility to stripe databases and log files across different disks. With RAID-arrays you also take one step towards a high-availability system.

Placing of data and log-files will be covered in more detail in Part 2.

Processor
CPU is very rarely a problem in database servers dedicated only to SharePoint farms, and usually a basic dual-processor server is enough. The more cores the better. SQL Server minimum requirements though are quite underestimated in most cases.

Instances?
If you have an existing SQL Server in your organization, you can use it, as MOSS doesn’t require a dedicated server. However, it would be a good practice to separate MOSS databases to a different database instance. Some administrators are afraid of instances, but there is no need for that. Instances are nothing more than isolated SQL Server processes sharing the same hardware platform without possibility to interfere each other. There is one thing to take care of though; don’t let one instance to take all your server-resources. Anyway, instances will help database maintenance operations, because everything concerning your farm can be handled separately.

Virtualization
Virtualization is supported nowadays in SharePoint-farms, but don’t just rush into it. Start with web front ends, then with application servers. Finally, and only if absolutely necessary, virtualize the databases, and even then you should use pass-through disks to avoid IO-bottleneck. Here is a good article about Virtualizing a SharePoint-farm. http://blogs.msdn.com/uksharepoint/archive/2009/03/08/virtualizing-sharepoint-series-recommendations-for-each-server-role-in-the-virtualized-sharepoint-environment.aspx

SharePoint 2010
The upcoming SharePoint 2010 will raise the need of proper database administration, as the new Shared Service Application -model grows amount of system databases. This means that you will have many more databases to look after. A very good thing is that SP 2010 is promised to have many built in improvements as well as support for different SQL Server features. Finally you can have more control over your content databases, and these will be covered later when SP 2010 is released.

Popularity: 5% [?]

15 comments to “SQL Server – What Every SharePoint Administrator Should Know – Part 1”

  1. [...] SQL Server – What Every SharePoint Administrator Should Know – Part 1 (SharePoint Blues)During the time I have been working with SharePoint, I have noticed that there are quite a lot of admins that don’t know much about one of the most important part of any SharePoint-farm – the database. My purpose is to clarify some of the very basics of SQL Server running in SharePoint-farms. These writings will cover subjects such as defining a backup and restore strategies, as well as some other database maintenance routines. [...]

  2. UMESH says:

    Hi,
    Its really good for developer they might get these kind of problem Some time if u dont aware then these are big issues

  3. Anil says:

    Good post and nicely elaborated..!!

  4. sandeep says:

    realy good one very helpfull..!!

  5. MoLandtech says:

    Great Post; each category is well articulated. Thanks for taking the timeout to explain this very basic, but reallly important aspect of SharePoint farm administration. The fact is, far too many of us [SharePoint Admins] due to organization/colleagues pressure tends to depend too much on SQL Database Admin for the day to day management of SQL; and as a result, we loose the most important aspect of what we’re responsible for.

  6. Dinesh says:

    Really good info, it was very helpful! Thank you!!

  7. LongHairedWeirdo says:

    Just a tad bit of extra information about memory…

    SQL makes all changes to data in memory. It never works directly on disk. So, the idea is to have enough memory to keep enough data in memory so that it can be updated and then written to disk at leisure. What’s “at leisure”?

    Well, there’s a counter in the Buffer Manager portion of PerfMon called Page Life Expectancy. It measures how long, in seconds, an average page (8kb of the database) can sit in memory without SQL deciding that it has something better to put in memory (i.e., flushing it to disk, and marking it as ‘free’).

    They used to suggest 300-900 was good. That number was formulated when maximum memory was about 2 gigs, so now, people should try to keep Page Life Expectancy at about 300 for every 2-4 gigs of memory. That way, SQL and your IO system has about 5 minutes to allow every 2-4 gigs of changes to be flushed to the disk, and so your disks are never overloaded under normal processing.

    Don’t worry about deep valleys in Page Life Expectancy – if it drops to nearly 0 for a minute or two, and then recovers to a reasonable figure, that’s okay. That just means SQL had to work on a boatload of data for a while, and then was done working on it. But if you can’t keep it up nice and high Page Life Expectancy, you want to see what’s eating up your memory.

    One interesting note: on 64 bit SQL Server, always set Max Server Memory. More memory is usually better, but 64 bit SQL can end up soaking up so much memory that the OS is a bit starved, and that can cause problems. So, cut back max server memory to leave at least a gigabyte of free memory on the server if you can. (Check that under Memory – Megabytes Free in Perfmon). It sounds wasteful, but SQL sometimes needs a big chunk of memory and if Windows has a spare gigabyte, that’s enough to feed most such memory needs without having to start writing a bunch of stuff to the page file.

  8. Thank you for all your valuable effort on this website. Debby really loves carrying out research and it’s really simple to grasp why. A lot of people notice all of the lively medium you produce worthwhile things through your website and in addition improve response from other ones on the point while my child is undoubtedly becoming educated so much. Take advantage of the remaining portion of the new year. You are conducting a brilliant job.

  9. [...] Sharepoint and SQl -> What Every SharePoint Administrator Should Know -> Default database created by Sharepoint 2010 – [...]

  10. Arturo says:

    As the admin of this site is working, no hesitation very rapidly it will
    be well-known, due to its quality contents.

  11. Men feel they own some form of shortcoming if they do not have elongated penises. This is yet understandable because they make this as the main point of their masculinity. With all those male models exhibiting larger and longer penis, progressively more males out there feel anxiety overwhelming them.

  12. CarliAZenz says:

    We have been exploring for a bit for just about any high-quality articles or weblog posts in this particular sort of space .
    Exploring in Yahoo I eventually discovered this website.

    Reading this information So i am happy to convey that I’ve a remarkably excellent uncanny feeling I learned exactly things i needed.
    I a lot undoubtedly will make sure to don?t put away from your mind this
    website and supplies it a glance regularly.

    my web site CarliAZenz

  13. Lara says:

    Unquestionably imagine that that you stated. Your favorite reason appeared to
    be on the net the simplest thing to remember of.
    I say to you, I certainly get annoyed while folks
    consider issues that they plainly don’t realize about.
    You managed to hit the nail upon the top as smartly as defined out the entire thing
    without having side effect , other people could take a signal.

    Will likely be back to get more. Thank you

  14. Lupe says:

    Truly when someone doesn’t understand afterward its up to
    other visitors that they will help, so here it takes place.

Leave a Reply