T-SQL Test Automation (Part 4 of 4): Are we there yet?

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?

HELP!! The run-on sentences have been banished, but I’m now trapped in my office by a mixed metaphor generator and a punctuation doubler is guarding the door for it!!

If you have been following along as I published this series, thank you. If you sat down and read the whole thing front to back at once, thank you very much. If you read this and think I didn’t even cover everything I should have, you have my eternal gratitude.


If you recall, I started this whole thing with a simple premise: get started. Make a rough plan and get going. It doesn’t matter what part of your situation you tackle first. Just get that boulder rolling (see Newton’s First Law).

We’ll deal with the finish line in a minute.

Keep it rolling

A funny thing happens when you take something that appears immovable and get it to move, even if just a little bit. You gain the confidence of those around you. You also present an implicit challenge:

If I was able to budge this thing by myself, imagine how far we can move it if we work together!!

That is what you’re going to capitalize on.

We left off at the end of the last installment in a state where you were seeing some victories. Looking good, Billy Ray!! Feeling good, Louis!! Impressing the rest of the team. You’ve got some war stories for the next SQL Saturday. Now what?

Well, for starters, get out of that comfort zone and realize that the road keeps going.

“But I’ve been working my butt off; writing tests; piecing together scripts; comparing my databases!! What gives??”

What you’ve done so far is optimize some tasks. A noble cause, certainly. It’s probably enough to earn you somewhere between Hero and Champion status. But that’s not your full potential. With some more effort you can become a Master of the Corner Office Universe!!

Looking for moss?? Go somewhere else!!

I worked for a technology vendor who had a neat-looking button image on the public web site that read “[get product] Now“. From our perspective, we saw the message as “this will take you to the sign-up form“. One day, we had a customer approach us about that button. They expected that clicking the button would let them get a completely provisioned instance of the product, fully configured on their network. To meet their expectation would have been no easy feat. Were they asking too much? Perhaps, but the real lesson is in setting a goal that challenges you.

We can be very productive with some scripts and tools in place. But how much more can we get done with a button that sets up an entire execution environment?

Establishing unit tests and overall integration tests gave you a lot of peace of mind. Now add the capability to have any version of your code available as a sandbox. This is

  • Freedom to experiment because it won’t take you 2 days to rebuild it.
  • Easier reproduction of bug cases so you can fix them.
  • Testing new platforms or designs without interfering with anyone else.

Now you’re cooking with Crisco!!

What would this look like? Well, here’s one possible sequence:

  1. Vagrant provisions the virtual machines that you need.
  2. Your image is a basic Windows install. If you don’t want to canned machine images, you could run Windows Unattended Install.
  3. You have PowerShell scripts that run a silent install of SQL Server using a response file.
  4. Once that’s complete, more scripts in the chain configure SQL Server (create AD-based logins, setup database mail, etc.)
  5. Run CREATE DATABASE statements.
  6. Deploy database objects and static data.
  7. Deploy configuration settings that are stored in the database.
  8. Insert test data sets.
  9. Connect logins to database users to roles.
  10. Add SQL Agent jobs
  11. Add SSIS packages.
  12. Configure service broker.
  13. If the environment will be around for a while, there could be some database maintenance plans that you’d also want to deploy.

You’ll also need to get good at identifying the user input needed for each of those. For example you need to provide:

  • server names
  • user names & passwords
  • code version to deploy
  • version of SQL Server to use
  • short-term vs. long-term environment
  • size of test data set to insert

Note that the machines don’t even have to be in your shop. There are several IaaS/PaaS providers who can provide the infrastructure you need.

Is this even harder than the task optimizations from part 3?? Absolutely. After all, you’ve now reached the really steep part of the climb. This is where the “Script” button on all those SSMS dialogs can come in handy. They serve as pretty good templates for those types of actions.

Don’t let that list limit you, though. Do you need to test failover scenarios?? Install the machines as an availability group. Want to reduce project management overhead?? Allow for an execution flag that means “when the continuous integration tests pass, change the related scrum board item from “in progress” to “resolved”. The sky’s the limit.

If you get really bold, you can even code the whole thing into a custom C# application with a GUI.

No Stone Unturned

A GUI for deploying an entire group of services!! I’m off my rocker, right?? Let’s find out.

You won’t always need that single-button deployment. The cost/benefit ration often just isn’t there. That doesn’t mean you shouldn’t entertain the thought. Rather, you should weigh the situation before you spend 2 weeks trying to come up with deployable database maintenance plan templates or building a HTML5 MVC app to configure a deployment run.

So when is enough enough?? Can we uncork the champagne already?? Well, you’re going to get the DBA answer… wait for it… almost there… it depends!!

If Once you reach this point, you should have a pretty clear sense of where you are with respect to technical debt, competing priorities, and what parts of the dev/test process are still causing problems.

Maybe you are faithfully attempting to get it all perfect, but there are some hurdles that are proving to be too stubborn. Move on to something else for a while. At this point, you’ve improved a lot from the Tedium Edition in part 2. Don’t beat yourself up if you’re running into difficulty now.

That’s the beauty of this process. You’ve added value at every step of the way. Your whole team may declare “no more pushing!!“. Turn around and take another look at how far your boulder has rolled. If it really has gotten to the point where the team is fatigued with this work, you’ve probably reached the point of diminishing returns.

You may not be all the way to the finish line. There may not even be a real finish line. But you’re in a much better place than where you started. And for that, you should celebrate.



There are a couple of points that I just didn’t manage to work into the other parts of these posts.

You need to decide where your source code ends and environmental configurations begin. This is not always clear-cut, but these shouldn’t be stumpers, either.

For example, your partition schemes, partition functions, and filegroups are probably part of your source code. The actual files that comprise the filegroups are not.

When it comes to security, the logins are environmental, as are the users to which they map. I’m assuming that you’re not using a contained database, which would change this arrangement a bit. However, the users’ memberships in security groups are part of the source control.

The other key takeaway here is that this level of automation also encourages best practices. Trying to coordinate your schema compare tool and your environment-specific scripts manually can be quite tricky. If all of your security permissions are granted to roles, then your schema deployment tools can work with that all day long. This leaves your custom script to focus on wiring up logins to users to roles.

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. It doesn’t even need a separate install as of Visual Studio 2013.

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