Post SQL Installation Configuration Script

After installing SQL Server and applying the latest or required Service pack, there are always a number of tasks that may be completed in a particular environment. These can be quite repetative, especially in large environments with many servers being deployed.

Some of these tasks may include configuring SQL Mail, changing the default configuration for the Model database, setting up operators, etc.

Below is a list of these tasks:
Set up default maintenance plan
Configure SQL Mail
Change recovery model of Model database to simple
Change default file growth settings in Model
Set up SQL Agent notifications and DDSQLTeam operator
Change job history
Set up error logs to cycle and keep 31 logs

Most of these can be accomplished using T-SQL or powershell. The only one I didn't take the time to script was the default maintenance plan.

The rest of the configuration options are in the script below:

-- Create a Database Mail profile 
EXECUTE msdb.dbo.sysmail_add_profile_sp 
@profile_name = 'DEVS SQL Mail', 
@description = 'DEVS SQL Mail' ; 

-- Create a Database Mail account 
EXECUTE msdb.dbo.sysmail_add_account_sp 
@account_name = 'DEVS', 
@description = 'DEVS', 
@email_address = 'DEVS@za.com', 
--@replyto_address = 'john.doe@domain_name.com', 
@display_name = 'DEVS SQL Mail', 
@mailserver_name = '10.10.10.1' ; 

-- Add the account to the profile 
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
@profile_name = 'DEVS SQL Mail', 
@account_name = 'DEVS', 
@sequence_number =1 ; 

-- Grant access to the profile to the DBMailUsers role 
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 
@profile_name = 'DEVS SQL Mail', 
@principal_id = 0, 
@is_default = 1 ; 

SELECT * FROM msdb.dbo.sysmail_profile 
SELECT * FROM msdb.dbo.sysmail_account 


-- Alter Model db recovery setting
ALTER DATABASE model
SET RECOVERY SIMPLE
GO

-- Alter Model file growth settings
ALTER DATABASE model
MODIFY FILE 
(
Name = modeldev,
Size = 100,
FILEGROWTH = 100
)
GO

ALTER DATABASE model
MODIFY FILE 
(
Name = modellog,
Size = 20,
FILEGROWTH = 20
)

-- Add SQL Team operator

USE [msdb]
GO

EXEC msdb.dbo.sp_add_operator @name=N'SQLTeam', 
  @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'SQLTeam@gcn.local', 
  @category_name=N'[Uncategorized]'
GO

-- Change Job history
EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.DEVS\SQLServerAgent', @value_name='JobHistoryMaxRows', @type='REG_DWORD', @value=100000
EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.DEVS\SQLServerAgent', @value_name='JobHistoryMaxRowsPerJob', @type='REG_DWORD', @value=1000

-- Change number of error logs
EXEC master..xp_regwrite @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.DEVS\MSSQLServer', @value_name='NumErrorLogs', @type='REG_DWORD', @value=31

-- Add Job to cycle error logs
USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 06/01/2011 13:30:22 ******/
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'Cycle Error Logs', 
  @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'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Cycle Log]    Script Date: 06/01/2011 13:30:24 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Cycle 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 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'Daily', 
  @enabled=1, 
  @freq_type=4, 
  @freq_interval=1, 
  @freq_subday_type=1, 
  @freq_subday_interval=0, 
  @freq_relative_interval=0, 
  @freq_recurrence_factor=0, 
  @active_start_date=20090420, 
  @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



--Enable SQL Agent Mail
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'DEVS SQL Mail'
GO

Just a bit about each option.
Configure SQL Mail is straightforward. Once you've done that you need to enable SQL Agent to use the SQL Mail profile you've created.

Changing the Model database growth and recovery model settings is quite important in a large busy environment. The default growth out of the box for the data file is 1MB, starting at 1MB. This is NOT ideal and if a database is created without changing this setting, the growth will cause severe fragmentation of the disk and of the mdf. In my particular environment there are 1600 databases. The policy is to have all db's use the SIMPLE recovery model. This means that if a database is created and the default setting is not changed, the recovery will be FULL. This new database log file will grow very big and will not be backed up.

The SQL Agent Operator is also quite obvious. Each SQL Server Agent has jobs that will inevitably fail sometime or another. Notifications need to be sent to a group of DBA's. This is a standard distribution list.

Changing the Job history is not so obvious. The default setting here is 1000 entries in the job history table with a max of 100 per job. This will be overrun very quickly on a busy server with many jobs that run frequently. If you want to see a jobs history, it may have been overwritten. Hence this change. Its two registry keys that are changed using xp_regwrite.

Changing the number of error logs to keep is quite handy when troubleshooting. I like to keep each days worth of SQL error logs in 1 log file. I then keep 31 error logs, so I have a months worth of error logs. I set up a job that cycles the error log at midnight. Neat, tidy, efficient. There is nothing worse than trying to open a huge SQL error log to try and find an error that may have happened 2 weeks ago.