A Wandering Mind

From Linchi Shea’s blog:

In SQL Server, it is rather handy to retrieve data from a different SQL Server instance and use the result locally in another SQL statement for further processing. In theory and in the set purists’ fantasy land, it shouldn’t matter where you get your data or even how you get the data as long as you can use them to further compose a set-based solution.

That is all fine. After all, in a distributed environment the data you want may not be on the same instance where the processing takes place. Well, that is fine until it comes to performance and troubleshooting. If you use linked servers indiscriminately, sooner or later you’ll run into these issues, and they are not pretty.

We have used linked server functionality at various points at work. As Linchi points out, it can be quite useful when you have data distributed across multiple systems. But I have seen cases where it’s caused issues, such as excess network traffic, authentication problems (anyone actually been able to get Kerberos auth to work for a two-hop Windows auth’d linked server?), and general performance malaise.

So what are some alternatives?

• Replication – true, it may not solve the network issue, and depending upon the type used, it might cause a delay in data transfer. But it would allow for queries to execute against a single server.
• Static data transfer using SSIS – I would say this would be fine for offline use of data such as reporting. There have been very few cases in my experience where true real-time data has been a hard business requirement, though at times the point has been, shall we say, vigorously argued.

Today I caught myself getting rather ticked off after spending around 2.5 hours trying to script out changes to around 25 SQL Server stored procedures. To explain in a non-geeky (if that’s possible coming from me) way, I had to comment out a common line in all 25 procs that needed to be disabled in order to use the proc in my test system. (OK, that failed only slightly in the non-geeky department.)

I’ll be the first to admit that once I get focused on solving an issue, it pretty much consumes my attention, to the point of near obsessiveness. This was no exception, with me getting more and more worked up as attempts to automate this change (rather than cracking the code open on all twenty plus one at a time) failed. Finally I threw up my hands in disgust and walked away, having undoubtedly raised my blood pressure a notch or two in the process.

A short time later, it occurred to me that most of what I was trying to accomplish was completely outside of the work necessary to accomplish my pre-stated goals for this project. In effect, I was trying to change twenty plus bits of code, when this particular effort required just one change. Yep, you heard me, just one. So why even make the attempt to automate the process?

I suppose it’s mostly because I’m a lazy coder by nature; if chances are better than 50-50 that I’ll need to repeat some action in the future, I’ll probably at least take a stab at scripting or otherwise automating the process. Plus, in this case, it presented a worthy challenge, which is always more than enough to entice me into jumping in over my head. I’m a sucker for challenge, almost to the point of it being a character flaw.

In hindsight, perhaps if I’d stopped at the onset and thought things out in a rational, methodical manner, I would have saved myself not only a brooding headache, but almost two hours of time as well. Two hours. Think of all the things you could get done in two hours time.

Next time, I’m going to follow my own advice, and make sure I properly define what my work is going to be, lay out all the time and effort needed, and only then consider adding scope to my plate. Sure, automation is a wonderful thing, but only when used with discretion. Spending two hours to avoid doing something that ultimately took around 10 minutes to accomplish manually just isn’t worth it.