Voice of the DBA

Tag: Redgate

Getting the Script from the SQL Compare Command Line
In the last few posts, I’ve written about using the SQL Compare command line for a specific object and shown how to get a report. This post will look at getting the actual script.

This is part of a series I have on SQL Compare from Redgate Software. It’s an amazing piece of software that you should try if you haven’t. Download an eval today.

When we run the command line or generate the report, we see what’s changed, and even the details of the chances, but how will that get deployed? The actual script is often important to a DBA to ensure that these changes won’t cause problems in a live environment.

To get the script, there is a /scriptfile parameter (or /sf) that will output the file. You add this in similar way as you do the report file, including the path. The important thing here is to ensure that you have write access to the path.

I have added a few differences in my databases, and you can see them in my complete report:

If I want to see what will be run for all these changes, I can add the /sf and get the script. In this case, I’ll add this to the end of the CLI call:
```
/sf:C:\Users\Steve\Documents\changes.sql
```
This produces a script that looks like this:

It’s a normal “SQL Compare” script, with comments at the top as well as the various transaction items.

This is good, because I can see there is a table drop in here. I actually renamed a table, so this is a problem. I might want to then decide how to handle this, or not to deploy this change.

Note: Using SQL Source Control or SQL Change Automation allows this to be handled in other ways.

I can also combine this with a single table inclusion to check one item. For example, I can run this:
```
sqlcompare /server1:Aristotle\SQL2017 /server2:Aristotle\SQL2017 /database1:compare1 /database2:compare2 /include:table:mytable /sf:C:\Users\Steve\Documents\mytable1.sql
```
When I do that, I see my script has a table rebuild in it, which is something else I might be concerned about.

With the other posts on the SQL Compare CLI, we can now choose what to compare, get a report, and see the actual scripts being run. This should allow us to choose the way that we want to deploy changes with SQL Compare from the command line.

I don’t know that these are the best way to deploy to production, but when you need to sync something quickly, get a report and script, and then decide if this works for you.

There are lots of options and ways to use SQL Compare, and I’d urge you to explore a bit as you look to improve your database deployments. If you don’t have it yet, download an eval and give it a try.
July 5, 2021
Command Line Report with SQL Compare 14
I wrote recently about the use of the command line in SQL Compare 14 to find differences for specific items. Now I want to actually see the changes to decide what to do.

This is part of a series I have on SQL Compare from Redgate Software. It’s an amazing piece of software that you should try if you haven’t. Download an eval today.

In the previous post, I saw the changes listed in the CLI, but not any details of what’s changed. In the SQL Compare GUI, we get a report and the option to see things. It’s nice, but slow. Sometimes I just want to get things going.

There is a switch that let’s me generate a report file. I need a path, and I have to remember that the SQL Compare install folder isn’t usually writeable for me interactively. I use the /report (or /r) switch to build the report.

Here’s the command line I used:
```
sqlcompare /server1:Aristotle\SQL2017 /server2:Aristotle\SQL2017 /database1:compare1 /database2:compare2 /report:C:\Users\Steve\Documents\comparereport2.html /reportType:classic
```
The default report is an XML report that doesn’t render well. It’s a PIA to read, so it’s nice to include the ReportType switch as well (or rt) to set this type to HTML. The new HTML report is nicer to read than the classic one. The Classic one is here:

The newer HTML one is here:

This allows me to see a good view of what might be changed if I run the tool itself. I can use this to decide if I’m getting the object changes I want.

There are lots of options and ways to use SQL Compare, and I’d urge you to explore a bit as you look to improve your database deployments. If you don’t have it yet, download an eval and give it a try.
June 28, 2021
A Weekly SQL Clone Image Creation Process
SQL Clone is a neat product from Redgate that I wish I’d have had when I was doing database software development. It lets me have a consistent image for all developers, and create/reset databases to that starting point in seconds.

There are two parts to this process: image creation and clone database creation. I’ve written about both in different places, but in this post I want to tackle a weekly image creation process with some tips and recommendations for how to handle this.

If you want a basic image creation post, read Creating a SQL Clone Agent and a First Image.

The Goal

There are a few goals with a weekly image refresh process for database developers:
- I don’t want to interrupt developers’ work
- I want consistency that allows other people’s scripts to just run
In this case, as I create a new image with updated schema and data, I don’t want to require developers to stop working for me to update the image. I also don’t want them to stop while I switch out images. This means I need multiple images for a short period of time.

The other thing, which wasn’t a recommendation early on, was in naming. Lots of early customers, and us Advocates, were naming images with timestamps or some unique value. However, in an ongoing process, this doesn’t work well.

This post is the result of some learning, experiments, and feedback from customers.

The Process Outline

Rather than start at the beginning of a project, let’s assume we are in an ongoing development process. There is an image, and multiple developers are using this in their cloned databases. For simplicity, let’s say I have this setup:
- A production database – ADW_Prod
- Developer Kathi, with a cloned database against ADW_Current – AWD_Kathi
- Developer Grant, with a cloned database against ADW_Current– AWD_Grant
- An Image, ADW_Current
Given all this in use, how do I update ADW_Current with the latest version of production?

The basic process to follow is this:
- Create a new image from production, ADW_New
- Check if there is an ADW_Old image.
- Rename ADW_Current to ADW_Old
- Rename ADW_New to ADW_Current
That’s it. In an ongoing process, I need image rotation, hence the _New->_Current->_Old. If there isn’t an old image, I skip a couple steps.

In this process, developers that are using the current image, ADW_Current, are left alone, though they are now using ADW_Old as the image.

If developers are 2 versions back, on ADW_Old, their databases are dropped. I could deploy new copies of from ADW_Current (or ADW_New), but really, I want developers to be thinking about saving their changes in a VCS often, and not making special little databases they keep for days or weeks.

Really, I want a developer to finish some work, commit it, and then destroy and recreate their dev database. That’s the whole point of SQL Clone. In about 7sec, I have a new copy of the database. I can then pull everyone else’s changes from VCS and be up to date.

The Code

How does this work? Well, I have a single script that I added to a repo on GitHub. I’ll use some images here to show parts, but get the code from there.

The newimagerotation.ps1 is the script you want. In here, I have some help at the top to give you parameters from PoSh. Then we set some items.I set defaults and then add some standards for my New/Current/Old structure. Feel free to change if that doesn’t make sense to you.

The next part is where I create the New image. If this exists for some reason, like an error, I remove it. Possibly you want to check if there are clones against this and stop, but I never want someone using this.

After this, we want to rename the current image to old. However, if an old image exists, we remove it. Before we can do that, we need to loop through and remove cloned databases. Protection against someone accidentally removing an image, but I am purposefully doing it here.

Once this is done, we rename the new to current, and we’re done.

Summary

This is an easy process to follow weekly, and it rotates your image so that if people are using scripts or the GUI, they always know to use the _Current image to create a new database.

This also gives developers a grace period that equals your image refresh process for using an old image. If you run this daily, they can use an image for 2 days. If you run it weekly, they can use it for two weeks.

If you want to warn them, add a call in the “rename current to old” section to send a message to developers that there are databases that will be removed when the next image is created.

If you haven’t tried SQL Clone , download an eval and give it a try today. It’s a great way to speed up developer’s experimentation and ensure consistency in dev and test environments.
June 16, 2021
Using SQL Compare for One Procedure
A customer recently was concerned about the time to run SQL Compare for a large database. They were synching with the command line, but at times they want to just sync up a procedure or two from one database to the other.

I knew this could be done and passed along some ideas, but decided to write a post. This post looks at how to do this.

A CLI Comparison

The SQL Compare command line is pretty easy to use. Lots of switches and options, but the simple thing is point it to a couple instances and databases and get a comparison. Here’s a command line.
```
sqlcompare /server1:Aristotle\SQL2017 /server2:Aristotle\SQL2017 /database1:compare1 /database2:compare2
```
And the result. You can see below I have a table and three procedures that are different.

If I want to limit what’s compared, I can certainly use a filter, but from the command line, there’s a simple way to see certain objects. There is an INCLUDE switch that I can use to just set a filter here without creating a file.

For example, if I want to just see stored procedures, I can do this:
```
sqlcompare /server1:Aristotle\SQL2017 /server2:Aristotle\SQL2017 /database1:compare1 /database2:compare2 /Include:storedprocedure:
```
This gives me just my three stored procedures.

Likewise, I can also change this to a table and just get that object.

If I want a specific object, I can get that as well. Here I use the include like this:
```
/Include:storedprocedure:\[GetMyTable\]
```
Then I get just my one object, with a faster compare. Only this one is checked.

Then if I add the Synchonize switch, the changes will get deployed.

I often find that people are looking to deploy quickly just a known object or two for some hotfix or out of band change. Using the command line let’s me pick an object that I know about and build a comparison for just that object.

There are lots of options and ways to use SQL Compare, and I’d urge you to explore a bit as you look to improve your database deployments. If you don’t have it yet, download an eval and give it a try.
June 11, 2021