Execute queries on multiple SQL Server Instances

Back in my SQL Server DBA days, I worked for an Application Service Provider (ASP) which would now be called Software as a Service (SaaS).  I recall during application upgrades I often had to run a few thousand line script in each of our client's databases. Now this would be fine if it were only 5-10 client databases but we had anywhere from 75-100 at any given time. At that time we had to use native tools so I would execute the script in each client database manually by changing the database after each successful execution. This operational nightmare could have been easily avoided with Toad for SQL Server's Group Execute functionality available within the SQL Editor.

By default, Group Execute is disabled but it can easily be enabled by opening a SQL Editor window and selecting the Editor menu, then Windows and Group Execute. A dockable Group Execute window will display alongside the SQL Editor. You can then check each SQL Server instance to execute the script (figure 1 below). Results are displayed for each SQL Server instance in their own result set grid.

Figure 1.

Extending this feature further, you can also select particular databases on each SQL Server instance to execute the script. Press the yellow database button in the upper right corner of the Group Execute window so that the databases under each SQL Server instance are populated (figure 2 below).

Figure 2.

You can also merge the results in one result set by pressing the icon highlighted below in figure 3. This allows you to export to Excel or another supported format by right clicking anywhere on the results grid.

Figure 3.

Now you can see how powerful and time saving Group Execute functionality can be when running scripts on multiple SQL Server instances. This functionality is also available in Quest's cross-platform query and data preparation tool Toad Data Point. Enjoy!