Stay Secure: Update to Flyway V10 now Update now

How Spawn is revolutionizing development for the unit testing framework, tSQLt 

IT consultancy, sqlity.net, specializes in helping organizations get the best performance and business value from their SQL Server estates through Data Platform DevOps. The founder, Sebastian Meine, is also one of the co-creators of tSQLt, the only open source unit testing framework to enable true continuous integration and continuous delivery for SQL Server.

We recently sat down with Sebastian and sqlity.net consultant, Liz Baron, to ask them about their experience of introducing Spawn to their own Test-Driven Development process. Spawn is a new solution from Redgate’s R&D division that enables users to spin up free database copies in the cloud, in seconds, avoiding the hassle of managing any hardware.

What’s the challenge you’re using Spawn to address and how were you working previously?

tSQLt was created and published for the use of the developer community ten years ago and has since become the de facto unit testing framework for SQL Server. It remains open source and we’re now trying to make it easier for the community to contribute to.

tSQLt itself was developed in a test-driven way and CI was a big part of that, but the pipeline ran on our laptops, which wasn’t very scalable. We started looking at new possibilities and transitioned to Azure DevOps to help overcome it. tSQLt needs to be tested on all SQL Server platforms that are in use and are supported, and currently that’s versions 2008 R2 and up. The CI pipeline needs to run on every version to make sure it works across them all, despite any differences.

To be able to do that, we have to spin up a new virtual machine for every SQL Server version, so every time the CI pipeline runs we would need to spin up six virtual machines, which in Azure takes forever. The average waiting time for a new VM is somewhere around half an hour if you’re using the Azure DevTest Labs platform they offer.

But we also needed to keep costs low, because tSQLt is open source and doesn’t make money by itself. We switched to containers but that requires you to maintain a Kubernetes environment and the VMs the containers run on, so it has a high maintenance overhead.

We then decided to try Spawn which makes it all a lot easier. We just issue the command and have a machine running. Our goal is to be able to offer that every time somebody creates a pull request, so it automatically kicks off the CI pipeline and reports back to them.

How has Spawn helped you?

Previously, it took us a couple of days to figure out how to spin up a new instance, get it installed and configured, put it into our CI pipeline and get it running and tested. But with Spawn, it took us no more than a couple of hours to get Linux servers up and running and start testing.

It’s also removed the overhead of maintaining a Kubernetes environment, which in itself can be hours of work a week. With Spawn, that disappears because, for example, we can set data images or data containers to automatically expire very quickly, saving us time and money.

The other benefit we’ve seen from using Spawn is teaching people CI. As part of our consulting business, we run training classes for our clients, and using Spawn they are able to spin up their own CI environment in minutes which is created and then dropped afterwards. It’s a very easy, very clean and very fast way to learn how to use CI.

In fact, Spawn makes CI accessible. Typically, working inside an organization it can take weeks to spin up a static SQL Server CI environment because it involves several different teams. It makes learning about what CI and DevOps can do for the data platform inaccessible for many people. But with Spawn, you can easily whip up data containers, play with GitHub actions and create a CI environment and think about what it would look like for CD. That can revolutionize how people work. It can be transformational for a team – and transformational to how a company thinks about the work that they do and how quickly they’re able to deliver

How is Spawn helping you open up tSQLt to additional contributors?

It’s early days, but the vision is that every time someone submits a pull request, the tSQLt CI pipeline starts up and runs the tests, those that exist already and those they added in their pull request, and then reports back to them if everything is green or if there are issues that need to be addressed.

This will automate much of the work we do and enable people to get feedback much more quickly. Currently, we’re the bottleneck to providing feedback to the community around us making improvements to tSQLt and we don’t want that to be the case. We want this to be a vibrant community and for people to feel they have some ownership of this open source framework that helps them do their jobs better.

By integrating pull requests in Spawn, people can fire up data containers, run their tests and they get immediate feedback. It takes that feedback and shifts it left, making the process much more rapid, and giving the community ownership. Spawn is enabling that for us.

What is your favourite feature and why?

The public data images. It means we don’t have to worry about data images as they’re already available and we can just use the image name and they will get us what we want very quickly. We don’t have to create a Docker file, build it and make it available. The combination of Spawn supporting the data container and having the public data images available to just fire up is incredibly helpful for the CI pipeline. The fact we can expire them too is wonderful.

Also, the support from the Spawn team has been fantastic. The documentation is great but quick and easy access to the team to help address our questions has been a huge plus.

How would you describe Spawn?

Really fast, on-demand SQL Server instances that you can use for all your testing needs.