Voice of the DBA

Author: way0utwest

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 Dangers of Not Upgrading

About ten years ago my sister-in-law broke the screen on her mobile phone. She’d had an older iPhone and when she went to upgrade, none of the upgrade processes worked because her OS was so far behind that they couldn’t transfer her information smoothly. She had been avoiding OS updates because they interrupted her life, but that was now a problem because the world had marched so far beyond her version that there weren’t tools, or at least, no one was interested in trying to perform an upgrade across multiple OS versions (I think it was 3 or 5 versions).

I ran into this recently with someone else I knew, but not for a mobile phone. For TFS 2015. This customer had been working along with this older system and is finally ready to upgrade to Azure DevOps in the cloud. They wanted to know if they could somehow upgrade the TFS database and move all that data easily into the cloud. I said this wasn’t likely easy as this isn’t an upgrade, but an export and import of a lot of data. Microsoft offered a path, but it was multiple upgrades before an export/import, which was deemed too expensive. Right now, I’m not sure what they’re doing to do.

I know that many people keep old versions of SQL Server out there. Brent’s population report still shows some 2014 and older databases, and I’m sure there are plenty of 2008-era (or older) instances in use. Many of you continue to run older software because it works. That might especially be true on desktops, where we often install specific software for a task that might not change for years. I think there is plenty of value in older software that works well, though there might be security concerns.

However, for platforms or software where there is a regular upgrade path that you will need at some point, it pays to keep up, at least a bit. Maybe you don’t upgrade with every version, but you likely shouldn’t fall more than two versions behind. Vendors don’t want to maintain compatibility for too long and certainly don’t want to keep providing updates for new features. Even security updates get expensive to produce for old versions with complex test matrices needed to ensure the patches work and don’t break the application.

Software is a bit of a crazy business. In the analog world, if we own a product and it breaks or has an issue, often there are third parties who can repair it, or even create the necessary parts for the DIY market. Software is different, and the pace of change can be a bit overwhelming, expensive, and annoying. We can’t fix old software, heck, we’re not even allowed to fix old software. We can get into legal trouble if we try.

I don’t know if there is any good solution, but I certainly do know that if you manage software, you ought to be careful to keep abreast of how often the software upgrades and when support for previous versions is waning. Even if you don’t care about customer support calls, you likely care about upgrading to a newer version at some point, so make sure you know when they might drop support for upgrading from your version. Stepped upgrades work, but they can be expensive and time-consuming, and if there are issues, often vendors aren’t interested in why the upgrade didn’t work from your extremely-old version to a slightly-less-old version.

I still expect a database version to run for 10 years, but I know that support is tricky, and I also know that I better be ready to upgrade at that time.

Steve Jones

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

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
Free SQL Server

No, I don’t have a way to license a SQL Server instance for your application for free. Microsoft still charges for production workloads. However, there are several ways that you can do development or testing for free, or at a greatly reduced cost. Bob Ward spent a bit of time researching options and he’s written a post that outlines a number of your options. It’s very comprehensive and I know a lot of us are grateful to Bob for summarizing our options.

As I work with developers in organizations, there are often questions about the licensing for dev editions. This comes up more with Oracle than other platforms, but SQL Server does get asked about. We made a decision to support SQL Server Developer Edition in Redgate Clone, which is in line with what Bob’s post above says: this edition is for developers (and it’s free). There is one caveat here, which I never knew. The licensing guide says you cannot build test data and move that into production. I’m assuming this doesn’t mean you can’t create a list of US states, countries in the world, or statuses. I think this means something more substantial, but if anyone knows, let me know.

One question I get asked a lot is about how do you develop locally if your production target is Azure SQL Database? The best option is a container, as outlined in this MS Learn article. It’s not completely the same, or necessarily as up-to-date, but it’s close and likely good enough for most of us. If you really want an Azure SQL DB instance for dev/test, it’s not going to be free. Unless you can live within the monthly Azure credits, which are pretty low for this purpose. If you are a Visual Studio subscriber, you can get some discounts, however, and I didn’t know this, you can’t use Azure credits with the VS benefit.

There is also a “free offer” for Azure SQL database. This is fairly limited, but you do get free compute and 32GB of data for the lifetime of your subscription. If you work with small development databases, and you aren’t querying like crazy, that might work well for you. It’s also a great way for students or job-seekers to get some experience with the platform at a low (or no) cost. You can even try Managed Instance for free for 12 months.

The cloud isn’t for everyone, every organization, or even every application, but it does work well in some situations. Whether that fits you or not can be tricky to determine, but you certainly should be working in the cloud in a dev/test manner to help inform the decision. I would hope that most of you have dev/test databases in place for on-premises/IaaS systems as well. I don’t run into too many people developing on a production instance, but I do see some, and I certainly see some that work in heavily shared environments.

Cost shouldn’t be a factor here, at least not for SQL Server, and there are options to help you ensure you have all the dev and test instances you need. Read Bob’s post and see if you can help make your developer’s lives easier with some of the resources. And learn to follow good software development practices for your SQL code, just like the C# developers do.

Steve Jones

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

February 23, 2024