Which settings should be thoroughly evaluated in SQL Server?

Question

I am not a DBA by any means, but being a web developer means that I will have to install, setup, and administer databases. In the past, I have just followed the default installation for SQL Server. Over time, I have grown smarter and learned that default installations almost always leave doors open and leak.

So...What are the critical settings that should be thoroughly evaluated when installing SQL Server 2005 for someone of my caliber? or 2008? Or is every setting "critical?"

Any good resources that will guide through a "proper" setup of SQL Server 2005?

Answer 1

Every setting is critical - but the things I always double check are:

1. Authentication type - Integrated or SQL? If SQL, make sure you put in a very strong sa password.

2. Service Accounts - think through what you want for the accounts. I generally create a domain user for each service separately, and run them with least priviledges.

3. database paths. Decide up front where you want your system databases & user databases - its much easier to do that during the install than deciding after the fact.

Answer 2

Though this has mostly gone away with 64-bit, I always make sure that "AWE" is enabled if SQL is the only thing running on the server and it's 32-bit. This way, SQL Server has access to all the memory the server has (up to 3GB in a 4GB server) instead of being restricted to the normal 2GB.

I second the opinion that they're all important, and they all have different purposes, so it's a matter of your environment.

Answer 3

Most settings can be accepted with the default settings. SQL 2005 has been changed to be more secure by default.

Be sure to do the steps to prepare accounts to use.

You might also look at your server hardware and try to separate OS, SQL Binaries, Data and Logs onto separate drives.

http://msdn.microsoft.com/en-us/library/ms143516.aspx - for all the juicy details.

Post install, you should set up dbmail and then set up maintenance plans that notify you on failure of backups or maintenance.

Answer 4

The Microsoft documentation in Books on Line and elsewhere is pretty thorough and accurate. I think it's the best documentation that Microsoft does. So I'd be rigorous about "RTM" before anything I would write here, which would be incomplete and inadequately explained in any case by comparison.

That said, the first priority should be Do No Harm. I've seen many more cases where imperfect understanding of the consequences of changing the settings has caused problems, than that the default settings need to be altered. (Note: This advice does not apply to MySQL, whose defaults are pretty random IMHO.)

Answer 5

Drive configuration! Where you'll place your log and database files is of absolutely paramount importance in terms of performance

Answer 6

My number one setting to check is the server's collation. This will control the collation of the system databases, and you should ensure that the new server's collation is the same as the old one to avoid collation errors. Ideally your user database will be the same collation.

If the two collations are different, and you compare varchar data from TempDB to varchar data from your user database, you'll need to specify the collation.

Just about every other setting can be changed later, but server collation is one that you're stuck with for keeps. (Technically, you can rebuild the system databases with a new collation, but it's not worth the effort, and still requires you to run Setup again).

Answer 7

One that's always forgotten that goes hand-in-hand with putting tempdb/ldf and mdf's on separate disks is making sure windows instant file initialization is setup for the SQL Server service account. This way, expanding/creating space inside data files happens right away.