Adding Your Entire Enterprise into Red-Gate SQL Multi Script Distribution List

RedGate sells a product call SQL Multi Script. The product allows you to asynchronously execute T-SQL scripts to large quantities of servers. There are a number of useful administrative tasks which can be made easier with this product.

One task, however, that is difficult to perform in SQL Multi Script is adding servers to the distribution lists. To be clear, adding one server, or maybe a half dozen, is easy. The tedium comes into play when you have an existing enterprise with dozens or hundreds of SQL Servers. Manually adding each server and saving the distribution list becomes time consuming.

I have created a solution which solves that problem, if you are using SQL Server’s Central Management Server to keep track of your server list. This solution could easily be modified to read from a text file or Excel as well, I’m just not providing the complete code for those avenues.

Bulk loading servers into a distribution list

SQL Multi Script saves the server entries for your distribution lists in XML format in an Application.dat file.

%AppData%\Roaming\Red Gate\SQL Multi Script\Application.dat

Snippet from the Application.dat file

application-dat-xml1 application-dat-xml2

Generate the XML

In order to load our server list, all we will need to do is generate the XML and copy/paste. The below query can be run on your SQL Server Central Management Server and generate the two sets of XML.

Generate-XML-For-MultiScript

Click here for the code in text format.

Paste

Make sure that you close SQL Multi Script before opening the Application.dat file. If you don’t, there is a chance that SQL Multi Script will overwrite your changes, reverting them back to its original state.

With the Application.dat file open in your favorite text editor, copy/paste the entire [Multi-Script Database Elements] column into the databaseLists XML element.

Next, copy/paste the entire [Multi-Script Server Elements] column into the addedServers XML element.

Save the file and launch SQL Multi Script. You should see your distribution list populated on the right.

distributionlist

Leave a Reply

%d bloggers like this: