Skip to content

Steps to Follow for Best Practice After Installing SQL Server

  • by

Post SQL Server Installation Steps :

  1. Add the group SQLServerMSSQLUser$ServerName$InstanceName to the Root path of drive(ex: D) with Full Control, and List Folder Contents Permissions for Data, Log and Tempdb Drives.
  2. Add the group SQLServerMSSQLUser$ServerName$InstanceName to SQLData folder on Data and Tempdb Drives with Full Control.
  3. Add the group SQLServerMSSQLUser$ServerName$InstanceName to SQLLogs folder on Log Drive with Full Control.
  4. Remove the Active Directory (AD) Service User Account from the Root Path. (This will decouples the Service Account explicitly and rely on the group) .
  5. Add the groups SQLServerMSSQLUser$ServerName$InstanceName, SQLServerSQLAgentUser$ServerName$InstanceName, or any other group accounts to Backup or sharing folders as needed.
  6. In Local Security Policy, add the group SQLServerMSSQLUser$ServerName$InstanceName to policies Lock Pages in Memory objects and Perform Volume Maintenance Tasks.
  7. Verify with Antivirus(Ex: Macaffe) team if they have excluded the folders Data, Log, Tempdb, any Backup file paths, and the SQL Server Binaries folders from AntiVirus Scans.
  8. Check\Verify backup is excluding mdf, ndf, and ldf files.
  9. Remove the Accounts Builtin\Administrators and Builtin\user logins.
  10. Enable the option All Login Auditing in the SQL Server Security Settings
  11. Enable the protocol TCP/IP and also change default port 1433.
  12. Enable the option remote DAC connections.
  13. Enable xp_cmdshell, OLE Automation and SQLCLR for the SQL Server Instance as required.
    a. Configure xp_cmdshell proxy account as needed.
  14. Enable the option Database Mail and configure default private and public accounts.
  15. Configure SQL Error Log retention with 99 log files
  16. Configure SQL Agent job to perform nightly .
  17. Configure SQL Server Maintenance Plan jobs for system and user database backups, index maintenance, CHECKDB, statistics updates, history cleanup and backup cleanup.
  18. Move the MSDB Database files to SQLData and SQLLogs .
  19. Reconfigure Tempdb Database with data files equal to 1/2-1/4 the physical CPU’s on the server based on load characteristics. Set all data files to equal size based on load characteristics in 4096MB increments for Datafiles, and 1024MB increments for Log files. Set AutoGrowth to 1024MB for data files and 512MB for Log file.
  20. Enable Trace Flag 1118 on SQL Server 2005/2008/2008R2/2012/2014 and 2016 for Tempdb.
  21. Change the recovery model for Model database to SIMPLE recovery, 2048MB as default datafile size and 1024MB as default logfile size. Set AutoGrowth to 1024MB for data files and 512MB for Log file.
  22. Set Max Server Memory based on RAM installed on Server and installation type (Latest\New Servers are all 64bit, but enable the option AWE as needed on 32 bit servers).
    a. 8GB RAM = 6144 Max Server Memory
    b. 16GB RAM = 12228 Max Server Memory
    c. 32GB RAM = 28672 Max Server Memory
    d. The above values are base values that can later be adjusted based on the Memory\Available MBytes counter value > 150 on the Server( based on your environment ).
    e. Memory configuration on clustered Instances should account for multiple instances on a one physical node in case of fail over.
    f. In general, we calculate Max. Server Memory using the formula: Max. Server Memory for a SQL Server Instance = (Total RAM available to the OS) – {(OS: memory pool, filesystem cache, PTE, desktop heap, Driver Images etc…) + (Non-buffer Pool region of SQL Server allocated to Multi Page Allocators, Worker Threads, COM, Extended SPs, Backup Buffers, CLR, Linked Server…) + (SQL Server Agent, Replication Agents, Bulk Copy, SSRS, SSAS, SSIS, and Full Text) + (Log shipping file copy depending on the size of log backups) + (Other SQL Server instances running in the box) + (Other applications running in the box (Antivirus, Monitoring Softwares, Compression softwares etc…) }
  23. Set max degree of parallelism sp_configure option based on the number of physical CPU cores installed and anticipated workload
    a. For OLTP servers, generally we set to 1/2 or 1/4 of the physical cores available on the server.
    b. we can Adjust up or down based on wait stats and load.
  24. Set cost threshold of parallelism sp_configure option based on the anticipated load.
    a. General default value of 5 is low for most OLTP workloads and should be increased.
    b. Base value of 20-25 used for most server installs.
  25. Add AD login (standard for environment and locked out in AD by default) for patching and emergency server access to Local Administrators Group.
  26. Set SA user password to standardized password that is changed quarterly on all servers and maintained in password safe.
  27. Install the SQL Server Performance Dashboard Reports
  28. Create default alerts for severities 16 through 25.
    SQL Server’s alerting system has the ability to notify operators whenever major things break inside the database. These include running out of space in log files, backup failures, failed logins and other things DBAs just need to be aware of. Don’t rely on this as your only SQL Server monitoring system, because it only sends alerts when it’s too late to take proactive action, but still, it’s better than nothing.

The below script will set up an alert for severity 16. Copy this and repeat the same thing for 17-25, but change ‘Database Team’ to be the name of your default operator. Notice that @delay_between_responses is set to 60 – that means if it sends out an alert, it won’t repeat that same alert for 60 seconds. This is useful because when a database runs out of drive space, for example, all hell will break loose for a minute or two, and you don’t want hundreds of emails and pages per minute.

USE [msdb]
EXEC msdb.dbo.sp_add_alert @name=N’Severity 016′,
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 016′, @operator_name=N’Database Team’, @notification_method = 7

Leave a Reply

%d bloggers like this: