Real World DBA Episode 5 – The DBA Checklist


http://edge.technet.com/Media/Real-World-DBA-Episode-5–The-DBA-Checklist/ 


This week:


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 .


 


Feature:


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.


 

















































































Frequency


Task


Description


On System Implementation


Configure the system


Check the optimal settings for your system and configure accordingly


On System Implementation


Install and configure SQL Mail or Database Mail


Install and configure SQL or Database Mail


On System Implementation


Automate Maintenance and Logging


Set up SQL Server Agent to process all maintenance and logging tasks possible.


On System Implementation


Document system


Create paper or electronic documentation on the system state, check in to your chance control management system.


Daily


Check Backups


Check the backups using the logs your process creates.


Daily


Check Event Logs


Examine the System, Application and Security areas in the Windows Event logs.


Daily


Check SQL Server Logs


Check the most recent log file in SQL Server.


Daily


Check Drive Space


Ensure that the drives that store database logs, data files and operational files have sufficient space.


Daily


Check Jobs history


Check the history of each job to ensure that it ran correctly.


Daily


Check Agent Logs


Check the agent logs on your system.


Weekly


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.


Weekly


Test Restore Operations


Restore the database backups to a test server.


Monthly


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.


Daily; Weekly; Monthly; Yearly


Archive Backups


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


Data Archival


If you have data that loses it’s immediate value, create a “rollup” strategy to aggregate the data to save space.


On-Demand


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.


 


Web Link:


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.


 

Comments (1)

  1. Bill Richards recently wrote a nice article for SLQ Server Central. You can read the original article