Database Deployments for Non-Developers

Ever since my first disaster of a manual deployment as a DBA I’ve been on the DevOps train. Maybe I didn’t know exactly what it was called but certainly this is a solved problem I said to myself? How do other organizations find the changes that need to be deployed from their dev/test environment into production? How can I ensure that we have a process that has the best chance of succeeding when we go to deploy? And most importantly: can I get out of logging in after hours to manually run scripts?

This, dear reader, is what brought me to Redgate originally: the SQL Compare tool was exactly what I was looking for in early 2014. Working backwards I started requiring that we wrap tooling around our SQL deployments. It started small (and slow): prior to deployments developers needed to meet with me to go over the change report to identify production ready changes they wanted deployed but ended big: last year the DBA team was out of the deployment game altogether since the development team had implemented SQL Change Automation steps in Team City and Octopus Deploy. But not so fast: what about the database development not done by the development team? I am sad to report that the DBA owns its own data repository, helper databases on every SQL instance and the occasional one off database that ends up under our care. Additionally our BI team fully owns the development of our data warehouse and staging databases. Few of these were in source control and in the case of our help db there was nothing in place to ensure they were up to date across our environment. For various reasons we decided we needed to own our deployment tools and we recently found ourselves with our own Team City and Octopus Deploy server. The next couple of blog posts will document the places where I had to do some quick learning/trouble shooting to get it all working right since: I have not been in the development world since 2003 and having solely been a production DBA since my return to tech in 2013. Hopefully if you are doing database development—but aren’t what folks would consider a traditional developer—this guide will prove helpful for standing up and taking advantage of what all this automation is able to offer you!

  1. Configuring Team City
  2. Configuring Octopus Deploy
  3. Building a deployment pipeline.