SQL Server Install Guide Part 1

In part 1 of a series on installing SQL Server, I’m going to discuss things to do prior to running the installation program for SQL Server.  There are a number of install guides for SQL Server on the internet and plenty of recommendations around installation best practices and how-to’s.  This will be what I’ve arrived at over the course of time.

Working with the Database Migration Assistant

This first step I’m going to introduce makes the assumption that this install is going to be for one or more databases that you’re planning to migrate from an existing instance.  My first step is to run the Database Migration Assistant from Microsoft.  This product is free and can be installed away from your SQL Servers.  I run my install from my laptop.  After opening the UI and starting an assessment, this tool will scan the source databases selected and determine if there are any breaking changes, behavior changes, or deprecated features between the source and target for your migration.

The Database Migration Assistant will also do two additional things in regards to migrations.  First, it will make suggestions on new features that may be advisable to use in the new instance.  Second, the software can also actually do the database migration for you.  That includes migration to a recent SQL Server version on premises and migration to Azure.

Naming Your SQL Server

The next item on my list is a relatively easy one to handle, but it’s still worth at least some forethought.  The database instance needs a name.  For a default instance, the computer name will be the instance name.  Which means that in some cases your name is decided for you by a System Administrator.  If you have input into the name, try to use a logical naming convention that will have some longevity.  In some environments you might have a convention like “Application” + “Purpose” + “Location” + “some number” such that a machine name might be CitrixWebSTL01 for a Citrix web server in Saint Louis.  I would recommend something similar to this so that when the machine name is viewed the person knows exactly what it is for.  Going generic, like SQL01, SQL02, SQL03, etc. just doesn’t say much about what the SQL instance is for.

Creating Service Accounts for SQL Server

The third item on my install guide is to identify or create accounts for the SQL Server modules you plan to install.  For instance, if you’re installing the database engine, utilizing SQL Agent and maybe installing SSIS on the box, then you really should use three different accounts with different passwords, one for each service that will be installed.  Doing this will help keep the machine and the environment more secure.  If you use one account for every service and that one account is used on every SQL install, you’ve now made your entire environment vulnerable if that username and password is ever compromised.

Standardize The Drive Letters

Fourth, identify and agree upon drive letters for files.  If the machine is physical and you can have some influence over what is used, be sure to separate mdf from ldf files and be sure to get TempDb on its own drive.  Doing these things will help overall performance as each of these file types has its own usage pattern.  Mixing those files types on the same drive is going to hurt performance.

If the environment is virtual and so a SAN is involved, I would still encourage you to have a scheme for where the files go because it makes administration easier. Also, having a consistent design across all servers helps when it comes time to restore databases from one place to another.  No more pesky WITH MOVE statements in your RESTORE DATABASE scrips if across the environment there is a consistent way drives and files are laid out.

Consider Enabling Instant File Initialization

Enabling instant file initialization is the fifth item on my SQL Server install guide.  Instant file initialization is a performance enhancing option that will really help with data file growth times and restore times.  It allows SQL Server to claim space on a drive without zeroing out the data that might be on that part of the drive.  This means that the data is still retrievable by a skilled hacker until SQL Server writes over the data. However, there are many layers of security to be bypassed before that would be possible and there are substantial benefits to instant file initialization. Check out a post by Kimberly Tripp on the subject.  Instant File initialization is a multi step process prior to SQL Server 2016, but now we have a handy check box.

Next Steps To Take

1. Download and use the Database Migration Assistant to assess your SQL Server.
2. If you don’t know what the naming conventions are for computers in your environment, talk to one or more SysAdmins and find out.
3. Do some research on instant file initialization.
4. If you would like help with anything in this post, or with something else related to SQL Server, reach out to me here, or on Twitter, and I’ll be glad to offer assistance.