Execute an SP from one server against multiple others

  • I'm trying to figure out what is the best way to execute 1 stored procedure against multiple other SQL servers. SSIS, Linked Server, or something else. What do you think?

    There is an exception to every rule, except this one...

  • If you're going to execute them from SSMS, there is a piece of functionality that's really cool. In your Registered Servers, right-click the name of one of your groups, then click New Query in the context menu. SSMS opens a new window for you that's connected to all the servers in that group. You can then issue SQL to run against all of them and the result set will include the server name as the first column. You will also notice your status bar at the bottom will tell you that you're connected to multiple servers.

    HTH

  • Thanks for the reply. I should've been more detailed. The SP needs to run on job schedule. It's not a one-time thing.

    There is an exception to every rule, except this one...

  • Have you tried Central Management Servers?

    http://blogs.technet.com/b/lobapps/archive/2013/01/01/manage-multi-servers-effectively-using-sql-server-central-management-server-cms.aspx

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I forgot about CMS, thanks. I'm still not sure how to execute an SP to a CMS group through a job schedule.

    There is an exception to every rule, except this one...

  • I've done this successfully through Linked Servers and SSIS, although I'd say the former is the easier/quicker of the two to configure. Also, if you use linked servers and want to add another instance in the future, you can cater for this by using a CTE in your job to return the relevant linked server names from master.sys.servers before executing.

  • I explain how to do the multi-server execution on scripts in this article[/url] (it's focused on backups, but should work for you). Phil Factor talks about some other aspects of what you can do in this article[/url].

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply