Execute an SP from one server against multiple others

Question

Execute an SP from one server against multiple others

SQLHeap

Hall of Fame

Points: 3572
More actions
February 11, 2014 at 11:04 am

#288126

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...

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

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

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

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQLHeap Hall of Fame Points: 3572 More actions · Answer 2

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...

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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

SQLHeap Hall of Fame Points: 3572 More actions · Answer 4

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...

simmonssql Old Hand Points: 301 More actions · Answer 5

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.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

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