SQL Server database continuous integration workflow TEST step – Running SQL unit tests against the changes

In this part of the continuous integration aka “CI” workflow, the Unit test step will be described. After the Populate step is successfully finished, where the new database test data is generated, the Test step is initiated, that will use SQL Server unit tests.

When talking about SQL Unit tests we must know that it is important to execute them in order, as part of our continuous integration and delivery sequence, to make sure that when the database is changed, its integrity, functionality or relationships will not be violated. This will allow us, upon successful results, to proceed to the next step.

It would be ideal to run these tests not only as part of a continuous delivery process but periodically on the development server itself. Once this step has been completed, it can be run independently and automatically, even outside the process of a new build.

Workflow

In this workflow step, we will focus on running SQL Unit tests on the previously populated database. These unit test will be executed automatically with a script, over the newly populated database. In order to create SQL Unit tests and run them automatically, we will use ApexSQL Unit Test. This is a tool specifically designed for executing SQL Server Unit tests. As a pre-requisite, the tSQLt framework must be installed.

To walk through the instructions to create this step, see these articles:

• How to create and run SQL Server database unit tests automatically
• How to automatically process SQL Server databases with Unit tests

By using ApexSQL Unit Test, we can create an easy way of performing the Test step of CI workflow, with minimal adjustments when needed.

In the next article, the final step of the CI workflow, the Sync step, will be described.

Useful links:

• tSQLt – The Database Unit Testing Framework for SQL Server
• Creating and running a SQL Server Unit Test
• Create an Empty SQL Server Unit Test
• Creating and Running a SQL Server Unit Test
• Using a Custom Test Condition to Verify the Results of a Stored Procedure

All articles in this series:

1. SQL Server database continuous integration workflow COMMIT step – Initially committing a SQL database to a source control repository
2. SQL Server database continuous integration workflow BUILD step – Building a new SQL QA database from source control
3. SQL Server database continuous integration workflow POPULATE step – Populating the newly built SQL database with data
4. SQL Server database continuous integration workflow TEST step – Running SQL unit tests against the changes
5. SQL Server database continuous integration workflow SYNC step – Creating the synchronization/migration script
6. SQL Server database continuous integration workflow DOCUMENT step – Creating database documentation

A complete SQL Server CI/CD solution

Please see the ApexSQL DevOps toolkit for a complete, end to end SQL Sever CI/CD solution including full, open source PowerShell scripts. Learn more