zondag 15 juli 2018

DevOps : master.dacpac does not exist error message in VSTS (part V)

One of the problems I had with automated builds was the database reference in the .sqlproj file. I noticed that the master- and the msdb dacpac was referenced to a location on the C: drive. This works fine in the situation where you're the only developer, but when working together in a team and even more when you use VSTS as your build environment, it is a not a good practice. If you try to build in VSTS when the dacpac has a standard location, you'll get a message like this:

C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\
does not exist.

To get rid of this error message is to include the master.dacpac (and the msdb.dacpac) file (!) in your project (with location option, add database reference) and so I did...

But building the project in VSTS kept on giving this error. Investigating the .sqlproj file showed me that the reference was still there(?!) even when I deleted the database reference in the solution explorer.

I finally manually removed the entries in the .sqlproj file and the problems were away...

<ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\
<ArtifactReference Include="C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\


vrijdag 13 juli 2018

DevOps : Building your SSIS project in VSTS (Part IV)


In previous blogposts, I've explained how to build a database project (Part I), how to release a database project (Part II) and how to use pre- and post deployment script (Part III) in an automated build and release process with VSTS. The next step is to examine how to build and release a SSIS project. This blogpost is the result of this exploration.

Building code in a database project is done with MSBuild. It is not possible to use MSBuild for building a SSIS project (as far as I know). The options I've have found so far are :
  • Using command line task with devenv.com. 
  • Download a custom SSIS task from the marketplace.
I'll examine both ways but will describe the first one in this blogpost. I've used the following sites as an inspiration : http://abelsquidhead.com/index.php/2016/10/06/build-and-deploy-ssis-packages-using-vsts/

Creating a SSIS project in a solution

Now the first step in this demo is creating a SSIS project in my WorldWideImporters (I think I mixed up the name;-) ) solution. The database project is still present. It is the same project as I described in my previous blogposts.

The next step is to build the SSIS project. In the following step the changes are committed and pushed to the Git repository.

The first step is checking whether the build and release is still working and it is. But now we have to change the build process in VSTS. We not only have to build a database project but also the SSIS project now.

Build the SSIS project in VSTS

The first step is changing the VSTS build definition in VSTS. I've made a copy of the build definition and added some tasks.

I've added two variables for the build configuration and one for the SSIS project. I did not configure the configuration properties of the properties. I just took the defaults.

Build task
The Build task is a command line task in VSTS and there are two version 1.* and 2.*. I took the 1.* version.

This example is based on the "Hosted" agent queue

C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\devenv.com

Using the "Hosted VS2017" agent has another location of the devenv.com

C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\devenv.com

These are the arguments I used for the build.

/rebuild $(BuildConfigurationSSIS) 
/project "$(Build.SourcesDirectory)\SSISWorldWideImporters-SSDT\SSISWorldWideImporters-SSDT.dtproj"

Copy task

Use the copy task again as I described in the database build blogpost.

Publish artifact

Publish the artifact to the Artifact repository.

You can find these artifiacts in the overall result screen of the build. These will be used in the release processstep.


Once you understand the folder structure and using the variables it is easy to write the script to build the SSIS project with Devenv.com.



woensdag 4 juli 2018

DevOps : Releasing your database code with pre- and post deployment scripts (Part III)


In a previous blogpost about DevOps (Part I and Part II) the basics of Build and Release process has been described. As I already mentioned in my previous blogpost, database DevOps projects are a bit more challenging than code DevOps projects. One of the problems is that tables contains data and you want preserve the (state of the) data.

For this blogpost I've used a table (sales.InvoiceLines) from the WideWorldImporters example database. First I've added an extra column to the database and as I will describe in this blogpost, I'll delete this column in the Visual studio project, execute the change script on the development database and commit and push the code to the Github repository. The automatic build and Release will execute the change on a QA database where the column is deleted. I'll show that if nothing is done (extra) the script will fail because of possible loss of data. For this blogpost, I'll execute a deployment script to handle this kind of errors.

Pre-and post deployment scripts are run once before the actual change script of Visual Studio. And, the pre- and post deployment scripts are only run with the publish option and not with the compare functionality. Although there may be other ways to handle this more clever, I approached this case as follows. In a pre-deployment script, I've created the following steps in T-SQL :
  1. Check if the column (TestColunn) in the original table exists.
  2. Check if there is a temporary table present and if so, drop the table.
  3. Copy the data from the original table in the temporary table.
  4. Disable Foreign keys.
  5. Truncate the original table.
The step in between is executed by the normal deployment script, to be exact, remove the TestColumn. Next step is the post deployment script.

In a Post Deployment script, I've written the following steps in T-SQL:
  1. Check if the rows in the original table is deleted.
  2. Copy the data from the temporary table to the changed original table.
  3. Enable the foreign keys again.
  4. Check if the rows are copied correctly.
  5. Remove the temporary table.
One thing that comes to my mind is that actually you want to put this in one transaction: Pre and post deployment. Because, you want to roll back the change when something goes wrong. Perhaps this is something for a future blogpost.

Remove a column from a table

 As said before, first I've added an extra column to the database and then I'll delete this column in the Visual studio project. Below the CREATE script

CREATE TABLE [Sales].[InvoiceLines] (
    [InvoiceLineID]  INT             NOT NULL,
    [InvoiceID]      INT             NOT NULL,
    [StockItemID]    INT             NOT NULL,
    [Description]    NVARCHAR (100)  NOT NULL,
    [PackageTypeID]  INT             NOT NULL,
    [Quantity]       INT             NOT NULL,
    [UnitPrice]      DECIMAL (18, 2) NULL,
    [TaxRate]        DECIMAL (18, 3) NOT NULL,
    [TaxAmount]      DECIMAL (18, 2) NOT NULL,
    [LineProfit]     DECIMAL (18, 2) NOT NULL,
    [ExtendedPrice]  DECIMAL (18, 2) NOT NULL,
-- [TestColumn]  INT    NULL,
    [LastEditedBy]   INT             NOT NULL,

If this script is deployed to a database it will return an error

(43,0): SQL72045: Script execution error.  The executed script:
           FROM   [Sales].[InvoiceLines])
RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127)
        WITH NOWAIT;

Deploying the script to VSTS will also result in the same error.

Here the actual log of the  unsuccessful deployment.

Block on possible data loss
There seems to be an option in the options window present : "Block on possible data loss". This will ignore the table check and deploy the database change anyway. Now, perhaps you have multiple changes to tables and in some cases you want to use this option and in some other cases not. So, In my opinion not very granular.

Add a pre- and post deployment file 
In order to control your deployment process, it is possible to use pre- and post deployment scripts. Pre- and post deployments are scripts that run every time a change is deployed to a database. Therefore these scripts should run in all possible situations. In the example of this blogpost, delete a column, the script should run in the case whether there is a column and there is no column (because the script has already ran before). Yet another is that you have to manage your pre- and post deployment scripts. If you have process of deployment with multiple environments you have to be sure that the code is deployed in production before deleting the code from the script.

Below, an example of the script I've created. I'm not saying that this a best practice script. It is just an example on how to handle both situations: there is a column and there is not a column.

This is an example of a Pre Deployment script.

--Check existence of the column in the table
          WHERE  TABLE_NAME = 'InvoiceLines'
     AND TABLE_SCHEMA = 'Sales'
                 AND COLUMN_NAME = 'TestColumn') 

 --Check existence temp table
     FROM information_schema.tables
     WHERE table_schema = 'Sales' 
     AND table_name = 'tmp_InvoiceLines')
  DROP TABLE Sales.tmp_InvoiceLines

 INTO [Sales].[tmp_InvoiceLines]
 FROM [Sales].[InvoiceLines]

 --Disable the constraints
 ALTER TABLE [Sales].[InvoiceLines] NOCHECK CONSTRAINT [FK_Sales_InvoiceLines_Application_People];
 ALTER TABLE [Sales].[InvoiceLines] NOCHECK CONSTRAINT [FK_Sales_InvoiceLines_InvoiceID_Sales_Invoices]; 
 ALTER TABLE [Sales].[InvoiceLines] NOCHECK CONSTRAINT [FK_Sales_InvoiceLines_PackageTypeID_Warehouse_PackageTypes] ;
 ALTER TABLE [Sales].[InvoiceLines] NOCHECK CONSTRAINT [FK_Sales_InvoiceLines_StockItemID_Warehouse_StockItems]

 -- Remove the data.
 TRUNCATE TABLE [Sales].[InvoiceLines]

This is an example of a post-deployment script.

IF (SELECT count(*) FROM  [Sales].[InvoiceLines]) = 0

 -- Check existence tmp table?

 --Insert the data from the tmp table in the new created table
 INSERT INTO [Sales].[InvoiceLines]
 SELECT [InvoiceLineID]
 FROM [Sales].[tmp_InvoiceLines]

 --Enable the FK's
 ALTER TABLE [Sales].[InvoiceLines] CHECK CONSTRAINT [FK_Sales_InvoiceLines_Application_People];
 ALTER TABLE [Sales].[InvoiceLines] CHECK CONSTRAINT [FK_Sales_InvoiceLines_InvoiceID_Sales_Invoices]; 
 ALTER TABLE [Sales].[InvoiceLines] CHECK CONSTRAINT [FK_Sales_InvoiceLines_PackageTypeID_Warehouse_PackageTypes] ;
 ALTER TABLE [Sales].[InvoiceLines] CHECK CONSTRAINT [FK_Sales_InvoiceLines_StockItemID_Warehouse_StockItems]

 -- Drop the tmp Table if the data is copied correctly)
 IF (SELECT count(*) FROM  [Sales].[InvoiceLines]) > 0
     FROM information_schema.tables
     WHERE table_schema = 'Sales' 
     AND table_name = 'tmp_InvoiceLines')
  DROP TABLE Sales.tmp_InvoiceLines

As mentioned before, it is just an example script. May be there are better patterns for solving these kind of changes.

Check into VSTS
Next step is checking the code in VSTS and see whether the Build and deploy works just fine. I commented the Testcolumn in the Table script in order to fake a deletion of the column and check the code into VSTS.

With the automated build and deployment of the commited code changes the deployment database.

And in the deployment database I can see that the table is recreated and the column is gone.

One more test is needed and that is that the Pre-and Post deployment script is executed when there is another change on another table and see whether that is handled properly.


Now, although I like the approach of pre- and post deployment in combination with an automated build and release process, there are some drawbacks:
  • The code has to be present in the script until the change is deployed in the production environment. So pre- and post deployment management is necessary.
  • The pre- and post deployment depends om some logic whether the change should execute or not. In my case whether the test column exists or not. Also, there is no 'connection' between the code in the pre- and post deployment script. I'm starting to think that this should be handled in a change management metadata system where you add a change, maintain the change and remove a change when it's in production. 

My 3 cents ;-)...


dinsdag 26 juni 2018

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


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.

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.


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


zaterdag 23 juni 2018

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


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.


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


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.