Voice of the DBA

Tag: Redgate

Foreign Keys in SQL Data Generator
A customer recently asked about using FKs in SQL Data Generator, and I decided to write a short post showing how these work.

The Scenario

I’ve got a copy of the Northwind sample database on my system, named Westwind so I can experiment. As you can see below, the Order Details table has a few FKs in it, one to Orders and one to Products.

This is a very standard setup in many databases, where there are links between tables. I’m also going to add another table with no FKs, but I want data in here from another table.
```
CREATE TABLE OrderHistory
(OrderID INT
, OrderDate DATETIME
, Complete BIT
)
GO
```
This should have an FK declared to dbo.Orders.OrderID, but as I often see, this wasn’t set up.

Using SQL Data Generator

I’ll create a new project in SQL Data Generator that points to this database. When this configures itself, I’ll deselect all tables except for Orders, Order Details, and OrderHistory.

When I do this, if I look at Order Details, I can see that in the preview, both OrderID and ProductID are listed as generated data using the keys. In this case, this means I’d get data from both the Orders and Products tables.

If I check OrderHistory below, you can see that the OrderID is set as an integer to be generated. However, that’s not what I want.

Fortunately, I can set a manual key here. If I select the Generator drop down at the top, I see there is a SQL Type generator, and one of the subtypes is a FK. We’ll pick that.

This changes the configuration and I need to pick a table and column. I’ll choose orders.

Now I can generate data. Before I do this, I check Orders and there are 840 orders in there. I’ll just generate 10 more. I’ll also generate 10 OrderHistory rows and not delete existing data. Once I generate the data, let’s look at some results.

Below, we’ve added 10 orders, which makes sense. My OrderHistory table has 10 rows, but when I join with Orders on the OrderID, I get all 10 rows back. Data generator has respected a manual, non-DRI, FK.

SQL Data Generator should detect your declared FKs, but even if you don’t have them or it doesn’t, you can add them into your generated dataset.

SQL Data Generator is part of the SQL Toolbelt, and fantastic set of productivity tools for SQL Server developers. If you’ve never used these, download an eval and give them a try today.

Video Walkthrough

I also have a video walkthrough of this post, if you’d rather see this in action.
February 26, 2024
The 2024 DevOps Airways Tour is On

The Tour kicked off last month in San Jose, where I presented all day with Andrew Pierce, one of our solutions engineer. It was a good day, and the tour continued in Charlotte, NC, where Grant presented at that one with one of our talented sales engineers.

To get you a little excited, we also have a promo video:

We’ve got other dates as well, so let your team know, your friends know, and join us on DevOps Airways tour. If you want a concentrated day of learning, pick one of the workshops. If you want a larger event, come to London, Chicago, or New York and attend a Redgate Summit.

February 26, 2024
Friday Flyway Tips–Capture the Filegroup for Tables
A customer asked recently why Flyway doesn’t detect the filegroup for some changes. I showed them it does and decided to write a post on 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.

Setting Up Filegroups

I don’t see a lot of SQL Server databases using filegroups. They can be helpful, and they can create complexity, but they aren’t good or bad. Some people use them, and for various reasons. If you use them, you’ve probably run some code like this:
```
ALTER DATABASE [EngineRoomDemo_1_Dev] ADD FILEGROUP [indexes]
GO
ALTER DATABASE [EngineRoomDemo_1_Dev] ADD FILE ( NAME = N'index1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\shadow_index1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [indexes]
GO
```
Here I’ve added a new filegroup, called indexes, and a file in this group. This isn’t set as the default.

Since I’m working with Flyway Enterprise and generating scripts, I do need to also alter my shadow database, so that I can both detect changes and also verify my scripts. I’ll use this code:
```
ALTER DATABASE [EngineRoomDemo_1_Dev_Shadow] ADD FILEGROUP [indexes]
GO
ALTER DATABASE [EngineRoomDemo_1_Dev_Shadow] ADD FILE ( NAME = N'index1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\shadow_index1.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [indexes]
GO
```
Now we can detect changes.

Detecting Table Changes

First, let’s actually create a new table on this filegroup. To do that, I create a table, but I add an ON clause, as shown here. After the table creation, I use ON with the name of the filegroup. This ensures this table exists on the file(s) for this filegroup.
```
CREATE TABLE [dbo].[newtable](
     [myid] [int] NULL,
     [mychar] [varchar](20) NULL
) ON [indexes]
GO
```
Once this is done, I can go to Flyway Desktop and detect the change. When my schema model refreshes, I see the change, but no filegroup.

Hmmm, what’s wrong?

By default SQL Compare ignores filegroup information. If I click the Comparison options button, I get a dialog.

In here, go to the Comparison options dialog and type “file”. Hopefully the lower option below will be fixed to say “filegroups” and also add commas soon (sorry, I’m an editor).

We ignore these by default, so I’ll uncheck the lower box.

I’ll save this and refresh my schema model. Now I see the proper script with the ON clause.

Problem solved. If I care about where objects are created, I now have the filegroup location captured as part of my code.

Try Flyway Enterprise 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:
February 16, 2024
Friday Flyway Tips–Dark Mode

I saw a post that Flyway Desktop has a dark mode and I had to try it out.

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.

Dark Mode

In general, I’m not a big dark mode person. I tend to use browsers in light mode, like this:

I regularly work in VSCode, which is light for me.

And of course, SSMS is light. I know lots of people have wanted a dark mode, and you can configure that, but it’s a mess. To me, I just like light mode.

However, someone posted a note internally a Redgate thanking engineers for dark mode.

Wha??? I had to try it.

It looks interesting.

I don’t know if I’ll use it, but it’s easy to enable, and I can switch anytime. In the top bar, there are a number of icons. There’s a moon for one, which you see above and below, because I’m in dark mode.

If I click the left-most one above, I see this:

Now I have a sun, and a light mode.

This works on all tabs, on the VCS blade, and it gives you the option to work with either a white or dark background.

That’s pretty cool. I don’t care so much, but I know lots of people do, so they now get the option.

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

February 9, 2024