One thing I've noticed as a contractor is the inconsistencies of the configurations of SQL Server installs. So now I ensure I always follow some basic steps, when I arrive at a new company, some of these apply to new server installs as well.

Backups

Are there any database standards?:-

• Scripts or maintenance plans? Is there any standards or do I set something up? Either way set up regular backups of all databases.
• Check if the company has any documentation on this, and ensure that if servers are backed-up locally that they are backed up prior to a file backup being taken off the server (is this happening? with SQL 2005 you can mirror a copy of the backup, I use this as another way to ensure a copies of backups are available off the server itself if other backup tools aren't performing this function).
• Test you can restore backup files that are being kept, especially important you know how to do restores if third party tools are being used, you don't want to try a restore for the first time when a disaster happens.
• I check disk space and documentation to help make the decision on how long local files are kept. If I'm fortunate I might get some documentation from users dictating what service availability should be and how much data loss could be lived with if the server fails - I must admit I've never found this document yet, unless I've created it, but I live in hope. I would use this document to decide on the frequencies of backups, retention and whether the mode should be simple or full. It is up to the business to specify database business decisions.
• I check all the databases to see what mode they are running under, if any databases are set to Full, I ensure that the backup jobs include backing up the logs, as well as the databases.

Maintenance Tasks

Reindexing/deleting files, retaining history

• Check how frequently you should be running database maintenance tasks such as re-indexing.
• Review fragmentation and impact of indexing jobs
• Is performance an issue, have you got a baseline
• What are the user issues re performance, are there any issues? Any priority
• Are the service packs up to date?
• Know the configuration of any mirroring or replication jobs. Script them and keep a backup. Likewise with the configuration of Custer servers or any other complex component, ensure you are aware of the configuration and can reproduce it if its ever needed.
• If 2000 servers have DTS packages script them and store them as local files as a backup.
• Ask questions, seek out problem areas and prioritise a review of the issues.
• If performance is an issue, one of the many things to check is the fill factors, and be aware when you use SQL Server 2000 maintenance plans and accept the defaults, it sets all tables in the plan to a fill factor of 90%, but SQL 2005 defaults to 100%, so its often missed as an area to look at in SQL 2005. - It can make a big difference to performance on busy servers.
• Put in place disk space monitoring jobs, scripts on this site can get you started with this.

This list can be endless, but concentrate on the necessary first then move on to the nice to have jobs after.

Alerts

Check are alerts set up?

• I check whether any alerts have been added, SQL 2000 adds some basic alerts as standard but SQL 2005 doesn't. I add the normal SQL 2000 alerts as well as a few others that I believe may be useful. The script is attached to this article You may want to review the alerts later depending on what the server will be used for and whether you have any issues that you need to monitor for. The script runs for SQL 2000 servers as well, renaming the original out of the box alerts.

Adding Database Mail

Is database mail set up?

• I have attached a script that will set up database mail and send a test email. IMPORTANT: Before running the script, search and replace the ServerName in the script to the name of the server you are installing DB Mail on, so that alerts sent include the server name, and alter the mail server, and email address, where marked.
• Lastly set up the agent to fire alerts, by enabling the profile you just created:- From SSMS right click on SQL Server Agent and select Alert System. Click on Enable mail profile and point to the Mail profile created in the previous script.

Enabling the alerts to fire to Windows event log

Is MOM being used?

• By default SQL Server 2005 will not fire alerts into the application log. This can be an issue if you use MOM as it won't pick them up. Run the script from the following site to fix, even if you don't use MOM to monitor your servers, it may be useful to have a record of errors in the application log anyway:- http://weblogs.sqlteam.com/tarad/archive/2006/12/14/51945.aspx

Ensure job failures and alerts fire an email from Database Mail

Should alerts and failures send you an alert?

• The script attached adds a DBA operator using a team mailbox, adding all server jobs and alerts. Alter the operator name and email address as marked.

I do a number of other things relating to monitoring events using Reporting services, but I'll save them for another day. If you want to add anything to the list I would be grateful for the input.