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. Show
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 hotfixesThe 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 hotfixA 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 hotfixA 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 appliedA database hotfix can be applied in many ways, including the following:
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 desiredWe may need a hotfix for a database in a number of scenarios, including the following:
A database hotfix testing lifecycle can be as simple as follows:
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 upTo begin SQL database hotfix testing, we need the following things:
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 databaseWe can create a sample database named SQLDevArticlesFixes by running the following T-SQL code against the master database: Code:
View Author tableLet us have a quick look at the Authors table by running the following simple query:
The output is as follows:
Please keep in mind (or you may refer to it later on) the last record where the registration date is 01 Jan 2019. Install tSQLtIn 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:
Please refer to the article, Conventional SQL Unit Testing with tSQLt in Simple Wordsto get more information about tSQLt installation if you are still having any issue. Quick CheckPlease test run all the SQL unit tests:
The results are shown below:
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 testingWe 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 scenarioLet 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 hotfixThe 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 hotfixNow 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 logicThe hotfix test should be based on the following things:
Creating SQL unit test for the hotfixCreate SQL unit test for the hotfix by running the following T-SQL script:
Running hotfix testWe can run all the SQL unit tests now:
The results can be seen below:
Running hotfixPlease feel free to run this hotfix against the sample database to see it in action:
View Author table after applying the hotfixIt is time to view the Author table after we have applied the hotfix:
The results are shown below:
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
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 How to apply hotfix in SQL Server?The steps to apply hotfixes to a SQL Server that participates in either a database mirroring or transactional replication is documented in the following topics in SQL Server docs: Upgrade or patch replicated databases.. The mirror.. The witness/distributor.. The principal/publisher.. The subscriber(s). What is SQL Server cumulative update?The latest Cumulative Update (CU) download is the most recent CU released for SQL Server 2019 and contains all updates released since the release of SQL Server 2019 RTM.
How often are SQL patches released?A major SQL Server version is in Mainstream Support for five years after its initial release. During Mainstream Support, the product will get servicing releases in the form of CUs and GDRs. CUs are released every month for the first 12 months, and every two months for the remaining four years of Mainstream Support.
What is Trace Flag 4199?Trace flag 4199 was used to collect hotfixes that were intended to become on-by-default in a future release, whereas other trace flags were used for situations in which a fix was not intended to become on-by-default in current form.
|