Voice of the DBA

Tag: testing

  • Testing is Becoming More Important

    Many of us know that testing our code is important. The adoption of unit testing by many software application developers as a normal course of business has dramatically improved the quality of applications. Mobile software, especially, has benefited from the requirement for most software to include, and constantly run, a suite of unit tests.

    For database software, I find relatively few organizations formally test their database code. A few people have adopted tSQLt or the Microsoft Unit Testing Framework, but most don’t bother. In fact, many queries that are embedded in application code, or built by ORMs, aren’t tested beyond a developer looking at the results from their own (limited set of) test data. That often doesn’t catch errors until someone in production runs their application against a larger set of data.

    What might be worse is that refactoring those queries might produce different results that aren’t tested against regressions.

    In this new age of AI-assisted coding, testing is becoming more important. Grant wrote an interesting post on LinkedIn that discusses your job changing in the age of AI. You need to have more testing that ensures you validate code that the AI produces, which is going to be more important as the amount of code grows. AI will produce lots more code, and potentially, lots more poor code. We will need to ensure that the generated code  has some validation that the results are what we expect.

    Unit tests help here, and while I know these can be tedious to write and maintain, this is a great use for AI assistance. Generating unit tests, with default data based on data in current tables, is something AI agents can do well. They can also use these to verify functionality as code is generated and refactored. Of course, humans still need to be in the loop as there are plenty of reports where AI Agents write tests that return success without actually testing code. This is something humans have done as well.

    You need to validate the tests, and ensure your AI uses those tests to validate its work. Those tests can also be used by humans if they write code.

    AI is an amazing tool, but like an intelligent, over-eager, junior developer, it needs clear communication and strong guidance.

    And a little review of its work.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Adding Test data to msdb.sysjobhistory

    One of our internal people was looking to test some process in (I assume) Redgate Monitor and needed more job history than they had in msdb.sysjobhistory. They wanted to use SQL Data Generator to help, but couldn’t make it work.

    This gives the solution I sketched for them. It can work with some other system tables, but not all. Many system tables do not allow user data to be inserted. Some do.

    The Main Problem

    The main problem here is that SQL Data Generator doesn’t see system tables. If I open a project in msdb, I see this:

    2024-11_0297

    That matches what I see in SSMS. Only user tables.

    2024-11_0296

    However, I know I can do this and it works.

    INSERT INTO dbo.sysjobhistory
      (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server)
    VALUES
      ('EA6B3BC3-D358-4B0C-A793-8C3C558098AB', 0, 'mystep', 0, 0, 'Executed as user: NT Service\SQLAgent$SQL2022. The job script encountered the following errors.'
      , 0, 20241111, 175438, 0, 0, 0, 0, 0, 'ARISTOTLE\SQL2022')

    Now, how to get SQL Data Generator to help?

    A Little System Table ETL

    Since I know I can insert data into the table, how can I generate data? Apparently SQL Data Generator cannot read these tables, but I can use a trick that I’ve used in the past.

    First, I’ll run this in msdb:

    SELECT *
      INTO mysysjobhistory
      FROM dbo.sysjobhistory AS s
      WHERE 1 = 0

    This code will make a copy of dbo.sysjobhistory with no data. However, this is a user table. Once I do this, now I can refresh SQL Data Generator and I can see my table.

    2024-11_0094

    Now I can use the settings to get the type of data I want. Here’s a preview of some data I set, using the data in my existing table.

    2024-11_0095

    Now, I can click generate data and I have data added to my user table. If I query this table, I see data:

    2024-11_0096

    The last step is to move this data. I’ll use this query:

    INSERT dbo.sysjobhistory
       (job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server)
    
SELECT job_id, step_id, step_name, sql_message_id, sql_severity, message, run_status, run_date, run_time, run_duration, operator_id_emailed, operator_id_netsent, operator_id_paged, retries_attempted, server
      FROM dbo.mysysjobhistory AS m

    Once this runs, I can see the data in msdb.

    2024-11_0097

    Of course, I’d also have to populate sysjobs if I wanted this linked to a job and shown in the Agent Job History Viewer.

    Summary

    This post showed how I’ve sometimes worked in situations where I couldn’t directly access a table from an application. In this case, I want to get data into sysjobhistory, but SQL Data Generator doesn’t support that directly. My solution:

    1. make a copy of the table
    2. insert data into the copy
    3. move the data into the original

    This has worked in a few situations as well where I might need to move/stage data before it gets into an application table. In this case, we wanted to generate some random history for Redgate Monitor to read.

    This can work for other tables as well, as long as you can insert..select into them.

    SQL Data Generator is a neat tool to generate data quickly for a variety of purposes in SQL Server. Give it a try, especially if you already have the Toolbelt Essentials.

  • Where to Test Your Code

    The last 10-15 years in software development have seen a widespread embracing of unit testing. Before the popularity of mobile phones and their apps, most of the organizations I’d worked in gave lip service to automated unit testing, and often even more complex integration/system tests.

    These days, it seems more and more people embrace unit testing, and I hear about that often from customers and attendees at events. I don’t often hear about more comprehensive integration and system testing, I found an interesting article from the Pragmatic Engineer that looked at how the Bluesky social network was built. The article is partially paywalled, but I  have subscribed because of the interesting thoughts they publish. In this article, there was a really interesting part of the article on testing. This is a section titled “Integration tests over unit tests”.

    This isn’t described in any detail, but there is a note that the priority is for integration tests. There are unit tests, but I’m guessing this means that they write mostly integration tests first and then perhaps fill in with unit tests. The article does note that the backend is heavy on integration tests, looking to test the flow of data through the network. I assume network means their application here.

    I think a lot of companies think about skipping unit testing in the database and instead might run some sort of integration tests from the application that hit the database. At least, I hope they do this. If they use unit tests that mock the database, that’s not necessarily a great way to ensure that the things in the database work as expected. Data types, defaults, rules/constraints, etc. All of these things might be different in a database over time, which is why devs need a database that is regularly updated from production (though is likely smaller in dataset size).

    I can see some value in looking at integration tests more than unit tests. If we can only test so much because of time pressures, ensuring that the flow of read/write to and from the database makes sense. Queries as well, since potentially there are writes that store data and queries that read or aggregate data that might be transformed somehow in the database. Imagine writing a field, but having queries that read from a computed column that has normalized the data somehow. Or even triggers that have changed the data.

    I am a bit fan of testing, and I think there is value in database unit testing, but I also understand that many people struggled to get started there. If you aren’t going to do database unit testing, then ensure that you are running integration tests that call through to a database. I realize this can be a hassle to set up in a pipeline but building better quality software means some investment in automated testing. That requires a way to build and update dev/test databases over time. A crucial part of embracing DevOps in the database.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Serious Software Glitches

    Recently Robert Sterbal pointed out a podcast to me. This link is for Apple Podcasts, but it’s for the Journal, which is on other platforms (I listened on Spotify). It’s the story of a computer glitch in UK post office software, which resulted in quite a few local postmasters being criminally prosecuted, many convicted, and even a few committing suicide. It’s a sad story, and it’s complex, but there are some technology-related elements.

    First, the overall story is Fujitsu sold the UK a point-of-sale system for post offices. There was a computer glitch here, which incorrectly calculated lots of totals and showed postmasters owing more money than they should. They were upset, called support, got nowhere and many were liable for paying money they didn’t owe. The UK postal management hid information about the widespread nature of the problem, while prosecuting many local postmasters. Fujitsu support didn’t disclose to callers how others were experiencing this same issue. This also coincided with a (an unrelated) law that changed saying computer systems were presumed correct and anyone accused of a crime had to prove the computer was wrong.

    Without a doubt, there are human failings here with support people, management, a vendor, and likely others. I don’t want to minimize those, and I do think quite a few people involved, especially management, should face charges. However, since this is a database-related site, I wanted to focus on the code quality here. I don’t know the exact nature of the calculation issue, but there is clearly a bug somewhere in the system. Do we, as technologists, think we’re better developers or database people than those at Fujitsu? Would we not produce calculation bugs that might be hidden in aggregations? I have to say that I see this stuff all the time and not just in development. I run into these bugs in production, and I think this is often because we don’t embrace enough testing. I see this in all sorts of systems, with developers of many different experiences.

    While application developers have gotten very good at unit testing, that same habit hasn’t gotten as widely deployed among database developers. What’s more, I often find that people writing aggregation queries for reports often use lots of live data, and they don’t write tests or even perform calculations to ensure complex formulas are correct. If you’ve ever done complex aggregations in SQL or DAX, you might find there can be strange effects from filters, from NULLs, and even from the way a window or range of rows is processed. It’s easy to say that a report on 1,000 rows of data out of 100,000 is roughly correct with some total, when you haven’t actually verified that calculation manually.

    I certainly think Fujitsu deserves a lot of blame in this case. Ultimately, they are the source of issues. Those that covered up the problems, both at the UK government organization and at Fujitsu should be prosecuted and held liable, but the programmers and testers are also at fault. They didn’t do a good job testing their software, and worse, didn’t do the job of tracking down the bugs, finding issues, and correcting them. I hope those issues are fixed now, but they weren’t addressed promptly as this situation took place across years.

    I often work with companies trying to build software better, but I find it hard to get them to test database software. I know the testing frameworks are immature, the tooling is poor, and honestly, too few of us have a good test data management process in place. However, we can start to learn to add unit tests to our code. At the very least, we ought to write some repeatable, automated test when a bug is reported. Clearly, in that situation, we (as a team) didn’t write good code if a bug was found. Either because of tech skills or we didn’t get the specification correct. In either case, we need to improve and automated tests to ensure we don’t make this mistake again are a way to start getting better.

    Much of the software I’ve worked on isn’t directly related to affecting human lives. That’s probably true for most of you unless you write software that controls some sort of vehicle movement or medical device that dispenses care or drugs. My son works on rocket flight software, and he takes that seriously since people will be riding those, but for most of us, the work we do isn’t critical to anyone living or dying.

    However, this story shows that we might still affect human lives. We ought to take that responsibility seriously and ensure we are doing the best job we can to produce quality software. Having some testing (and good test data), is a way to double-check ourselves and our team. It’s worked well to raise the quality level of mobile software dramatically. We database people ought to learn from that and adopt better testing.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.