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
Leave a Reply
Want to join the discussion?Feel free to contribute!