SQL 2008 Standards

These are the changes I make on my SQL 2005 servers. I haven't verified that they all still apply to 2008 but I think most of them will. In no particular order:

Server Settings

Right click on the server in the Object Browser and select Properties.

Processors Tab:

Tick the Boost SQL Server Priority box on the Processors tab. (Assumes this server is dedicated to SQL).

Security Tab:

Under Login Auditing, choose Failed Logins Only.

Connections Tab:

Tick the Arithmetic Abort connection option. (Our apps use Because ADO.Net and it sets this option on its connections. I like to make this the default so I get the same cached plans the users are using when I'm trying to diagnose performance issues.)

Enable Instant Initialisation

Go into Control Panel | Administrative Tools | Local Security Policy and give the SQL Server Service account the right to Perform volume maintenance tasks. Paul Randal's blog will explain this one.

Create Alerts

I set up alerts to notify me when severe errors occur (severity levels 19 - 25) or when an 825 warning occurs (a sign that the disk sub-system is in trouble).

I run this script to set up the alerts and then add the notifications onto them:

/****** Object:  Alert [Error - Severity Level 19]    Script Date: 03/05/2009 08:21:13 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Error - Severity Level 19', 
    	@message_id=0, 
    	@severity=19, 
    	@enabled=1, 
    	@delay_between_responses=0, 
    	@include_event_description_in=1, 
    	@category_name=N'[Uncategorized]', 
    	@job_id=N'00000000-0000-0000-0000-000000000000'

/****** Object:  Alert [Error - Severity Level 20]    Script Date: 03/05/2009 08:21:23 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Error - Severity Level 20', 
    	@message_id=0, 
    	@severity=20, 
    	@enabled=1, 
    	@delay_between_responses=0, 
    	@include_event_description_in=1, 
    	@category_name=N'[Uncategorized]', 
    	@job_id=N'00000000-0000-0000-0000-000000000000'

/****** Object:  Alert [Error - Severity Level 21]    Script Date: 03/05/2009 08:21:33 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Error - Severity Level 21', 
    	@message_id=0, 
    	@severity=21, 
    	@enabled=1, 
    	@delay_between_responses=0, 
    	@include_event_description_in=1, 
    	@category_name=N'[Uncategorized]', 
    	@job_id=N'00000000-0000-0000-0000-000000000000'

/****** Object:  Alert [Error - Severity Level 22]    Script Date: 03/05/2009 08:21:41 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Error - Severity Level 22', 
    	@message_id=0, 
    	@severity=22, 
    	@enabled=1, 
    	@delay_between_responses=0, 
    	@include_event_description_in=1, 
    	@category_name=N'[Uncategorized]', 
    	@job_id=N'00000000-0000-0000-0000-000000000000'

/****** Object:  Alert [Error - Severity Level 23]    Script Date: 03/05/2009 08:21:48 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Error - Severity Level 23', 
    	@message_id=0, 
    	@severity=23, 
    	@enabled=1, 
    	@delay_between_responses=0, 
    	@include_event_description_in=1, 
    	@category_name=N'[Uncategorized]', 
    	@job_id=N'00000000-0000-0000-0000-000000000000'

/****** Object:  Alert [Error - Severity Level 24]    Script Date: 03/05/2009 08:21:59 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Error - Severity Level 24', 
    	@message_id=0, 
    	@severity=24, 
    	@enabled=1, 
    	@delay_between_responses=0, 
    	@include_event_description_in=1, 
    	@category_name=N'[Uncategorized]', 
    	@job_id=N'00000000-0000-0000-0000-000000000000'

/****** Object:  Alert [Error - Severity Level 25]    Script Date: 03/05/2009 08:22:07 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Error - Severity Level 25', 
    	@message_id=0, 
    	@severity=25, 
    	@enabled=1, 
    	@delay_between_responses=0, 
    	@include_event_description_in=1, 
    	@category_name=N'[Uncategorized]', 
    	@job_id=N'00000000-0000-0000-0000-000000000000'

/****** Object:  Alert [Warning 825: Read-Retry Required]    Script Date: 03/05/2009 08:22:16 ******/
EXEC msdb.dbo.sp_add_alert @name=N'Warning 825: Read-Retry Required', 
    	@message_id=825, 
    	@severity=0, 
    	@enabled=1, 
    	@delay_between_responses=0, 
    	@include_event_description_in=1, 
    	@category_name=N'[Uncategorized]', 
    	@job_id=N'00000000-0000-0000-0000-000000000000'

go

Notifications

I use Database Mail and a DBAs operator which maps to a Windows group that contains me. That way, when I'm away, we can put my stand-in into that group and everything goes to them as well. My Agent jobs only notify me when they fail because I want to know when something goes wrong but I don't want to be emailed all day to tell me it's all fine. I rely on a scheduled Reporting Services report to monitor that my jobs are all running.

I like to have SQL Server save 30 instead of the usual 6 errorlogs. And configure tempdb to use one datafile per cpu core