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 :
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.
- 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.
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 :
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
- Select the project from your Github repository.
- Build the (database)Project.
- Copy Files from the Build.
- Publish artifacts
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
Geen opmerkingen:
Een reactie posten