SQL Multi Script v 2008 multi server functionality

Category: Frequently-asked questions
Date: 16 Dec 2010
Product: SQL Multi Script
SQL Server 2008 introduces some basic multi server query functionality in Management Studio. How does this compare to using SQL Multi Script?

The functionality in Management Studio is basic and limited. SQL Multi Script is more versatile for running scripts against multiple database, and copes with different situations. It's also more intuitive to use.

SQL Multi Script offers the following benefits beyond SQL Server 2008:

SQL Multi Script can combine the results of queries that return differently structured results.

In SQL Multi Script you can run scripts against multiple databases which have a different name on different instances or servers. In Management Studio you can only run queries against multiple databases if they have the same name. This is a big limitation of SQL Server 2008 multi query capability.

SQL Multi Script can handle much larger files and result sets than SSMS.

SQL Multi Script saves previous results for the duration of the session, so if you run a query then run something else, you can go back to the last set of results, whereas SSMS only keeps the last set of results per query window

You can save distribution lists of databases for which you want to run queries against.

You can run more than one script against multiple databases. In SQL Multi Script you can order a set of scripts and run them in one go compares with SQL Server which only allows a single script to be executed.

You can configure the behaviour if there is an error during execution of a script

SQL Multi Script is much more intuitive to use

SQL Multi Script lets you configure the number of databases to run scripts against in parallel, allowing the user to optimise the network traffic caused by running expensive queries in parallel.

SQL Multi Script has a more robust CSV output than SSMS. (It's quite easy to get SSMS to generate invalid CSV output)