Basic settings for SQL Server 2012/2014, part 4

Part 4 – scripts for the previous parts

Below are scripts for most of the SQL Server specific settings I discussed in the previous three parts. Please note that not quite everything is included (trace flags, instant file instantiation, etc.).

-- Set memory amount
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'max server memory', 40000; GO RECONFIGURE; GO



-- Turn on optimize for adhoc workloads
EXECUTE sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXECUTE sp_configure 'optimize for ad hoc workloads', 1; GO RECONFIGURE
-- Turn on DAC

EXECUTE sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

-- Adjusting TEMPDB
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 100MB , FILEGROWTH = 50MB);
GO

-- Script to create TEMPDB files like the one existing
USE [master]

go

DECLARE @cpu_count     INT,
@file_count    INT,
@logical_name  SYSNAME,
@file_name     NVARCHAR(520),
@physical_name NVARCHAR(520),
@alter_command NVARCHAR(max)

SELECT @physical_name = physical_name
FROM   tempdb.sys.database_files
WHERE  NAME = 'tempdev'

SELECT @file_count = Count(*)
FROM   tempdb.sys.database_files
WHERE  type_desc = 'ROWS'

SELECT @cpu_count = 8

WHILE @file_count < @cpu_count
BEGIN
SELECT @logical_name = 'tempdev'
+ Cast(@file_count +1 AS NVARCHAR)

SELECT @file_name = Replace(@physical_name, 'tempdb.mdf',
@logical_name + '.ndf')

SELECT @alter_command = 'ALTER DATABASE [tempdb] ADD FILE ( NAME =N'''
+ @logical_name + ''', FILENAME =N'''
+ @file_name
+ ''', SIZE = 100MB, FILEGROWTH = 50MB )'

PRINT @alter_command

EXEC Sp_executesql
@alter_command

SELECT @file_count = @file_count + 1
END;



--Create operator DBATeam
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'DBATeam',
@enabled=1,
@pager_days=0,
@email_address=N'foo@bar.com; gazonk@bar.com';
GO
--Add alerts

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'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 017',
@message_id=0,
@severity=17,
@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 017', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 018',
@message_id=0,
@severity=18,
@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 018', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 019',
@message_id=0,
@severity=19,
@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 019', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 020',
@message_id=0,
@severity=20,
@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 020', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 021',
@message_id=0,
@severity=21,
@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 021', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 022',
@message_id=0,
@severity=22,
@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 022', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 023',
@message_id=0,
@severity=23,
@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 023', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 024',
@message_id=0,
@severity=24,
@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 024', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 025',
@message_id=0,
@severity=25,
@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 025', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error Number 823',
@message_id=823,
@severity=0,
@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'Error Number 823', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error Number 824',
@message_id=824,
@severity=0,
@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'Error Number 824', @operator_name=N'DBATeam', @notification_method = 7;
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error Number 825',
@message_id=825,
@severity=0,
@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'Error Number 825', @operator_name=N'DBATeam', @notification_method = 7;
GO
--Set failsafe operator
USE [msdb]
GO
EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator=N'DBATeam',
@notificationmethod=1;
GO
-- Set database mail profile
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @databasemail_profile=N'MailProfile';
GO
-- Set SQLAgent to use database mail
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,
@use_databasemail=1;
GO
-- Configure parallelism and backup compression settings.

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'cost threshold for parallelism', N'50';
GO
EXEC sys.sp_configure N'max degree of parallelism', N'2';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'show advanced options', N'0';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'backup compression default', N'1';
GO
-- Adjust MODEL
USE [master]
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', SIZE = 100MB , FILEGROWTH = 50MB);
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modellog', SIZE = 100MB , FILEGROWTH = 50MB);
GO
-- Example of creating DBA_DB database and user for replication

USE [master]
GO

CREATE DATABASE [DBADB]
ON PRIMARY
( NAME = N'DBADB', FILENAME = N'D:\MSSQL\Data\DBADB.mdf' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 51200KB )
LOG ON
( NAME = N'DBADB_log', FILENAME = N'E:\MSSQL\Log\DBADB_log.ldf' , SIZE = 10240KB , MAXSIZE = 2048GB , FILEGROWTH = 51200KB )
GO

USE [master]
GO
CREATE LOGIN [dba_repl] WITH PASSWORD=N'xxxxxxxx', DEFAULT_DATABASE=[DBADB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DBADB]
GO
CREATE USER [dba_repl] FOR LOGIN [dba_repl]
GO
USE [DBADB]
GO
ALTER ROLE [db_owner] ADD MEMBER [dba_repl]
GO