DevOps: Why Don’t Database Developers Use Source Control?

I keep doing work at sites where none of the database code is stored in version control (source control) systems. I keep wondering why that is.

At a recent site, all the source code was in individual files just sitting in a single folder. That’s just not sensible.

I’m left wondering why it is that almost every team that I see working with higher-level languages just assumes that some form of source control would be used, yet it’s almost the opposite when I’m working with data teams.

Having decent source control makes such a difference:

  • No more overwriting changes and losing them.
  • No more wondering what changed between versions, or who changed them.
  • And so on and so on.

There seems to have never been a culture of source control among DBAs; and database developers are somewhere in between these two worlds.

One aspect of this is tooling.

Vendors like Red-Gate do a reasonable job with their source control offerings for T-SQL but some clients want a “pure-Microsoft” solution for some reason.

In earlier versions of SQL Server Management Studio (SSMS), there was support for an SCCI (Source Code Control Interface) provider add-on. That would let you connect SQL Server script projects to source control. Sadly, that disappeared in recent versions of SSMS. I gather that there might be a way to attach the Visual Studio Team Explorer to it but I haven’t pursued that and I really hope that a standard interface will return soon. I feel that SSMS should interface directly with both TFS and Git as part of a default install. Having tools like this without source code interfaces built in, helps to push an inappropriate direction.

If however, you are using SQL Server Database Tools (SSDT) to build your databases, then both TFS and Git are standard connections from Team Explorer.

I just find that I can’t do my database development work directly in SSDT. I find very few people do that. Most use SSMS for development.

I’d love to hear others’ thoughts on why this culture has evolved this way, and how to change it.

4 thoughts on “DevOps: Why Don’t Database Developers Use Source Control?”

  1. My own theory is that it relates back to the artificial barriers between DBAs and developers; all things data related “need” a DBA, even if they consider themselves to be “development DBAs”. In other words, if you work with data, there’s a tendency to see yourself in terms of the particular product you use and not part of the general family of “developers”

    Developers should use source control. As long as database developers see themselves as DBAs and not developers, source control isn’t part of their language.

  2. In our development shop we have a developer database that developers can access. And much of it (stored procedures, functions, indexes, views) are stored in TFS source control. We have an in-house tool that compares what is in source control with what is in the developer database, and if there are any differences, the build will fail. The tool will also sync the database to source control or vice-versa. Sadly, other database artifacts are not yet part of this system (foreign keys, table schema) but maybe some day. Let’s just say this system, home-brew and flawed though it is, is far better than nothing, and has saved our bacon more than a few times.

  3. Interesting…. I have yet to work at a place where db code (and schema) is not in source control. It was always in Visual Source Safe, svn, cvs, git at every place I worked since 1999. Current place everything is built upon checkin from Jenkins in dev, twice a day in test. Maybe I got lucky 🙂

    Denis

  4. @pmbAustin – I love the idea of breaking the build if something in dev doesn’t match production. I would exclude any object starting with “dba_” so things needed for dev work can be excluded.

    @Denis – I’d like to hear more about how you handle structure changes.

    Having worked in lots of environments, I can say the issue is complex, and that alone is a deterrent, especially in shops with little IT/DevOps chops. Stored code (sprocs/triggers/functions/types) should be in source control for sure. But the issue is tricky for referential integrity and schema. Once the database is put in production, the nature of the game changes forever, and I’ve only had custom processes to deal with rolling DDL to production.

    Current and longstanding practice where I’ve worked is to package all db changes into an update package (set of scripts or some other tech) in two parts: 1) Get the stored code changes deployed (create and alter procedure statements); and 2) Other DDL SQL.

    #1 is straightforward- Package all create/alter statements into one or many files, that the deploy uses .

    #2 is via an idempotent update.sql that is only ever appended to after initial creation (when is initial creation? See below). This script checks for existing first when adding/removing columns, altering referential integrity, adding tables/views, dealing with sequences, look up table hydration or revisioning (which can be much more tricky) etc.

    Also handled would be changes that need complex orchestration to handle data evolution related to the change. There is a reason Entity Framework (Microsoft’s answer to Hibernate) doesn’t automate schema changes. It’s just too complex of an issue for any tool to automate. That’s why data engineers and DBA’s make the big bucks!

    So when is initial creation of the update.sql? The first time you deploy changes after the database was put into production!

    Yes, that means the update.sql can have way ancient stuff in it. But considering the complexity of all the issues that could occur in a schema rollback and/or database restore scenarios, this works well. It also works for revisioning to V current different databases which may of different versions (say you have a copy of production used for analysis that isn’t revisioned at the same rate as production and you don’t want to recreate which would require a data reload).

    In my experience, an update.sql statement spanning years of development is no problem if written with great care. It runs fast, and ensures consistent state every time. It does however, require a culture of development where the team works under a DBA or data developer SPA to hand craft the update.sql file for every rollout containing database code or schema changes. But that level of organization pays huge dividends in a crisis.

    Sean

Leave a Reply

Your email address will not be published. Required fields are marked *