Filtering Objects with DLM Dashboard

I’ve been looking at some of the features of DLM Dashboard as I go through work building database development pipelines. In this post I wanted to cover one of the lesser used features, filtering objects.

Note: DLM Dashboard is a free tool from Redgate Software. Use it to monitor the schema of your development, test, and production databases and get notified when changes are made.

Why would you filter objects if you’re auditing changes? Well, this isn’t really an audit per se. It’s more a tracking mechanism that provides auditing, but sometimes you don’t want to audit everything.

For example, in my database pipeline, I have these databases:

2016-08-23 12_04_29-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

As much as I want to track down the changes to code, there are things I don’t want to deal with. For example, I don’t care about users. I (properly) use roles to manage security, and the users in each environment aren’t going to be deployed from one database to the other. More importantly, we don’t need to track them. So let’s stop.

If I go to the right side of my pipeline, I can see a “Filter objects…” link.

2016-08-23 12_04_49-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

When I click that, I get this popup on the left, where I can upload a filter file. The filter file is the same format that SQL Compare uses, and indeed, the easiest way to create one is with SQL Compare. I’ll do that.

2016-08-23 12_05_09-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

I’ll run SQL Compare and then grab two random databases. It doesn’t really matter since I don’t care about the comparison. Here I’m comparing a database to itself.

2016-09-05 08_24_52-New project_

When the comparison finishes, I can go to the left side and set filters.

2016-09-05 08_25_26-SQL Compare - New project_

There are a lot of choices here, but I’ll simply remove the checkbox on “Users”.

2016-09-05 08_25_43-SQL Compare - New project_

Once I do that, I can save the filter file. There’s a save icon near the top of the filter dialog.

2016-09-05 08_26_00-SQL Compare - New project_

Clicking this gives me a dialog to enter a file name.

2016-09-05 08_26_15-Save As

Now I can just close SQL Compare. By default, these filter files are in %My Documents%\SQL Compare. Once I’ve saved that file, I can see it in the Windows Explorer.

2016-09-05 08_26_38-C__Users_way0u_Documents_SQL Compare_Filters

Now let’s go back to DLM Dashboard. I can browse to my filter file and load it. Once I do that, the filter is applied, and my main page notes that I’ve got a filter applied on that pipeline.

2016-08-23 12_10_49-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

There is a warning here, that notes the change of a filter is actually a drift detection change. This means the schema is not recognized. The same things happens if you remove a filter.

2016-08-23 12_13_51-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

If I look at the details, you’ll see the filter has been applied.

2016-08-23 12_14_07-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

Note: This filter is only applicable to those database in this pipeline.

Now, let’s test this. Users are ignored in this pipeline, so if I add a new user to the database, it shouldn’t affect the system. Let’s do that.

2016-08-23 12_13_06-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

SQL Source Control detects this (though I could filter it here as well).

2016-08-23 12_13_30-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

After I commit the change, it flows through the CI process and gets deployed to the Integration database. I can see the login here:

2016-08-23 12_16_47-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

However, I don’t see drift.

2016-08-23 12_16_56-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

You’ll have to trust that I named the drifted schema this, but what if I include a few changes? I’ll add a new procedure and commit it to my VCS. The CI process runs and this is deployed to integration. Now I can see a change in Integration.

2016-08-23 12_20_08-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

This is the same schema I named in Development (I know, I should use numbering). It’s marked as a change from the CI process, and I need to acknowledge that.

The details of the change:

2016-08-23 12_20_18-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

The history, after I’ve Acknowledged the changes

2016-08-23 12_20_48-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

Filtering Helps

When you’ve got environment specific items, or things that you want to exclude from tracking, filtering works. These might be schemas controlled by other groups or a third party. This might be security information. This could be anything.

By using a filter, you can reduce the noise. By deploying these filters throughout your DLM process, in SQL Source Control, in DLM Automation, in DLM Dashboard, you can limit the extra information that isn’t necessary for you to view.

Getting Started

You can start using DLM Dashboard for free today. Download a copy, at no charge, and monitor up to 50 databases from a single installation. Or install multiple instances to watch more databases.

I think you’ll find DLM Dashboard is a handy tool for tracking those development efforts you want to be sure are deployed completely to downstream environments, while ignoring those that aren’t important.


About way0utwest

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