Using Red Gate SQL Source Control with DiscountASP.NET Team Foundation Server

A frustrating problem that can happen in database development is when a “change” is made to “fix” an issue that only results in producing another problem. I hope that not everyone has overheard or been in the following exchange because we all exercise perfect discipline in all stages of development:

(As a forward note, the following characters and exchange do not represent any staff members employed by DiscountASP.NET or any conversations that may have occurred in the office.)

Cecil: Hey Robespierre, there’s something wrong with your stored procedure.
Robespierre: Oh, I’ll get right on it and fix it.
Robespierre: Cecil, I’ve taken care of the problem on live.
Cecil: I don’t know what you’ve done but everything’s down!

I’ve covered using Team Foundation Server 2010 Source Control from SQL Server Management Studio but it’s limited to just covering a few scripts. What if the whole situation could’ve been avoided completely?

Since we introduced shared Team Foundation Server 2010 hosting, a product that we have had our eyes on is Red Gate’s SQL Source Control which offers the ability to source control your entire database and I’m very excited to provide you with the configuration steps as it a very excellent utility.

First, make sure that you have SQL Server Management Studio installed.

Next, I would highly recommend either creating a new Team Project and then creating a new folder or just creating a new empty folder. If you need a hand creating a new Team Project, follow the instructions in our “Creating a New Team Project (Visual Studio 2010)” Knowledge Base article.

I would highly recommend using Source Control Explorer by accessing the View menu and then selecting the Source Control Explorer option from the Other Windows menu to create a new folder. Commit the change. At this point, please note the server path as it’s something that will be required later.

Download and install the 28-day trial version of SQL Source Control 2.2 if this is something that you’re interested in evaluating and you can skip this portion if you already have the application installed. After the installation has finished, opening SQL Server Management Studio produces the following:

Connect to your database server and then select the database name:

For this article, I’ll actually be using a live database from my DiscountASP.NET web hosting account to highlight how we can use SQL Source Control across the two separate products.

Make sure that you’re under the “Setup” tab and then click on “Link database to source control.” From the next menu, select Team Foundation Server from the source control system options.

For the server URL field, you will need to append your Team Project Collection name to the URL or you’ll encounter an error suggesting that there’s some type of permission-related issue:

As an example, if you were hosted on TFS10 and your Team Project Collection name is MYTPC, the server URL that you’d want to use is https://tfs10.discountasp.net/tfs/MYTPC.

If you’re not sure what the source control folder path is, remember, you can connect to your Team Foundation Server 2010 server through Visual Studio, open Source Control Explorer (View > Other Windows > Source Control Explorer), select the folder that you created and the path will be available in the source location:

An error that you might encounter is “The path must be an empty folder” so make sure that you have created or selected an empty folder.

When the database has been added successfully, you’ll see that it’s now green:

Now, from SQL Source Control, click on the “Commit Changes” tab and then click on the “Commit” button:

I created a sample “Users” table and just added a couple of columns and then committed the table that I created.

If you open Source Control Explorer in Visual Studio and then drill-down to the Tables folder, you’ll get to see how the change is stored physically.

I decided to create a “View” using the “Users” table that I had created and you’ll also see how it gets added to source control:

We have extended 30 days free promotion all the way to the end of 2011 so if you’d like to try out the DiscountASP.NET Team Foundation Server 2010 hosting and also SQL Source Control from Red Gate, you can establish an account through the DiscountASP.NET TFS Sign Up form.

I’m also pleased to announce that Red Gate is offering a 20% discount on SQL Source Control for DiscountASP.NET customers. For more information, check the DiscountASP.NET TFS Marketplace in your TFS Control Panel and the offer is also available for our web hosting customers in the DiscountASP.NET Marketplace as well.