This stairway series covers the process of setting up DLM Dashboard to monitor your databases, configuring various environments and signing off approved schema versions. DLM Dashboard is a free tool that monitors the schema of up to 50 databases, and sends an email notification each time an unauthorized schema change is made.

When the schema of a monitored databases changes, DLM Dashboard records which object was changed, when it changed, and the user responsible for making the change. Once you’ve reviewed the changes, you can approve them. This adds the new schema to the history of approved schema revisions, which are stored in a separate database, and accessible from within the tool.

Whether your server has a couple of databases or a whole host of different environments, you probably want to know if the schema changes. Wherever there’s more than one person making changes to a database, the this task becomes more and more difficult with scale.

DLM Dashboard is a free tool that provides a goto place for checking out the current state of the database schema on any instance against previously reviewed and approved schema revisions. The tool can track changes for all databases in a software development pipeline, or just a single database on a production instance. If there are any changes, DLM Dashboard alerts you of the details and allows you to review the changes and audit who made the changes and when.

Figure 1.1: DLM Dashboard monitoring Adventure pipeline, no drift or updates.

Reviewed versions of a schema are shown in green. When a monitored database schema deviates from an acknowledged schema revision, or changes to a different, previously acknowledged schema, the badge on the dashboard changes to reflect this:

Figure 1.2: Adventure_Dev with Drift (left) and Update (right).

The red DRIFT badge indicates that DLM Dashboard has never seen this particular version of the objects in the database. Orange indicates that there has been a change, but this change was already detected and reviewed, and named, in some database that DLM Dashboard monitors.

Selecting Review opens a more detailed page with specific object changes, along with who changed them and when. It also allows you to acknowledge the changes, adding the new schema revision to the list of approved revisions.

This level outlines some of the main reasons why you should consider monitoring your database schema, the benefits of doing so, and explains how following the rest of the stairway can help you to improve your development process.

Peace of mind

Schema changes can cause all sorts of knock-on effects and issues, and these issues often don’t appear until sometime way down the line. Manually reviewing every schema change before it goes ahead gives you control and peace of mind, but can be difficult to keep on top of. With large numbers of changes by different users, across different databases, the task can slow down development and impair teamwork.

One of the more common issues involves changes made in production databases in response to some issue. Without ensuring those changes are captured and introduced in development environments, future deployments may fail. Having a system in place to track all changes and capture the code can reduce issues that might arise from emergency hotfixes.

With DLM Dashboard set up in your environment, you’re notified every time a database schema deviates from an authorized revision. The change has already happened, and if you’re happy with it, you can simply acknowledge the new schema as a recognised revision, giving it a version number or name.

If a schema changes to a previously acknowledged revision, a notification won’t be triggered, but the change is still recorded. Wherever you are, you can log in and review the schema change: which objects changed, when they changed and who made the change.

The Audit Trail

Database security is becoming more and more important all the time. With several data breaches making front page news in the last couple of years, many organisations are scrambling to keep their data secure. A reliable audit trail can identify potential threats before they happen, or trace the source and severity of data leaks immediately after they occur.

Some of the most common security breaches exploit default and weak passwords, buffer overflows or excessive user/group permissions. Most database audit tools build a detailed history of transactions; each time a user accesses data, the interaction is recorded and forms part of the audit trail. This kind of information is invaluable in preventing insider threats, where the perpetrator is a company employee or hired contractor with access to the data.

An angle that can be often overlooked in the audit process, however, is unauthorized changes to the database schema, which include security principals. Through SQL injection, potential attackers can add rogue users to a database and access sensitive data. DLM Dashboard detects changes to users and logins (shown below), meaning any unauthorized changes are flagged up immediately.

Figure 1.3: Changes highlighted with the date/time and user that made the changes.

DLM Dashboard can send an email notification of a schema change to quickly highlight potential security threats, allowing you to take action as soon as possible. DLM Dashboard also builds an intelligent audit trail, painting a picture of how each database schema has changed over time. This allows retrospective analysis of schema changes and security across all your databases.

Environment Overview

DLM Dashboard takes some of the stress out of managing various evolving databases. Databases in DLM Dashboard are organised by server, category or pipeline, and can be assigned to customizable environments. This provides an overview of all of your databases and environments in a single place, bringing clarity to your overall process.

You can quickly check which revision each schema is currently at, indicating development progress across environments. This makes it easy to track deployment progress across all environments, and gives you confidence that the last thing you deployed is still what you’re running.

Since many software development teams typically make changes on two, three, or more instances before deploying to a production database, there is the chance that steps are forgotten or missed. By grouping databases into pipelines that mimic the flow of changes through various environments, it becomes easy to determine if all the changes made in a development system have been deployed to other databases.

For example, we can see below that the schema changes made in Version 1.1 have been deployed to Testing and Pre-Production environments, but production is still running Version 1.0. In addition to this, we can see that work has continued on the Development environment, which is now at Version 1.2

Figure 1.4: A pipeline of databases showing the state of databases in different development and live environments.

Monitoring and Measuring

DLM Dashboard allows you to audit and track the changes to all the code in your databases. Tables, views, stored procedures, functions, users, roles, and more are tracked and versioned according to the needs of your organization.

By having this independent monitoring system, administrators and developers can be sure that they are aware of all changes being made, and they can be alerted to unauthorized or unexpected changes.

Implementing users and groups, which we’ll cover in Level 4 of this stairway, allows you to restrict which users in an organisation have access to the schema information stored in DLM Dashboard. Administrators can restrict access to the schema entirely, and decide which users and groups can authorize new schema revisions.

Conclusion

This level highlights some of the main reasons why you should consider monitoring your database schemas and tracking code changes. In future levels, we’ll look at each topic in more detail and address the ways in which DLM Dashboard can support your development process.