Loopback and kiss myself

A consequence of using transactions to rollback and thus cleanup after database dependent tests is that some code, which would not otherwise be run within a transaction context, doesn’t work when it is. One such situation is the case of a loopback server, which I’ve encountered several times over the years. 

when people stop being polite... and start getting real...The Real World

A real world production environment might consist of several databases spread across several machines.  And sometimes, as distasteful as it may sound, those databases are connected via linked servers.  That is exactly the situation that we presently find ourselves in. We have quite a few views and procedures that make use of these linked servers, and those views and procedures invariably get called from within unit tests.  That in of itself isn’t an issue for transactional unit tests.  The critical factor is that our test environments, and more importantly our development environments aren’t spread across multiple machines, but instead host several databases on one local SQL server instance. 

There can be only one

In order for code that utilizes linked servers to be executable in development environments, we create linked servers that actually point back to the same SQL server instance, creating a loopback server.  Presently, loopback servers don’t support distributed transactions.  So what to do with transactional unit tests that call loopback code?

A few options come to mind, but are impractical for us:

  1. Turn off transactions on those tests, and write manual test data clean up code.
  2. Use aliasing so that the code doesn’t actually interact with the linked servers directly, and then simply don’t use linked servers in development environments, instead have the alias point directly to the tables, etc.
  3. Use virtualization to mimic physical production configuration.

These seem viable and actually #2 seems like a pretty good idea, but we have a mixed SQL 2000 and 2005 environment and aliasing is only available on 2005, so we’ve never even tried this.  Option #3, although it would more closely resemble the production configuration is more practical for a test environment than a development environment.  So, while it may solve the former, we still need to solve the latter issue, without the need for an overly complicated complete and self contained virtual environment for each developer.  Option #1 is just a step backwards that we’d like to avoid.

For instance keep your distance

There is a simpler option and is the path we recently chose after implementing transactional unit tests and finding numerous tests that immediately failed due to the loopback problem.  We simply set up a second instance of SQL server on the development machines, and then configured the linked servers to point not back to the same instance but to the two different instances.  For transactions to work, it turns out that the linked servers don’t have to be on two physically separate machines, just two different instances of SQL server.  This solution may have limited applicability for environments with multiple servers (and multiple links) or that don’t use linked servers at all.  But for us, with essentially only two databases that utilized the linked servers, it solved our issue without forcing us to change any code.  Its only slightly more complicated in that we have to run two instances of SQL server.  Eventually, we may end up moving to an aliasing solution, but that will require code changes and an upgrade, but for now we’ve sidestepped the loopback.

Comments

Post a Comment

Popular posts from this blog

RIF Notes #42

“In practice, problems are delegated but the power to address them is not.” - Maslach & Leiter Building the Object Model You Want with Entity Framework How you can improve your SQL with code analysis in SQL Prompt A growing number of young Americans are leaving desk jobs to farm – This is a head scratcher. Macro trends in the tech industry Technology Radar Vol 17 Essential .NET - C# 7.0: Tuples Explained – “What many folks didn’t realize when it was first introduced is that the new C# 7.0 tuple all but replaces anonymous types—and provides additional functionality.” Editor's Note – Misprint – “Any IT manager can regale you with stories of undiagnosable printer failures, but even at the home-office level printing is an exercise in fail” .NET Standard - Demystifying .NET Core and .NET Standard Devops - Continuous Data Migration Using Visual Studio and TFS
Read more

RIF Notes #4

“It is better to be feared than loved, if you cannot be both” – Machiavelli “A well-stated problem is half-solved” – John Dewey jsFiddle - Cool javascript testing tool Modernizr - Modernizr is a small and simple JavaScript library that helps you take advantage of emerging web technologies (CSS3, HTML 5) while still maintaining a fine level of control over older browsers that may not yet support these new technologies. Why We’re Going With HTML(5) Instead Of Silverlight – More on the debate between Silverlight and HTML5.  A year ago I would have said Silverlight, hands down, for LOB development, now its not quite as clear.  At least not if your users need to access your apps from their iPads and Droids. Bootstrapped, Profitable, & Proud: Braintree – Braintree Payment Technologies was the first company I contacted when I first started investigating credit card tokenization a few years back.  Remote Desktop Protocol (RDP) -- Technical Analysis - Matt found this article, for an
Read more

The greatest trick the AI ever pulled was convincing the world it wasn't THAT intelligent

Just listened to The AI Dilemma and it definitely elaborated on and articulated some of my own concerns, as did the news of OpenAI plugins. The synthesis of concern is: AI doesn't need to be that powerful to be destructive and destabilizing (e.g. social media algorithms) to people and society. These new LLM's are already much more powerful than social media algorithms. Many of the new LLM capabilities are emergent and poorly understood. The experts designing these systems don't fully understand them, and some of whom are already worried about what they've created. LLM's are being rapidly and recklessly deployed very broadly across society, in an arms race. The strongest argument against worry is that these LLM's aren't really intelligent, are really just a super-autocomplete that enables automating mundane tasks via natural language. But I don't think any of the companies developing or deploying these things are arguing that. Even if they were, see #1 &
Read more