This is part two of a four-part series on Microsoft SQL server deployment. Click here to view part one.

Operating System Deployment

Now that we have the prerequisites out of the way, next up is Operating System (OS) deployment and configuring the base system to run SQL Server.

OS Drive

Modern versions of Windows consume a lot of space on the system drive over time. Make sure your OS drive is set to 100GB – 128GB, even with Windows Core installations, and don’t install the SQL Server binaries on the OS Drive. If you’re deploying on a virtualization platform, you can thin provision the OS drive,  known as dynamically expanding disk.

Configure Storage Targets for SQL Server

As noted above, keep your OS separate from everything else. In addition, spread the SQL server files across multiple logical drives for best performance. In particular, make sure you keep database files (*.mdf and *.ndf files) and log files (*.ldf files) on separate logical drives. Database files experience predominantly random read/write accesses, while log files show almost exclusively sequential writes.

Make sure these logical drives are actually based on Physical Disks – meaning what the OS recognizes as physical disks (i.e. separate LUNs on a SAN; separate vhdx or vmdk files on Hyper-V/VMware), as opposed to partitions within a disk. Create separate logical drives for:

• SQL Server binaries.
• SQL Server database files. Consider dedicated separate drives for high-transaction volume databases.
• SQL Server log files. Consider dedicated separate drives for high-transaction volume databases.
• TempDB database files.

If you’re performing backups using SQL Server, also provision a separate logical drive for backups.

Here’s an example of what this could look like (substitute your preference for drive letters):

• Logical Drive C: Windows Operating System.
• Logical Drive D: SQL Server database files.
• Logical Drive L: SQL Server Log files.
• Logical Drive T: SQL Server TempDB database files.
• Logical Drive X: SQL Server binaries.
• Logical Drive Y: SQL backups.

When deploying on virtualization platforms, make sure the data drives  are fixed size, in the example above this would be drives D:, L:, and T:. You do not want the SQL server to wait for the hypervisor to complete an expand operation of a dynamic disk.

File Allocation Size

For all drives containing database or log files, ensure that the drives are formatted with an allocation size of 64KB. SQL server stores data in “pages”, which are 8KB in size. I/O operations are performed using “extents”, which are groups of eight data pages, which calculates to 64KB. So, formatting the drive using 64KB blocks reduces the number of necessary I/O operations, which in turn increases performance.

In addition, when using physical storage arrays, make sure the partition alignment is set correctly. Most modern storage systems do this automatically now, and if you’re in a virtualized environment your storage admin is probably all over this topic anyway. But it’s always a good idea to verify. Describing the technical details around this topic would be far out of scope in the context of this post, considering the many different storage arrays and disk sizes/types, but you can find a wealth of information in Microsoft’s technical documentation and on other sites.

And just to reiterate, if you’re using any kind of SAN storage make sure you have multiple paths defined.

Windows Page File

SQL server does most of its work in memory, there are no special settings necessary for the page file.

Set Anti-virus Exclusions

If you are running anti-malware software that is not “SQL aware”, make sure you define the appropriate file and process exclusions. Look here.

SQL Server Deployment

The settings described in this section are all defined in the SQL Server GUI installation dialogs. Make sure you change these settings during the GUI setup, otherwise you’ll have to move database files and change settings afterwards.

Service Accounts

Use separate dedicated accounts as service accounts. While SQL server will run very happily using the NTSERVICE accounts, you cannot access external services that require authentication, such as sending emails, with these. In addition, with HA architectures you need to use the same account on all instances, which means you need to use Active Directory and AD domain accounts.

There is no need to change permissions or group memberships for the service accounts prior to installation – SQL setup will take care of that.

Make sure you grant the “Perform Volume Management Maintenance Tasks” right to the SQL Server Database Engine account by hitting the checkbox at the bottom of the window. This enables instant file initialization, which (exponentially!) reduces the time autogrow operations or database file creation take.

Data Directories Tab

Set all your file paths as discussed in the section “Configure Storage Targets” above.

TempDB Tab

The rule of thumb used to be to have one TempDB database file per CPU core.

“SQL Server creates a logical scheduler for each of the CPUs presented to it (logical or physical). The reason for the multi-file TEMPDB recommendation is to allow each of the logical schedulers to loosely align with a file. Since you can only have 1 active worker per scheduler this allows each worker to have its own tempdb file (at that instant) and avoid allocation contention”.¹

However, various expert level sources have found that there does not seem to be any tangible benefit beyond 8 files. We recommend configuring one TempDB file per processor core up to a maximum of 8 files and not adding more unless there is contention. Note that from SQL Server 2017 on, the installer does this automatically.

Also, don’t rely on autogrow for the tempdb files. If these are created at a small initial size and allowed to grow, then each individual database file will fragment on the disk as opposed to occupying one contiguous chunk. Disable autogrow and set the tempdb files to their final size. 10GB should be fine per tempdb file, but of course you should always monitor the usage.

Stay tuned for part three which goes over SQL Server post-deployment.

As always, we’d love to hear your questions and comments in the Cireson Community.

¹Source: SQL Server TempDB – Number of Files – The Raw Truth – Microsoft Tech Community