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: 43% [?]