Voice of the DBA

Tag: Redgate

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
Finding Object Dependencies in SQL Doc
I had a customer recently ask if they could find out which objects are dependent on others using SQL Doc. This post shows how to do that.

Using SQL Doc

In the SQL Doc application, you can dive down into the various objects in your database. As I’ve shown below, I navigated on the left side down to a specific object.

This gives me the basics of this object, but I can scroll down and see more. The lower part below the script shows what this object depends on (Uses) and what other objects depend this one (Used By). In this case, this object depends on dbo.ErrorLog and dbo.uspPrintError.

The reverse of this can be seen in these objects. If I look at dbo.ErrorLog, then I see this proc (dbo.uspLogError) listed.

This is really a preview of documentation, not the docs themselves. While you can edit the MS_Description at the top, this isn’t the tool I’d expect most users to use. Instead, you ought to click Generate documentation at the top and choose the type you need.

Or better yet, use the command line and ensure this is regenerated after every release.

Searching the Documentation

Once the documentation is generated, it’s in static text files. These might be Word or some other format, but they are files in your machine.

If you want to search something, then you would use that search facility. A few examples below:

Word

In Word, as you can see, if I hit CTRL+F, I can search for something, like Error. That gives me a lot of results.

Not the easiest thing to search, but seeing this results in the TOC let’s me scroll down to jump to a page.

PDF

If I generate a PDF, this often renders in some application. For example, by default, this opens in a browser for me. If I want to search for “error”, I can again, CTRL+F and enter the term.

HTML

The HTML docs open in a browser, but they’re not directly searchable. There isn’t a place to click search.

If I use CTRL+F, it doesn’t work because the docs for each object are in separate files.

However, I can do some searching in the files if I use something like Visual Studio code to open the entire folder. It’s not the prettiest search, but it works.

Recommendations

I’d recommend you do two things.
1. Ensure that this is regenerated automatically with every deployment. Either to QA or prod, your choice. Depends on how your developers use docs.
2. Pic a searchable format, either Word or PDF.
SQL Doc is one of those utilities that can help a team share information, especially to report writers or others that might need to understand what fields are available and what they are used for, but don’t know the databases that well.

One thing to think about is a layer of abstraction for non technical people where you only generate docs for views that have well named columns for people to use in reports.
November 1, 2023
Creating a SQL Clone Image from a SQL Clone Database

A customer asked if a new image could be created from a database that was itself a SQL Clone. It can, and I’ll show that in this post.

This is part of a series of posts on SQL Clone, which is a virtualization product from Redgate Software.

Creating a New Image

To start with, I’ll look at the clones that I have deployed on a test server. As you can see below, I have a database, called “SimpleTalk”, on a local instance.

I’m going to use this to create an image. First, I’ll click “Create Image” on the left. I’ll choose SQL Server, since I will use the existing cloned database as the source.

I pick the instance and database. As you can see, I’ve chosen the database that was listed above as a clone.

Next we skip the classification screen. This is a test db, so no data in there to worry about.

I’m not going to modify this, as this is a test.

Now I pick the destination, which is a local share.

I give this a name which differentiates from the original db and other images. I normally want to name all images as _new or _current for rotation purposes, but here I’m adding a test name since I’ll delete this right away.

You can see this start working.

And it completes in about the time for a restore. If I had added masking, this would run a little slower.

I have the image created, so it’s time to test this with a new database.

Validating the Image with a Clone

Click “Create clone” on the left menu and you get a dialog that asks for an image. I’ll pick the one I created above.

Once we chose the image, we can modify it. We won’t here.

Now we add an instance and a name. I’ve entered those below.

Once this completes, I can see this database, and my other clone in the SSMS Object Explorer.

If I query both databases, I get the same data, since I haven’t changed either one during this process. Note below that each one of these queries is from a cloned database.

The SQL Clone cloned database is just a database. SQL Server sees it as any other database, as SQL Clone presents the mdf/ldf/ndf as any other database. You can back it up like any other one.

If you are looking to save storage space for multiple copies of databases, or give each user a consistent copy in seconds, check out SQL Clone.

October 16, 2023