December 18, 2011 Leave a comment
When installing Microsoft SQL Server, there are a lot of options and settings to think about. For these situations I always have a shortlist of options i need to think of. In this blog post I will try to explain why these options are important, and the reason of my choice.
If you are picking out your SQL Server hardware, consider the diverse options of hardware and types of storage. In some cases you’re on a budget, and hardware isn’t your main concern. In that case you don’t want to follow this by heart, but take some shortcuts to save some money on your budget.
In my case, I always like to see the following disk separation:
2) SQL Server Binary (Server install)
3) Database files (.MDF)
4) Log files (.LDF)
5) Temp DB
6) Backup- / CSV- / Text-Files for import, storage, etc (this one is optional, so only if you really need it)
Why the separate disks? Simple: performance! If you want to have a good performing SQL Server, give it the best hardware settings you can get away with. Especially with the “classic” disks (spinning disks, instead of SSD disks), I/O is 90% of the bottleneck in normal cases.
So why separate the MDF and LDF files? Just to contain the I/O actions to one single disk. If you are executing a big update statement, it saves the data and log to different disks, preventing I/O issues. By separating the disks you get the best performance you can get.
Keep in mind, use separate disks and NOT different partitions! Partitions are virtual disks across physical disks, and can be a first issue in performance drops!
If the installations asks you to choose between default and named instance, always choose named instance. This ensures that your SQL Server is always reachable by name, even if you install 2 or more different versions of SQL Server on one machine. In practice, I’ve seen multiple versions of SQL Server on one machine, and none of these instances could be reached. This was because both version used the same default TCP/IP port 1433 as default. So I always use [Machine]\SQL[Version] as instance name (example: (local)\SQL2008).
If you’re asked about authentication (NT only or mixed mode), I always choose mixed mode. This gives you the ability to use SQL Server logins next to Windows NT users (domain users). This way, your flexible to use NT users or SQL Server logins. In practice you will see that SQL Server logins are often used in applications that only need (read-)permissions on one SQL Server. NT users are mostly used when an application needs permissions on multiple machines across the domain / network.
During your server installation, there are a few more things you need to check, so that performance is (potentially) increased. One of these steps is making sure that newly created databases are stored in the correct location. You can do this in the installation steps, when you are specifically asked for it. Make sure that all default paths are set correctly, to prevent wrongly placed databases or logs.
There are several things in or on the OS you can tweak for more performance, but one of the most important (which people easily forget) is to configure your anti-virus. The software needs to skip MDF, LDF and NDF (Secondary Data Files) as it scans your system. This can be a little performance booster, and can be a pain if you do it after putting your server into production.
This is a short explanation of common practice I’ve seen working with SQL Server for multiple companies. In most cases the common practice is determined by the function of the SQL Server and the budget. So taking a shortcut here and there doesn’t need to be bad. But always keep in mind what the intended function of the machine is, and the load the machine has to keep up with. Putting everything on 2 disks isn’t that bad for a test machine, but if you put everything on 2 disks for a financial production system you’re doing something wrong!