Haroon Ashraf
SQL Database hotfix testing

SQL database hotfix testing with tSQLt

August 10, 2020 by

This article talks about getting your database hotfixes tested with tSQLt provided they do not have any inherited complexities or dependencies on things other than SQL database.

In this article, I am going to walk you through the steps of creating and running tests to check a hotfix before it is actually run against the database.

Additionally, the readers are going to get a conceptual understanding of database hotfixes alongside knowing the flexibility of our chosen SQL unit testing framework for simple hotfix testing.

About database hotfixes

The understanding of database hotfixes may vary from scenario to scenario, but that does not stop us from defining a database hotfix, which (definition) can be generally accepted.

What is a hotfix

A hotfix is generally a change to be applied to the Production system often without bringing it offline and with very little disruption to the existing system.

What is a database hotfix

A database hotfix is generally applied to Production database(s) in order to fix an existing or potential issue related to consistency, integrity, or performance of data.

However, under exceptional circumstances, a database hotfix can be applied to the databases other than Production, such as QA or even HOTFIX (database).

How is a database hotfix applied

A database hotfix can be applied in many ways, including the following:

  1. Running a T-SQL script against the database
  2. Using tools such as SSMS (SQL Server Management Studio) to apply changes to the database
  3. Building and running an SSIS Package to apply changes to the database
  4. Using an automatic recommendation along with an auto-generated script (as in case of Azure SQL database)

However, before we apply the hotfix, it must be tested, and if it is a simple SQL database hotfix, then tSQLt is the best candidate for testing it.

When is a database hotfix desired

We may need a hotfix for a database in a number of scenarios, including the following:

  1. A bug is found in the Production database
  2. New changes destabilized an existing object or objects
  3. Some new rules have come into place which requires existing data to be modified
  4. Your test team finds an issue with the data when testing something else

SQL database hotfix testing Lifecycle

A database hotfix testing lifecycle can be as simple as follows:

  1. A bug is detected in the Production database
  2. A database hotfix is written to resolve the issue
  3. The database hotfix is tested to ensure it works well
  4. The database hotfix is applied to the Production database once the test is passed
  5. The database hotfix is modified further if the test is failed
  6. Finally, there is no harm in post-testing the Production database after the hotfix is applied

SQL Database hotfix testing

However, this may get pretty complicated when you have multiple versions and multiple environments of your database, but let us keep our focus on getting hands-on testing hotfix with before it gets deployed to Production or any other desired environment.

SQL database hotfix testing set up

To begin SQL database hotfix testing, we need the following things:

  1. A sample database to work with
  2. A SQL unit testing framework installed on the sample database

Please remember the sample database in this article represents a development database, and it is not at all recommended to install tSQLt into your Production or QA database(s).

Set up a sample database

We can create a sample database named SQLDevArticlesFixes by running the following T-SQL code against the master database:

Code:

View Author table

Let us have a quick look at the Authors table by running the following simple query:

The output is as follows:

Author table data before hotfix

Please keep in mind (or you may refer to it later on) the last record where the registration date is 01 Jan 2019.

Install tSQLt

In order to create and run SQL unit tests against your development database, please download the tSQLt.class.sql file from tsqlt.org and run it against SQLDevArticlesFixes.

Alternatively, you can install it by clicking this link: tSQLt_V1.0.5873.27393

A successful installation must be showing the output similar to the one below:

Successful installation of SQL unit testing framework

Please refer to the article, Conventional SQL Unit Testing with tSQLt in Simple Words to get more information about tSQLt installation if you are still having any issue.

Quick Check

Please test run all the SQL unit tests:

The results are shown below:

Dry test run of SQL unit tests

If your output is the same as above (with the exception of date and time), then you are good to go as we have not yet written any SQL unit test, but we can see tSQLt has been successfully setup.

SQL database hotfix testing

We can now create and run SQL unit tests against our sample database; however, we need some solid requirements before we start our work.

Hotfix testing scenario

Let us suppose we have just been informed about the following business requirements:

“All the registration dates of the authors must be changed to the next day (02 Jan) if they fall on 01 Jan 2019 or 01 Jan 2020.”

The infrastructure team decides to apply this change as a hotfix, but they have asked the development team to create this hotfix so that they can apply it in the Production environment.

Creating an object for the hotfix

The development team decides to create a stored procedure to apply this hotfix, which is going to change all the registration dates from 01 January to 02 January, where the registration year is either 2019 or 2020.

We can call this Jan01ToJan02HotFix so let us create its stored procedure as follows:

Creating a test class for the hotfix

Now that we have created an object which applies the hotfix, we must not let it go without testing it, and that is the reason we set up tSQLt. Let us create a test class for hotfixes as follows:

Hotfix testing logic

The hotfix test should be based on the following things:

  1. Remove all the data from the Author table by creating a fake table
  2. Insert a few records where authors were registered on 01 Jan 2019 and 01 Jan 2020
  3. Create an expected table in which authors registered on 01 Jan 2019 have had their registration date changed to 02 Jan 2019 and do the same (date change) for the year 2020
  4. Run the stored procedure which applies the fix to the Author table
  5. Compare Author table after applying the fix with the expected table
  6. If the results match then the test has passed else check your code

Creating SQL unit test for the hotfix

Create SQL unit test for the hotfix by running the following T-SQL script:

Running hotfix test

We can run all the SQL unit tests now:

The results can be seen below:

hotfix test has passed

Running hotfix

Please feel free to run this hotfix against the sample database to see it in action:

View Author table after applying the hotfix

It is time to view the Author table after we have applied the hotfix:

The results are shown below:

Author table after hotfix has been applied successfully

Congratulations, you have successfully tested a hotfix with one of the most advanced SQL unit testing frameworks, followed by applying it to the sample database.

It is easy to say that tSQLt cannot only help in database object unit testing, but it can also play a vital role in SQL based simple hotfix testing, plus all of these hotfixes can be grouped into a separate test class for future reference and (re)use.

However, this approach is suitable only for simple SQL based hotfixes, as discussed in this article. As for more complicated ones, you have to work with other tools, technologies, and team(s) to test them properly.

Table of contents

tSQLt – A Forgotten Treasure in Database Unit Testing
Conventional SQL Unit Testing with tSQLt in Simple Words
Fundamentals of Test-Driven Database Development (TDDD) with tSQLt unit testing
10 Most Common SQL Unit Testing Mistakes
Why you should cleverly name Database Objects for SQL Unit Testing
Three Standard SQL Unit Tests you can write against any Stored Procedure
Creating SQL Unit Testing Utility Procedures with tSQLt
SQL Unit Testing Data Warehouse Extracts with tSQLt
The Concept of Test-Driven Data Warehouse Development (TDWD) with tSQLt
Using tSQLt for Test-Driven Data Warehouse Development (TDWD)
SQL database hotfix testing with tSQLt
Test-driven database hotfix development (TDHD) with SQL unit test based framework (tSQLt)
Three ways you can add tSQLt to your SQL database projects

Haroon Ashraf
Testing

About Haroon Ashraf

Haroon’s deep interest in logic and reasoning at an early age of his academic career paved his path to become a data professional. He holds BSc and MSc Degrees in Computer Science and also received the OPF merit award. He began his professional life as a computer programmer more than a decade ago, working on his first data venture to migrate and rewrite a public sector database driven examination system from IBM AS400 (DB2) to SQL Server 2000 using VB 6.0 and Classic ASP along with developing reports and archiving many years of data. His work and interest revolves around Database-Centric Architectures and his expertise include database and reports design, development, testing, implementation and migration along with Database Life Cycle Management (DLM). He has also received passing grade to earn DevOps for Databases verified certificate, an area in which he finds particular interest and potential. View all posts by Haroon Ashraf

283 Views