So far we have seen how to setup TeamCity and connect to TFS. In this blog we will be looking at how to automate building and deployment of database projects, other BI project types are a little more complicated and will be covered soon in a subsequent post.
The idea is that upon check-in of the solution TeamCity will automatically build all database projects it contains and deploy these to a predefined development environment.
Prerequisites
Prior to commencing this tutorial you should have installed TeamCity and setup a connection to your TFS project as detailed in previous blog posts:
http://adatis.co.uk/getting-started-continuous-integration-team-city-installation-and-setup
http://adatis.co.uk/configuring-team-city-to-connect-to-visual-studio
Visual Studio must be installed on the build server (the one running TeamCity) in order to use MSBuild. For the building and deployment of database projects you must also install the Data Tools extension for Visual Studio as this contains the dll’s required to enable MSBuild to build the projects.
At the time of writing the latest versions of data tools can be downloaded from: http://msdn.microsoft.com/en-us/data/tools.aspx
For automated deployment you also need to have either SQL Server installed on the server or alternately the Data-Tier Application Framework, part of the (very handy) SQL Server feature pack available from: http://www.microsoft.com/en-us/download/details.aspx?id=29065 for 2012 or http://www.microsoft.com/en-gb/download/details.aspx?id=42295 for 2014.
Part 1: Enabling The Automated Build
To automate the building of the project you will need to create a build configuration and VCS root as detailed in the previous blog posts.
Following this navigate to: Administration > Root Project > [YourProjectName] > Build Configuration Settings
Click ‘Add build step’ and configure the build configuration as per the below.
Save the changes and navigate to the Triggers section below.
Add a VCS trigger and set it to trigger a build on each check in as below.
You should now be ready to go. To test this is working check in your solution in TFS and you should be able to view the project build (or fail) by clicking the projects icon in the top left corner which will give an overview of current build history.
You can also download the team city tray notifier (recommended) which you can set to pop up a notification when a build fails.
If you browse to the directory C:TeamCitybuildAgentwork[UniqueProjectId]you should be able to view the output of the built solution. Note that only projects which can be built by MSBuild will have been built, we will look at other project types (eg SSIS) in a later blog post.
If you only want to enable an automated build without deployment then you do need to follow the remaining steps.
Part 2: Automating the Deployment
Now that the build is automated we can add a second build step to deploy the databases to SQL Server.
First we need a way of telling Team City where to deploy the databases. I am going to deploy them to a local instance of SQL server in this example, however you can use this method to deploy to any compatible SQL instance on your network. In order for this to work you will need to make sre that the Service account which Team City is running under has the required access to the target database in order to deploy/update the database.
First you need to add a publish script to the database(s) you wish to deploy. To do this right click the Database, Select publish
Set the connection string appropriately, chose a name for the deployed database. you can also set the advanced options if required.
This will create an xml file within the database project.
As this is a BI Solution and is likely to contain many database projects I have been using an xml file which details each of the database projects which need to be built in the top level of the project. Using this approach we only have to maintain this top level xml file and the publish xml file for each database and we can avoid creating additional build steps for each database project added.
The template xml file that I use is in the format:
<?xml version="1.0" encoding="utf-8"?> <Project >="https://schemas.microsoft.com/developer/msbuild/2003" DefaultTargets="Publish"> <PropertyGroup> <SqlServerRedistPath Condition="'$(SqlServerRedistPath)' == ''">$(MSBuildProgramFiles32)Microsoft SQL Server110DACBin</SqlServerRedistPath> </PropertyGroup> <ItemGroup> <DBProj Include="SSDB"> <DBName>CI.SSDB</DBName> <DBRoot>CI.SSDB</DBRoot> </DBProj> </ItemGroup> <Target Name="Publish"> <Exec Command=""$(SqlServerRedistPath)sqlpackage.exe" /Action:Publish /sf:"./%(DBProj.DBRoot)/bin/Debug/%(DBProj.DBName).dacpac" /pr:"./%(DBProj.DBRoot)/%(DBProj.DBName).Dev.publish.xml"" /> </Target> </Project>
You may need to amend the file a little to add your own databases and potentially to change the path for SQL Server.
Create a new (second) build step in the project with the following configuration:
The completed build steps configuration should look like this:
Now, if all has gone to plan we should see that when we check in the project both build steps will be run in order and the database will be deployed to the instance specified in the xml configuration script.
The build should run as per the below, if we click to view the build log (very useful) as highlighted in red
Then you should see something which looks roughly like this:
That’s it for now, in the next post http://adatis.co.uk/continuous-integration-with-team-city-automating-ssis-build-and-deployment I detail the process of setting up CI builds in Team City for SSIS.
Meet the Team – Jason Bonello, Senior Consultant
Meet Jason Bonello! Jason has been with us for just over two years and works
Apr
Meet the Team – Matt How, Principal Consultant
Next up in our series of meet the team blogs is Matt How. Matt has
Apr
MLFlow: Introduction to MLFlow Tracking
MLFlow is an open-source MLOps platform designed by Databricks to enable organisations to easily manage
Apr
Adatis are pleased to announce expansion plans into India
Adatis has offices in London, Surrey and Bulgaria – and has some big expansion plans
Mar
Querying and Importing Data from Excel to SSMS
Introduction There are couple of ways to import data from Excel to SSMS – via
Mar
Data Engineering for Graduates and New Starters
This blog aims to give potential graduates and other new starters in the industry some
Mar
Passing DP-900 Azure Data Fundamentals
Back in December, I took the DP-900 Azure Data Fundamentals exam which is one of
Feb
Real-time Dashboards Provide Transparency to Everyone
Real-time dashboards enable data analytics firm Adatis to be agile and transparent with its team
Feb