SQL Server Data Tools in Visual Studio 2012

In August I’ll be giving a couple of presentations at devLink. One of them will be on the new SQL Server Data Tools that was released with SQL Server 2012. As you may be aware, I’ve been a proponent of Visual Studio Database Projects since their initial release with Visual Studio 2005.

With SQL Server 2012 the SQL team took ownership of the database projects. They completely retooled them so now they can release them as “out of band” add-ons for Visual Studio. The new version is called SQL Server Data Tools, or SSDT for short. It’s included with VS2012, or you can download a version compatible with Visual Studio 2010 at http://msdn.microsoft.com/en-us/data/tools.aspx.

I’ve been using it for a real world production project for some time now. While I like it, there are some major differences between the new SSDT and the former database projects. Over the next few blog posts I want to highlight some of those differences, culminating with the devLink presentation.

For this series of posts I’ll be using the Visual Studio 2012 Release Candidate, which from here on I’ll simply refer to as VS2012. As this is a Release Candidate there shouldn’t be any noticeable changes between now and the final release.

If you are still on VS 2010 don’t fret, what I’ll describe applies to it as well, assuming you have gone to the link above and downloaded the SSDT add in.

The first difference is with creating a new project. With VS 2012 the older database projects are gone. Only in 2010 can you still do both. Here’s the new project screen shot from VS2010:

SNAGHTML30634815

 

Here is the screen shot from VS2012.

SNAGHTML3065daa3

As you can see, the Database branch is gone and only the SQL Server Database Project exists.

Now for the next difference. With VSDB Projects, when you created a new project you were immediately walked through a wizard that helped you with various default choices, and allowed you to import a database. With SSDT, once you create a new project you are given a blank slate, an empty project to start from.

image 

If you are creating a new database from scratch, it is left to you to create the entire folder structure, and to name your files correctly.

I’d highly suggest though that you import at least one database, to see how the wizard organizes things, so that you can follow suit. Importing a database is as easy as it was in VSDB Projects, but we’ll save that for the subject of the next blog post.

Column Cut Copy Paste in VS SSMS and PowerShell

Did you know it’s possible to do Column based cut, copy and paste in Visual Studio, SQL Server Management Studio, and PowerShell v3? Not many people do. Even less people know that with VS 2010 and SSMS 2012 you get a little “extra” functionality. Watch the video to find out all the juicy details.

 

Using TFS2010 with Visual Studio / BIDS 2008 and SQL Server Management Studio

When I come to a customer site, I often have to help them get setup with TFS (Team Foundation Server) 2010, Microsoft’s source code control / ALM (application lifecycle management) system. This is so they can work with their BIDS (Business Intelligence Developer Studio) projects as a team, giving the added benefit of source code control. I’ve had to do this often enough I wanted to record the steps for my own use, and hopefully others too.

Installing the TFS 2010 tools for Visual Studio / BIDS 2008

First off, thanks to Derek Miller for covering most of the steps involved in his blog post http://derekjmiller62.wordpress.com/2010/10/19/using-tfs-2010-with-bids-2008/. I won’t go into the detail he did, but will summarize into these basic steps.

1. If you haven’t installed Visual Studio 2008 Service Pack 1, do so by downloading it and installing.

2. Next, you will need to install the Visual Studio 2008 Team Explorer.

3. After installing Team Explorer, you will have to go back and reinstall VS SP1 (from step 1). Don’t skip this step! Team explorer has some older components that overwrite the SP1 components, and you will have reinstall them.

Now this next part I really haven’t seen anywhere else and was a real pain to find, and thus is the main reason for this post. During the SP1 install, we often see “Visual Studio SP1 Installation Failed”. Checking the error log, buried deep you will find “Returning IDOK. INSTALLMESSAGE_ERROR [Error 2902. An internal error has occurred. …”

When you see this, go to your Control Panel, and then to Add Remove Programs. Look for a program called “Microsoft Visual Studio Web Authoring Component” and uninstall it. This is actually installed as part of the Office suite, and you don’t really need it since you likely have much more powerful web authoring tools, or since you are doing BI development won’t be doing an web development in Microsoft Office.

After uninstalling it, SP1 should then install, and you are ready for step 4.

4. Install the Visual Studio Team System 2008 SP1 Forward Compatibility Update for Team Foundation Server 2010. That probably took you longer to read than it actually will to install. After installing, it may prompt you to reboot. Even if it doesn’t ask you should reboot anyway, we’ve seen a few times when we weren’t able to connect until we rebooted.

After that you should be able to go into Visual Studio and go to Tools, Connect to Team Foundation Server. If you still have problems connecting, I will refer you to Derek’s post where he describes some registry entries you can try. So far we haven’t found them necessary, but you may.

Installing the TFS 2010 Tools

Note that there is one big limitation to using TFS 2010 with VS2008. You can connect to a TFS site and upload your solutions and projects, but you can’t create a new team site with VS2008. To do so, you will need the VS2010 shell with the TFS components, a free download.

Installing TFS 2010 for SQL Server Management Studio (SSMS)

Now that you have BIDS all setup to work with TFS, it only makes sense to make your SQL Server Management Studio (SSMS) also work with TFS. Joseph Jun has a great blog post that goes into all the nitty gritty of how to do this. The short version though, is after you install the TFS 2010 tools in the step above (and they are a prerequisite) you need to install the Team Foundation Server MSSCCI Provider 2010.

After the install, you should see a new Source Control menu option under the File menu in SSMS. From here you can launch the TFS 2010 management shell or open an existing SSMS project / solution. If you have a solution you need to add, simply right click on the solution in the Solution Explorer window and pick Add to Source Control.

Visual Studio Database Projects

Note that if you are using Visual Studio Database Projects, any SQL Server 2008R2 development must be done in Visual Studio 2010. VS2010 is already setup to talk to TFS 2010. If you are using VS 2008 database projects to build a SQL Server 2008 (non-R2) database, then with the steps above you should be good to go for checking in your database project into TFS.

And away we go!

And with that you should be setup to manage your BI Development in Team Foundation Server 2010. It’s a lot of work, but well worth the effort. Using TFS will let your BI staff work as a team to develop projects. Additionally you have the benefit of source code control, something invaluable in the case of package corruptions or needing to track history.

Data Dude Webinar for Pragmatic Works

Just wanted to let everyone know I’ll be doing my first webinar for Pragmatic Works this Thursday, June 9th 2011. It will be at 11 am Eastern time.

I’ll be doing an introduction to “Data Dude”, Visual Studio Database Projects. We’ll cover such things as generating a project from an existing database, using the safe refactoring tools, and generating sample data. There’s no cost, and I hope you can join us live so I can answer any of your questions. If you can’t, then we’ll be recording the session for later viewing. To register, jump on over to our website and register:

http://pragmaticworks.com/Resources/webinars/Default.aspx

If Data Dude isn’t your thing don’t worry, there are lots of upcoming webinars. Next week Brian Knight is doing an intro to MDX session, and Ben Evans will be doing one on Data Driven Website Design. There’s also a nice selection of already recorded past webinars available for instant viewing. And like I said, it’s all free. How cool is that!

SQL Rally – Data Dude for Devs and DBAs

I’m at SQL Rally today, presenting a spotlight session on Visual Studio Database Projects, aka Data Dude.

While the slide deck is not overly large, I did want to pass it along. You can download it from here.

Here are some of the topics we’ll be covering

–Creating a project

–Refactoring

–Deployment

–Data Creation

–Schema Comparisons

–Data Comparisons

–Unit Testing

If you are mostly interested in the links, here they are:

SQL Name Game – My example on how to generate Sample Data – http://arcanecode.com/2009/04/02/sql-server-sample-data-the-sql-name-game/

Data Dude Blog – http://blogs.msdn.com/gertd/default.aspx

VS Database Development Tools Forum – http://social.msdn.microsoft.com/Forums/en/vstsdb/threads

VS Development/Database Edition Home – http://msdn.microsoft.com/en-us/vstudio/dd408380.aspx

Finally, here are the tools we’ll be using, along with links to previous versions:

Visual Studio for Database Professionals “Data Dude”

Included with VS2010 Premium and higher

Included with VS2008/2005 Professional and higher

2008 GDR2 – http://www.microsoft.com/downloads/details.aspx?familyid=BB3AD767-5F69-4DB9-B1C9-8F55759846ED&displaylang=en

•2005 DB Pro Addon – http://www.microsoft.com/downloads/details.aspx?FamilyID=7de00386-893d-4142-a778-992b69d482ad&displaylang=en

Visual Studio Database Project Course

I’ve recently completed another course for Pluralsight, this one is on Visual Studio Database Projects. In it, I cover how to reverse engineer an existing database, then the many options you can configure for deployment. I then cover data comparisons, schema comparisons, and pre/post deployment scripts.

One of the most powerful features of Visual Studio is the data generation tools which give you the ability to generate vast quantities of realistic, predictable test data. Finally, I cover how to unit test your database code.

While I use Visual Studio 2010 and SQL Server 2008 R2 in the course, everything I teach is applicable all the way back to Visual Studio / SQL Server 2005, so even if you are still on an older version of your development tools the lessons you learn here can still be applied.

Visual Studio Database Developer – Data Dude at SQL Saturday 21

On Saturday August 21, 2010 I will be presenting “Visual Studio Database Developer Edition – Data Dude” at the Nashville SQL Saturday #51. This looks to be a great event, and I’m excited to be a part of it.

My slide deck is downloadable from this link: DataDude.pdf

In my presentation I also used some data from a previous post to generate data. That post has detailed information on how to generate sample data, and can be found here: http://arcanecode.com/2009/04/02/sql-server-sample-data-the-sql-name-game/

Thanks to everyone who attended, hopefully after my presentation you’ll have the same passion for Data Dude that I do.

Follow

Get every new post delivered to your Inbox.

Join 104 other followers