July 24, 2009 at 9:19 am
Hi all,
I am working on a task that would use one server in a farm of servers (50+) to connect to the other (49) servers and select against a set of tables (basically reading the sql job history)
In the past I would set up a linked server from the "head" server to the other secondary server(s) and execute a 4 part named query to the table(s) in question...inserting them to a local table for the secondary work...
I want to be able to have the process (querying the sql job history tables) run through a list of linked servers and execute the same code.
In the event a server is down...I was hoping to come up with some logic / error handling that would allow the error to be logged and handled which would allow the process to continue on without stopping the whole process in general...
QUESTIONS
1. In the world of SQL 2008 is a linked server the best way to go these days...
2. Would there be a way to execute all the commands (selects against the linked servers) concurrently or is a one at a time method the best way to approach this process...
I can give more details on what I was hoping to accomplish...just hoping to streamline this if there are any new methods available in SQL 2008 for executing remote code vs. creating linked servers path...
Any recommendations or questions are appreciated...
Lee
July 24, 2009 at 1:49 pm
I can't answer your question directly as I do not have much experience with linked servers, and frankly I do not like them. But, you might want to look into SSIS. It'd be pretty simple to put together a package that's grab all that information and drop it to your central server. Additionally, you get logging and such, basically, built in.
Cheers!
July 24, 2009 at 2:18 pm
Lamprey13 (7/24/2009)
I can't answer your question directly as I do not have much experience with linked servers, and frankly I do not like them. But, you might want to look into SSIS. It'd be pretty simple to put together a package that's grab all that information and drop it to your central server. Additionally, you get logging and such, basically, built in.Cheers!
As sad as it is to admit...I was directed "for lack of a better term" to use the linked server model...
SSIS is not used at all where I work from a management or development standpoint...so no one knows how to use or implement it.
'Stick with what works' I guess...
Also the linked server method would be used for other things (queries...ect)
I would like to go the direction of SSIS...possibly creating a DR kind of setup but that hasn't been investigated...or viewed as needed.
July 24, 2009 at 2:51 pm
Try/Catch will catch it at a higer scope. Something like this will work:
BEGIN TRY
exec ('select * from mattserver.mytable.dbo.tablename')
end try
begin catch
print 'that is not a valid server'
end catch
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply