Tag: 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.

Recently a customer wanted to share their distribution list with another person on their team. They asked how to do this, and it turns out to be very simple.

We’ve built import and export into the tool. I’ll look at how you can do this.

First, open SQL Multi Script and then pick the tools menu. Here you will see the export and import items, as shown below.

If I pick export, I get a dialog that shows the distribution lists I’ve created.

I can pick all, one, or a group of them. Note that all of these are exported into a single file, which is useful if I can want to share all my lists with a colleague.

Once I click “Export” I have to choose a file name and location from a standard Windows Explorer dialog. I always think about how I’d sort these in a large list, so I like to pick something that will make sense in a month. In this case, the app and then the list..

Once I click OK, the file is created, and I can see it in the file system. Here I’ll open it in Sublime Text to see what it looks like.

The file is XML, which isn’t ideal, but it it easy to read. I have my XML file below, with all instances using Windows Authentication. If I had used SQL Auth somewhere, this would end up with an encrypted password, which I assume SQL Multi Script can import and decrypt. Here is my file:

<?xml version="1.0" encoding="utf-16" standalone="yes"?>

<!--

SQL Multi Script

SQL Multi Script

Version:1.5.4.1390-->

<databaseListsFile version="1" type="databaseListsFile">
   <databaseLists type="List_databaseList" version="1">
     <value version="2" type="databaseList">
       <name>MixedAuthList</name>
       <databases type="BindingList_database" version="1">
         <value version="6" type="database">
           <name>AdventofCode</name>
           <server>(local)</server>
           <integratedSecurity>True</integratedSecurity>
           <connectionTimeout>15</connectionTimeout>
           <protocol>-1</protocol>
           <packetSize>4096</packetSize>
           <encrypted>False</encrypted>
           <selected>True</selected>
           <cserver>ARISTOTLE</cserver>
           <readonly>False</readonly>
         </value>
         <value version="6" type="database">
           <name>dlm_3_qa</name>
           <server>LOCALHOST</server>
           <integratedSecurity>False</integratedSecurity>
           <username>Joe_Admin</username>
           <savePassword>True</savePassword>
           <password encrypted="1">25eJUgEGChcaG13SEpATBg==</password>
           <connectionTimeout>15</connectionTimeout>
           <protocol>-1</protocol>
           <packetSize>4096</packetSize>
           <encrypted>False</encrypted>
           <selected>True</selected>
           <cserver>ARISTOTLE</cserver>
           <readonly>False</readonly>
         </value>
         <value version="6" type="database">
           <name>dlm_2_integration</name>
           <server>LOCALHOST</server>
           <integratedSecurity>False</integratedSecurity>
           <username>Joe_Admin</username>
           <savePassword>True</savePassword>
           <password encrypted="1">25eJUgEGChcaG13SEpATBg==</password>
           <connectionTimeout>15</connectionTimeout>
           <protocol>-1</protocol>
           <packetSize>4096</packetSize>
           <encrypted>False</encrypted>
           <selected>True</selected>
           <cserver>ARISTOTLE</cserver>
           <readonly>False</readonly>
         </value>
       </databases>
       <guid>4e255407-343c-4b88-a54a-4e981ad2beac</guid>
     </value>
   </databaseLists>

</databaseListsFile>

This is a useful feature for sharing lists in a team. It’s a little clunky, but it works well.

If you’ve never given this a try, download SQL Multi Script today, or get the Toolbelt and give it an eval. You might find it’s quite handy.

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.