Why Can't SQL Server and Visual Studio Get Along?

Complaining runs the risk of making you look like a miserable crank.

Only, a recent problem that I bumped into while trying to install SQL Server Management Studio 2008 R2 on my workstation highlights a time-consuming, expensive, and recurring theme of versioning and incompatibility between Visual Studio and SQL Server that really needs to be addressed.

Specifically, I'm not writing so much about a single problem that caused me grief—but a manifestation of a trend that just seems to keep getting worse. Specifically, my beef in this case has to do with the ways in which the most recent versions of Visual Studio and SQL Server's management and development tools consistently manifest costly and problematic incompatibilities.

Installation of SSMS 2008 R2 Failed
By way of background, my workstation has Visual Studio 2008 (SP1) and Visual Studio 2010 running on it—because I still have some clients using VS2008 solutions. I also have SQL Server Management Studio 2008 installed because that's what I use to connect to my own development databases (on my dev server) and to some of my more regular clients' SQL Servers as well.

I also have a number of virtual machines (using VMware Workstation) on my desktop that I use to connect to clients' servers via VPN. And, since some of my clients are using SQL Server 2008 R2, I also have a VM with SQL Server Management Studio 2008 R2 installed.

With SQL Server Management Studio 2008 R2 successfully running on a VM, I haven't really felt the need to put it on my workstation. That and I've just worried about any issues or problems that would come from trying to put SQL Server Management Studio 2008 R2 on my box. But yesterday I asked myself: "how bad could it be, especially if I've just done a full system backup?"

Well, it turns out that trying to install SSMS 2008 R2 on my workstation didn't mess anything up.

But it also wouldn't install.

During installation, the installer started off by performing some compatibility checks. Imagine my surprise then when SSMS 2008 R2 complained about two problems that make absolutely no sense.

First, the installer complained about finding Visual Studio 2008 on my computer—without SP1. I've complained a number of times (even in this column) about how insanely hard it is to get Visual Studio 2008, SQL Server Management Studio 2008, and SP1 all living (happily) on the same box. More importantly, the only reason I have all three running on my box together now is because I rebuilt my machine a few months ago to make sure I'd be able to get those three things running on my box at the same time. And I've definitely got SP1 installed (it shows up on the About/Splash page for VS2008, and it's in the Add/Remove Programs listing as well).

Yet, the installer for SQL Server Management Studio 2008 R2 doesn't detect SP1. I can only begin to image how much fun I'll have troubleshooting that one.

Likewise, the same installer tells me I've got SQL Server 2005 Express tools installed. Huh? What? Seriously? Since I just repaved this box a few months ago, I'm sure I've never had SQL Server 2005 on this machine. Nor does anything with 2005 even get listed in the Add/Remove programs listing.

Yes, I'm sure I could probably google these issues and find some workarounds that folks in the community have figured out. But, ultimately, that's something I really shouldn't have to do. My workstation is running Windows 7. It's never had beta versions of Visual Studio or SQL Server on it.

Moreover, if this were just about reformatting my machine to start over from scratch, that would be one problem. But my gripe with that "necessity" is that there's nothing out of the ordinary with my workstation. Instead, what I'm bumping into here is just another manifestation of a recurring theme where it seems like Microsoft is totally dropping the ball when it comes to putting the latest versions of Visual Studio and SQL Server Management Studio on the same workstations.

Not an Isolated Incident
The problem I bumped into when trying to install SSMS 2008 R2 is more than just an isolated issue. In fact, compatibility problems between Visual Studio and SQL Server's client tools are so commonplace now that it sadly didn't come as a surprise when I ran into problems trying to install SSMS 2008 R2 on my workstation.

Moreover, this isn't just a problem for application developers—or even for application developers like me who spend gobs of time in SQL Server Management Studio. Instead, this is a problem that manifests itself over and over again in a lot of really ugly ways for business intelligence (BI) developers as well. And I guess the reason I bring all this up in an article is because I get the sense that Microsoft isn't doing enough to address these problems.

Again, complex software is complex, and there are going to be issues. But when the same problems with versioning between Visual Studio and SQL Server keep cropping up in each successive release (Visual Studio 2008 and SQL Server Management Studio 2008 being the only exception since 2003), you'd expect someone at Microsoft to take note and figure out ways to prevent these issues from regressing into subsequent releases. But from what I've seen (with the exception of the 2008 releases), they're quite blind to this issue.

Par for the Course in the BI Development World
If you're not familiar with Business Intelligence Development Studio, or BIDS, it's the "specialized" version of Visual Studio that gets installed when you put SQL Server client tools on your box and check the proper option. And while it may look and feel like Visual Studio—and use the same core binaries (depending upon which year or release you're playing with), it's important to note that it brings its own sets of assemblies, dependencies, and complexities into the fray when you try to install it and Visual Studio on the same box.

But, in many ways, BIDS is also the "red-headed stepchild" of the Visual Studio family. Yes, it ships with SQL Server (which costs $5000 to $60,000 per core), but it seemingly gets very little attention from release to release, despite the fact that it's the core tool that SSIS, SSAS, and SSRS developers need to use to author their BI solutions. More importantly, with each release of Visual Studio and SQL Server, it's always anyone's guess whether the version of BIDS that ships around the same time as Visual Studio will work with the latest set of technologies released in Visual Studio, or with the previous version.

Likewise, internal consistency problems with BIDS and it ability to play nicely with Visual Studio seem to get worse as time goes on. For example, did you know that Visual Studio 2010 (Microsoft's latest and greatest development IDE) won't even let you open BI projects created with SQL Server 2005, SQL Server 2008, or even SQL Server 2008 R2. (And it's the fact that SQL Server 2008 R2 and Visual Studio 2010 are incompatible that gives rise to the perception that the Visual Studio and BIDS teams at Microsoft don't ever talk to each other.)

That's right, if you just transitioned your organization to Visual Studio 2010, your BI developers will have to manually upgrade or convert all their BI projects and solutions through a tedious process of creating new project, copying in all the assets from the older project version, and saving it that way.

As you can imagine, this hasn't gone over very well with Microsoft's customers.

But wait, it gets worse!

Of course, Microsoft's problems with SQL Server and Visual Studio components "leap-frogging" each other manifests itself in some even more "ugly" ways than just problems with different versions of Visual Studio not being able to work with different kinds of BI solutions.

For example, did you know that SQL Server Management Studio 2008 R2 can't display SSRS Reports created by SQL Server Management Studio 2008?

That's right. SSMS 2008 R2 can only display SSRS reports created by SSMS 2005 or SSMS 2008 R2.

Why? Simple: compatibility problems between different versions of the report viewer. Or, in other words, SSMS 2008 R2 is built with the Report Viewer that shipped with Visual Studio 2008—not the viewer that shipped with SQL Server Management Studio 2008.

Confused? You should be, because this kind of stuff just doesn't make sense.

Or as Jamie Thompson (SSIS junkie) said: "I hear a lot about backward-compatibility issues, but I never thought that current-version–compatibility would ever be a problem. How wrong could I be!" And if you really want to feel like a dog chasing its tail, check out the discussion that launched Jamie's post.

End Recurring Problems with Real Fixes
Assuming that complex software will be without bugs or issues would be naïve. However, the fact that Microsoft keeps letting these problems regress into all versions of Visual Studio and SQL Server except for the 2008 editions gets quite old. I've personally lost entire days to this problem over the years. And I'm just a one-man shop. This problem has to be much more expensive for bigger organizations.

That, and Microsoft has been at this business too long to keep letting these problems crop up in release after release. Moreover, the thing that's probably the most frustrating about this recurring problem with versioning and compatibility problems is that Microsoft sure seems like they take an insanely cavalier attitude toward these problems whenever they crop up. Take a look at any of the references I've linked up above, and whenever Microsoft does respond to these problems it's typically something along the lines of stating that their hands are tied due to internal versioning problems or publishing some asinine matrix that feels like they want you to play bingo with your configuration and tooling choices.

So, again, I don't expect their software to be flawless. It would be nice however if the same problems would stop manifesting or regressing into each release, though. And when these problems do crop up, Microsoft needs to do a much better job of providing actual fixes instead of just telling people that the behaviors they're encountering are "by design" or are due to versioning or compatibility limitations on their end.

Michael K. Campbell ([email protected]) is a contributing editor for SQL Server Magazine and a consultant with years of SQL Server DBA and developer experience. He enjoys consulting, development, and creating free videos for www.sqlservervideos.com.