Generic best practices for SQL Server 2008 R2

Question

I'm trying to find some good references for best practices for planning and configuring an MS-SQL 2008 R2 server.

I'm thinking in the lines of storage configuration, file locations, recommended application or user separation, common pitfalls etc.

Answer 1

http://msdn.microsoft.com/en-us/sqlserver/bb671432.aspx is worth a look.

Answer 2

I think SQLCat had a whitepaper on MSSQL 2008 R2 Best Practices.

Also Microsoft has the Best Practice Analyzer (BPA) for MS-SQL 2008 R2.

Answer 3

There's a few things from my experiences that I'll mention.

File Locations

You should try to split your data, log, and temp files up as well as keeping them away from the OS. Keeping them away from the OS is to improve performance as well as make sure that if your databases end up filling the drives, it only causes a problem for SQL and not the entire server.

The data files should reside on a RAID 5 setup if you'll be doing mostly reads or a RAID 1/RAID 10 setup if you'll be doing a large mix of reading and writing. Our data warehouses which are mostly read are set up on RAID 5 so we could squeeze the most space out of the drives. We have a server that runs OLTP databases that uses RAID 1 for the data files for the performance gain. Remember, RAID 5 pays a hefty penalty for write operations. The downside of RAID 1 or RAID 10 is that it ends up costing a lot more to get the space you need.

Log files are very read/write based so try to put these on RAID 1/RAID 10. I would strongly suggest against RAID 5 for the log files and would only put them on one if money really stopped you from doing anything else.

TempDB also does a lot of writes so we put ours on RAID 1. I'd say this is the most important database to keep on it's own. We had problems in the past when our TempDB was growing too much and filling up the OS drives. Unless you have a very good handle on your TempDB usage you'll definitely want it to be separated.

The system databases (master, msdb, model) are pretty safe anywhere. I usually just leave these in the default SQL install directory.

Security

The Windows/mixed mode authentication is dependent on your situation. Microsoft recommends Windows authentication if you can get by with just that.

For the service accounts, we generally create a generic account to run SQL for us. This account does not need to be an administrator. SQL Server will add whichever account you choose to run the services to groups that are created during the installation. Do not use one of the employees accounts to run the services. If that employee ever leaves and their account gets disabled SQL will not be able to run.

If you're using mixed mode authentication, make sure you use a strong password for SA. I end up disabling the SA account after the installation as I don't like generic admin accounts but this is up to you.

Microsoft has a page for Security Considerations for a SQL Server Installation. I recommend you read that as well to get a good idea of a few other things to look out for.

Services

Only install the services you need. Determine if you'll need to install Analysis Services, Reporting Services, or Integration Services before doing the install and make sure you don't install anything you don't need. It's easy to install components you missed later and this way you won't have any services unnecessarily taking up resources on your machine.

The only service I would say is a pretty safe bet is Integration Services. One of the big uses for it is the maintenance plan tools it provides. Unless I'm very sure that I won't be making use of SSIS maintenance plans I always tend to install Integration Services.

Outside of those things, there's not a lot of other decisions to be made during the installation. Things like the collation should be left alone unless you know what you're doing and have a good reason to go outside of the defaults. The above 3 things are what I consider whenever I've had to do a new SQL Server installation.