Introduction
Okay. You have a new server and a fresh install of SQL Server. What are some of the things that you need to do before you sign off and hand it over to production? What do you need to do to keep the server running smoothly? Here is a list of things I keep in mind before handing a server over to production.
Preproduction Checklist
1. Make sure SQL Server has the latest patches.
This is pretty self explanatory, but before a server gets turned over to production make sure it has the latest patches. There is no need in causing downtime later because you forgot to patch the box.
2. Enable full auditing on both Successful and Failed Logins.
Under the Security Tab in the Server Properties, there is an option to audit both failed and successful logins. By default, this is set to failed logins only, but I like to be able to correlate events on the server with who may have logged in around that time.
3. Increase SQL Server log history threshold in order to maintain logs for a longer amount of time.
Under the Management folder, right-click on SQL Server Logs and select Configure. By default SQL Server will keep 6 error logs before they are recycled. I increase this to 99, which is the maximum value.
4. Set a default path for data and log files.
Under the Database Settings Tab in the Server Properties, there is a place to set the database default locations. I like to change this in case a database gets created without specifying a location, it will not go in the install directory.
5. Set up Database Mail.
Instead of reinventing the wheel here, I will just point you to a couple of articles that I used to setup Database Mail. The following articles are script based, so they can be modified and included in your post install scripts.
http://articles.techrepublic.com.com/5100-10878_11-6161839.html
http://articles.techrepublic.com.com/5100-10878_11-6164310.html?tag=rbxccnbtr1
6. Determine Drive Structure and move system databases if necessary. Specifically, move the tempdb to its own drive if it is possible.
The system database that is most likely to cause disk contention and grow the largest is the tempdb. I typically try to put the database and log file on its own drive. Here is the MSDN link for moving system databases.
http://msdn.microsoft.com/en-us/library/ms345408(SQL.90).aspx
7. Create a maintenance database.
A maintenance database is a good place to store objects that are required to perform maintenance on the system. This may also be where you store things such as the nums or tally (http://www.sqlservercentral.com/articles/TSQL/62867/) table that are required for certain queries.
8. Create a job to update statistics.
I have heard a little on both sides of this. If you are using auto update statistics and also have a job to update statistics, you are overworking your server. But on the other hand only certain events trigger SQL Server to Auto Update. I fall on the side of forcing the update. Here is a good script that will force the update for all databases. Keep in mind all of these stored procedures unless stated otherwise, I put in the maintenance database.
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/31472/
9. Create a job to cycle the error log.
Whenever SQL Server is restarted, it creates a new error log. If the server stays up for a while, this can make for a very large error log. I create a job that runs on a daily or weekly basis that executes the sp_cycle_errorlog stored procedure in the master database. This will create a new error log without having to restart SQL Server.
10. Create a job to cleanup the Backup History from the msdb.
As backups are performed the information is logged to tables in the msdb. Over time this can cause the msdb to become very large. There is a stored procedure in the msdb that can be executed to remove this history called sp_delete_backuphistory. This accepts a date parameter and will remove any history prior to the given date. I create a job that runs the following command on a weekly basis.
DECLARE @DeleteBeforeDate DATETIME
SELECT @DeleteBeforeDate = DATEADD(month,-1,GETDATE())
EXEC msdb..sp_delete_backuphistory @DeleteBeforeDate
11. Create a job to maintenance indexes.
I don't like jobs that just go reindex everything in the database. I like to selectively reindex; only rebuild the indexes if they are beyond a certain fragmentation level. Here is a link to script I am currently using.
http://blogs.digineer.com/blogs/larar/archive/2007/07/30/smart-index-defragmentation-for-an-online-world.aspx
12. Create a job to run a DBCC CHECKDB to run against all databases.
A lot of times this is thrown in with the backup jobs, but I like to create a separate job that may be able to run at a different time.
13. Create a job to check for long running jobs.
One of the issues I have faced in the past is not being notified that a job is having issues because instead of failing it is just hung. I create a job that runs once an hour or so to look for jobs that have been running over x amount of time. If a job is found, then the long running job fails or sends an error notification. The following article can be referenced for details.
http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=AgentLongRunning&referringTitle=Home
14. Determine a backup and retention strategy.
There are several different strategies here. They differ by organization and even by server. The main point here is to come up with a solution and test it thoroughly.
15. Make sure each maintenance job has an output file in a standard directory.
In the advanced tab under the job steps create an output file. This will allow you to see the full description of the job execution. There is nothing more annoying than not being able to see why a job failed.
16. Determine the optimal memory settings for the server.
A number of factors affect the way the settings should be configured, but here are a couple of articles that may help.
http://msdn.microsoft.com/en-us/library/ms175581(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms190673(SQL.90).aspx
17. Run the SQL Best Practice Analyzer to determine if there are potential issues in the database environment.
18. You may also be interested in testing your disk drives using SQLIOSim.
http://support.microsoft.com/kb/231619
Conclusion
Most of the items above can be scripted and applied to the servers before moving to production. Come up with the script that best suits your organization and servers and moving a server into production should be a fairly simple process.