Post SQL Server Installation Steps

Post SQL Server Installation Steps

1.     Add the SQLServerMSSQLUser$ServerName$InstanceName group to the Root path with Full Control of D, and List Folder Contents Permissions for Data, Log and Tempdb Drives.

2.     Add the SQLServerMSSQLUser$ServerName$InstanceName group with Full Control of SQLData folder on Data and Tempdb Drives.

3.     Add the SQLServerMSSQLUser$ServerName$InstanceName group with Full Control of SQLLogs folder on Log Drive.

4.     Remove the AD Service User Account from the Root Path. (This decouples the Service Account explicitly and relys on the group)

5.     Add the SQLServerMSSQLUser$ServerName$InstanceName, SQLServerSQLAgentUser$ServerName$InstanceName, or other group accounts to any Backup, or processing folders as needed.

6.     In the Local Security Policy, add the SQLServerMSSQLUser$ServerName$InstanceName group to the Perform Volume Maintenance Tasks and Lock Pages in Memory objects.

7.     Verify the Antivirus( Macaffe) team excluded Data, Log, Tempdb, any Backup file paths, and the SQL Server Binaries folders from AntiVirus Scans.

8.     Verify backup is excluding mdf, ndf, and ldf files.

9.     Remove Builtin\Administrators and Builtin\user logins.

10.  Enable All Login Auditing in the SQL Server Security Settings

11.  Enable TCP/IP and change default port from 1433. 

12.  Enable remote DAC connections.

13.  Enable as required xp_cmdshell, SQLCLR, and OLE Automation for the SQL Server Instance.

a.     Configure xp_cmdshell proxy account as required.

14.  Enable DatabaseMail and configure default public and private accounts.

15.  Configure SQL Error Log retention for 99 log files

16.  Configure SQL Agent job to perform nightly log rollover.

17.  Configure SQL Server Maintenance Plans for system and user database backups, CHECKDB, index maintenance, statistics updates, backup cleanup, and history cleanup.

18.  --Move MSDB Database files to SQLData and SQLLogs respectively.

19.  Reconfigure Tempdb with data files equal to 1/2-1/4 the physical CPU’s on the server based on load characteristics.  Set data files to the same 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 2000/2005/2008/2008R2/2012/2014 and SQL Server 2016 for Tempdb.

21.  Set Model database to SIMPLE recovery, 2048MB default datafile size and 1024MB default logfile size.  Set AutoGrowth to 1024MB for data files and 512MB for Log file.

22.  Set Max Server Memory based on installed RAM and installation type (Newer Servers are all 64bit, but enable 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.    These are base values that will later be adjusted based on the Memory\Available MBytes counter being > 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, you should 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, generally set to 1/2 or 1/4 of the physical cores available on the server.

b.    Adjusted 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]

GO

EXEC msdb.dbo.sp_add_alert @name=N’Severity 016′,

@message_id=0,

@severity=16,

@enabled=1,

@delay_between_responses=60,

@include_event_description_in=1,

@job_id=N’00000000-0000-0000-0000-000000000000′

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 016′, @operator_name=N’Database Team’, @notification_method = 7

GO