There are lots of people who talk about Application Lifecycle Management. But, the database is a major part of every application and if you do a similar search, there aren’t very many people talking about Database Lifecycle Management at all. Why not? I’m positive you’re deploying a database with your applications. I’m also positive, because of the unique problems that databases present, primarily around data persistence, that you need to think about how to get your database(s) deployed. Unfortunately, even for strong, capable data professionals, deployment is something thought about later. Or, you’re still doing the old school method of waiting until there’s a deployment script that you’re going to review, line-by-line, before you run it against production. There’s a better way.
What you need to do is start thinking about mechanisms for automating your database deployments. This means getting your database into source control to start with. Yes, your database in source control. Right along side the application, getting versioned with the app, labelled and branched with it. This is fundamental to your automation because if you don’t know what’s getting changed when, you can’t automate anything. Then, you want to set up mechanisms to start testing your database, both the behavior and the deployment. This means taking advantage of all the work done by our dev peers on continuous integration (CI) using a CI server to automate builds and tests from your source control system. You can also use some the tools and methods around unit and behavioral test automation. Finally, once you get through these mechanisms, you can explore going for full blown continuous delivery or even continuous deployment by automating your processes using a deployment manager.
You need this, because if you want to have smooth, accurate, successful builds to production, you don’t want the production server to be the first place you run your deployment script. You want to test that thing, 5, 10, 100 times before it gets to production. And you do this through automation.
Interested? Steve Jones and I are going to be talking about this, in detail, and at length at the SQL in the City event in London coming up this Friday, October 24. We’ll also talk about query tuning and other topics. But, if you want to really explore database deployment automation and begin to get your database deployments under control and more tightly integrated with your application code deployments, then come on down so we can discuss this.
Great Topic!
It’s really a great problem. Because this could be very hard in action. I think it depends on many factors such as organizational culture ,software type and architecture and even customers habits.
One problem is changing the team members traditional thinking about database development. One can destroy everything if he/she has still the habit to change database manually on production site.
Other problem is software types like database centric ERP systems which dynamically change the DDL.
Moreover, one customer who bought such software might change database structure using its form generator or other modules.
I know that there were so many improvements like SSDT, RedGate applications etc. But I think we have a long road to achieve Database Life-cycle Management goals.
Thanks Grant for sharing! 🙂
Excellent points and thanks for sharing.
I agree. Team, culture, habits are actually the toughest part of the problem. Getting tooling is actually blindingly simple. Heck, one of the hardest things to convince people of is that they actually do have a pain point in their deployment processes. But, usually, you walk them through it and you’ll spot the “And then we spend 2 days reviewing code, testing, and rewriting by hand…”
And yeah, third party apps just have different rules to apply, you’re right.
[…] Database Lifecycle Management – Grant Fritchey (Blog|Twitter) […]
[…] Database Lifecycle Management by Grant Fritchey […]