DevOps : Testing of a database project in Azure DevOps (Part VI)

Introduction

In my previous blogpost, I've mostly talked about the Microsoft hosted agent and that works great when you want build and release your data warehouse and BI project. The code is checked in, an automatic build is started and the code is released to a release environment. But, what about testing? Testing is one of the key elements of Azure Devops (formerly known as VSTS): build the code, test it and release the code to a new environment. I'm using the terminology VSTS and Azure Devops interchangeably throughout this blogpost as the name change just happened while I was writing this blogpost.

If you want to test your database code with unit testing scripts, there has to be an environment where the testscripts can be run against it. Now as far as I can see now, there are a couple of options here:

• Deploy the database code to the localDB instance on the Azure Devops server and run the database tests on the localDB instance.
• Deploy the code on an on - premise box and run the test locally. For this setup there must be local agent installation.
• Deploy the code to an Azure Database / VM box and run some tests on that database/machine.

I've noticed that as a starter it is a good idea to begin with the first option : Deploy the code to the localDB on the Azure DevOps server. It is possible to deploy and run the test against the localDB on the Azure DevOps server, although it took me while to figure it out. So, this blogpost is a report of my experiments with unit testing and automating this on a localdb instance, in a DevOps environment.

I've used an article by Rahul Mittal on code project for setting up a Unit testing. I've used this example to automate my builds and testing in Azure DevOps.

DBUnitTesting project

The database test project as described in the article on CodeProject describes 7 types of tests on a database. For this blog post,  I followed the article in detail and executed the steps. I can recommend doing this too, if you want to know more about this subject.

There are a couple of important steps that are necessary to implement a database test project:

• Create a database project in VS2015 or VS2017 (I used VS2017).
• Create a testproject in VS2015 or 2017 together with the database project in the solution.
• Define the tests in the project.
• Execute and test the testproject.

As said before, there are 7 types of test possible:

• Counting the number of rows in a table.
• Data checksum in order to check whether some data has changed in the table.
• Check if the schema has changed.
• A performance check if a stored procedure is performing within some time boundaries.
• Check if a query or a stored procedure returns an empty resultset.
• Check if a query or a stored procedure returns a non-empty empty resultset.
• Standard not test option: "inconclusive".

The tests are located in the test explorer where it is possible to run the tests.

I made a couple of mistakes while setting up the environment in Azure DevOps. One of the mistakes was that I didn't use the right dll. One helpful tool was vstest.console.exe to test the different local test dll. So I quickly found out that I had to use the another dll.

C:\Users\Administrator\source\repos\SQLUnitTestingDB\TestCases\bin\Debug>
 "C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\
 Common7\IDE\CommonExtensions\Microsoft\TestWindow\vstest.console" testcases.dll
Microsoft (R) Test Execution Command Line Tool Version 15.7.2
Copyright (c) Microsoft Corporation.  All rights reserved.

Starting test execution, please wait...
Passed   SqlTest1
Passed   SqlTest1
Passed   SqlTest1
Passed   SqlTest1
Passed   SqlTest1
Passed   RowCountCompany
Passed   SqlTest1

Total tests: 7. Passed: 7. Failed: 0. Skipped: 0.
Test Run Successful.
Test execution time: 0.8713 Seconds

If you use the wrong dll you will get the following messages :

       
C:\Users\Administrator\source\repos\SQLUnitTestingDB\TestCases\bin\Debug>
"C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\
Common7\IDE\CommonExtensions\Microsoft\TestWindow\vstest.console" 
sqlunittestingdb.dll
Microsoft (R) Test Execution Command Line Tool Version 15.7.2
Copyright (c) Microsoft Corporation.  All rights reserved.

Starting test execution, please wait...
No test is available in 
C:\Users\Administrator\source\repos\SQLUnitTestingDB\TestCases\bin\Debug\sqlunittestingdb.dll. 
Make sure that test discoverer & executors are registered and 
platform & framework version settings are appropriate and try again.

Additionally, path to test adapters can be specified using 
/TestAdapterPath command. Example  /TestAdapterPath:<pathToCustomAdapters>.

Build the database project in VSTS (Azure Devops)

1. Let's start with setting up the build process in a CI process in VSTS. The first step is to Create a build definition. Click on Build& release in the toolbar and click on Builds. When no build definitions have been created so far you should see a empty Build Defintions screen

2.  Click New Pipeline. There are a number of options to choose from: Select a source control system, the team project , the repository and the branch.

3. Click on Continue. Now you should see a template screen where you can choose from a template Pipeline. Choose the Empty Pipeline template.

4. Click on Apply. and the empty pipeline should look like this. You choose a name, get the resource and choose which agent to run on the build.

5. Choose the Default Pipeline in the Agent pool selection.

6. Choose the Team project, repository, branch and set some options if you want.

7. Choose Visual Studio build task on the agent and click on Add.

8. Click on the Visual Studio Build task and set the options. Set the following properties:

• Name Build  : SQLUnitTestingDB solution
• Solution : SQL UnitTestingDB
• Visual studio Version : Visual Studio 2017
• MSBuild Arguments: /t:Build;Publish /p:SqlPublishProfilePath=SQLUnitTestingDB.publish.xml

For the MSBuild option, I've included a publish file in my project. This will publish the database to the localdb on the VSTS server.

9. Add a Visual Studio Test Task to the pipeline and use the following settings:

• Display Name
• Testfiles : **\*Testcases.dll !**\*TestAdapter.dll!**\obj\**
• Search folder : $(System.DefaultWorkingDirectory)

Run the Database Unit Testing Pipeline

When you are done setting up the pipeline in VSTS (Azure Devops) it is time to test the Azure DevOps pipeline. If nothing is changed, the pipeline will run into an error. We have some results from the Visual studio Test Task but not what we want.

Failed   RowCountCompany
Error Message:
 Initialization method TestCases.CompanyScalar.TestInitialize threw exception. 
 System.Data.SqlClient.SqlException: System.Data.SqlClient.SqlException: A network-related or 
 instance-specific error occurred while establishing a connection to SQL Server.
 The server was not found or was not accessible. Verify that the instance name 
 is correct and that SQL Server is configured to allow remote connections.
 (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) 
 ---> System.ComponentModel.Win32Exception: The system cannot find the file specified.
Stack Trace:
 ........
at Microsoft.Data.Tools.Schema.Sql.UnitTesting.SqlDatabaseTestService.OpenExecutionContext()
at Microsoft.Data.Tools.Schema.Sql.UnitTesting.SqlDatabaseTestClass.InitializeTest()
at TestCases.CompanyScalar.TestInitialize() in D:\a\1\s\TestCases\CompanyScalar.cs:line 24

The problem is that the configuration settings in the testproject is set to localhost.  Therefore the settings in the app.config should be changed into localdb.

Commit the code to Azure DevOps and let's see whether the Visual Studio Test task will execute the testproject. As it is shown in the screen below, the test has failed, but 2 of the 7 tests went successful.

The one that went wrong is the one that assumes there is data in the table.

It is important to start your scripts from scratch or from a initial situation with some data. In latter case you have execute some scripts or restore a backup of the database on the local db. I'm not sure if it is possible to restore a database to the localdb instance. THat is one for a future blogpost.

Final thoughts

Setting up testing in your database project has some explorative aspects in it. There aren't much resources that clearly explains the possibilities, options and howto's. In this blogpost I've explored the possibility of testing a database project in the localDB. This blogpost is a report of experimenting with Azure DevOps.


Hennie