SQL Server Installation and Setup Best Practices - Article on SQLNetHub

SQL Server Installation and Setup Best Practices

by

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.
  • 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.

 

Check our online course, titled “SQL Server Fundamentals – SQL Database for Beginners“.

This course, is a complete beginners guide that helps you get started with SQL Server, SSMS and Azure Data Studio fast and easy.

SQL Server Fundamentals - SQL Database for Beginners
(Lifetime Access, Certificate of Completion and more)

Learn More

 

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.

 

Strengthen your SQL Server Database Administration skills – Enroll to our Online Course!

Check our online course, titled “Essential SQL Server Administration Tips (Hands-On Guides)“.

Via this course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more.

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access, Certificate of Completion, Downloadable Resources, and more!)

Learn 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.

Enroll to the Course!

 

Frequently Asked Questions

Below, I have included some relevant frequently asked questions about the topic which you might find useful.

How can I ensure optimal performance and resource allocation for SQL Server when deploying it in a virtualized environment, such as VMware or Hyper-V?

Deploying SQL Server in a virtualized environment introduces additional considerations for performance and resource allocation. To ensure optimal performance, administrators should closely monitor resource usage, such as CPU, memory, and storage, within the virtualized environment and adjust allocations as needed. Techniques such as workload isolation, affinity rules, and storage optimization can help improve SQL Server performance in virtualized environments. Additionally, administrators should ensure that the underlying virtualization infrastructure is properly configured and optimized for SQL Server workloads to minimize overhead and latency.

 

Are there any specific considerations or best practices for SQL Server installation and setup when migrating from an older version of SQL Server to a newer version, such as SQL Server 2019?

Migrating from an older version of SQL Server to a newer version, such as SQL Server 2019 or 2022, requires careful planning and consideration of compatibility, feature differences, and potential impacts on existing applications and databases. Administrators should thoroughly review the documentation and release notes for the new version of SQL Server to identify any deprecated features or behavior changes that may affect their environment. Additionally, testing the migration process in a non-production environment can help identify and mitigate potential issues before performing the actual migration. Proper backup and rollback plans should also be in place to minimize downtime and data loss during the migration process.

 

The article mentions using Windows Authentication Mode as a best practice. Are there any scenarios or use cases where using Mixed Mode (SQL Server authentication and Windows authentication) would be preferable or necessary?

While Windows Authentication Mode is generally recommended for its simplicity and integration with existing Active Directory environments, there are scenarios where Mixed Mode authentication may be necessary or preferable. Mixed Mode allows for the use of SQL Server logins with username/password credentials in addition to Windows logins. This can be useful in environments where users need to connect to SQL Server from non-Windows systems or applications that do not support Windows Authentication. Additionally, Mixed Mode may be necessary for certain legacy applications or third-party tools that require SQL Server authentication. In such cases however, this is an additional reason to ensure that SSL encryption for data in motion is enabled and enforced, so that there is no unencrypted communication to SQL Server (such as the credentials of local SQL Server users).

 

What are some common troubleshooting steps or recommendations for addressing issues that may arise during the SQL Server installation process, such as compatibility errors or installation failures?

During the SQL Server installation process, administrators may encounter various issues such as compatibility errors, installation failures, or configuration problems. Common troubleshooting steps include reviewing the installation logs for error messages or warnings, verifying system requirements and prerequisites, ensuring proper network connectivity, and checking for conflicts with existing software or services. Additionally, consulting the Microsoft support documentation, community forums, or reaching out to Microsoft support can provide additional guidance and assistance in resolving installation issues.

 

The article briefly mentions enabling TCP/IP and changing the default port for SQL Server. Can you provide guidance on how to perform this configuration securely and any potential implications or risks associated with changing the default port?

Enabling TCP/IP and changing the default port for SQL Server can enhance network security by restricting access to the SQL Server instance and reducing the risk of unauthorized connections. However, administrators should exercise caution when making these changes to avoid disrupting existing applications or services that rely on the default port. When changing the default port, administrators should ensure that the new port is not blocked by firewalls or other network security measures and that clients are configured to connect to the correct port. Additionally, implementing encryption and authentication mechanisms, such as SSL/TLS and IPsec, can further enhance the security of SQL Server connections over TCP/IP. Regular monitoring and auditing of network traffic can also help detect and mitigate potential security threats.

 

Featured Online Courses:

 

Read Also:

 

Check our other related SQL Server Administration articles.

Subscribe to our newsletter and stay up to date!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 16,919