Posts with Scripts

Initial SQL Server Configurations

October 19, 2015 /

Wonder if I Do Things Differently?

I am always wondering what other DBA’s do and if I am doing things differently. One such thing is my initial server setups, basically, what I configure for each of my new servers. So, why not blog about it and see what others chime in with after they read this. Keeping in mind that everyone has different requirements and different ways that they like to do the actual configurations.

For now, I am not going to go into what each one of these configurations do and why I choose the value I do. That’s for another time. If you want that information you can always go to Books Online or just Google it. In this case, I am just going to give you a running list with scripts; that I’ve added too over the years based on best practices and experience.

How Does Yours Compare?

I’d really love to hear what others do and if I may have missed something that you like to implement that may benefit me as well.  So leave a comment, tweet to me, or send me an email let’s compare notes.

The List

So here are the basics setups I do on every server post install in no particular order.

* Value varies based on server configuration

  1. Min and Max Memory *
sp_configure 'min server memory', 8000;
sp_configure 'max server memory', 16000;
  1. Enable and Configure Database Mail ( This is only to enable, full script will be in later post)
sp_configure 'show advanced',1
GO
reconfigure
GO
sp_configure 'Database Mail XPs',1
GO
reconfigure
  1. Set Default Database Locations
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\SQLData\'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'L:\SQLLogs\'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
 N'Software\Microsoft\MSSQLServer\MSSQLServer', N'BackupDirectory', REG_SZ,
 N'L:\Backups'
  1. Set SQL Agent Job History Retentions
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=3500,@jobhistory_max_rows_per_job=750
  1. Set Cost threshold for Parallelism *
EXEC sys.sp_configure N'cost threshold for parallelism', N'25'
  1. Set Max Degree of a Parallelism *
EXEC sys.sp_configure N'max degree of parallelism', N'8'
  1. Set Optimize for Adhoc work loads
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
  1. Change Number of Error Logs
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 12
  1. Create Cycle Error Log Job
USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MaintenanceCycleErrorlogs2', 
 @enabled=1, 
 @notify_level_eventlog=0, 
 @notify_level_email=0, 
 @notify_level_netsend=0, 
 @notify_level_page=0, 
 @delete_level=0, 
 @description=N'No description available.', 
 @category_name=N'[Uncategorized (Local)]', 
 @owner_login_name=N'AWSample', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Recycle the SQL Server Error Log', 
 @step_id=1, 
 @cmdexec_success_code=0, 
 @on_success_action=1, 
 @on_success_step_id=0, 
 @on_fail_action=2, 
 @on_fail_step_id=0, 
 @retry_attempts=0, 
 @retry_interval=0, 
 @os_run_priority=0, @subsystem=N'TSQL', 
 @command=N'EXEC master.sys.sp_cycle_errorlog;', 
 @database_name=N'master', 
 @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Monthly', 
 @enabled=1, 
 @freq_type=16, 
 @freq_interval=1, 
 @freq_subday_type=1, 
 @freq_subday_interval=0, 
 @freq_relative_interval=0, 
 @freq_recurrence_factor=1, 
 @active_start_date=20111110, 
 @active_end_date=99991231, 
 @active_start_time=0, 
 @active_end_time=235959 

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
  1. Add Additional TempDB Files All With Same Size and Growth Rates *
USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'Temp1', FILENAME = N'T:\Temp1.ndf' , SIZE = 5120000KB , FILEGROWTH = 256000KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'Temp2', FILENAME = N'T:\Temp2.ndf' , SIZE = 5120000KB , FILEGROWTH = 256000KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'Temp3', FILENAME = N'T:\Temp3.ndf' , SIZE = 5120000KB , FILEGROWTH = 256000KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 5120000KB , FILEGROWTH = 256000KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 1024KB , FILEGROWTH = 256000KB )
  1. Set Media Retention
EXEC sys.sp_configure N'media retention', N'5'
  1. Set Backup Compression Default On
EXEC sys.sp_configure N'backup compression default', N'1'
  1. Change to Audit Successful and Failed Logins
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3
  1. Set Default Growths in Model Not Be Percentages
USE [master]
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modeldev', FILEGROWTH = 256000KB )
GO
ALTER DATABASE [model] MODIFY FILE ( NAME = N'modellog', FILEGROWTH = 256000KB )
  1. Set AD Hoc Distributed Queries off  
    sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 0;
RECONFIGURE;
GO
  2. Set CLR Enabled off  
    sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'CLR Enabled', 0;
RECONFIGURE;
GO
  3. Set Ole Automation Procedures off  
    sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;
GO
  4. Set Scan For Startup Procs off  
    GO
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE
GO
EXEC sp_configure 'scan for startup procs', 1 ;
GO
RECONFIGURE
GO
  5. Set xp_cmdshell off 
    EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO
  6. Setup Operators
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'IT Help',

@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'ITHelp@xxxx.com',

@category_name=N'Alerting'
  1. Set Up Alerts 17-25 and Error codes  823,824,825 (Remember to add the alerts to the operator)
USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'017- Insufficient Resources',

@message_id=0,

@severity=17,

@enabled=1,

@delay_between_responses=120,

@include_event_description_in=1,

@category_name=N'[Uncategorized]'

GO

EXEC msdb.dbo.sp_add_alert @name=N'018- Nonfatal Internal Error',

@message_id=0,

@severity=18,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=0,

@category_name=N'[Uncategorized]'

GO

EXEC msdb.dbo.sp_add_alert @name=N'019- Fatal Error in Resource',

@message_id=0,

@severity=19,

@enabled=1,

@delay_between_responses=120,

@include_event_description_in=1,

@category_name=N'[Uncategorized]'
GO

EXEC msdb.dbo.sp_add_alert @name=N'020- Fatal Error in Current Process',

@message_id=0,

@severity=20,

@enabled=1,

@delay_between_responses=120,

@include_event_description_in=1,

@category_name=N'[Uncategorized]'

GO

EXEC msdb.dbo.sp_add_alert @name=N'021- Fatal Error in Database Processes',

@message_id=0,

@severity=21,

@enabled=1,

@delay_between_responses=120,

@include_event_description_in=1,

@category_name=N'[Uncategorized]'
GO

EXEC msdb.dbo.sp_add_alert @name=N'022- Fatal Error: Table Integrity Suspect',

@message_id=0,

@severity=22,

@enabled=1,

@delay_between_responses=120,

@include_event_description_in=1,

@category_name=N'[Uncategorized]'

GO

EXEC msdb.dbo.sp_add_alert @name=N'023- Fatal Error: Database Integrity Suspect',

@message_id=0,

@severity=23,

@enabled=1,

@delay_between_responses=120,

@include_event_description_in=1,

@category_name=N'[Uncategorized]'
GO

EXEC msdb.dbo.sp_add_alert @name=N'024- Fatal Error: Hardware',

@message_id=0,

@severity=24,

@enabled=1,

@delay_between_responses=120,

@include_event_description_in=1,

@category_name=N'[Uncategorized]'

GO

EXEC msdb.dbo.sp_add_alert @name=N'025- Fatal Error',

@message_id=0,

@severity=25,

@enabled=1,

@delay_between_responses=120,

@include_event_description_in=1,

@category_name=N'[Uncategorized]'

GO

EXEC msdb.dbo.sp_add_alert @name=N'823- Read/Write Failure',

@message_id=823,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=0,

@category_name=N'[Uncategorized]'

GO

EXEC msdb.dbo.sp_add_alert @name=N'824- Data Retrieval SAN Slowdown Page Error',

@message_id=824,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=1,

@notification_message=N'SQL Server has actually tried that I/O a total of 4 times before it finally declares a lost cause',

@category_name=N'[Uncategorized]'

GO

EXEC msdb.dbo.sp_add_alert @name=N'825-I/O subsystem is going wrong Read-Retry Required',

@message_id=825,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=0,

@category_name=N'[Uncategorized]'

Note: Most of these can be set using GUI as well as the scripts above. Also, in addition to these configurations, I make sure that the server is brought up to the most current stable CU or Service Pack. Everyone’s environment is different, my list may not be right for you.

Monica Morehouse (Rathbun), a Microsoft MVP for Data Platform, resides in Virginia and brings two decades of experience across various database platforms, with a particular focus on SQL Server and the Microsoft Data Platform. She is a frequent speaker at IT industry conferences, where she shares her expertise on performance tuning and configuration management for both on-premises and cloud environments. Monica leads the Hampton Roads SQL Server User Group and is passionate about SQL Server and its community, she is dedicated to giving back in any way she can. You can often find her online (@sqlespresso) offering helpful tips or blogging at sqlespresso.com.

Related Posts

So let’s talk naming conventions

September 15, 2015

New Resumable Online Index Create SQL Server 2019

November 28, 2018

Filtered Index Basics

May 22, 2019

2 Comments