Introduction

Rolling out changes to databases in another server is one of the most error-prone and repetitive tasks in a DBA's diary.  Most of the time, we need to transfer the same script across several servers, and this may have to be done several times. Red Gate, the provider of user-friendly, third-party tools for SQL Server has come up with a unique and much wanted solution for the DBAs in the field. SQL Multi Script is a new tool from the Red Gate family, and this review covers some of the features of this new tool.

Features

The following screen is what we see just after running SQL Multi Script.  There are two main parts in the screen. The first is in the upper section where we can configure the scripts and set the order of execution and the databases.

The second part gives us information about the most recent execution.

Now let's take a look at how SQL Multi Script works for a particular task. Let’s assume  that we need to transfer the Employee table, its data and some stored procedures to development and production databases. We need to have four scripts:

1.      Table creation

2.      Creation of foreign keys and indexes

3.      Insert data to created table

4.      Creation of stored procedures

We can create scripts in the SQL Multi Script editor or  write them in an editor such as SQL Server Management Studio or Query Analyser. We  then open and add the saved scripts. After creating our scripts, we can click the blue Up and Down arrows in the left-hand pane to adjust and set the order of execution of the scripts.

Once we have got our scripts ready for execution, we can click Configure to create a database distribution list. Database distribution lists specify a set of databases that SQL Multi Script can execute scripts against. We can create multiple database distribution lists and save them for use in the future.

We can add any number of databases to any number of database distribution lists. We can also add several databases, even if they are located on several servers, into a single database distribution list.

By clicking the Add a SQL Server Not Listed button, we can add a new SQL Server that is not already on the list of Databases to Add.

In the above screen, we get the normal parameters we need to enter to add a server.

Once we have the correct list of databases and servers to which we want to deploy the scripts we've created, we are almost ready to begin the script execution. Before we press the Execute button, we can uncheck any scripts that we don’t want to run in our list of scripts. We can also define what should happen from four different options if an error occurs:

After executing our scripts, we see the following output appear in the Results pane:  

Improvements

As this is a new tool, there are a few more features that DBAs would love to have.

·        If an error occurs during execution of a script against a database or set of databases, the database(s) can't be reverted back to the previous stage. This feature is, however, available with SQL Compare and SQL Data Compare from Red Gate.

·        The script editor could be more usable if SQL Multi Script had the option of integrating with SQL Prompt.

·        Security is a bit of a concern in this. Anybody who has access to this tool can run scripts that could potentially cause problems on your databases. You can of course leave the password reminder unselected so that the new user connecting has to know the password to run the scripts. Then again, it will be an issue for genuine users. If you have Windows Authentication on your SQL Servers you can get away with this. However, not all the SQL Servers in this world have Windows Authentication.

·        It would be much better if this product were project based, so that we could have several projects. Currently, we have a list of scripts, and we need to deselect the unwanted scripts, depending on the project.  If there were an ability to save our scripts inside a project, we would have the luxury of saving and operating them separately inside a project. In that case, we would have the opportunity to transfer projects between servers.

Conclusion

 SQL Multi Script is a new tool that has opened up a much easier way for DBAs to run their scripts. As this is a new tool in the market, there will be more features added to this in coming years. Use it yourself by downloading it from Red Gate.