SQL 2008 Standards

We are looking to upgrade to SQL 2008 and have to document 2008 Standards prior to doing so. What are some things others are doing as a standard on each server. Things such as ~ Turning on backup compression ~ Maintenance jobs ~ Any lock down scripts ~ Etc

Basically after an initial install of SQL 2008, what are the standard things you do, configure on each instance of 2008?

more ▼

asked Nov 19, 2009 at 07:25 PM in Default

avatar image

40.9k 39 94 168

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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'



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.

more ▼

answered Nov 20, 2009 at 06:25 AM

avatar image

David Wimbush
10.7k 31 34 43

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jan 18, 2010 at 09:54 AM

avatar image

1.7k 4 6 10

Was just looking back over some of my older questions and wanted to point out that 1 data file per Core is not the standard anymore with the 4 and 6 core chips out now. The adage IT DEPENDS" comes into play now. It tends to be running 1 file per 2 or 1 file per 4 cores depending on the number of cores. There are always exceptions but the old school of 1 per core is now obsolete.

Feb 02, 2011 at 10:13 AM Tim
  • Agreed. Very community-spirited of you to update this, Trad.

Feb 03, 2011 at 12:32 AM David Wimbush
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 19, 2009 at 07:25 PM

Seen: 2847 times

Last Updated: Nov 19, 2009 at 07:25 PM

Copyright 2018 Redgate Software. Privacy Policy