SQL Server Checklist

1. Post installation, run the below script to set MAXDOP, CTFP and reserve memory for SQL server

EXEC sys.sp_configure N'cost threshold', N'50'
 GO
 EXEC sys.sp_configure N'max degree of parallelism', N'4'
 GO
 USE master; EXEC sp_configure 'show advanced option', '1';
 /* Set max memory to 85% of the OS's memory: */
 DECLARE @StringToExecute NVARCHAR(400);
 SELECT @StringToExecute = N'EXEC sys.sp_configure N''max server memory (MB)'', N''' + CAST(CAST(physical_memory_kb / 1024 * .85 AS INT) AS NVARCHAR(20)) + N''';'
 FROM sys.dm_os_sys_info;
 EXEC(@StringToExecute);
 GO
 RECONFIGURE WITH OVERRIDE
 GO

2. Install DBATools (open source power shell modules) for remote administration. Prompt yes to all the prompts that follow and complete the installation

Install-Module dbatools

3. Install sp_WhoIsActive stored proc

Windowssystem32> Install-DbaWhoIsActive -sqlinstance SQLSERVER01 -Database master

4.  Install First Reponder Kit

Install-DbaFirstResponderKit -SqlInstance SQLSERVER01 -Database master

5. Enable Remote DAC (Dedicated Admin Connection). This is to be used only when you are troubleshooting emergencies. Promptly disconnect the session once you are done using it

sp_configure 'remote admin connections', 1;

6. Setup Database Mail to send out email alerts

--Declare and Set Values
 /*
 @SMTPServer - You will need to get SMTP server that you want to use to send email out.
 @AdminEmail - After setup, the script will send test email to this email address
 @DomainName - This will be part of SQL Server Email Name
 @replyToEmail - this is how you will see for reply to email when you get the email.
 */
 use master
 DECLARE @SMTPServer VARCHAR(100)
 DECLARE @AdminEmail VARCHAR(100)
 DECLARE @DomainName VARCHAR(100)
 DECLARE @replyToEmail VARCHAR(100)
 SET @SMTPServer = 'smtp.office365.com'
 SET @AdminEmail = 'sql-alerts@company.com'
 SET @DomainName = '@company.com'
 SET @replyToEmail = 'SQL-Alerts@company.com'
 --Change Global Configuration Settings
 exec sp_configure 'show advanced options', 1
 exec sp_configure 'Database Mail XPs', 1
 exec sp_configure 'Agent XPs',1
 RECONFIGURE WITH OVERRIDE
 --Database Mail Configuration
 --This will setup database mail. It does not require the SQL Server service to be restarted.
 --At the end of the script, it will send a Test Mail.
 declare @servername varchar(100)
 declare @email_address varchar(100)
 declare @display_name varchar(100)
 declare @testmsg varchar(100)
 set @servername = replace(@@servername,'','_')
 set @email_address = 'db_mail@company.com'
 set @display_name = 'Database Mail'
 set @testmsg = 'Test from ' + @servername
 IF EXISTS(SELECT * from msdb.dbo.sysmail_profile)
 PRINT 'DB mail already configured'
 ELSE
 BEGIN
 --Create database mail account.
 exec msdb.dbo.sysmail_add_account_sp
 @Account_name = 'SMTP_database_mail'
 , @description = 'Primary database alerts profile'
 , @email_address = @email_address
 , @replyto_address = @replyToEmail
 , @display_name = @display_name
 , @mailserver_name = @SMTPServer
 ,@mailserver_type = 'SMTP'
 ,@port = 587
 ,@username = 'sql-alerts@company.com'
 ,@password = 'Password here'
 ,@enable_ssl = 1
 --Create global mail profile.
 exec msdb.dbo.sysmail_add_profile_sp
 @profile_name = 'DBA_Profile'
 , @description = 'Default profile for DBA use'
 --Add the account to the profile.
 exec msdb.dbo.sysmail_add_profileaccount_sp
 @profile_name = 'DBA_Profile'
 , @Account_name = 'SMTP_database_mail'
 , @sequence_number=1
 --grant access to the profile to all users in the msdb database
 use msdb
 exec msdb.dbo.sysmail_add_principalprofile_sp
 @profile_name = 'DBA_Profile'
 , @principal_name = 'public'
 , @is_default = 1
 END
 --send a test message.
 exec msdb..sp_send_dbmail
 @profile_name = 'DBA_Profile',
 @recipients = @AdminEmail,
 @subject = @testmsg,
 @body = @testmsg

DB mail profile creation using powershell (Alternative method using dbatools):

$account = New-DbaDbMailAccount -SqlInstance 'SQLSERVER01' -Name 'SMTP_database_mail' -EmailAddress sql-alerts@company.com -MailServer smtp.office365.com -description 'Primary database alerts profile' -ReplyToAddress SQL-Alerts@company.com
 $profile = New-DbaDbMailProfile -SqlInstance 'SQLSERVER01' -Name 'DBA profile' -MailAccountName 'SMTP_database_mail'

7. Enable SQL Agent notification: Once database mail is setup, you now can enable SQL Agent notifications. To take effect, this may require the SQL Agent service to be restarted. In order to sent out email from SQL Agent, you need to configure the alert system. The script below will do this. It uses the extended stored proc “xp_instance_regwrite” to write two values to the registry. The first setting just tell the SQL Agent Alert system to use Database mail as the email option and the second setting tells the SQL Agent Alert system which mail profile to set.

USE [msdb]
 EXEC msdb.dbo.sp_set_sqlagent_properties
 @email_save_in_sent_folder=1
 EXEC master.dbo.xp_instance_regwrite
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent'
 , N'UseDatabaseMail'
 , N'REG_DWORD'
 , 1
 EXEC master.dbo.xp_instance_regwrite
 N'HKEY_LOCAL_MACHINE'
 , N'SOFTWAREMicrosoftMSSQLServerSQLServerAgent'
 , N'DatabaseMailProfile'
 , N'REG_SZ'
 , N'DBA_Profile'

8. Setup Email Operator

USE [msdb]
 GO
 EXEC msdb.dbo.sp_add_operator @name=N'DBA',
 @enabled=1,
 @weekday_pager_start_time=90000,
 @weekday_pager_end_time=180000,
 @saturday_pager_start_time=90000,
 @saturday_pager_end_time=180000,
 @sunday_pager_start_time=90000,
 @sunday_pager_end_time=180000,
 @pager_days=0,
 @email_address=N'SQL-Alerts@company.com',
 @category_name=N'[Uncategorized]'
 GO

9. Setup Email alerts to alert upon any corruption, data or log file being Full and other errors

USE [msdb]
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Database Filegroup Full',
 @message_id=1105,
 @severity=0,
 @enabled=1,
 @delay_between_responses=300,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Database TRNLog Full',
 @message_id=9002,
 @severity=0,
 @enabled=1,
 @delay_between_responses=300,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 14',
 @message_id=0,
 @severity=14,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 19',
 @message_id=0,
 @severity=19,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 20',
 @message_id=0,
 @severity=20,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 21',
 @message_id=0,
 @severity=21,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 22',
 @message_id=0,
 @severity=22,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 23',
 @message_id=0,
 @severity=23,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 24',
 @message_id=0,
 @severity=24,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 EXEC msdb.dbo.sp_add_alert @name=N'Demo: Sev. 25',
 @message_id=0,
 @severity=25,
 @enabled=1,
 @delay_between_responses=60,
 @include_event_description_in=1,
 @category_name=N'[Uncategorized]',
 @job_id=N'00000000-0000-0000-0000-000000000000'
 GO
 USE [msdb]
 GO
 EXEC msdb.dbo.sp_add_operator @name=N'DBA',
 @enabled=1,
 @pager_days=0,
 @email_address=N'SQL-Alerts@company.com',
 @pager_address=N''
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Database Filegroup Full', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Database TRNLog Full', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 14', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 19', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 20', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 21', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 22', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 23', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 24', @operator_name=N'DBA', @notification_method = 1
 GO
 EXEC msdb.dbo.sp_add_notification @alert_name=N'Demo: Sev. 25', @operator_name=N'DBA', @notification_method = 1
 GO

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: