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.
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:
- Turn off transactions on those tests, and write manual test data clean up code.
- 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.
- 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.
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.