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.
Also, if you are looking for additional knowledge on how to efficiently work with SQL Server, you might want to check my online courses “SQL Server Fundamentals (SQL Database for Beginners)” and “Essential SQL Server Administration Tips“. Both courses contain useful lectures and many live guides, that will help you learn how to efficienly perform a variety of tasks in SQL Server. This first course is for beginners, and the second course, covers many intermediate to advanced SQL Server administration topics.
I’ providing these best practices, following step-by-step the dialogs presented by SQL Server’s installation wizard (i.e. SQL Server 2019).
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.
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.
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.
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.
Strengthen your SQL Server Database Administration skills – Enroll to our Online Course!
Via this course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more.
(Lifetime Access, Certificate of Completion, Downloadable Resources, and more!)
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
Watch the Video Lecture – SQL Server Installation and Setup Best Practices
Check my video lecture, in which I talk about the SQL Server Installation and Setup Best Practices. Among other, you will learn how to efficiently perform a SQL Server installation.
Via this course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more.
(Lifetime Access, Certificate of Completion and more)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.com}. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.
Views:13,258
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit "Cookie Settings" to provide a controlled consent. Read More
This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
Cookie
Duration
Description
cookielawinfo-checkbox-analytics
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional
11 months
The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance
11 months
This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy
11 months
The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Loading...