Real World DBA Episode 5 – The DBA Checklist

Real World DBA Episode 5 – The DBA Checklist

Rate This
  • Comments 1

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.

 

Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
Page 1 of 1 (1 items)