Initial SQL Server Configurations

  By

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.

About Monica Rathbun

Monica Rathbun lives in Virginia, is a Microsoft MVP for Data Platform and Microsoft Certified Solutions Expert. She has nearly two decades of experience working with a wide variety of database platforms with a focus on SQL Server and the Microsoft Data Platform. She is a frequent speaker at IT industry conferences on topics including performance tuning and configuration management. She is the Leader of the Hampton Roads SQL Server User Group. She is passionate about SQL Server and the SQL Server community, doing anything she can to give back. Monica can always be found on Twitter (@sqlespresso) handing out helpful tips. You can find Monica blogging at sqlespresso.com 
Tagged  , , , , , , , , , , , , , .
Bookmark the permalink.

2 Comments

  1. Kendal Van Dyke
    October 19, 2015 at 11:40 am

    Nice list – I usually add a step to the cycle error log job to also cycle the SQL Agent error log (sp_cycle_agent_errorlog)

  2. Pingback: Just Check ALL the Boxes – A Shot of SQLEspresso

Comments are closed