February 11, 2014 at 11:04 am
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...
February 11, 2014 at 11:15 am
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
February 11, 2014 at 11:26 am
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...
February 11, 2014 at 11:31 am
Have you tried Central Management Servers?
February 11, 2014 at 11:45 am
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...
February 11, 2014 at 12:19 pm
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.
February 11, 2014 at 5:42 pm
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