T-SQL Test Automation (part 1 of 4): Get back on course

The series at a glance:

  1. Get back on course
  2. Live in the now, look to the future
  3. Time to focus
  4. Are we there yet?

WARNING: Excessive metaphors and clichés ahead!


Sometimes Often in life, the hardest thing to do is to just get started on something. Pay attention to how many of your thoughts begin with “I should…” Yep, same here. So, my most recent “I should” was “I should really use this blog site since I bothered to pay for a domain name.”

And on that theme, I offer you a plan to do the same… for the code in your SQL Server database.

You have a process by which you test and deploy your database code. When you can bear to think about it, you realize exactly how manual, error-prone, tedious, and occasionally soul-sucking that process can be. Well, it’s time you stop thinking about what is and focus on what should be.

Let me begin by painting the picture of your current situation:

  • You have SQL Server installed on a single shared development server that the whole team uses.
  • You managed to find enough headroom on your laptop to run a private copy of MS SQL Server.
  • You wrote a fair amount of T-SQL stored procedures and functions to support your application code.
  • You try to make changes on your local server before publishing those changes to the shared server.
  • You test you changes with some quick ad hoc scripts. If you save those scripts, they have names like myproc_scratch.sql.
  • You edit some manually managed T-SQL script so that you can commit your changes to source control.
  • Your application code has unit and integration tests that offer good code coverage.
  • Your application tests are faithfully executed by a build/CI server that has really cool plug-ins and reports.
  • You rely on your automated integration tests to make sure everything’s fine with your T-SQL code.
  • Your security configuration between local sandbox, shared dev, and source control don’t necessarily jive.

Just in case that list hit a little too close to home, know that I am not spying on you. You are in a much larger life raft than you thought. I didn’t say “boat” because if you’re in that situation, you are not navigating the waters of code quality. You are adrift on the current, fending off the hungry sharks with a plastic oar.

Why should I care?

Skipping past the obvious dangers in the list above, let’s take a look at some easily overlooked problems:

  • “My application’s integration tests succeed, so I don’t need to test the same edits twice.” Well, are all of your procedures used by the application? Are you sure you don’t have procs that are only called by SQL Agent jobs? If you have a 500-line procedure, are you certain that one join you just added didn’t affect the logical consistency of the code? This is not the time to be cavalier.
  • “I only ever need to test one version at a time, so I don’t need to automate anything.” Are you sure you only work on one version? What do you do when a bug is found in V.latest minus 2? Are you running performance and functional tests on 2 separate sets of servers? Is more than one SQL instance used to support your application? Can you afford to keep a set of SQL instances around at all times to cover every version currently supported (even if they’re virtualized)? How do you make sure that those instances are all in sync with the correct source control version?

Solving those problems can be daunting, right? Sure, but only if you try to address them completely in one sitting. Pick your favorite cliché:

  • How do you boil the ocean? One pot at a time.
  • How do you eat an elephant? One bite at a time.
  • The journey of a thousand miles begins with a single step.

Sick of these yet? Good!! It’s much more interesting to get started on automating your database tests anyway. For that, see second installment in this series.

FULL DISCLOSURE: Because I have been a user of the Red Gate SQL Developer bundle in the past, I am somewhat fond of their way of doing things. I am also glad to see that the Microsoft tools related to SQL Server Projects (with SSDT) have come a long way since their introduction as Database Projects. Of course, you could accomplish things like schema/data compare, source control tracking, etc. with free or roll-your-own solutions. Many people have successfully written utilities that use SMO to generate scripts and various methods of comparing the output. However, it is fair to say that the functionality provided by the packaged tools can save money, even in the short-term. No matter what you end up with, just make sure you’re not spending a dollar to save a dime.

The series at a glance:

  1. Get back on course
  2. Live in the now, look to the future
  3. Time to focus
  4. Are we there yet?

Thanks for stopping by. I do enjoy hearing from you...