September 22, 2010 at 10:05 am
Hello,
I'm trying to do more set based work rather than RBAR. So this is really a request to better my thought process I suppose.
I have a bunch of sql servers I monitor on a daily basis for job failures(backups / etl processes / report subscriptions /etc).
My issue is that I test each server using sp_testlinkedserver before trying to query it using a cursor, and I was wondering if there is a way to check connectivity to all the linked servers without using a cursor(or a while loop, or looping through a table variable). I'm trying to do it all at once if possible.
I'm not one of those people that hates cursors, like everything else in dbaland, it depends. But I would like to be able to offer a better way to do something as opposed to just saying no.
Any nudges in the right direction or advice appreciated.
Thanks,
-chris
September 22, 2010 at 4:30 pm
Some people use SSIS to perform some of these repetitive tasks and honestly, for some tasks, it works quite well.
I've been working as a DBA for 3 years almost, but my background comes from ETL, that's where everything started for me and I'm able to leverage my ETL/SSIS knowledge with my DBA tasks in some occasions.
I don't see a problem with using a while loop to test your linked servers, but on the future, you could use a SSIS package to perform that and other repetitive tasks you might have. You can use the SSIS package as a "wrapper" for all your repetitive maintenance tasks, while taking advantage of the ability to run tasks in parallel, naturally decreasing the amount of time your tasks might take.
Hope this helps.
September 23, 2010 at 2:19 pm
Thanks, I don't know why I didn't even consider SSIS. I'm in the middle of a dts/ssis upgrade now....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply