Thursday, August 20, 2015

5W1H for DBA's Database Health Check Role (Part I)

A DBA usually needs to perform activities of maintaining healthy database servers.

To simplify the road map for DBA's health check role, we present the outline of the task by the famous Five Ws as below:
  • What is a database server health check?
  • Why does a DBA need to perform the check?
  • When should a DBA perform health check?
  • Which tools should a DBA use to perform health check?
  • Where to store information collected from the check?
  • How to use (analyze) data collected in a check?
1. What is a database server health check?

A database server health check is a routine performance assessment, audit or analysis on a collection of metadata metrics. For MS SQL Server, we may have two types of metrics:
  • Windows performance monitor counters (real time & log data) related to server's resources.
  • Dynamic management objects (DMO) (i.e. views and functions) for SQL server instance. There are server-scoped and database-scoped DMOs with different permissions.
 2. Why does a DBA need to perform the check?

 The reason is straightforward, performing health check can help DBA ensure the performance, security, stability of the database. It will also be helpful before auditing, migration, upgrading, backup, etc.

3. When should a DBA perform health check?

Once an initial health check is done, it should be repeated regularly. Follow-up health check usually will be faster.

4.  Which tools should a DBA use to perform health check?

First, a DBA needs an inventory list on server landscape questions as below:
  • Where & what are the servers? What are their domains?
  • How can you connect the servers? (SSMS, RDP mstsc)
  • Version, build, service pack, etc.?
  • Type of authentication (Windows or SQL)? 
  • Clustered? virtual or physical?
Second, a DBA needs to develop a systematic checkpoints checklist about data to be collected. For example, we can categorize the checkpoints as:
  • Resources & State of Health: CPU, memory and disk I/O. Usage, up-time status, etc.
  • Configuration: Server info, tempdb
  • Query performance: Blocking, locking, and deadlocks.
Third, MS SQL Server itself is a fully featured DBMS, in which we could find tools like:
  • Activity Monitor
  • Activity and performance reports from SSMS + Performance Dashboard Reports
  • T-SQL, DMOs (system views, functions, stored procedures) 
  • SQL Trace/Profiler/X-Events
  • PowerShell
  • Management Data Warehouse (MDW): 2008 + : 
    • SSMS > Database Server > Management> Data Collection
Fourth, there are some additional Windows and Microsoft tools:
  • System and event logs
  • Performance Monitor (Control Panel > System & Security > Admin Tools)
    • SQL Server Performance Monitor (Data Collector): setup ODBC first.
  • Performance Analysis of Logs (PAL)
  • SQL Server Best Practices Analyzer (2012) 
We have summarized how a DBA can maintain healthy database servers in a 5W1H way. I discussed the first 4W, and will present the last 1W and 1H in Part II.

5. Where to store information collected from the check? (To be continued)

6. How to use (analyze) data collected in a check? (To be continued)

No comments:

Post a Comment