In the news, learn how you could win an Xbox, just for finding a bug. In this week’s feature, you’ll learn how to create your own DBA checklist. The web link shows you where all the SQL Server samples have gone, and the tip of the week is on side-by-side installations of SQL Server.
News this week:
You could win an Xbox, just for finding a problem in the next release of SQL Server. Microsoft will enter you for a drawing to win an Xbox if you report a bug in SQL Server 2008. You can even get a copy of SQL Server 2008 already installed on a Virtual Machine, so you don’t have to install anything on your system at all. More information is at tinyurl.com/2ldons .
There is a lot to remember when you’re a Database Administrator. Books Online has over 67,000 pages of content, and most of us have lots of web links, books, magazines and more to read and understand. In fact, there’s so much to learn and implement on your SQL Server that it is easy to get lost in the flood of information, and forget to do something important.
One of the ways I deal with this problem is to make checklists.
I make a lot of checklists – for just about any task I’m doing. My uncle once told me that “a short pencil is better than a long memory”, so I rely on these checklists to make sure I’ve done what I need to do. Today I’ll cover the basic DBA checklist I’ve created, and then in other podcasts I’ll cover a few more.
Before we start, you should know that this checklist is pretty small – there aren’t a lot of items here. I start with this basic checklist and then add to it as I work in that installation. Each situation will be slightly different, so I modify this list to show what I need.
The list is arranged by the frequency of the tasks, which again might change based on your situation. Some people do all of these steps every day, others in a more spread-out schedule.
On System Implementation
Configure the system
Check the optimal settings for your system and configure accordingly
Install and configure SQL Mail or Database Mail
Install and configure SQL or Database Mail
Automate Maintenance and Logging
Set up SQL Server Agent to process all maintenance and logging tasks possible.
Create paper or electronic documentation on the system state, check in to your chance control management system.
Check the backups using the logs your process creates.
Check Event Logs
Examine the System, Application and Security areas in the Windows Event logs.
Check SQL Server Logs
Check the most recent log file in SQL Server.
Check Drive Space
Ensure that the drives that store database logs, data files and operational files have sufficient space.
Check Jobs history
Check the history of each job to ensure that it ran correctly.
Check Agent Logs
Check the agent logs on your system.
Review Security and Access Points
Check the surface area of the server, meaning any entry point for network access, check who has failed logins, and review your security logs for any issues.
Test Restore Operations
Restore the database backups to a test server.
Check the efficacy of indexes
Check, document and report on indexes in the system to see if they are being used, and used effectively, as well as any you are missing.
Daily; Weekly; Monthly; Yearly
Perform database backups
Create a plan that executes an automated backup for all of your databases, including the system databases.
Determine when the data is no longer needed locally and send tapes or other media off-site for storage.
Daily; Weekly; Monthly; On-Demand
Update Indexes and Statistics
Based on how long your maintenance window is, you should develop a comprehensive reindexing strategy. This is part of overall maintenance.
Daily; Weekly; Monthly; On-Demand; Yearly
If you have data that loses it's immediate value, create a "rollup" strategy to aggregate the data to save space.
Update Change Management Scripts
As your server and database objects change, script them back into your change control management system.
To be sure, there are other items that you might put on your checklist. I encourage you to use this one as a starting point to customize for yourself.
If you’re looking for the sample databases and code samples on the installation media for SQL Server on the download center, you’ll find they’ve moved to a new website called CodePlex. Microsoft has decided to move all SQL Server samples there, and not include them on the installation media. You can find the new samples install location at tinyurl.com/dhu6b.
Tip of the Week:
If you want to host both SQL Server 2000 and 2005 (or any down-level and up-level versions, for that matter) you always want to install the earlier version first. The reason is that the later installations replace earlier libraries, such as the connection libraries in the case of SQL Server 2005, with later versions.
Bill Richards recently wrote a nice article for SLQ Server Central. You can read the original article