Category: Visual Studio 2010

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 – https://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: https://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.

https://arcanecode.files.wordpress.com/2010/08/datadude.pdf

BSDA Presentation on Visual Studio Database Edition

Last week I did a presentation at the Birmingham Software Developers Association on generating sample data using Visual Studio Database Edition, often called by it’s code name of “Data Dude” for short.  You can find my original posting, which has links to the code gallery site at https://arcanecode.com/2009/04/02/sql-server-sample-data-the-sql-name-game/ .

During my presentation I was using Visual Studio Team System 2008 Database Edition GDR R2, which you can find here: http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en 

This update assumes you have Visual Studio Database Edition installed. Most developers with an MSDN license have the Development Edition installed on their PC. When Microsoft announced the Database and Development products would merge in the Visual Studio 2010 product, they made the Development Editions of Visual Studio 2005 and 2008 available via MSDN. Go check your MSDN, and see if you have “Data Dude”. If so download and install it, then download and install the GDR R2 update from the link above. These will add new menus and tools to your Visual Studio environment.

Most notably you’ll look at the Data menu. there are menu options for Schema Compare and Data Compare. These will allow you to setup comparisons between a source and target for schemas or data.

Second Chances – The MSDN Developer Conference

If you are like me, you missed PDC this year, and are probably pretty bummed about it. Fortunately you do get second chances sometimes! Microsoft is hosting a series of developer conferences around the country called the MSDN Developer Conference. This is a one day event that highlights the best of the PDC presentations, put on by a mix of Microsoft employees and community leaders in the area where the conference is held. I’ll be attending the Atlanta event on December 16 2008, but there are events in Houston, Orlando, Chicago, and other areas beginning in December and running into February of next year. Be sure to check the site for more info.

There will be three tracks at the event, the new Azure Services Platform, Client and Presentation, and Tools Languages and Frameworks. There is a $99 attendance fee, but if you compare that to the cost of PDC that’s quite a bargain. There’s also going to be some cool swag, and attendance is limited so be sure to register today. 

Devs and Data Dudes Oh My!

Microsoft has made a big announcement regarding the next version of Visual Studio, Visual Studio 2010. Among other welcome news is that the Developer Edition and Database Editions of Team System will be merging into a single product. This is great news for folks like me (and I suspect many developers) who do a lot of work on both the database side as well as the application side.

The really great news though is that we don’t have to wait for 2010 to take advantage of this. As part of the announcement Microsoft said that effective October 1st, 2008 people who are MSDN Licensed for the 2008 (or 2005) version of Visual Studio Team System Developer will now have access to the Database version, and vice versa.

Database Edition has some great features. One of the ones I use the most is the database comparison tool. It lets me compare data in one database with another and get them into sync. This is great for keeping my local development database that sits on my computer identical with our production system.

I’m sure Database Edition will be new to many developers, so I’d like to mention to books that will help you get up and running with “Data Dude” (as Database Edition is often called).

masteringvstsde The first I have mentioned before, it is SQL MVP Andy Leonard’s Mastering Visual Studio Team System Database Edition Volume 1. This is a great book that focuses exclusively on the database edition. It’s a great resource and one of my favorite books on the subject, I can’t wait for the next volume to come out.

 

 

 

 

apressprovsts The other book is from APress, Pro Visual Studio Team System with Team Edition for Database Professionals. This book covers all aspects of VSTS, including the database tools. I think too often we make life harder on ourselves than we have to, if we took some time to learn the tools available to us, we could be much more productive. I’ve found this book to be a good aid to help me do just that.