Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Applying Your Scripts across Multiple SQL Server Databases

By Dinesh Asanka,

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.

Multi Script Main Screen.jpg

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.

Configure Database.jpg

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.

Add Server.jpg

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:

Error.jpg

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

Execute.jpg

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.

Total article views: 3261 | Views in the last 30 days: 11
 
Related Articles
ARTICLE

Reporting on Activity across Multiple Servers using SQL Backup and SQL Multi Script

Rodney Landrum presents a creative solution for dynamic reporting across all of his SQL Servers, bas...

ARTICLE

Multi-Script, Multi-DB Deployments

A way to handle application releases involving multiple scripts and/or multiple databases.

BLOG

PowerSQL – Find OS and Memory Usage Details – Multi Server Script – No Input files

Multi Server script to find OS and Memory details.  It doesn’t require a text file as its input but ...

ARTICLE

Multi-Server Administration

Multi server administration allows you to create jobs and maintenance plans once. You can then monit...

FORUM

multiple database creation script

how to execute schema creation script and maintain them in a database ?

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones