T-SQL Test Automation (Part 2 of 4): Live in the Now, Look to the Future

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?

In the first post of this series on T-SQL Test Automation, I laid the groundwork of why you should care and some pretty corny clichés as to how to get started.

First, some housekeeping is in order. I’m not going to dwell on the topic of how to test your T-SQL code in this series. Instead, I’m saving that for the future. If you can’t wait, there are several fine resources and frameworks out there.

Back to business. Part 1‘s clichés are gone1 and all we are left with is the stark reality of “the now”. No frilly tools. No push-button test executions. No manager-distracting shiny reports automatically emailed to anyone who cares (and a few who don’t). Welcome to your starting point.

Get in gear

So, you wouldn’t keep your job if you didn’t at least get your code right 60% of the time, right? And that wouldn’t have happened if you didn’t test it some way at least 20% of the time, right? Well, congratulations!! You have a testing process.2

Is it glamorous? No. Does it get the job done? Yes. The real question is how do we turn that mere “yes” into an emphatic “YES!!! It performs so well and is so manageable that I now have more time to focus on both the interesting coding work along with whatever I’m going to do with the cash bonus I received because my DBAs don’t have to spend all night in the NOC fixing the aftermath of scorched earth logic errors. Woo Hoo!!!”

So what do you do first to get to this professional cloud nine? Simple: write it down. It doesn’t matter how ugly it is or how inefficient it is. Write. It. Down. All you have handy is a napkin and a sharpie? Write. It. Down. You don’t have any technical debt to take care of? Fix it (because I know you have some anyway); then, look at your development process and Write. It. Down. Sensing the theme here? Excellent!

So, let’s assume you are in the situation pretty close to what is laid out in part 1 and you need to edit a stored procedure. Your current process might look like this:

Development Process, Tedium Edition

  1. Make sure the proc on shared dev matches the true version in source control3. This is the one the app’s integration tests use. You don’t want to miss any changes that aren’t in source control. If you tell me “we never make changes in shared dev without adding them to source control”, I won’t believe you unless you are a Master of the Universe (see below).
  2. Make sure the proc on your local sandbox db server matches the version in source control.
  3. Run ALTER PROCEDURE with your changes in your sandbox until your intended logic is in place, comments are appropriately added, and there are no syntax errors.
  4. Use ad hoc queries to make sure your base tables have the data they need to cover the situations you want to test.
  5. Manually execute the proc, changing the input parameters for each run; checking the output via the Results/Messages tabs in SSMS. Sometimes, you even save this script – remember myproc_scratch.sql from Part 1?
  6. If you don’t like what you see, keep making changes and manually re-test. If you’re happy with it, apply the new version to shared dev and source control.
  7. Re-run your test data setup and proc execution scripts from above, this time on shared dev. The scripts will likely have to be changed at least a little bit to account for different data. Also, you probably can’t wipe out most of the current data like you can in your sandbox db.
  8. Pray to your deity of choice that the application’s integrated tests aren’t executed by your continuous integration (CI) server at the same time as your bench test. When this happens, you often spend up to 15 minutes sorting out the mess.
  9. Let the application developers know the new version of the proc is ready for them to use.
  10. Update your documentation, especially if you changed something about the proc’s signature.

Now that wasn’t so hard, was it? BTW, I’m referring to the act of writing it down. The process above is still far from ideal.

Do you really need all that?

Some of what we do is by habit more than need. This is where we identify the low-hanging fruit and eliminate any steps that don’t add value. They’re easy to spot, but they can require some work to remove or replace. We’re not worried about fixing it yet. We need to document the process that should be. Note that the exact sequence may differ for you depending on your team’s workflow, who is responsible for which edits, whether the edit is a new proc or a modification, etc.

Development Process, Hero Edition

  1. Verify that your local sandbox db matches source control.
  2. Run ALTER PROCEDURE with your changes on your sandbox db until your intended logic is in place, comments are appropriately added, and there are no syntax errors.
  3. If necessary, modify your existing T-SQL unit tests to cover the change(s) made to your proc.4
  4. Run the T-SQL unit tests. The tests themselves include any necessary data scaffolding.
  5. Check the test results in your test runner.
  6. If you’re tests are green, commit to source control. If they’re red, fix the problem and re-run the tests.
  7. Let the application developers know the new version of the proc is ready for them to use5.
  8. Deploy the new T-SQL code to shared dev. If each engineer has their own SQL sandbox, they can “get latest” from source control.6
  9. Let the CI server deploy your committed version to its own dedicated SQL Server instance and run the full test suite. Depending on the nature of the change, some of the integration tests may be red until the application engineer finishes their changes. However, you’ll notice that you don’t have data values from multiple users competing in the same databases.
  10. Pray to your deity of choice because you just want to have a quick chat while the tests run, not because you need the stars to align in order to avoid having the data end up in an inconsistent state.
  11. Update your documentation, especially if you changed something about the proc’s signature.

Hold the phone! What happened? The list just went from 10 steps to 11! Any more and we’ll be in rehab. Ok, so maybe I went from 10 to 11 because I was looking for any excuse to make a Nigel Tufnel reference. Not really, but who doesn’t enjoy a good Christopher Guest moment?

Back to the point. Take a closer look at the steps. On the original list, you had a lot of manual-only work, especially as it relates to running your test code. Now check out the second list. Other that chatting with your deity and doing your job (steps 1 – 3), do you see any steps that can’t be scripted or automated in some fashion? I’m not talking about push a single button, wait 10 minutes, and then everything is done. Rather, there is some way to take each step’s repetitions from your fingers and give it to the computer.

A new perspective

Once you have a consistent, automated sync/deploy process with proper status reporting, you won’t need the paranoid check of all environments. In fact, if you have a build server deploying to shared dev, there’s going to be a report you can use to see if anything was amiss when the last comparison was performed. You can usually configure alerts if there are issues. So unless you receive an alert, you can trust that the contents of source control, shared dev, and your sandbox match each other.

Also, if every engineer has their own sandbox db server, you could argue that the shared dev db server is no longer necessary. Each sandbox may require multiple SQL Server instances. For example, I’ve been working on cloud storage systems. To get a proper sandbox going, I’d need at least 3 distinct SQL instances, sometimes more. So, if you have needs that go beyond a single instance, you may need to automate deployment of virtual machines along with silent (response file) installs of SQL Server. This all sounds daunting. But that’s only true if you try to tackle it all at once.

Lastly, we have not eliminated the need for developer discipline in the Hero Edition. Each engineer must still run a “get latest” for their local sandbox. With a tool like Red Gate Source Control or Visual Studio SQL Server projects, that is a trivial task. Remember that time is crucial when you’re working in an agile/scrum team. You likely have another engineer waiting on you to complete your T-SQL changes so they can proceed with testing their own code. Notice that you’re notifying them 2 steps sooner now. Anything you can do to reduce process overhead can have a big impact on your overall team velocity.

Are we done?

Not quite. I mentioned something about not having a single button press accomplish everything. That is not an impossible task. Instead, it’s part of the Development Process, Master of the Universe Edition.

To get to that point is pretty simple: iterate. That’s right. You probably aren’t paid to spend all day writing process documents if your LinkedIn profile says “Database Engineer”. But what you can do is iterate. Let’s break this down a little more clearly.

You’ve been doing your job for at least a few weeks (in most cases). You can stand back from the grind, envision your workflow as it is now, and put some scratch notes together in Notepad++. Once you have that, look at what the really tedious bits are and think about what you can do to automate it. Right now, think of “automate” in the broadest sense of the word.

Once you have that, ask some of your teammates what they think. This is an excellent time to talk it over with the application engineers and the QA people. They’ve been doing this automated testing thing for years now. Take the feedback they give you and update your workflow doc. You should be getting closer to needing OneNote or Evernote now.

In the remainder of our journey, we’ll take a look at figuring out what can be strung together and how we become Masters of the Universe.


1 I’ve replaced them with run-on sentences.

2 Let’s hope your real percentages are a lot better than that.

3 This had better be something up to date, like SVN, TFS, git, Mercurial, etc. If you’re still using SourceSafe, go directly to jail, do not pass GO, do not collect $200.

4 Yes, this assumes tests are in place. Like I said, we haven’t started improving yet. Focus on the plan right now. Some would call this a form of nerdy day-dreaming.

5 If you’re the one responsible for the changes to the Data Access Layer, this is even faster.

6 Check your SQL Server license agreements. There’s a good chance your license allows a development install of SQL Server for every production install you have. If your license doesn’t allow this, then just buy the Dev Edition. It’s only $50 per seat with all the features of Enterprise Edition.

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...