x

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 '09 at 07:25 PM in Default

Tim gravatar image

Tim
35.5k 32 40 138

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

2 answers: sort oldest

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.

more ▼

answered Nov 20 '09 at 06:25 AM

David Wimbush gravatar image

David Wimbush
4.7k 28 29 31

(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 '10 at 09:54 AM

Steinar gravatar image

Steinar
1.7k 3 4 6

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 '11 at 10:13 AM Tim
+1 Agreed. Very community-spirited of you to update this, Trad.
Feb 03 '11 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1816
x158
x66
x35

asked: Nov 19 '09 at 07:25 PM

Seen: 2390 times

Last Updated: Nov 19 '09 at 07:25 PM