In the previous posts
Deployment to several databases using SQL Server Data Tools and TFS using a Custom Workflow file
Deployment to several databases using SQL Server Data Tools and Team foundation Server
Continuous integration with SQL Server Data Tools and Team Foundation Server
I have been mostly writing about the interaction between SQL Server Data Tools and Team Foundation Server. Microsoft provides a hosted version of Team Foundation Build Service called Visual Studio Online. The configuration and functionality is mostly the same than what I have previously been writing about but there are some specifics things that we need to be aware when using Visual Studio Online Build Service.
What is Visual Studio Online?
Visual Studio Online is a cloud-based code repository, which is based on the capabilities of Team Foundation Server with additional cloud services. It can help you getting up and running without the need for infrastructure. When you create a Visual Studio Online account it automatically comes with 5 free users. From there, if your team grows you are able to expand as needed.
If you have Visual Studio with an MSDN subscription, you are free to join online projects and it won’t count for one of the five subscribers.
Limitations of Visual Studio Online
Seen from a database development perspective there are some limitations inherent to the fact that Visual Studio Online is a cloud software.
The first limitation might the version of SQL Server Data Tools that its supports. Since it is a cloud-based software Microsoft have the opportunity to update the product on another basis than the releases of the on-premises products. For some time the version running in the Cloud was the equivalent version of Visual Studio Team Foundation Server 2012. Which meant:
- Another default build template: thus giving some limitations to the scenarios I have been describing in the previous blog posts (like the ability to call a batch file upon post-deployment)
- The version of SQL Server Data Tools installed on the Visual Studio Online hosted build agent might also be different than the version you are running on-premises. When a new version of SQL Server Data Tools is released it doesn’t automatically makes it to Visual Studio Online
- Even if newer version of SQL Server Data Tools (mainly) maintain backward compatibility you might not be able to use new features until the right version has been deployed on the build agent.
The most important of these limitations is the fact that – from the cloud – you are not able to use Windows Authentication to deploy to your local database instance. Furthermore, you need to use a local build agent to be able to deploy to a database on-premises.
Using a hosted build agent to deploy from Visual Studio Online
First of all you’ll need a Visual Studio Onlne account. This is easy to register for and you can get started from there : http://www.visualstudio.com/en-us/products/what-is-visual-studio-online-vs.aspx
With the free account, you’ll get :
- 5 FREE Basic user licenses
- Unlimited stakeholders
- Unlimited eligible MSDN subscribers
- Unlimited team projects and private code repos
- FREE work item tracking for all users
- FREE 60 minutes/month of build
- FREE 20K virtual user minutes/month of load testing
- PREVIEW application monitoring and analytics
From your Visual Studio choose Team ➜ Connect to Team Foundation Server and when prompted add the URL to your newly created server. From there it is only a matter of finding the right team project.
The build template
IF you have read the previous blog posts, you should be aware of the changes necessary for the build template to work. If not here they come:
- In the Trigger pane choose Continuous Integration
- In the Source Settings pick the project you want to build
- The Build Defaults is where you choose the Build Controller, and this is where it gets tricky, please read ahead.
- In the Process pane you can specify which MSBuild arguments you need for the Continuous Integration to perform its work.
The Build Defaults
In the Build Defaults, you need to pick the Build controller that you will need to deploy your changes. By default, Visual Studio Online will pick the Hosted Build Controller but it is not the right choice since this controller runs in the Cloud which probably doesn’t have access to your on-premises database. Should you wish to deploy to an Azure SQL Database or to an Azure VM this is a different story. But for now, let us focus on deploying to on-premises resources.
You will need to change the Build Controller to another instance. The available instances are controlled by the Team Foundation Administration Console. This is where the troubles begin! (at least in my case).
See, I have a local installation of Team Foundation Server Express 2013
Which does not correspond to the version of Team Foundation Server running on Visual Studio Online, which in turn means that I cannot use another Build Controller.
If we abstract from that fact for one second and suppose that I have a local version of the Build Controller that is compatible with Visual Studio Online, I would be able to pick this Build Controller.
After picking this Build Controller I move on to the next step in the configuration : The Process
For the Process, I do not have all the possibilities that I have described in my previous posts since it is a template based on Team Foundation Server 2012 and not 2013. The figure below shows clearly what’s missing:
|
|
As previously mentioned Team Foundation Server 2012 doesn’t provide the ability to work with Post-build scripts (and Pre-build scripts for that matter). So no calling SQLPackage.exe here.
(A possibility would be to create a custom Process Template and add an SQLPackage step).
For now I want to keep it simple and uses those arguments as the MSBuild arguments:
/t:Build;Publish /p:SqlPublishProfilePath=ContinuousDeploymentVSOnline.publish.xml
Telling MSBuild that I want to Publish using the details provided in my publish.xml file. Since I cannot use Windows Authentication I have to provide a username and password in my publish.xml file.
Please remember that when you create the publish.xml file and even if you specifically check the box asking you if you want to save the password, the password will not be saved.
You will need to explicitly edit the file in an XML editor and add the password yourself:
Conclusion
In this last post in my series about Continuous Integration, we have seen how to use Visual Studio Online. It does not have feature complete than Team Foundation Server 2013 but it is a great tool for smaller projects that may or may not require CI. There are many other features to Visual Studio Online as they are many more features to Team Foundation Server. These features are not in scope for this posts about CI, but who knows, maybe in another series, I will have the opportunity to explore the more project related components of TFS online and on-premises.
In the meantime, happy deploying !!
His background and work experience mixes technology and business, providing him a unique edge to contribute to datawarehouse and SharePoint projects, being able to effectively interact with technical and business decision makers.
Régis is a frequent speaker at SQL Server conferences and is a board member of the Danish SQL Server User Group and the founder of the SQL Saturday Denmark community event.
In april 2014 Régis received the MVP award for SQL Server for his involvment in the product and the community.
View all posts by Régis Baccaro
- Continuous Deployment using SQL Server Data Tools and Visual Studio Online - February 2, 2015
- Creating Azure automation to start Azure VMs - February 2, 2015
- Deployment to several databases using SQL Server Data Tools and TFS using a Custom Workflow file - January 16, 2015