Recently while preparing a talk on the new features in SQL 2012 I decided that it would be a good time to explore SQL Server Data Tools (SSDT). I already had them installed on my laptop, or so I thought I had them installed because I had checked off the “SQL Server Data Tools” box under the Shared Feature section on the Feature Selection install page as shown in this image:
You might think this would install SSDT. To be honest I’m still not sure what was installed on my laptop as a result of my checking this box. I think the installer placed a shortcut in my Start Menu and that’s about it.
Data information could not be retrieved
Having used previous versions of SSDT (yes, I’m old enough to recall the Business Intelligence Development Studio, now GET OFF MY LAWN) I was familiar with some of the basic functionality available throughout the years. The one item I decided to test was going to be a simple data compare between two AdventureWorks databases. So I opened up SSDT and tried to do a data compare between an instance of SQL 2008R2 and SQL 2012. That is when I was greeted with this lovely message:
My first thought was “What do you mean that ‘Data information could not be retrieved because of the following error: SQL Server versions later than 2008 R2 are not supported'” ?
My second thought was “WTH”? How is it that SSDT 2012 does NOT allow for me to connect to an instance of SQL 2012? That makes absolutely no sense!
Why must life be so hard?
Visual Studio Shell Hell
After some digging around I could see that the shortcut for SSDT was pointing to the existing Visual Studio 2010, and it made sense that VS2010 would not able to connect to SQL 2012. I suspected that I needed to perform some updates. I used the instructions on this blog post in order to install the final few updates for SSDT 2012. As that blog posts says, you need to open up a database project and then you get led to download and install the necessary update.
Once the updates are finished I was ready to get started using SSDT 2012. I go right back to where I left off by trying to do a data compare and I am greeted with this:
Why must life be so hard?
A few choice words later and I start to see that I am still running with VS2010, which cannot connect to SQL 2012. OK then, I decide that now is as good a time as any to update to Visual Studio 2012. Fast forward to a few hours later and I now have VS 2012 installed. However, the shortcut inside the Microsoft SQL Server 2012 start menu is still pointing to VS 2010, which means that I will continue to launch the wrong program from that shortcut.
But that’s an easy enough fix. Just go to the start menu, find SSDT, right-click, then select ‘Properties’.
You should see then this window:
Change the 10 to be 11 (assuming you installed VS2012 to the same default location as you did for VS2010):
Don’t forget that you may need to circumvent the double-secret-probation security settings:
Now when you go back to the start menu you will see that you are pointing to the VS2012 bits:
Great news, right? Well…
SQL Server 2012 Data Tools Does Not Allow For Data Compare
Well….except I still can’t do a data compare, it would appear that functionality was left out of VS2012: http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/355d535f-4a09-4a02-84fb-679b053eb829
Oh well, at least I know I can stop trying to get that to work. I figured the option for a data compare was buried in a menu somewhere and I just could not find it. Good to know I can just stop looking.
Why was it so difficult to get SSDT working properly? I’m not sure. I suspect it is because that different products are released at different times and as a result you need to jump through a few hoops in order to have everything working. There’s no doubt in my mind that the next release of SQL Server won’t need this much hand holding in order to have all of the tools in working order right from the start.
I would have preferred that the option for SSDT to not even exist in the SQL 2012 install. Then I would not have had any expectations that it should have been working right from the start. It was quite frustrating for my SSDT2012 to point to VS2010 and not be able to connect to SQL2012.
I’m also frustrated in the lack of documentation. I really have no idea if what I have installed and configured is correct. It seems to work. I am able to create new database projects. I can connect to SQL 2012. And I am able to look at the shiny new things that people have been talking about. But I really have no idea if I have done this correctly.
I guess I’m spoiled by living in an age where I just expect things to work.
Why must life be so hard?
This sort of thing is why I use SQL Compare and SQL Data Compare from Red Gate. They work (but, then again, I love Red Gate)
Not sure when you wrote this blog, but: SQL Server Data Tools as the replacement tool to BIDS, does not provide you the components you need that are part of database projects in SQL Server Data Tools – Database projects which is a component add-in for either Visual Studio or the Visual Studio Shell that gets installed with SQL Server.
http://blogs.msdn.com/b/ssdt/archive/2012/12/13/available-today-ssdt-december-2012.aspx
I just did validation on SQL Server Data Tools and each of the integration with Visual Studio 2010 / Visual Studio 2012 using SSDT Data Projects in Visual Studio 2010 and Visual Studio 2012 to that perspective – Data Compare works nice and sweet, as the data projects should.
On thing is sure, projects like SQL Server Tools certainly bring the gap between the database developer and DBA closer, which means – learning and findings on both ends, prior to writing a blog on how things don’t appear to work or are confusing in terms of what needs to be installed.
Sounds like a session we should bring at TechED North America and TechED Europe.
Dandy,
I’m not able to use SSDT 2012 to do a data compare. Are you able to do a data compare between SQL 2008 and SQL 2012? Am I not using the correct version then?
I am not disagreeing that the tools are nice, and will be helpful for many thing. I am, however, saying that the process for my laptop to get all the tools up and running has been laborious for me.
I had some of the same pain (not quite so much). SSDT has a lot of great features even above compare, which is cool. But the delivery as a mysterious separate download using a totally bizarre path (make a project and click install? Really?) was … erm … unconventional. And by that I mean horrible.
I won’t disagree.
Darn… so we can’t get rid of that expensive Red Gate Data Compare software we purchased. I agree with Homer and Thomas on this one.
There is significant additional pain if you have a Visual Studio Database Project (Data dude) that you need to convert to SSDT. There are many subtle differences, in addition to features that simply don’t exist in SSDT (yet). They billed SSDT as the new and shiny replacement for Database Projects, but if knew then what I know now, I never would have converted. Even with the December 2012 release, it is missing significant functionality and there are no options to have it use similar file naming and location to Database Projects.
Mark,
I’ve been reading about those pains as well. My best guess right now is that Microsoft would suggest that folks make a clean break from their old environments and build themselves pristine, shiny new desktops and then install only the latest editions of software.
If you do that, apparently everything works.
Unless you still need to manage old database projects (like yourself). In that case you are just out of luck.
You can try Devart’s SQL Data ( http://www.devart.com/dbforge/sql/datacompare/ ) and Schema ( http://www.devart.com/dbforge/sql/schemacompare/ ) Compare Tools. They will save you time and effort and are not expensive. I choosed them long ago and wasn’t dissapointed!
Trying to work with Microsoft SQL Server has been the worst programming experience I’ve ever had. I think I will just stick to Oracle RDMS and try to make the best out of it. The complexity of MSSQLSERVER SUCKS!!!!
This post is almost three years old. At the time the addition of the extra download for the data tools was a nuisance. Microsoft has streamlined things a bit since then.
However, you are the first person I’ve ever heard voice displeasure over SQL Server being more complex than Oracle. Typically it is the other way around.
Sorry to hear you are so frustrated with SQL Server.