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?
asked Nov 19 '09 at 07:25 PM in Default
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:
Right click on the server in the Object Browser and select Properties.
Tick the Boost SQL Server Priority box on the Processors tab. (Assumes this server is dedicated to SQL).
Under Login Auditing, choose Failed Logins Only.
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.
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:
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.
answered Nov 20 '09 at 06:25 AM
I like to have SQL Server save 30 instead of the usual 6 errorlogs. And configure tempdb to use one datafile per cpu core
answered Jan 18 '10 at 09:54 AM