Voice of the DBA

Tag: Redgate

A Multitude of Summary Views in SQL Compare 15

A client asked for a summary of changes, so I wrote a post to show where to find this in SQL Compare 15. As I was checking out the Summary, I realized there are different ways to present this info, which can make your job as a data professional easier. This post shows the different views.

This is part of a series I have on SQL Compare from Redgate Software. It’s an amazing piece of software that you should try if you haven’t. Download an eval today.

In the previous post, I showed this image, which is the default view. This lists the changes by object, meaning that I see the objects listed on the left with schema and name, and then the actions on the right. In the image below, the first item on the left is dbo.Street, with two items after it. On the right, we see this is a need to drop a PK and then create a new PK.

This is a good list, but there are other choices. In the upper right, there is a drop down. If I click it, I see this:

The Object is the default one, but if I click Modification, I see this view, which lists the types of changes. You can see that I have 1 drop PK, 4 table alters, 1 view alter, etc.

A good view, though I don’t often case about this, other than I usually would look for drops because those are problematic.

The third view is the modifications, but in the order they’ll take place. I like this, since as someone who has been a production DBA, I want to know what changes are happening in what order.

In this view above, the alters are split out. So I get one alter of a table, BaseTable, then a view, then a refresh, then alters for two other tables.

Using these summary views are good ways to initially conduct a review of what is changing for a deployment. This is valuable because for a lot of changes, this makes sense. For some, I might want to see the code, so having the changes in order here let me quickly find this change in the script and view the exact way this is going to be deployed.

SQL Compare is an amazing tool and it makes life much easier. If you haven’t tried it, download an eval today and give it a try. If you’d like to see this in a pipeline or more automated system, give the Flyway suite of tools a try.

August 7, 2023
Getting a Summary of Changes in SQL Compare 15

A client asked for a summary of changes, so I wrote this quick post to show where to find this in SQL Compare 15.

This is part of a series I have on SQL Compare from Redgate Software. It’s an amazing piece of software that you should try if you haven’t. Download an eval today.

If you use SQL Compare, you might find yourself in situations like this, where there are a bunch of changes.

This isn’t a lot, but when you build the script, you then are confronted with a lot of information. If I choose all those objects, here’s my script in the deploy wizard.

This is a lot of code to look through. Most people can do this, but it’s also easy to miss something in this long listing. Humans make mistakes, and we want to minimize mistakes.

The UX isn’t great, but if you look at the top of the code window, below the 14 objects part, there are three items: Deployment Script, Summary, and Warnings. If there are significant warnings, that will actually be the default screen, but the summary is something people miss. You can see this below:

If I click Summary, I see this:

This is a much more manageable list of things that are being touched, and a good list to show in a standup or code review for others to watch.

Grabbing a screen shot is something you could easily send to people as well. If you use the Flyway solution, we’ll also generate a report of this with your CI or deployment processes.

Give this a try the next time you use SQL Compare. If you don’t use it, give it a try. It’s amazing.

July 31, 2023
We’re Not Faster with AI

At Redgate Software, we’ve been trialing Copilot from GitHub with our developers. I managed to get access for this experiment and have tried a few things, though I’m not sure I’ve found it very useful. I’ll continue to work with Copilot, but for now, I just don’t find Copilot AI helping me with the types of tasks I do.

However, our chief of technology, Jeff Foster, was interviewed about how he sees AI and Copilot in Redgate. It’s a good read, and I found a few fascinating things in the piece. First, I was worried about this a bit, as we have explicit guidelines in developers that say we can’t take the output from an AI and put it in our codebase. We worry about legalities since we sell software. How does that work with Copilot?

Well, I learned that there are a couple of things in here. First, with a business subscription, we can have the AI not show us copies of public code. If more than 150 characters of public domain code is in a suggestion, it’s hidden. This is in the Copilot docs as well. I don’t know if 150 is a good length or too long, but at least there are limits. We also found that Copilot doesn’t retain any snippets, which is important for us. We don’t want to share Redgate code with others, especially competitors unless we are explicitly putting a public repo out there.

The second thing I found really interesting is that we have a lot of guardrails around the way we produce code. We have tests, lots of automation to evaluate things, and plenty of peer review. I see requests for PRs in Slack constantly amongst our teams. We know that the code which gets generated likely needs some editing, but also that we want to be sure that whether it’s sent as is or edited by a developer, the code is still reviewed. Having these guardrails, test, etc. help us experiment with new ideas without impacting our productivity.

Perhaps the most interesting part for me was near the end. Jeff doesn’t think that Copilot makes us faster. He says “We probably invest ten times more time finding the right problem to solve than on the actual coding.” That makes sense, given what I’ve observed over the last 15 years. We are deliberate and careful about picking problems to solve and producing high-quality code. Sometimes it makes me a little crazy as I want things to move faster, but I’ve learned to appreciate that overall, this is an approach that doesn’t get us into places where we struggle to onboard developers to new projects or adjust our code to meet new requirements.

I’ve got a few quiet weeks coming up, so I’m going to give in more and use Copilot a bunch to see what I can get out of it. If you’ve tried it, let me know what works or doesn’t. If you want me to try and solve some problem for you, drop a note in the discussion with the challenge you have.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

July 31, 2023
A Security Change with SQL Compare 15

Actually, this is a change for many products and software that connects to SQL Server. Updated drivers require us to now decide to trust the server certificate.

This is part of a series I have on SQL Compare from Redgate Software. It’s an amazing piece of software that you should try if you haven’t. Download an eval today.

I opened a project recently in SQL Compare 15 to check something for a friend. I went to run the compare and saw this:

At first I thought that one or both of my instances were broken, but I realized that this is a security issue. Updated drivers changed the default encryption options, which means we need to specify this.

The products at Redgate have been getting updated across the last year to add in a “trust server certificate” box. It’s not set by default, which I find annoying, as I rarely find SQL Server’s with certs. Certainly none of my test instances, and I wish I could set this to check by default.

In any case. when I check the boxes in the image above, the compare runs.

Something to keep in mind and a muscle habit to build if you still use SQL Compare often. If you don’t, give it a try. It’s amazing.

July 24, 2023