Search results matching tag 'TFS'

Continuous deployment of SSDT database projects to Windows Azure using Team Foundation Service

jamiet — Sun, 27 Jan 2013 20:07:00 GMT

Continuous deployment is described by Wikipedia as:

Most CI systems allow the running of scripts after a build finishes. In most situations, it is possible to write a script to deploy the application to a live test server that everyone can look at. A further advance in this way of thinking is Continuous Deployment, which calls for the software to be deployed directly into production
http://en.wikipedia.org/wiki/Continuous_Integration

I think of Continuous Deployment as a natural extension of Continuous Integration where not only do we build the source code, we deploy it as well. As I continue to put together my SSDT Database Projects from the ground-up seminar it occurred to me that demonstrating Continuous Deployment for SSDT database projects would be very useful. It then occurred to me that the recently released Team Foundation Service (TFS) includes a facility to build source code so I wondered, could one perhaps use TFS to build an SSDT database project and deploy it to a Windows Azure SQL Database (aka SQL Azure)? It turns out that the answer is “yes” and this blog provides a step-by-step guide to doing just that.

Its worth noting that Team Foundation Service is free for up to five users. SSDT is also free. Windows Azure SQL Databases, however, are not free so you may have to pay a small amount to get one or alternatively adapt the steps herein to use Team Foundation Server to deploy to an on-premises SQL Server instance. Note that a MSDN subscription does include some small usage of Windows Azure SQL Database and that allowance will be more than enough to go through the steps herein.

We can break our process down into the following high-level steps:

Create a Windows Azure SQL Database
Sign up for Team Foundation Service (TFS)
Create an SSDT database project
Add the SSDT project to TFS Source Control
Setup your Publish Profile file
Create a Build Definition

Let’s dive into all of those steps.

Create a new Windows Azure SQL Database

Visit https://manage.windowsazure.com/ and sign up for a new Windows Azure SQL Database:

Your new database will be viewable in the portal immediately:

Windows Azure SQL Databases are, by default, closed to the outside world so you must edit the list of IP addresses that are allowed to access the server. Setting this up is outside the scope of this blog post however simply go to How to: Configure the Server-Level Firewall Settings (Windows Azure SQL Database) to learn how to do this (its very easy).

Sign up for Team Foundation Service

Visit https://tfs.visualstudio.com/ to sign up to use Team Foundation Service using your Microsoft Account (aka Windows Live ID). Once you have signed-up you will have a dedicated service URL (mine, for example, is https://jamiet.visualstudio.com/). Visit that URL and click “New Team Project”:

Fill in the pertinent details and hit “Create Project”:

Creating the Team Project will take a minute or so but will be ready for you when you need it shortly.

Create an SSDT database project

For this you’re going to need SSDT for Visual Studio 2010 or Visual Studio 2012. They are both free and can be downloaded from here; I am using SSDT for Visual Studio 2012.

You will need to create a new SQL Server Database Project:

Once the project is created it will appear in Solution Explorer and be empty at this point. We are going to be deploying to Windows Azure SQL Database therefore we must tell the project that that will be the case. To do that right-click on the project and select Properties:

In Project Settings select Windows Azure SQL Database as the Target platform:

You’ll need to add a database object to your project otherwise this is all rather pointless. Given that this is simply for demo purposes we shall simply create a table called [Table1].

OK, we now have a Windows Azure SQL Database, a TFS service (is that Team Foundation Service service???) and a SSDT database project with a simple table in it. Let’s now hook them all together.

Add the SSDT project to TFS Source Control

Interaction with TFS from within Visual Studio is done using the Team Explorer pane.

From Team Explorer you will need to connect to your new TFS. Click on the address bar in Team Explorer and point to “Connect to Team Projects…”

Choose your project and click Connect. Your server is simply the name of the server that was setup earlier; remember that mine was jamiet.visualstudio.com (note that it uses an https connection, not http):

You should now be connected to your Team Foundation Service. Add your source code to TFS Source Control from inside Solution Explorer:

And check in your SSDT database project (or “commit” if you prefer that terminology):

You will now be able to browse to your TFS home page (mine is https://jamiet.visualstudio.com), click on your project and browse through your checked in code:

Setup your Publish Profile file

You need to tell SSDT where to deploy the project to, that information is stored in a Publish Profile file. In Solution Explorer right-click and select “Publish…”

Enter the details of the Windows Azure SQL Database that you prepared earlier:

Once the Publish Profile file has been saved it will be saved in your project:

At the time of writing Windows Azure SQL Database only supports SQL authentication so we have to edit the Publish Profile file to include the password for the account that will be used for deployment. Right-click on the Publish Profile file, select “Open with…” and from the resultant dialog select “XML (Text) Editor:

Edit the Connection String to include your password:

NOW you can check in:

Create a Build Definition

A Build Definition defines how and when the source code in the project should get built. Create a new Build Definition by selecting “Builds” in the Team Explorer address bar and choosing New Build Definition:

Follow the steps shown in the screenshots below to setup your Build Definition:

(Credit for informing me about the MSBuild arguments goes entirely to Jakob Ehn and his blog post Deploying SSDT Projects with TFS Build)

And you’re done!

If you have set everything up correctly then any future check in should trigger a build and thus a Publish of the SSDT project to your Windows Azure SQL Database. Here is my first build report:

and back in SSDT I can use SQL Server Object Explorer to browse my newly deployed table:

That’s Continuous Deployment for SSDT projects to Windows Azure SQL Database using Team Foundation Service. Awesome!

@Jamiet

Undoing a change will now undo a check-out in Visual Studio 2012

jamiet — Sat, 26 Jan 2013 22:41:12 GMT

In my blog post SQL Server devs–what source control system do you use, if any? (answer and maybe win free stuff) of 18th October 2012 I complained about one aspect of Source Control in Team Foundation Server (TFS) that infuriates me:

…there are aspects to TFS source control that annoy me day-in, day-out. Chief among them has to be the fact that it uses a file’s read-only property to determine if a file should be checked-out or not and, if it determines that it should, it will happily do that check-out on your behalf without you even asking it to. I didn’t realise how ridiculous this was until I first used SVN about three years ago – with SVN you make any changes you wish and then use your source control client to determine which files have changed and thus be checked-in; the notion of “check-out” doesn’t even exist. That sounds like a small thing but you don’t realise how liberating it is until you actually start working that way.

This evening I have been noodling around in Visual Studio 2012 and noticed that this behaviour has changed, as I shall now demonstrate. I opened an XML file (an SSDT Publish Profile file as it happens) that was checked into TFS Source Control:

I made a change to the file and instantly it got checked-out:

I then simply hit CTRL+Z to reverse the change I had made earlier and CTRL+S to save. At that instant the earlier check-out was reversed:

Finally…TFS Source Control now determines whether any changes have actually occurred before considering the file “checked-out”. I know this seems like a really minor change but I promise you, it delights me – especially given I use TFS so much (not through choice I might add).

Perhaps this new behaviour is common knowledge in the TFS fraternity but it was news to me so I figure it might be news to some of you too. Hopefully I’m not the only one that is suitably enamoured upon learning about it.

Note that I am using Visual Studio 2012 and Team Foundation Service.

@Jamiet

Using TFSPreview: Step 1, Connecting

andyleonard — Wed, 28 Sep 2011 11:00:00 GMT

In my last cloud-related post - Introducing TFSPreview: Application Lifecycle Management in the Cloud! – I covered how you can create an account to use Microsoft Team Foundation Server in the cloud via a cool new service called TFSPreview. In this post, we’ll cover connecting Visual Studio 2010 to TFSPreview and adding a project to TFS Version Control.

Connect!

Open Visual Studio 2010. If you have installed Team Explorer for VS2010, you will see a “Connect To team Foundation Server” link on the Start Page:

Clicking this link brings up the Connect to Team Project window. If your TFS server is listed in the “Select a Team Foundation Server” dropdown, select it. If not, click the Servers… button:

Clicking the Servers… button brings up the Add/Remove Team Foundation Server window:

This part is the same if you’re connecting to a TFS server on your network. If the TFS server you wish to reach is not listed (TFSPreview is not listed here), click the “Add…” button to open the Add Team Foundation Server window. Enter the server name or URL (in this case it’s the URL to my TFSPreview instance):

Click the OK button to proceed to sign-in. Click the Windows Live ID link to sign in:

Depending on your cookie settings (Stay signed in or not), you may or may not be prompted to sign into Windows Live:

Once sign-in is successful, you are returned to the Add/Remove Team Foundation Server window, and the TFSPreview server with which you connected is listed. If it is not selected, select it and click the Close button:

Your Team Projects in the Team Projects checked-list is yet another indication of success! Select the Team Projects you wish to access from TFSPreview and click the Connect button:

If connection succeeds, you are now ready to begin using TFS from within Visual Studio 2010!

Add To Source Control…

Open the VS2010 solution you desire to manage with TFSPreview (if it is not already open). From Solution Explorer, right-click the solution (or project) and then click “Add Solution to Source Control…”:

Select the Team Project you wish to use for Application Lifecycle Management (ALM) and click the OK button:

Check It In!

Once the solution (or project) is added to source control, Solution Explorer indicates this by placing yellow “+” icons beside the objects that will be stored in source control. This indicates TFS is aware of the existence of project artifacts that have not yet been checked in. Right-click the solution (or project) and click Check In…:

When the Check In window displays, you can add a comment to indicate information about the version. When done, click the “Check In” button:

Once checked-in, Solution Explorer displays a blue lock icon to indicate the project artifact is now stored in TFS:

That’s it – all checked in! You should sleep better tonight.

Conclusion

There are two kinds of developers: those who use source control and those who will.

:{>

Introducing TFSPreview: Application Lifecycle Management in the Cloud!

andyleonard — Tue, 20 Sep 2011 11:00:00 GMT

At last week’s Build conference in Anaheim California, Microsoft unveiled TFSPreview.com. I was able to score access so I set up access to the server, updated Visual Studio 2010, and began playing around with the functionality. This post describes what I needed to do to get my workstation ready for ALM (Application Lifecycle Management) in the Cloud!

Step 0 – Obtain an Invitation Code

I am not sure about Microsoft’s plans or channels to provide more codes. Brian Harry's blog is a good place to monitor for additional activation codes - this post in particular.

Step 1 – Create the Account

Start by browsing to tfspreview.com:

Click the Create Account button at the bottom of the page. This brings up the Account Signup page. Enter an identifier for your Server URL: This will create https://<YourIdentifier>.tfspreview.com. Enter your Invitation Code, read the Terms of Service, and check the “I Accept” checkbox agreeing to the terms. Then click the Sign Up button:

You may need to sign in to sign up – I did. I used my Windows Live ID. After that I had to wait while my account was provisioned. That’s it! When Step 1 is complete you will see a screen similar to the following:

Step 2 – Create a Team Project

Click the “create team project” link to begin. When the Create New Team Project window displays, enter the Team Project Name, an optional description, and select a Process Template from the dropdown. Here, I named my Team Project “AndyWeather”, added the description “Manages the http://andyweather.com applications, driven by the @AndyWeather weather station”, and selected the Microsoft Visual Studio Scrum 2.0 – Preview 1 Process Template:

I had to wait some while the Team Project was being created – about the same amount of time it takes to create a new Team Project on my local server, so no big surprise there. I think the feedback message is cool:

That’s it for Step 2! Click Close.

Step 3 – Download Software

This took a while when I did it. I’m sure part of the reason was because I was battling a bunch of other early-adopter geeks on release day. My bad. Downloading the software is important because you simply cannot make a connection between Visual Studio 2010 and TFSPreview without it:

Clicking the Download Software link took me to a post from Martin Woodward:

Step 3.1 – Install Visual Studio 2010 Service Pack 1

The first thing I needed to do was install Visual Studio 2010 Service Pack 1.

Click the Download button. You can either click Save to download Visual Studio 2010 SP1 and then manually run it, or click Run to download it and start execution immediately. I clicked Run. Once the file downloaded and executed, I was presented with the following window:

Click Next. Read first, check if you agree, and check if you want to send stats back to Microsoft (anonymously):

Click Install:

Once complete, click Finish.

I saw nothing in the instructions that informed me I should reboot, but I needed to reboot at this point. I encourage you to do the same.

For me, installing VS 2010 SP1 was the most time-consuming step.

Step 3.2 – Install KB2581206

After rebooting, return to Martin’s blog and download and install KB2581206:

Installing the KB doesn’t take very long:

Click the Next button to execute the installation:

You must accept the license terms and it’s cool to send anonymous data back to Microsoft; they use it to improve their processes.

Conclusion

I like this idea a lot, and I’m testing it using more than just my AndyWeather project.

Next up is Using TFSPreview: Step 1, Connecting.

:{>

TFS Log Substitution Policy

jamiet — Wed, 14 Sep 2011 12:36:00 GMT

I recently joined a new client at which Team Foundation Server (TFS) is being used as the source control repository and Continuous Integration (CI) server (I think these things collectively gets referred to as Application Lifecycle Management or ALM for short). I'm fairly ambivalent as to which ALM tools gets used on my projects (in the past year I have used various combinations of TeamCity, Jenkins, Git, Github, Subversion and Jira and haven't had any particular problem with any of them) however returning to TFS did give me an opportunity to try out a utility that I have been intending to look at for a while - $log$ / Keyword Substitution / Expansion Check-In Policy (in the interests of brevity let's call it Log Substitution).

Log Substitution is a TFS Check-in policy that will look for and replace the string "$log$" with information about that check-in; what that means in practise for us T-SQL developers is that you can include the check-in history of a stored procedure within the stored procedure definition. Here's an example of what that looks like:

CREATE PROCEDURE [UKTax].[csp_EntityLoad]
   @pProcessDetailID           SMALLINT    
,  @pDomain                    VARCHAR(64)
,  @pUserName                  VARCHAR(255)
,  @pRowCount                  INTEGER         = 0                  OUTPUT
AS
/*
   <history>
       $log$
       Previous Revision 3104 2011/09/09 14:42:36  Sup_ThomsonJ
         refactoring. Eliminated duplicate code - now adhering to DRY
       Previous Revision 2974 2011/09/08 17:13:43  Sup_ThomsonJ
         Transaction needs to be committed
       Previous Revision 2898 2011/09/01 09:58:31  Sup_ThomsonJ
         Adding $log$ directive for substitution policy
   </history>
*/
BEGIN
   SET NOCOUNT ON;
   SET ANSI_WARNINGS ON;
   SET ANSI_NULLS ON;
   SET DATEFORMAT DMY;
   
   --code goes here etc...
END

As you can see we have a comments section that contains, for each check-in, the following information:

The most recent revision number for this file prior to the check-in
Date and time
The person checking-in
The check-in comment

I asked the various communities within the company what they thought of this and was interested to discover that the .Net developers seemed dead against it - they didn't see the point in copying the check-in information to a different place - whereas the SQL developers were all for it; their reasoning was that it is useful to be able to look at a stored procedure in a production environment and see who to blame when it goes wrong. If you think about it there is no analogy to stored procedures for .Net developers because .Net source code does not get deployed, the compiled object code does, so perhaps its not surprising that there is a dichotomy of opinion here.

I should point out that there is a downside to TFS Check-in policies - they are enforced on the client rather than on the server (which is stupid) and moreover this means that a client install is required. This seems like a rather large limitation to me and although the problem can be alleviated somewhat by the use of TFS Powertools that, again, requires those Powertools to be installed everywhere beforehand (why the TFS Powertools don't just get delivered with Team Explorer is beyond me). Anyway, if you can get around those little nuances Log Substitution can be a useful little utility and given that it went down so well with my fellow SQL developers here I thought it might be worth sharing with all of you. Let me know if you have any opinion on Log Substitution, especially if you have installed it and used it yourself.

@jamiet