Voice of the DBA

Author: way0utwest

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:

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.

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.

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.

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

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

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

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

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.

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.

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.

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

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.

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

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

However, I don’t see drift.

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.

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:

The history, after I’ve Acknowledged the changes

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.

August 31, 2016
Rogue Algorithms

This editorial was originally published on Sept 5, 2-12. It is being re-published as Steve is on vacation.

I have always thought that computers do some amazing things, but they tend to do what we tell them to do, even if what we tell them to do is not what we want them to do. In those cases we usually find computers are helping us make mistakes faster than ever before. As we automate and link more and more systems, this can become a bigger and bigger problem in the world.

I ran across this short link on a rogue algorithm that caused a fluctuation in a number of stocks. It looks like this was a case of poor analysis and poor QA at one firm, but imagine if we had some type of “update” released to a large number of companies? This type of problem could cause a dramatic short term fluctuation in the stock market, which shouldn’t affect most of us in the long run, but you never know. If this caused a company to go out of business, and they were the company holding your retirement savings, you might feel differently.

This isn’t necessarily a problem that affects just financial institutions and their custom software. This is potentially a problem in any business that writes its own algorithms. That includes many of our companies, and many of us. We write queries, reports, and develop algorithms that help “the business” analyze data and make decisions that can affect our companies.

This is worrisome to me, especially when we have companies that press for more and more analysis, with algorithms written more and more quickly to respond to business events. Success in this area often depends on the developer having an understanding of the nature of the business and the meaning of data, and strong working relationships with the business analysts. That comes over time, and is easily lost when employee turnover is too high.

I hope that companies are learning that there is value to retaining employees, especially those that work with data and have invested effort in learning more about their business. Unfortunately I think that it take a disaster like this for some managers to understand the value of retaining employees and their knowledge. Even more unfortunate is the fact some managers never learn that.

August 31, 2016
Finding out Who’s Touching Your Database at DevConnections

I’ve got a session this fall at the IT/DevConnections conference (Oct 10-13, 2016) called “Who’s Touching My Database?” This is something I’ve worked on for a few years, but never really gotten it to the point where I was ready to deliver it.

Earlier this year, with all the security issues I’ve seen across the last couple years, and having had some questions from customers of Redgate, I decided to finish this talk off. I submitted it to DevConnections and started trying to focus the talk down to showing ways in which you can audit the activity on your database server.

I’ve worked in a few environments where security was taken very seriously and auditing was critical to proving we were properly managing our systems. These days I slowly see the importance of security and auditing growing to all industries, becoming more and more important. I hope to showcase the flexibility and possibilities (As well as drawbacks) of tracking activity on your database servers.

If you’re looking for some training, some inspiration, and are a full-stack developer, or multi-technology IT worker, think about coming out to DevConnections. There are a slew of technologies being covered (VS, ASP.NET, C#7, Azure, PowerShell, AWS, SQL Server, Sharepoint, Exchange, Hyper-V and more).

Register today, use 500SPKR to save $500, and I’ll see you in Las Vegas this October.

August 30, 2016
Avoiding Stored Procedures

This editorial was originally published on Aug 13, 2012. It is bein re-published as Steve is on vacation.

I ran across this piece from a developer on why he avoids stored procedures and thought it made some good arguments. The primary thrust of the piece is that ORM tools (Object-Relational Mapping) have evolved to handle most of the requirements of many applications. They also save a ton of development time since so many stored procedures written are simple CRUD type operations.

In many cases I agree. Having developers write stored procedures is silly and a waste of time. Procedures that select a few fields, or that update a table based on a primary key are mind-numbingly simple to write, but they take time to get in place. That’s time a developer isn’t spending thinking about the application and logic. Plus with any ORMs and tools like LINQ, you can write one line of code and let the ORM handle all of the work of getting or storing the data. Good points, and in many cases that’s correct. If you do mostly CRUD type work, this is a good reason to perhaps avoid stored procedures and let a tool do the work for you.

Unless you use a different tool. There are plenty of tools, most of them free, that will generate that CRUD code for you. A few templates or snippets will handle the front end side of the call as well, building code to call stored procedures. If you’re actually typing this stuff over and over, you are wasting time.

My brain started to wander when I saw “A database should be limited to the role of a persistence layer” which is silly sounding when you move beyond CRUD operations. It completely shut off when I saw “your stored procedures would need to be re-written in order to migrate to MySQL, Oracle or another database” since I think this rarely happens. If it does for you, fine, but the vast majority of apps never leave their initial database.

There are benefits in ORM tools, but you need to understand how the ORM works, what it’s strengths and weaknesses are. Blindly following the basic pattern for your state-lookup-data-editing dialog for all reporting screens is a sure way to cause yourself some problems. Allowing the ORM to define your relational database, without spending some time thinking about the benefits of good database design and proper modeling is asking for performance problems, or even integrity issues.

ORM tools are just that tools. Used well, they can perform admirably, but just as I don’t use a hammer to drive a screw into wood, don’t depend on your ORM handling everything database related for you in an efficient manner.

Steve Jones

August 30, 2016