SQLTeam.com | Weblogs | Forums

Linked Servers issues

tsql

#1

I wrote an article a while back on linked server naming: http://weblogs.sqlteam.com/billg/archive/2011/08/15/how-to-name-linked-servers.aspx

I'm still frustrated at using linked servers in production. We're struggling with DTC configuration issues -- especially in our cluster. I still dislike the naming of them. They lead to unexpected dependencies when applications connect to server A through server B.

But they are just so handy to pull data across. I've been thinking about small JSON based APIs into the linked servers that the applications could call. But that's even more to maintain.

I'm sure somewhere there's a right answer. I just don't know what it is.


#2

Have you read Mladen's articles on Service Broker? Admittedly the setup can be daunting, and using it somewhat non-intuitive, but you avoid all the problems with DTC and most security issues. You can write some helper procedures to send messages and process them to return results. The other nice thing with 2012 and higher is multicast send. And you still retain transactional processing if you need it. I'm dabbling with it and it's pretty sweet.


#3

Yeah, I've been doing a fair bit with Service Broker. That's one of the things we're considering. It has its own setup issues though. I'm afraid it would be like the old joke about using regular expressions as a solution...