DevOps : Releasing your database code using VSTS (Part II)

Introduction

Now, you have successfully build a Database project of your database (Part I), but now what? Off course, you have to deploy the code to an integration -, a central deployment -, or a QA environment and finally you want to deploy your code in a production environment. Now, in order to minimize the impact on production, you can introduce a staging database where you can simulate a production deployment in order to test and make 100 % sure that your code works on production.

In the diagram above, a possible release process that will work over a certain number of environments. In this blogpost I'll write some steps in order build up such an environment. I'll add more information in future blogposts. In this blogpost I'll outline some basics about setting up a release process with VSTS. The following steps are needed to build up a release:

1. Create a release definition
2. Choose the right artifacts
3. Create one or more environments
4. Add some additional options like approving

Create a release defintion

The first step is create a release definition. A release definition is an important part of your Build and release process. Below, I've included a screenshot of a release definition. Two steps are visible: choosing the artifact and where to deploy the artifact (in a so called environment) . It is possible to create multiple environments, eg. QA, staging and test.

Choose the right artifacts

In the build process we have created a artifact in the artifact repository. This is a deploy able component of a project. In this blogpost, I'll focus on Database project artifacts.  We have to specify the location in the build process and we have to use this location as an input in the release process.

It is possible to choose the latest build or specific branches.

Create one or more environments

Next step is to deploy the artifact in a specific environment. In this example I've created one environment : QA. It is also possible to create multiple environment. The Database release task : SQL Server Database Deploy requires that a Deployment Group is defined and a deployment group is collection of multiple servers. So, it could be possible to deploy to multiple servers in a QA environment (for instance).

The SQL Server Database Deply task requires some settings, like name, the servername, the database name, how to authenticate, etc.

Deploymentgroup
Below a definition of a deployment group. In the example screenshot, the deployment group is off line, but when the server is online, it will indicate online. It is also necessary to execute a powershell script on the local machine to make it work (under Detail). Use Personal Access token in the script for authentication.

Conclusion

This is the second blogpost about DevOps and specifically releasing a database project with VSTS on a QA environment.

Hennie

DevOps : Building your database code using VSTS (Part I)

Introduction

In this blogpost I'll describe the first step by step how to create a "Build and Release" process for SQL Server database scripts : The build process. I'll include more information about releasing, SSIS, SSRS  and SSAS (tabular, MD) projects in future blogposts. This is the first blogpost about DevOps for a data warehouse and BI environment. Build and Release is often mentioned together with DevOps. Devops is short for Development and Operations. Integrating these two (different) worlds should lead to shorter development cycles. Now, Build and Release is functionality that is available on the VSTS site. In VSTS you can setup a Build first and then a Release. In order to build software you need software, right? So, there are a couple of components involved :

• A central repository (eg. Git) where your code is stored.
• Build definition + possible tests.
• Release definition.
• Deployment machine(s).

There is a difference between DevOps with normal coding and DevOps with Databases. In a normal coding DevOps Process code can be generated easily and deployed. The problem with Databases you want preserve the (state of  the) data in the database. Yet another issue is SQL Coding. There are certain statements for creating objects and other statements for altering objects.

One more difficult matter is that BI environments consists of different kind of software components. For instance, We use SSIS for ETLing the data into the different layers of a data warehouse, We use a cube for storing the data optimized for (ad hoc) analysis (with possible two different options : Multidimensional and Tabular), We have reports in SSRS or in PowerBI. All of these different tooling has to work together in order to fulfill a satisfactory experience by users.

One prerequisite is that we need to select an agent pool under Agent Queue. By default, it will show Hosted and Default (no agent pool). We need to have our own agent pool in order to queue new build. Now how to add agent queue is simple. You need to download agent for under agent pool from settings tab, configure it and run a PowerShell command.

For this blogpost, I'll use the WideWorldImporters database as an example. This is not a data ware house, off course. It is just for used for practicing Build and releasing.

Overview

In this blogpost and in the following blogposts I'll explore the best practices, can do's and don'ts, tips  and tricks of DevOps in a data warehouse and BI environment. Below, I've drawn a picture about a possible process of DevOps.

There are different strategies on how to branch your code from the master in a version control system, like Github. An easy way is to branch the code per developer and when a developer is ready, he or she will merge the code with the master branch again. 

The next step could be (automatically) build the code in a version control system (eg. VSTS). Here you can check whether the master code is still building with the new integrated branch of the developer. 

The following step in the process could be to release (deploy) the code of your project in a certain environment. This can be a simple environment or a complex environment with all kind of different machines with different software.

In this blogpost, I'll describe my experience with setting up the simple Database project with Visual Studio, Git and VSTS. 

1. Setup a Version Control repository with Git in VSTS

The first step is to set up a Github repository in VSTS, by opening an account at VisualStudio.com. Here can you store your code in a centralized manner. Below I've some test projects stored. For this blogpost I use the WideWorldImporters demo database.

I've imported this database in VisualStudio and connected this project with the Github repository.

2. Setup of the Build process

The next step in the DevOps process is to configure a Build process. A simple Build process consists of four elements :

• Select the project from your Github repository.
• Build the (database)Project. 
• Copy Files from the Build.
• Publish artifacts

I'll elaborate about these steps in more detail.

2.1 Select the project from your Github repository.
First, select the repository where your code is stored, for instance Github. This will be the base for the Build task. Therefore you need to say to VSTS Release that the code is there. Select the project solution file, branch and the Team project as a source for the build process.

2.2 Build the (database)Project. 
You can build a project with a Visual studio build task or with a MSBuild task (link). There seems to be several options on how to build a Database project. In this example I choose for VS Build. Here you also select the right solution file, some arguments for the MSBuild executable.

2.3 Copy Files from the Build.
After the build is succeeded, the next step is copying the Build files. I've included fixed directory paths with some variables like $(BuildConfiguration). Other options are possible.

2.4 Publish artifact.
And finally the Build files are inserted in the Artifact folder and ready to released. This is executed with a Publish Artefact task.

Now the first phase is done and the Build process is ready for testing. We put the build process in a Queue and it can succeed or it can fail. If one of the steps fail, the build process will stop. I've included a Screen with 5 of the last builds. Some succeeded and some failed.

If you click on a successfull build you'll see something like the information on the screen below. I've some warnings due some database reference that is not properly done.

And here is a screenshot of a failed Build process

Conclusion

This is a first introduction in building your code with VSTS. In the near future I'll explore the VSTS Build and Release process in more depth and will continue to update this blog with more information about this. 

Hennie