SQL Server Installation and Setup Best Practices

Installing SQL Server, especially on standalone servers, is a relatively easy process. However, efficiently installing SQL Server, is a whole different story. Via this article, I will be sharing with you, some useful tips regarding SQL Server Installation and Setup Best Practices. The list of best practices presented in this article, is not exhaustive, but I believe, they help a lot, towards efficiently installing a new SQL Server instance.

 

Planning/Preparation

The first thing to do for installing a new SQL Server instance, is not the installation process itself, but rather perform the proper planning.

To this end, you need to make sure that you are allocating an adequate amount of CPUs and RAM, based on the estimated workload to be processed by SQL Server.

Also, regarding storage, please check the below recommendations.

• Provision of adequate disk space
• Best Practice: Make sure that you plan for the right disk capacity for your data, logs, backups and tempdb files.
• Proper allocation unit size for drives
• Best Practice: Usually, a 64KB allocation unit size is used for data, logs, and tempdb file drives.
• Benchmark drives with diskspd
• https://github.com/microsoft/diskspd
• Using the above tool, you can get useful performance info about the storage/disks you will be allocating for SQL Server, and thus check if the available throughput will be adequate for your SQL Server instance’s performance needs.

Installation Process – Feature Selection

The first step when you begin the SQL Server installation process, is the feature selection. This is the process, where among other, you select which features of SQL Server to install.

Below, I list the sub-steps of this process, along with providing the best practices recommendations.

• Instance features
• Database Engine Services, Replication, Full-Text Search, etc.
• Shared Features
• Client Tools Connectivity, Integration Services, etc.
• Paths
• Instance root directory, Shared feature directory, Shared feature directory (x86)

• Best Practices:
• Install only the features you need – C drive can be used for the above paths.

 Installation Process – Instance Configuration

The nest step in the installation process, is the instance configuration options. In this dialog, you are presented with 2 options:

• Option 1: Default Instance
• You connect to SQL Server, by only specifying the server name or IP (i.e. server1)
• Option 2: Named Instance
• You connect to SQL Server by specifying the server name or IP, along with the instance name (i.e. server1/instance1)

• Best Practice:
• If you will just be using one instance on the server, you can go with the default instance option, otherwise, use named instances.

Installation Process – Server Configuration

The next step, is to select the server configuration options. To this end, in this step, you will have to set up the service accounts as per below.

Service Accounts

• SQL Server Agent
• SQL Server Database Engine
• SQL Server Browser

• Best practices:
• Use domain users (not admin) account for Database Engine and Agent. If not on a domain, use local windows accounts as service accounts.
• Use local service for Browser service.

The “Perform Volume Maintenance Task” Privilege

Another option you are presented with in this step, is to grant the “Perform Volume Maintenance Task” privilege to SQL Server Database Engine Service. This allows using the “Database Instant File Initialization” option which allows faster database creation.

• Potential security risk

• Possibility of someone hacking into your server and possibly read the non-zeroed memory spaces and retrieve that data

• Best practice:
• To be used only when SQL Server will be hosting large databases and only in the case you took adequate mitigation actions for the above security risk.

 

Installation Process – Database Engine Configuration

Next, you are presented with the Database Engine configuration options. This configuration step, contains multiple, critical configuration sub-steps which are presented below, along with the relevant best practices.

• Server Configuration
• Authentication Mode
• Windows Authentication Mode
• This option, allows only Windows/Active Directory users to connect to the SQL Server instance.
• Mixed Mode (SQL Server authentication and Windows authentication)
• This option, besides allowing Windows/Active Directory users to connect to the SQL Server instance, it also allows creating local SQL Server users with a username/password, for connecting to the SQL Server instance.
• Best Practice:
• The best practice is to use Windows Authentication (given that the applications to be connecting to SQL Server, work with this option).
• Specify SQL Server Administrators
• You can add current user and/or group or other users.
• Critical: Don’t lock yourself out – make sure that you add at least one a SQL Server administrator.
• Data Directories
• Data root directory
• System database directory (read only – inherits from the data root directory)
• Never use the OS drive
• User database directory, User database log directory, Backup directory

• Best Practices for Data Directories:
• Use separate physical disk pools for database data and log files
• Use separate physical disk pools for tempdb data and log files

• TempDB
• For data files
• Number of files
• Initial size (MB)
• Autogrowth (MB)
• Data directories

• Best Practices for TempDB Data Files
• Use the same number of tempdb files as the number of logical processors up to 8 logical CPUs (if more than 8, don’t add more unless you observe contention).

• For log files
• Initial size (MB)
• Autogrowth (MB)
• Log directory

• Other Best Practices for TempDB:
• Use 2 dedicated disks for TempDB data and log files for parallelism

 

• MaxDOP
• Maximum degree of parallelism
• Distributes a SQL Server request for parallel execution against the available logical CPUs
• SQL Server installation wizard (since the 2016 version), recommends the MaxDOP value to be based on the available NUMA nodes on the server (formula)
• Best Practices:
• Usually the recommended MaxDOP value is OK.
• If you add more NUMA nodes in the future, you might need to revise this value.
• Memory (RAM)
• Min
• Max
• Recommendation is automatically given based on available RAM resources
• Best Practices:
• Make sure you leave an adequate amount of RAM for the OS.
• If you add more RAM in the future, you will need to revise this value.
• FILESTREAM
• Use only if required.

 

Post-Installation Initial Setup

Below, I’m also providing some basic, initial post-installation steps for SQL Server:

• Configure the “Model” System Database
• Recovery Mode
• Initial size and autogrowth settings for data and log files
• … (any other settings you want to include for your new databases that you create on the instance)
• Note: The Model system database serves exactly what its name implies: is the template for every new user database that is created on the SQL Server instance. To this end, whatever settings this database has, are inherited by default to each new database that is created.
• Enable Failed Login Auditing in the SQL Server Security Settings (if not enabled)
• Enable TCP/IP and change the default port from 1433 to something else (range you can use: 49152–65535)
• Read the MS Docs article for SQL Server Security and take the relevant actions