Voice of the DBA

Tag: Redgate

Creating a Scripts Folder in SQL Compare

While I was at a conference recently, someone asked me about the Scripts Folder feature in SQL Compare and how to set that up. This post just looks at how to get SQL Compare to set up a Scripts folder on a machine.

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.

Getting Started

I’ve got a D: drive on my machine that looks like this.

I’m going to create a folder, called “Compare Script Folders”, which you can see is empty. This is where I’ll store the scripts for various databases.

I also have a couple databases I use to test things on a local instance. You can see below my “compare1” database has a few things in it.

Now, I want to get a text copy of my schema from SQL Compare that I can use to check for changes, so let’s do that.

Creating a Scripts Folder

In order to create a scripts folder, I need to run SQL Compare. Once I do that, I see a screen like that shown below.

By default, SQL Compare looks to compare two databases. However, I can change the source. If I click the drop down next to Source, I see all of the options I have available. One of these is Scripts folder, which I’ll select.

Once I do this, my dialog changes. Now, I have the ability to compare a Scripts folder to a database. How do I get the Scripts folder? I click the Create link shown below.

I missed this a few times, so that’s part of why I wrote this post. Once I click that, I get a new dialog. This is the one that let’s me choose the way I want to create the folder.

Just like the comparison, I have a number of choices for the source. I can use various items, but in my case, I’ll choose database.

Once I do this, I enter the credentials to access the database. Note that I’ve clicked “Trust”, which is required for all modern versions of SQL Server. I have also selected the new folder I created.

Once I click Create, the engine will start to script out my database into separate files. This is the same process used when running a comparison, but in this case the results are just output to files rather than held in memory to compare with another set of objects.

Once the comparison completes, I see a new folder created below the folder that was in my dialog.

Inside this folder are all my objects, separated into different folders, which is the SQL Compare structure. This is the same structure used in SQL Source Control and Flyway.

Now I can use this as the basis for a source or target against another source or target in SQL Compare.

If you want to do this from the menu, there’s also an item in the File menu to get the Create Scripts folder dialog.

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.

December 20, 2023
Friday Flyway Tips–Deploying Migrations with a Target

Recently I was working with Flyway Desktop (FWD) and helping a customer work on deploying part of their work. They weren’t sure how easy this could be, but this post follows what I showed them.

Using the ability to run Flyway commands in FWD, we can deploy some migrations and not others. This post shows how to configure this.

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.

Picking Migrations

I’ve got a FWD project here, and you can see the migrations below. In this case, I’ve selected a target of my QA machine and we can see that I have migrations applied up to 5 and there are pending migrations from 6-8 (ignore the undo).

If I want to apply migration 6, but not 8, I can do that. First, I’ll click the Advanced settings on the right side. When I do that, I see text with a “add parameters” button.

If I click the Add parameters button, I get a drop down that is searchable.

I can start typing “ta” in here and you see matching items. “Target” is the last one and this is the parameter that you want.

The value of the target is the last migration you want to run. In this case, I can pick 6 and it will run only migration 6. If I pick 7, it will run 6 and 7.

Once I do this, I can click back (or add more parameters) and on the main screen I see that target is in blue, as a parameter added. In the command text box, I’ve highlighted this command as added to the CLI.

Note: This text is what you could run in a CI system or at a cmd/shell .prompt

When I click migrate, the command is run and I get output about which migrations ran.

If I close this, then I see 6 is successfully applied (after unchecking only show pending) and 7 and 8 are above target. In another post, I’ll explain those.

Try Flyway Desktop 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:

December 15, 2023
Clearing Intellisense in SQL Prompt

I got this question from an account rep: if a customer is using SQL Prompt and a snippet, for example AT, that was also used as an alias, is there a way to avoid it triggering the snippet or do they just avoid hitting the tab key?

It’s a good question. Let’s see how to deal with this.

This is part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag.

The Scenario

Say I have this code:

Notice I have an alias that is the same as a snippet, in this case AT. Seeing this pop up is annoying, but there are ways to avoid getting concerned about this.

First, the Esc key will get rid of the intellisense popup. Pressing escape at this point brings me back to this:

My other option is to type a ., essentially keep typing. If I do that, I get this:

The same thing if I had a longer alias. Here I’ve added 2 characters, but if I type the 3rd, the snippet goes away and I get the table. I could hit tab here to fill this in or type the s.

The other thing to do is raise the time for intellisense if it’s popping up too quickly and annoying you. I don’t know the default (nor do I want to reset lots of stuff), but here I’d set it to 100ms, which is a nice delay for me. I find 500 too long. Of course, you can CTRL+space to pop it open.

If you haven’t tried SQL Prompt, download the eval and give it a try. I think you’ll find this is one of the best tools to increase your productivity writing SQL.

Video Walkthrough

I made a video of getting rid of intellisense. You can also see all my SQL Prompt Tips.

December 13, 2023
Friday Flyway Tips–Comparison Options
Recently a customer asked how they could get index changes to be captured in Flyway Desktop. In their case, they wanted a different fill factor, but I decided to investigate a bit more how things work.

This post looks at how to control the comparison options in Flyway Desktop (FWD).

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.

The Setup

I’ve got a table and an index, which I created with this script.
```
CREATE TABLE [dbo].[Customer]
(
[CustomerID] [int] NULL,
[CustomerName] [varchar] (75) NULL,
[PrimaryContact] [int] NULL,
[PrimaryAddress] [int] NULL,
[PurchaseLimit] [numeric] (10, 2) NULL,
[Status] [tinyint] NULL
)
GO
CREATE NONCLUSTERED INDEX [nci_customer_custname] ON [dbo].[Customer] ([CustomerName], [Status])
GO
```
I saved this in Flyway Desktop, which we can see here in the filesystem:

and here in VS Code.

If I refresh the schema model tab in FWD, there are no changes.

Making Index Changes

I’m going to alter this index. Specifically, I’m changing the pad index, fill factor, and statistics options. Here’s the script I’ll run.
```
ALTER INDEX [nci_customer_custname] ON [dbo].[Customer] 
REBUILD PARTITION = ALL 
WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = ON, ONLINE = OFF, 
       ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
```
Once this runs, I’ll refresh FWD and I see this. Note that I see an index change, but only one option is captured: STATISTICS_NORECOMPUTE.

What’s happening is that FWD is using the SQL Compare engine and the default options set in the engine. Among these are to ignore fill factor and pad index. However, I can change this.

Changing Configuration

In the past, I would need to edit a config file to make this change, but the team has enhanced FWD to add new options. In this case, notice the button near the top of the Schema model tab: Static data & comparisons. Not a great name, but it’s there:

Once I click that, I get a new dialog. This starts with static data, but I’ll click the second tab, which is Configure comparisons. This shows all the options available in the Compare engine

Rather than scroll, I’ll type in the search box, and I see fill gets me the “Ignore fill factor and index padding” option.

I’ll uncheck this and click OK.

Once I do that, I’ll refresh the comparison, and now I see my options.

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:
December 8, 2023