As an IT department, it is crucial to ensure that your database administration function is running effectively. But how do you know if you have implemented the necessary operational processes? And what are these “effective” operational processes?
While there are plenty of resources available on SQL Server audits, including best practices and tips, there is a lack of information specifically focused on operational audits. This article aims to fill that gap by providing a framework or checklist to objectively assess the effectiveness of your database administration operations.
Defining the Audit Items
Before we delve into the framework, let’s define the purpose of an operational audit. An operational audit is conducted to rate an organization’s database administration area against known best practices in an objective manner. To develop our checklist, we can draw inspiration from the Microsoft SQL Server 2000 Operations guide, which outlines key operational processes:
- Change, Configuration, and Release Management
- Security Administration
- System Administration
- Monitoring and Control
- Capacity and Storage Management
- Problem and Incident Management
- Service Management
In addition to these processes, we believe that SQL Server licensing should also be included in an operational audit. Licensing is often misunderstood, and improper licensing can lead to compliance issues or unnecessary expenses.
Creating the Audit Report Format
Now that we have a framework, let’s discuss the best way to present the information gathered during the audit. We recommend adapting the format used in Graham Thompson’s paper on Auditing a SQL Server 2000 Server. The audit report should include the following sections:
- Overview and Identification
- Checklist
- Summary
The overview section provides a brief description of the purpose of the operational audit, an overview of the department or company (including IT infrastructure), and a list of all SQL Servers included in the audit.
The checklist section is a concise one-page table that includes the following columns:
- Reference: Provides a description and URL for the operational check, including a link to the corresponding chapter in the SQL Server 2000 Operations guide.
- Objective: Describes the objective of the operational process.
- Risk: Documents the risk of not having the operational process in place.
- Compliance/Expected Results: Documents the expected outcome of the operational process.
- Review Details: Describes how data will be collected for the review.
- Review Results: Documents the review results as Compliant, Partially Compliant, or Non-Compliant.
- Reviewer Notes: Provides the reviewer’s comments and more detailed information on the results.
The summary section includes a one-page executive summary of the findings. This can be presented as bulleted text or a dashboard-like scorecard with red, yellow, and green checkmarks to indicate compliance levels.
Collecting the Data
To conduct the audit, data will be collected through interviews, reviewing existing documentation, and querying system configurations. Some processes may require only interviews and documentation review, while others may require scripts or other automated processes.
For processes that can be automated, it is recommended to use scripts or utilities that work best for your environment. Microsoft’s SQL Health and History Checker (SQLH2) is a free tool that can collect a lot of information on multiple SQL Servers quickly. However, it relies on Reporting Services for producing reports, which may not be lightweight. Choose scripts and utilities that suit your needs and thoroughly test them in your environment.
Executing the Audit
When executing the audit, it is important to consider the perception of the word “audit.” To avoid any negative connotations, you can replace the term “audit” with “review” or “assessment.” The goal of the audit is to report findings, not to create a run book or fix issues immediately. Addressing issues can be planned as follow-on work.
Constructive criticism should be delivered with tact and diplomacy, as nobody likes to be criticized. It is worth noting that most database administration departments would not pass all key operational processes, including our own. However, striving towards adopting best practices will make it easier to support database operations in the long run.
Conclusion
This article has provided a framework for evaluating the effectiveness of database operations using existing guides and best practices. By using this framework, you can extend your evaluation to include additional items within the key operational processes outlined. The operational audit template will serve as a useful method for evaluating an organization’s database operations and identifying areas for improvement.