What’s Database Drift?

The first time I heard someone mention drift at Redgate, it made sense to me. After all, in the context of the schema of your database, I can see the code “drifting” away from what you expect. Someone noticed this was a problem when implementing continuous delivery and DLM Dashboard was born.

I grew up on the water, and I learned that if you don’t anchor these things down, they move.

drift1

In a database, our code can be the same way. Actually, all code is like this, which is why most professional software developers learn to use a Version Control System (VCS). Code changes, and you want to have some way to anchor down the code you need to work with.

We don’t want to prevent changes to a database with some heavyweight process. I’ve worked in those places, and it’s an impediment to getting business done. On the other hand, we can’t have uncontrolled changes. I’ve been in those environments, and apart from the instability for the business, this creates bad relationships between technical people.

That’s one reason we build DLM Dashboard at Redgate Software. We recognized that tracking and being aware of what’s changed is important. It allows you to respond, and respond quickly if needed, but doesn’t prevent changes.

More importantly, you can track down those items that might have drifted to a new location and feed the changes back to development. Even if they appear to be all spread out.

Vietnamese_fishing_boats

In Practice

How does this work? Well, download DLM Dashboard and get started. It’s free and for each installation, you can monitor up to 50 databases.

Once that’s installed, you can add the various databases that make up your pipeline for monitoring. For example, I’ve got a SimpleTalk pipeline with four databases in my environments, as shown here:

2016-07-21 13_55_39-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

There are for the following purposes

  • Integration – Gets an integrated build of the database from CI, with all developers changes to the head of the branch of development.
  • Testing – A particular version of Integration is deployed for additional testing.
  • Acceptance – A pre-production environment, where the database upgrade is practiced.
  • Production – The live, final environment for database changes.

I could have other databases in my pipeline (DR, training, beta, etc.) , or even multiple databases at each stage. However, this is a fairly simple pipeline.

Now, let’s suppose I realize we have an issue in production. I need to change a stored procedure that’s got a bug. Someone forgot a WHERE clause in this procedure, and I need to make an emergency fix.

2016-07-21 13_58_36-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

Let me add code and  recompile the procedure.

2016-07-21 15_05_03-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

This is a new version of my procedure on production (note the RED outline, thanks SQL Prompt). I have drift. My schema is not in the same state as it was. Production now has a red note, with the drift image.

2016-07-21 15_21_42-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

This change isn’t recognized by DLM Dashboard. The version of each object, of all the code, is stored and tracked. The previous version was 50.17, which is the version of Acceptance. If I click the “Review”, I can see the details. At the top, I see the old schema version, as well as a summary of what changed and by who. I can name this schema if I want, and add comments about the changes.

2016-07-21 15_39_20-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

Scrolling down I can see the actual code that changed. Using the style that SQL Compare and other tools use, I see the additions to the code highlighted, showing what was in the previous version as well.

2016-07-21 15_39_29-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

At this point. I can copy this code, put it back to development, and run it through the same cycle as all my normal development changes, including the testing that might ensure my “hotfix” is actually the change that was needed.

Depending on my development process, this might be deployed as a new change, or it might be ignored when the next deployment takes place. Either way, my process should be able to handle this appropriately. If not, I need to better manage my development.

I think DLM Dashboard has a number of uses, but certainly the capture of changes to production, ensuring you’re aware of what changes, is a valuable one.

68_dlm dashboard red wfill

I hope you’ll download it today, since it’s free, as in beer, and it’s worth a test in your environment to see how it can help you.

Photo Credits

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

4 Responses to What’s Database Drift?

  1. rsterbal says:

    How did Redgate come up with a 50 database limit?

    • way0utwest says:

      Not sure. I think some research. The limt used to be 10 w/ 30 on registration. I think 50 is a lot for a single dashboard, which the developers already don’t like.

  2. Doug Tucker says:

    I need a dashboard capable of monitoring 350 databases/server across 11 production servers, but the 50 database cap might be OK for some of my ancillary databases so I’ll be giving DLM Dashboard a try.

    • way0utwest says:

      In this case, I think we’d suggest a few instances of DLM Dashboard installed, organized by the software development streams/pipelines you have.

Comments are closed.