“Does anyone actually do this stuff?”
This question came from a member of the audience at one of Brent Ozar’s training classes on SQL Server Database Continuous Integration. Mercifully, plenty of people confirmed in the comments that Database CI was alive and well, which was a relief, since I’d been toiling for several days on editing Grant Fritchey’s article on this topic.
I’ve since moved on to the topic of Database Testing, and a general Interweb search seeking answers to the question “How do people actually do SQL Server database testing?” immediately begs a rather similar question: “Do anyone actually do database testing?” It’s a rather barren landscape.
Pondering this, I recalled a conversation at SQL in the City last year. Having watched a fair few Database Lifecycle Management-related sessions, a friend and I wound down the event over a beer. “Hey, I have a question for you” he asked, suddenly “Where did Redgate get that figure of 74%, or whatever the crazy high number is, for devs who do database testing? You’re using it on your posters.”
“I think it was a SQL Server Central survey” I replied, to a look of deep skepticism. “And I think that basically refers only to unit testing”. Still skeptical. “Well, what percent do you think is closer to the truth?”
“Honestly? Zero”. I was amazed, but he was adamant that in most of the customers he saw, proper application testing was still relatively new, and database testing completely off the radar.
Can it be true? Is database testing one of those topics where developers, and DBAs, feel entitled to drift from the truth? Like how much fruit they eat, how many books they read, or their age?
Certainly, database unit testing seems to be a relative oasis of plenty, with numerous frameworks available such as NUnit, T-SQLt, Pester, or the unit test templates in SSDT. That’s fine for simple assertion tests on a single database object, making sure CHECK constraints work properly, and so on, but what about the classic black box Integration Test? Here, we need to run an end to end business process, which may need access to multiples tables or even databases, and test that for all the possible inputs, that we get the expected outputs, and that the database and its data ends up in the expected state.
Does anyone do this stuff? Do we quietly run these tests and decline to blog about it, or refer to it in articles, or do we release databases untested, and pray to Codd it all works?