Someone once told me at the shop that we have plenty of software utilities laying around that could be very useful that no one is utilizing. I got to thinking about that from a DBA standpoint; we are fortunate enough to have the Tool Belt from Red Gate and with that comes a plethora of utilities such as SQL Data Generator, SQL Document Manager, SQL Compare, SQL Data Compare, and the list could go on (full listing).
One of those said utilities is a little gem called SQL Multi Script. For me I have found this utility most useful. First of all I’m a heavy SQL Compare (why use SQL Compare) user. We utilize it here in the shop daily for the most part and I like the ease of how to call all of Red Gate’s apps from one location:
By clicking on the icon in top left you will be presented with a list of utilities in your tool belt (pending on your licenses and I’m using version 12.1)
As you can see the SQL Multi Script utility is the last on my drop down menu:
So, now that we’ve established how easy it is to call the utilities from any said location within the product line; exactly what does multi script mean for me? Glad you asked; I’m going to try to take you on a journey inside a utility while painting an example of how to use to tool in a real life example.
When you first come into the utility you will see a screen similar to the one below:
What I like about the utility is the ease and flow of the product. It didn’t take long to get up and running with it. If you notice on the left hand side you have the option of adding existing or new scripts to the template. In my case for releases this allows us to save multiple schema changes for various databases and incorporate them into one setting for a quick review and execution. One thing that I have ran across, that is no fault to the utility, is static data scripts that may need to be run in a certain order, but that is up to me to put in the correct order of execution once I get the scripts into multi script. I will show an example below of what multiple scripts look like within the utility.
On the right hand side you will see a distribution list. By clicking on configure you can set up a list of servers that you normally deploy or execute to in one location. One thing to keep in mind is to execute the scripts according to which server (databases) you select. Be cognizant of which ones you are selecting within the utility.
Example of adding existing scripts:
Example of configuration set up:
At the far right you can see I have a test distribution list set up. This affords me the ability to add servers from the list on the left or I can add any SQL server into my listing. From there I can pull down exact databases on that server to execute scripts against. For this purpose I’ll pick a couple of databases from the local server for review:
I have two scripts I want to execute against two databases on my local. I can execute both scripts if I want at the same time or I can execute one script at a time. I also have the ability to select only one database I want to run the scripts against or I can select both databases to run the script against (think of syntax and proper use of database names if you go this route in the script). This is just flexible functionality in which the utility presents.
Back to my case at hand; since we deploy to multiple servers against multiple databases also think about the big picture. I can include databases from all over the enterprise environment if needed and let the Multi Script utility control when to run what, where, and in what order. If you look closely you will notice a blue arrow pointing down in the “Scripts to Execute” section. This arrow, along with the up error next to it, allows me to move my scripts up and down in the order I want to. So I can add all the scripts I want at one time and then come back through and organize them when I am ready.
Now, after the script execution is complete you can review the results in the lower window pane of the Multi Script utility. Will look similar to results window below:
A feature I like is the ability to save multiple script executions in different formats on work that was completed; this comes in handy when supplying back a summary report of what was changed to a:
- Change Management Team
- QA Team
- Dev Team
Said All That To Say This
There are various utilities available to us that we may not even have explored yet. This little utility by Red Gate has come in real handy for myself and my team. Take the time to look at what is available to you and see what may fit your needs. This concept does not just pertain to Red Gate utilities. Look at various things in your everyday routine and ask yourself:
- What can I utilize to become more efficient?
- Why am I not utilizing the utility?
- Is there a lack of knowledge?
- Where are my gaps that I can improve on and how do I get there?
- Is there a utility out there where I won’t have to reinvent the wheel?
Look for the hidden gems and continue to provide “Leadership Through Service”
Questions 1 – 5 are like a weekly self-test for me. I periodically start typing to answer a question like “What are all the possible reasons for a slow-running query?” and simply do a brain dump. Now that I am moving into the SQL Database realm the self-test is helpful to get things straight in my head.
Can SQL Multiscript used to execute script(s) against database(s) from the command line or via Powershell cmdlet? I have a CI process that uses SQL Multiscript manually and I’d like to semi-automate it.
Thanks for the question Doug. I have not utilized command lines to automate the SQL Multiscript tool itself; I’ve utilized a combination of Octopus Deploy, Red Gates’ SQL Compare, and TFS to automate such tasks via Nuget packages.