Building out processes and mechanisms for automated code deployments and testing can be quite a lot of work and isn’t easy. Now, try the same thing with data, and the challenges just shot through the roof. Anything from the simple fact that you must maintain the persistence of the data to data size to up time, and you have real problems in front of you.
However, adopting database deployment automation and testing has enormous benefits. Faster, safer, production deployment enhances the protection built around your production systems. Whether we want to use the loaded term of DevOps or not, the benefits of this style of development and deployment are easily documented and measured.
So, why are so few people doing it?
Conservation of Momentum
If we were talking about a mechanical system that we wanted to modify so that it moved faster, we’d have to address the simple concept of the conservation of momentum. The core idea here is that an object in motion will be constant unless acted upon by an outside force. Same thing goes for an object at rest.
If we go back about 30 years or so, deployment automation of applications was getting going good and strong. Team based development lead to the use of source control repositories in order to help the team manage changes. Having this source of truth lead to the automation of deployments. Ultimately even the development of DevOps style automation of builds, deployments, testing, and all the rest.
At the same time as we were building out this stuff on the application side of things, we tried to do the same with databases. Very quickly, almost immediately, it became clear that databases were going to be a pain the bottom. There were no tools that helped us build & maintain a database through scripts. We were forced to do it all by hand. Add to that the fact that deployments back in the good old waterfall days only happened occasionally, so we had enormous scripts that had to be maintained, built, deal with. Again, no tools, little process, so each of us was left on their own. Most of us just figured out how to put together giant scripts and then test them as best we could.
In other words, our automated deployment object had no momentum. It was sitting still.
If we go back 15 years or so, DevOps and other means of automation, were introduced as a concept. Happily, right around this time, a number of different tools were introduced that suddenly changed the possibilities of database deployment and testing automation.
However, the momentum is still against this. People just aren’t motivated, or organizations aren’t, to put forth the energy to overcome the momentum and move their databases towards automation.
I’d like to know why.
Redgate State of Database DevOps Survey
Redgate is once again running our State of Database DevOps survey. Please, take the time to go and fill it out. Especially if you’re not moving towards database deployment automation. Let us know why.
A few pieces of incentive. First, for every survey, we’re donating to charity. Second, as usual, we’ll be sharing the results of the survey. You can figure out how you stack up against the industry in terms of adopting faster, safer, means of deploying databases.
Heck, maybe this information will help you overcome the inertia that’s keeping you from moving faster.
Well, like you said “change for DBs is hard”. Add that in with a lot of places using a shared development database and then needing to piece together those scripts so they all work together and it adds a lot more complexity. I know Red-Gate offers some great tools and SSDT isn’t a bad option. The hardest part is likely getting proper motivation and buy-in. That usually comes after too many painful releases when the teams realize that something needs to change.
Oh no doubt. Add to the fact that, if you’re deploying mostly safely, regardless of how slow or painful, motivation for change is likely to be difficult to find. Hope you took a couple of minutes and filled out the survey.
We’ve fully automated our database deploys – a production deploy targets 20-30 servers and approximately 1400 – 1700 databases. Certain types of changes that would result in table rebuilds are not allowed (such as adding a new column anyplace other than the end) and if a table rebuild is required, then we try to truncate the table as a pre-deploy task to minimize the impact. The need to have our DBAs involved for manual work during a production deployment has been limited to mostly observational just in case a validator needs assistance. It’s been a lot of hard work but greatly paid off in terms of consistency and ease of deployment.
Sounds great. And yeah, that must have been hard work.
Mainly Cross database dependencies, everywhere.
That absolutely creates pain and difficulty. Please, do fill out the survey.
The short answer is lack of support/prioritisation/buy-in from higher up in the company, but I’m not sure that’s a useful answer by itself.
The company I work for has managed, quite successfully, for the last 20 years without any of these things, and without a lot of generally accepted good practices in both database and software development. The development team has never been large, and when I started was largely made up of people who had been there a long time (in some cases it’s the only place they’ve ever worked) and new starters.
Me and a couple of other relatively new hires, combined with some of the older guard leaving and a new dev manager starting just before me, means the balance has shifted towards people who want to make improvements, but the wider business is used to having a dev team that largely works on chargeable work for clients, not internal improvements.
This makes for quite a tough sell when it comes to making any meaningful progress. The dev manager is very encouraging, but struggles to carve out the time for us to make these changes, and there is still some resistance within the team and across the wider IT department to making these changes.
One of the new projects has had some success with this, and this is starting to shift the higher level management thinking, but I suspect we will always have to do this kind of improvement work incrementally, as part of larger chargeable pieces. We are also making some progress on selling the wider business on the general idea of spending time on improvements and documentation. We’ve had some major issues this year because we have overly engineered incomprehensible systems with no documentation who were always maintained by the people who originally built them. Some of these people left this year (in some cases leaving us with systems nobody still at the company had ever worked on), and I think our level of fragility and vulnerability has shocked the business a little.
Hi Grant,
can you point me some documentation that gives an overall roadmap on how to implement?
I have a hodgepodge of links on the topic, but none that say – start here, do this, end happily. I haven’t found one of those yet.
Well, the “end happily” part is actually something that never quite arrives. You’re always growing and expanding and adjusting. However, there are three key steps:
1) Source control. You have to have a known source of truth and source control gives you that along with history, auditing, branching. So you have to have a way to get your database in and out of source control. That can be manually with the source control tool, or using a 3rd party tool like Redgate Deploy.
2) Deployment automation. This is where a bit of magic has to be applied for the database. Tools do it best, but you can build it out yourself (although, tough choice). You can build it yourself by using a manifest, run these scripts in this order. However, that’s a lot of work. Again, something like Flyway or Redgate Deploy does this part of the work.
3) Deal with environments. A mechanism to reset an environment so it looks like production or like the last release or… whatever, and then a way to get your deployment automation to run there. This part entails a ton of work, scripting and what not.
The real key to it all is steps 1 and 2. Database in source control. Database out of source control and onto a server. All the rest comes naturally from there.
Here’s a video to outline this. I also have a ton of presentations on this topic. Getting people started is my favorite thing to do.
https://www.youtube.com/watch?reload=9&v=3cwvGaOIJVc
This is an all day class I teach on the topic:
https://www2.slideshare.net/GrantFritchey/devops-for-the-dba?qid=af843d70-752c-4087-bc33-7e3bc72f9e81&v=&b=&from_search=2
I hope that helps.
One of the reasons we are not doing it because there is no documentation from MS on how to use Pipelines for Azure SQL.
What is the Input? An SSDT Database Model Solution?
Where do you upload that solution to a Devops “thing†and publish it?
What is the deliverable? A SQL script? An updated SQL instance?
Where do you setup all the moving parts of a pipeline?
That is what is killing adoption of Devops
And since 99% of our database are on-prem…