Pusher and QueryPusher Pick Up Where CMS Leaves Off

Downloads
102922.zip

Central Management Server (CMS) in SQL Server 2008 is a great tool that lets both T-SQL novices and experts execute T-SQL statements against a group of SQL Servers machines simultaneously and receive the results in a single result set. However, it has some limitations. CMS runs only on SQL Server 2008, and you need to register servers. Plus, you can't set up a SQL Server Agent job to run a script through CMS.

I created two utilities—Pusher and QueryPusher—that pick up where CMS leaves off. Pusher is used to push out code that creates objects or performs an action against a list of SQL Server machines. For example, you can use Pusher to run a script that creates a stored procedure. Pusher isn't designed to return results back from a query—for that, you need to use QueryPusher. With QueryPusher, you can execute complex queries against multiple servers. Unlike CMS, Pusher and QueryPusher work on SQL Server 2005 and don't require you to register servers. And you can use Pusher to execute a command that starts a SQL Server Agent job.

 

Using the Pusher Utility

To use the Pusher utility, follow these steps:

1. Download Pusher and QueryPusher by clicking the Download the Code Here button near the top of the page. The 102922.zip file contains the utilities as well as some sample scripts you can use to test them out.
2. Save the code that creates an object or performs an action in a script. Place the script on the SQL Server machine you want to run it from. The script must be accessible by the SQL Server service. For demonstration purposes, let's say you want to use Pusher to run the PushedObject.sql script that's in the C:\dpg directory on Server1. (You can find PushedObject.sql in 102922.zip. This script creates TestSproc, a stored procedure that returns the current date and time.)
3. Log on to Server1 and copy the Pusher code into a new query window in SQL Server Management Studio (SSMS).
4. Find the code in Listing 1. In the code at callout A, you need to specify whether you want to use the Preview mode (set the @choose variable to 'P') or Execute mode (set the @choose variable to 'E'). In Preview mode, Pusher displays the code to be executed but doesn't run it. In Execute mode, Pusher runs the code. You should always preview the code before running it, so set the @choose variable to 'P'.
5. In the code at callout B, set @file to the fully qualified pathname of the script you want to run. (Leave in the letter N and the single quotes.)
6. In the code at callout C, modify the INSERT @Servers statement so that it specifies the servers you want the script to run against. (Leave in the letter N and the single quotes in each server name.) You can increase or decrease the number of servers as desired. No matter the number, the last SELECT statement should not include the UNION ALL clause.
7. Execute the Pusher utility.
8. If you're satisfied with the preview, set the @choose variable to 'E'.
9. Execute the Pusher utility again.