Skip to main content
Our Tech Ideas

SQL Server Installation Best Practices

SQL Server Installation Best Practices

Introduction

SQL Server is a powerful database management system that is used by businesses of all sizes. However, if SQL Server is not installed correctly, it can lead to performance problems, security vulnerabilities, and other issues. A SQL Server Installation Best Practices could solve all issues.

This blog post will discuss some of the best practices for installing SQL Server. By following these best practices, you can help ensure that your SQL Server installation is stable, secure, and performs well.

Windows Requirements

For new Windows + SQL Server Installation, ensure that the windows server must be latest. The Virtual Machine should have minimum 4 cores but 8 cores would be recommended with minimum RAM of 8 GB or as per below requirements.

Service account

Have a separate service account for each SQL Server stand-alone server/cluster. The service account to have admin privileges on the windows server

Typical Drive Layout

  • Disk C: for Operating System
  • Disk E: for SQL Server binaries and system databases (master, msdb, model, resource)
  • Disk F: for User Master Data File (MDF)
  • Disk L: for User Transaction Log Data File (LDF)
  • Disk T: for Tempdb Database

Format Disk Drives with 64K allocation unit (all non-OS drives)

In VMware, use paravirtual SCSI drivers for all database drives. Data, Log, and Tempdb drives should have separate logical controllers.  Based on the number of drives on a server, the optimal number of drives per controller to be defined by VMware admins from a performance perspective

Grant DBA Team admin access to windows for servers. For legacy servers, please use the name as per your organization policy/requirements

Grant SQL Server service account privileges to register its SPN into Active Directory on its own. (Refer: https://support.microsoft.com/)

Also, the service account should have “trust for delegation” permissions in Windows AD for performing Kerberos based authentication

Anti-virus software configuration

Standards regarding the anti-virus software configuration. Exclude the following file types from the scan.

  • .MDF
  • .NDF
  • .LDF
  • .BAK
  • .TRN
  • .XEL

HA & DR configuration requirements

  • Additional Considerations for Windows Cluster setup used for AlwaysON
  • Request for the separate heartbeat and public communication IPs
  • Windows team to setup the cluster in the same data center(same subnet) or across data centers (multi-subnet)
  • Request for a cluster IP and virtual name
  • Request for an AAG listener name and IP
  • Grant SQL Server service account and windows cluster computer account privilege to register the SQL Server listener name into AD.
  • Use NIC Teaming to provide redundancy for NICs (future state)
  • Discount the votes for all nodes that are not going to participate in AlwaysON Failover. Cluster Quorum Settings will be Node + File Share Witness for an even number of nodes meant for failover. In the case of an odd number of failover nodes, the node majority is fine.
  • For multi-subnet clusters, increase the heartbeat thresholds:
    • SameSubnetThreshold = 10
    • SameSubnetDelay = 1000
    • CrossSubnetDelay = 20000
    • CrossSubnetThreshold = 10
    • Discount the DR or remote data center node vote so it does not contribute to quorum.

Install Process/Checklist

  • Locate the appropriate media from the central software repository: \xxx-yyy-zzzsoftwareMicrosoftSQL Server
  • Proceed with the SQL Server Install and select appropriate features needed
  • Features that are allowed and not allowed for server installations.
    • Features Allowed
      • Database Engine
      • Full Text
      • Replication
      • SQL Server Reporting Services (SSRS) – Native
      • SQL Server Reporting Services (SSRS) – SharePoint
      • SQL Server Analysis Services (SSAS)
      • SQL Server Integration Services (SSIS)
      • Client Tools Connectivity
      • Client Tools Backwards Compatibility
      • Master Data Services (MDS)
  • Features not Allowed (Development tools or not approved for production servers)
    • BIDS/SSDT
    • Client Tools SDK
    • Data Quality Services
    • Data Quality Client
    • SQL Client Connectivity SDK
  • The best practice is to keep separate dedicated servers for SSAS, SSRS, MDS whenever possible. If not, then the resource usage of these installations needs to be defined clearly so that SQL Server does not get impacted because of other workloads running on the same server.
  • While installing, please ensure to use a domain service account for SQL Server services is configured and grant sysadmin access to DBA groups only.
  • Ensure the following standard location:
  • User database directory is Disk F: MSSQLDATA
  • User database transaction log directory is Disk L: MSSQLDATA
  • Tempdb files are located in Disk T: MSSQLDATA
  • Ensure Tempdb is configured appropriately:
    • Proper TempDB configuration is crucial for SQL Server performance
    • A minimum of 4 files must be configured for all SQL servers regardless of size.
    • All TempDB data files must be of the exact same size so that they can be allocated to evenly.
    • Data Files – TempDB data files will have their own LUN(s).
    • A minimum of 2 LUN’s for large servers (>=16 Cores)
    • Log Files – Log files may have their own dedicated LUN or on existing data LUN.
    • Space will be pre-allocated to the TempDB data files and fill 90% of the available drive space.
    • All data files will be evenly sized.
      • Example: 400GB LUN with 4 TempDB data files.
      • Calculation: 400*0.94 = 90GB TempDB data files.
    • Less than (<) 8 logical CPU’s
      • TempDB data files = 4
    • Greater than equal to (>=) 8 logical CPU’s
      • TempDB data files = 8
  • Reference:  http://support.microsoft.com/kb/2154845

Post-Install Configuration

  1. Max server memory
    • Single Instance
      • OS: 10% of Total Memory or 2GB, whichever is larger.
      • Max Memory: (Total Memory – OS)
    • Multiple Instance
      • OS: 10% of Total Memory or 2GB, whichever is larger.
      • Max Memory: (Total Memory – OS – SUM(Other Instances Max Memory))
        • The installation of a new instance my require adjustment of existing instances memory if additional memory is not added as part of the installation.
        • Any adjustments should be completed prior to the installation of the new instance and tested for a minimum of 2 weeks prior to the new installation.
      • Individual Instances are to be adjusted according to workload.
  2. Min Memory
    • Greater than equal to (<=) 128 Max Memory
      • 50% of max memory for instance but must meet below criteria
        • New Min Memory + Cluster Min < Cluster Max
      • > 128 Max Memory
        • 70% of max memory for instance but must meet below criteria
      • New Min Memory + Cluster Min < Cluster Max
  1. Set Errorlog files to retain to 90 and set up daily Job to Recycle Errorlog
  2. Optimize for Adhoc workload to true Always
  3. xp_cmdshell – Security problem, should be disabled unless needed by any application and approved by DBA Team.
  4. CLR Enabled – should be disabled unless application wants to use it and approved by DBA Team
  5. Cost threshold for Parallelism = 50
  6. Max Degrees of Parallelism (Individual tuning allowed for based on a server by server basis)
    • NUMA
      • Set to max Logical CPUs per node.
        • Example – 16 Logical CPU’s with 2 NUMA Nodes
          • Set to 8
        • Example – 32 Logical CPU’s with 8 NUMA Nodes
      • Set to 4
  1. Backup Compression
    • (Set to On only if we’re going to use Native backups only. DDBoost backups work better with compression set to OFF)
  2. Trace Flags
    • T1118 for any builds lower than SQL 2016
    • T834 for large-page allocations for the buffer pool
  3. Windows Power Settings
    • Switch power setting from Balanced to Performance Mode
  4. Database Mail
  • The settings that are to be configured on all servers for database mail
  • Profile Account Name: xxx.yyy@zzz.com
  • SMTP Settings
    • Email Address: xxx.yyy@zzz.com
    • Display Name: xxx.yyy@zzz.com
    • Server: xxx.yyy@zzz.com
    • Port: xxx
    • Anonymous Authentication
    • Set to Public
  1. Service Account Privileges
  • SQL Server service account will need the following permissions if it is a non-admin on the local server.  These can be manipulated via the Local Security Policy console on the server:
    • Act as a part of the operating system
    • Bypass traverse checking
    • Adjust memory quotas for a process
    • Logon as a service
    • Increase a process working set
    • Other domain level resource permissions (file shares, backup shares, etc. as needed)
    • Lock Pages in Memory
    • Perform Volume Maintenance Tasks
  1. SA account password
    • Choose a random password for SQL Server and store SA password
    • For other things like SSRS or SSIS catalogs, backup the Encryption Key and stored the encryption password in the same vault.
  2. Backups and Maintenance
    • We will use the Ola Hallengren SQL Server backup/maintenance solution on all SQL Servers in the environment (https://ola.hallengren.com/sql-server-backup.html)
    • This tool covers backup, index maintenance, and consistency check, and more
    • It can also support all types of backups – native and third party
    • Before setting up this tool, create a standard DBA database on the newly installed SQL Server instance.
      • Run the script located at ScriptsBuildOlaHallengrenMaintenance.sql
      • This script will set up objects inside the DBA database and will create a set of jobs on the SQL Server Agent:
        • Goto each type of job and setup parameters based on our need – like the type of backup, retention of backup
        • If we are going to use DDBoost backups, install DDBoost and configure it per the DDBoost installation document
        • Setup the schedules appropriately for all the jobs as needed.

For more about the various options to backup and setup maintenance on databases, refer the https://ola.hallengren.com/sql-server-backup.html and https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Create as many jobs if you want to setup parallel jobs for backup, maintenance and integrity check

  • Create a job on every server to recycle SQL Server Errorlog every day. Also, set the number of errorlogs to be retained to 90
  • Right-click on SQL Server Logs from SSMS and change the max # of files:
  • Create a job named “DBA – Recycle Errorlog” to execute this command “sp_cycle_errorlog”

Feel free comment for missing content