Voice of the DBA

Tag: Redgate

Quick #SQLPrompt Tips – Expanding Wildcards

I tend to try and get code on the screen quickly and then start to remove things. I’m a visual person, and it’s helpful for me to see some tables, joins, filters, and columns as I’m structuring a query.

One of the ways I work quickly is with SQL Prompt is that I’ll write a query, using the SELECT * to hold the place where columns will appear. Since I’m not always sure what columns exist in a table, using the asterisk allows me to complete a valid query.

However, I don’t want to leave the asterisk there. Let’s put the cursor behind it. As you can see here, a tip pops up.

When we hit Tab (or your completion hotkey), the entire column list expands. All columns, from all tables, qualified if necessary, according to my SQL Prompt settings.

Now I have a well written query, or if I don’t need all columns, I can easily remove those that I no longer want to retrieve.

This is a quick tip, one that doesn’t do a lot, but has the potential to make developers really think about all the data being returned in large queries with a SELECT *.

Give this a try the next time you find yourself writing a SELECT * query and then remove the columns that you really don’t need. You might also check out a similar piece I wrote for the Redgate blog.

If you aren’t a SQL Prompt user, then think about downloading an evaluation and becoming a more efficient T-SQL developer.

You can see a complete list of SQL Prompt tips at Redgate.

September 15, 2016
Down Tools Week 2016

Last week I went to the Redgate Software office in Cambridge, UK. I travel there a few times a year to meet with product groups and touch base with the other people in marketing. However, this trip was planned around Down Tools week, which is an event that Redgate has once or twice a year. This is similar to what other companies have done, like Atlassian ShipIt day, and I had the chance to participate a bit in one of the projects. It was quite fun, and a memorable experience.

The idea is that a project is pitched as an idea for a single week. These are usually ideas that aren’t worth funding as a large project, or would help the world somehow. A team comes together for a long week and has to showcase their work by Friday afternoon. There have been projects just to try something fun at Redgate and investigate something. We had a number of projects, including a charitable image recognition project for Waterscope. That one was really interesting, as some of the software and documentation improvements that were made will be pitched to their investors and taken our for field trials.

I got involved with the Rescue DLM Dashboard project. I like DLM Dashboard as a tool, but it needs some work and should provide more value. A team got together with the idea of seeing where we could add more value and make this a commercially viable product. We also tried to fix a few bugs and get some UX love for the tool. By the end of the week, we had integrated DLM Dashboard with a couple other projects, and had other items to work on. We did win a couple of the contests (best t-shirt, best presentation), but we still have a commercial brief to write and get approved before any more work will be done.

The project structure itself was interesting, with a daily standup at 11am, and teams of programmers working in pairs to add features. I didn’t do any coding, mostly because my C# skills are far below others, and I had other commitments during the week. I was in and out of the dedicated conference room, talking with the project managers and watching developers work through the coding. We had a few interns that worked with experienced developers, and it seemed that people worked well together, sharing ideas and solutions for issues.

I was impressed that the setup of everyone’s workstations, all moved to a conference room, connected, and with cloned git repos was done fairly quickly, with working builds for most people by Monday at lunch. It’s not as simple as one might expect to grab a new project and get a working build, especially on a complex piece of software, and it was fascinating to watch people debugging issues across web pages and local services. I was also pleased to see how open other teams were to lending us a person for a day or two in order to facilitate integrations or extend APIs.

Down Tools week is expensive, but it certainly could be done in different ways. An organization wouldn’t need to cater food every night. Pizza or other alternatives might be fun for some groups. However, I think this can be a great way to create some excitement for your developers, as well as investigate some research that might not otherwise be feasible to undertake. I don’t know that you need to make as big a production as Redgate does, but I’d encourage you to think about taking a week off from normal projects once a year and letting developers work on things that might excite them at your organization.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.7MB) podcast or subscribe to the feed at iTunes and Mevio .

September 14, 2016
Team Purple

This is Down Tools Week at Redgate Software, and I’m visiting the office. I had hoped to dive into a team and participate, but scheduling and other commitments mean that I’m only working with a team part of the time.

We had our first standup yesterday. They tried desperately to clip me out of the picture here.

After some morning setup, we walked down to the atrium and gathered in a group, talking about our plans and the work for the afternoon.

We’re using a kanban board, which will live on the glass wall of our conference room. One of the team is “Doing” work on the “doing” part of the wall, filling in the letters. I don’t see a post-it in the “ToDo” section, so we’re already off track. Hopefully when he gets to “Done”, we’ll get a ticket in that column.

We have a number of people working full time this week on a few items, with support from other teams that we’ll need API help from. The developers are pair programming, in 3 groups, with really three goals for the team. They are thinking to switch a few times so that each group gets to work on each part of our project.

Breaks are frequent since the coffee machine is downstairs.

We have a couple of people helping to coordinate and keep things on track, and luckily we got the conference room with the couches.

I’ve been in and out, helping work on the presentation that we’ll give on Friday. Hopefully we’ll have some real, tangible results to show as well as a talk. So far things look good, with lots of progress on the first integration point with another product.

September 6, 2016
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