Voice of the DBA

Tag: Redgate

Deploying Indexes with SQL Compare
I suspect many people assume this is the case, but a customer recently asked if SQL Compare handles indexes. It does, and this post shows the basics of index comparisons with no filters.

This is a part of a series of posts on SQL Compare on my blog. You can read other posts I’ve written by clicking the link.

The Setup

I have two databases that are completely synced from a schema perspective.

I’ll now create an index in Compare1. This is a simple index on a single table. I use this code:
```
CREATE INDEX IDX_mychar ON dbo.MyTable (Mychar)
```
Once I refresh the compare, I see this. Note that I’ve selected the table that has a difference and it shows the new index. This bottom left shows the scripted version of the code I ran above.

If I create the deployment script, I see the index in it, as shown here:

By default, SQL Compare includes almost all objects and that includes indexes. There are options to change the behavior with indexes, and I’ll cover those in future posts. You can also set a filter that might exclude indexes (or include those), but those are also future posts.

SQL Compare is a fantastic product for simplifying work and it does so much more than this. Give it a try if you own it or download an evaluation today.
November 15, 2023
Friday Flyway Tips – Adding the Type of Database Project

There was an update to Flyway Desktop which lets you see the type of database your project is associated with, and this post shows how to get this in your list of projects.

As an example, you can see below my first project is a “SQL Server” project.

I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

Two Simple Steps

The first step to seeing the type of database project is to upgrade Flyway Desktop. The team is releasing basically every week. My version, upgraded before this post, is 6.9.3, so anything after this should have this capability.

The second step is you need to open your project. If I open the “DBCode” project above, it starts the comparison.

I don’t have to wait for the comparison, I can just close the project. Once I do, the type of project appears on the right side.

If you’ve been working with Flyway Desktop for awhile, you might have noticed an “Upgrade project” in the upper right. This is to upgrade the project from a JSON format to a TOML format, which doesn’t matter for you, but it does make the management of the internals of Flyway and Flyway Desktop easier for the developers.

In any case, you don’t need to upgrade the project. If I open and close a PostgreSQL project, I see this:

If I reopen the FWPoC_PostgreSQL project (named before this feature appeared), I see the upgrade is still there.

Try it out today. If you haven’t worked with Flyway Desktop, download it today. There is a free version that organizes migrations and paid versions with many more features.

Video Walkthrough

I made a quick video showing this as well. You can watch it below, or check out all the Flyway videos I’ve added:

November 10, 2023
Running a Command Line SQL Compare Comparison
Recently a customer was looking to automate some of their SQL Compare checks, but they wanted to do this in a dynamic way, since they needed to do this at scale. Their idea was to not have a SQL Compare project, but build a mapping at the command line.

This post addresses a part of their issue. This will look at how to set up a basic SQL Compare command line.

This is a part of a series of posts on SQL Compare on my blog. You can read other posts I’ve written by clicking the link.

Adding Parameters to SQL Compare.

To build up a comparison from the CLI, let’s start with adding parameters slowly. First, let’s just connect. To do that, I’ll run SQLCompare.exe with a couple parameters. We need server and database parameters.

The command line I’ll use will connect to my local machine (Aristotle) and then two databases: dlm_1_dev and dlm_2_integration. The code I’ll run is:
```
sqlcompare /Server1:"Aristotle" /db1:dlm_1_dev /Server2:"Aristotle" /db2:dlm_2_integration
```
This uses the /Server1 and /Server2 parameters, which can be shortcutted to s1 and s2. The structure is a colon and the value. For the database, we can use database1 or db1, and database2 or db2. I’ve shown both the long and short items above.

When I run this, I see results, which scroll off the screen for these two items:

If I look at a couple named instances, then I need to add in the instance name inside the quotes with a \ that will designate the appropriate instance. This is my command for named instances:
```
sqlcompare /Server1:"Aristotle\SQL2022" /db1:compare1 /Server2:"Aristotle\SQL2017" /db2:compare2
```
As you can see below, this gives me a few differences, with the indicators on the right about in which databases the changes are located.

This post shows the basics of working with the command line. I’ll look at more advanced options in future posts.

SQL Compare is a fantastic product for simplifying work and it does so much more than this. Give it a try if you own it or download an evaluation today.
November 8, 2023
Friday Flyway Tips–Seeing Pending Migrations

I find that quite a few people using Flyway will end up with a lot of migration scripts over time. While you can certainly re-baseline and split scripts into separate folders, visualizing these over time can be hard.

The Flyway Desktop team added a nice little option that makes it easier to see new work as opposed to old work.We’ll look at that in this post.

I’ve been working with Flyway Desktop for work more and more as we transition from older SSMS plugins to the standalone tool. This series looks at some tips I’ve gotten along the way.

Lots of Migration Scripts

We might see a lot of migration scripts over time in a folder. Certainly I can see this in the file system for one of my projects.

In Flyway Desktop, here is my view.

That is a lot of scripts. Since these are ordered as they would apply, it can be a lot of scrolling to find the ones that haven’t been applied.

However, if I click an environment on the right, I get a different view. Now I see a checkbox above the migrations that says “Only show pending migrations”.

If I click that, I see a view of the few that haven’t been applied to this environment.

A quick way to see what work has been added to the project, but not applied to other environments.

Try it out today. If you haven’t worked with Flyway Desktop, download it today. There is a free version that organizes migrations and paid versions with many more features.

Video Walkthrough

I made a quick video showing this as well. You can watch it below, or check out all the Flyway videos I’ve added:

November 3, 2023