Take the Pain Out of Debugging Databases by Getting a Change History

Introduction

Database source control is becoming increasingly mainstream, particularly as .NET developers feel the pain of debugging SQL Server databases without a full change history. But it hasn’t always been possible to get the database into your version control system, and even now, it’s sometimes seen as the preserve of the database specialist, and not something application developers need to worry about.

A financial services company in the US has recently adopted SQL Source Control across its development teams, connecting their databases to their existing source control system. We interviewed their Senior .NET Developer, Lyle Keeton to find out what difference source controlling the database alongside the application code has made to their development lives.

1. What does your company do?

We are a financial services company offering software as a service. We also host the call-center for the software and handle the transactional data. The primary "product" is a custom ASP.NET website that each call-center agent keeps open on their screen as they handle customer calls. They can look up customer information, and perform various actions related to their data. There are various other .NET applications to support the primary website. We have multiple locations in multiple states so we have a somewhat complicated network topology.

2. What was life like before you had your database changes in source control?

Well, before implementing SQL Source Control, we had nothing. Somebody would occasionally script out a database, and maybe there was an automated job, but for the most part, we just didn’t version control our database.

That meant manually emailing scripts to our DBA, and that created a huge mess because the scripts would then be updated or manually adjusted before going into production.

So when someone complained to tell me that something was no longer working, there was no way for me to go back in time to see what had changed. Basically debugging the database was a real pain.

3. What did you do with SQL Source Control?

We started checking our changes into our source control system. It then allowed our DBA to pull the latest version from source control for the purpose of deploying. In doing this, we eliminated the manual scripting part of the operation.

As a development team, we just have to remember to commit objects and tag the commit appropriately so our DBA can find it later. It’s super easy to commit objects and we spend less time on menial tasks.

4. What is life like now? What has changed?

So far, we have been using SQL Source Control to investigate changes to stored procedures and have an easier and safer way to roll back if we deploy a bad change.

The next stage will be to get some continuous integration in place so that we can reduce the manual process of promoting database objects through the test and stage environments.

5. What benefits has SQL Source Control brought to your development process?

I reckon we save at least 1 to 2 hours each week by not having to deal with outdated and manual scripting.

Because we now have a structured method for deploying database changes, we spend less time deploying, and there are fewer fires to put out the next day. That’s because if something goes wrong, we are able to quickly roll back database changes. We can then investigate them easily because we’ve got a version history of the changes and it’s easy to see what particular object changed recently.

For me, SQL Source Control has made debugging database changes much easier and faster.

6. What’s next for you and the dev team?

Definitely continuous integration. We’d like every environment except Production to be automatically updated as code commits happen, and as code is merged into upstream branches in source control.

Lyle and his peers have quickly seen the benefit of improved change tracking for database changes. Deployment got faster and debugging has become a lot easier.

If you’d like to try SQL Source Control with your team, you can download a trial version, or request a demo on the Red Gate website.