Voice of the DBA

Tag: Redgate

Creating a Distribution List in SQL Multi Script

SQL Multi Script is a lesser known tool from Redgate Software that is designed to easily allow you to run scripts against many server instances with one click of a button. It’s similar to a Central Management Server, but it returns results a little cleaner, and has a few extra features that make things run better. I have a number of customers using this to deploy to many instances, both for database changes and instance config updates.

The main way to get setup is with a distribution list. This is a list of the instances and database you want to connect to. This post will show how to make one.

The Default List

By default, you have one list, and you can add instances to this. As you can see below, I’ve added a few instance and databases to my list, which is shown on the right side of the main application window.

If I click the “Configure” button, I get a dialog that lets me manage these lists. It is shown below, with a mover in the middle to add or remove databases to the current list. In the upper right is a “New” button to add a list.

Let’s click that. This gives me a simple dialog to add a name. I’ll choose “InstanceMasters” for all the master databases on instances. This is a handy list when I want to add a login to all instances or make a config change.

Once I click “Create” I get back to the mover. This is where I select databases.

I can expand the instance on the left, and see all the databases. One note, the system databases are listed last, so scroll down. I’ll click “Add” in the middle to add this one.

I have a second instance on my machine, so let’s pick that. I’ll click the “Add a SQL Server Not Listed” at the bottom.

I get a connection dialog to specify the name and credentials.

Once I complete this, I go back to the mover and I see my second instance. I’ll add that master database as well.

If I click OK, I now have my distribution list selected and set up. Any scripts I execute will go against these two master databases. If I needed to exclude one of the databases for a script, I could uncheck it, and in the image above, I can change distribution lists with the drop down below the Delete button.

That’s a quick look at lists in SQL Multi Script. The tool lets me run multiple scripts against various databases, execute an ad hoc script, and get all the results with the instance/database name as well.

It’s a very handy tool that not enough people use, so if you have the Toolbelt, give it a try. If down, download a SQL Multi Script evaluation today and see how this can help you in your daily work.

July 12, 2021
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