Voice of the DBA

Tag: Redgate

Help Build an Amazing Summit in 2021

For a good portion of my career, I’ve been going to the annual Summit in the fall with lots of my fellow SQL Server/Data Platform professionals. I have been to 16 or 17 before last fall’s virtual one.

This year, Redgate has purchased the trademark and brand for the Summit, and they are looking to run a virtual event in the fall. Like many other events of the last year, this will be free, and I’m excited to see how this goes.

Redgate is looking for feedback from those of you that have attended Summits in the past, trying to understand what worked well and what didn’t. They are looking to create a memorable event and evolve the Summit into something better in the future.

Fill out the survey before Apr 15 and you could win a Peloton bike. Not a bad prize for sharing some thoughts on an event I suspect many of you enjoyed and would like to see come back.

April 7, 2021
Running Scripts in Parallel with SQL Multi Script

One of the lesser known tools in the Redgate Toolbelt is SQL Multi Script. While few customers used it, those that did loved it, as it solves a problem that many people have: deploying a script to a lot of servers at once. While Central Management Servers help a little here, they don’t quite have all the features of Multi-script, which is not only a management, but a deployment tool as well. With companies using sharding and multiple systems for clients, this is becoming more important all the time.

A few years ago a customer asked me if we could make this run faster. At the time, we had limited the tool to 25 threads at a time, which made sense when it was developed. However, with modern hardware, I asked the developers to remove this limit. This was an easy change, and now you can scale up your script execution.

You can do this with the “Application Options” in the menu. As you see below, parallel execution is enabled.

What’s more, this value is dynamic. If you start executing this agaisnt a series of databases and need to scale up or down, you can change this value and it will take effect immediately.

If you’ve never given SQL Multi Script a try, and have the need to run scripts against a lot of servers, give it a try. It’s a little known, but incredibly valuable, tool.

April 5, 2021
Filtering SQL Compare to a Schema

One of the things that numerous clients have some to Redgate about is allowing a team of developers to work on a single shared database without creating conflicts. While this isn’t necessarily a simple thing to do, it is easy if you structure the projects appropriately. This post looks at one of those ways, with schema filtering.

In a recent case, a customer had multiple schemas, and they wanted a SQL Change Automation project scoped to a single schema. Easy enough to do, as a new project gives you a place to pick your filter, but in order to do that, you need to create it first in SQL Compare. This post looks at how to do that.

The Scenario

I created a demo database with a couple schemas inside, and a few objects inside. I had a blank database to simulate a new QA environment. I opened SQL Compare and created a new project, pointing to these two databases. From here, I want the initial comparison. you can see this below, with a number of objects listed in different schemas.

On the left is the filter pane, and near the top is a “Custom filter rules” link. I clicked this to open up the filter dialog. In here, I see a default set with no rules.

I added a new rule to set the schema name equal to “Sales”, to filter all objects away that do not exist in Sales. This is to give me a “Sales” project that developers can use in this schema.

I click OK, and then I refreshed the comparison. I saw this:

I still have the Inventory schema listed. That’s not ideal, as this could be confusing to juniors developers. Also, depending on the comparison options, this could allow dependent objects to leak into this project.

Filtering Schemas

Schemas are not owned by themselves, and are separate. As a result, I need an additional item in this filter. If I scroll down the left side, there is a “schema” checkbox. If I put my mouse on this, I can see that there is an “edit” link.

I click that and then create my filter rule. In this case, I want the “Object name” to equal Sales. The object is the schema.

Once I click OK, the Inventory schema disappears.

I need to repeat this for each schema that I want to build a filter for. Note that there isn’t a “save as” for filter files, so I need to manually copy each one in the file system when it is complete, or create a new SQL Compare project.

Summary

Once I have the filter files, I can load the appropriate one into each SCA project, allowing me to have projects that only see a small portion of the objects in a database.

One other thing I often do here is remove global type objects, like users, from the project as well by unchecking those boxes in the filter pane.

SQL Compare is an amazing tool that underpins much of what makes DevOps tools and automation work well with Redgate tools. I’d urge you to give it a try if you never have. You’ll be amazed as how much is helps you get work done quickly.

March 22, 2021
Summarizing a Script with SQL Prompt

I have never used this feature, but someone was asking for feedback on Prompt, and I noticed this in the menu: Summarize script.

I had guessed that it might look at the code and give me some outline, which is what it does, but I wasn’t sure how it might work. I decided to try it on a few scripts.

I had a demo script for a customer, and I ran it there. I got what I expected, an ordering of various operations.

Useful in some sense. I can see I cleaned up the CREATE with the DROP. However, I could also see that easily if I looked at the script. Depending on length, this might be helpful to remind me or let me see if I’ve dropped all the code I expected.

I picked a longer script from some of the Advent of Code stuff I’ve been slowly working on. In this one, there is some looping, as it’s a looping type of problem (to me). In this case, I see something more complex.

Not a lot of information from the SELECTs, but I do see some looping. The actual code is a bunch of math changes, and I could have used SET, which might have helped here. This let’s me see that the code is more of a procedural construct, which looks like this:

What about other types of code? I looked at a CTE, which wasn’t that helpful. I can’t see the base tables here, which isn’t useful.

However, in AdventureWorks, there is a procedure that shows me a TRY CATCH. In a long set of code, this might help me make sure I’ve actually included the CATCH, among other things.

I’m not sure how useful this feature is, but it’s now in my mind to try a few times and see what I think. What is the outline and structure of my code.

If you have other ideas, I’m sure Redgate would appreciate suggestions. Otherwise, give it a try and let me know if it works.

If you don’t have SQL Prompt, it’s an amazing developer productivity tool, and we now offer it as a subscription, so you can try it for a bit longer than than evaluation without committing.

March 17, 2021