One of the biggest decisions you will make when implementing database DevOps is whether to employ a model-driven or migration-driven (or hybrid?) approach to database version control and deployment. It’s important to get this right and I’ve been talking about the topic for a while.
This recording, of a session I delivered at NDC Oslo, is a bit dated now but it covers a lot of the important points:
The elephant in the room: Continuous Delivery for Databases – Alex Yates from NDC Conferences on Vimeo.
In summary:
Model-driven/state-driven/declarative database development solves many problems associated with managing a large number of development changes but introduces some problems when it comes to automated deployment. It’s great for large teams and for complicated databases that contain business logic. It provides a practical way for large teams to manage their changes effectively.
Migration-driven/script-driven/imperative database development solves many of the problems associated with deployment automation but is difficult to implement at scale. It’s a simple concept and is clearly the most reliable way to automate deployments. As a result it is very popular with DevOps folk. However, it can be very difficult to maintain at scale and does not cope well when things go wrong or if people break the rules – for example, by patching the production database without going through source control.
There are some hybrid approaches that attempt to provide the best of both. While certainly very powerful, and probably the best approach for a large number of people, they are often more complex and come with a price tag.
You can see a recent slide deck here:
I referenced various blog posts, docs pages and videos during my talk. Here’s a list:
- Octopus Deploy documentation: SQL Server Databases, Paul Stovell (b|t)
- Declarative Database Development, Gert Drapers (b|t)
- Critiquing two different approaches to delivering databases: Migrations vs state, Alex Yates (b|t)
- SSDT
- Hybrid feature: SSDT refactor log
- Hybrid feature: SSDT pre/post-deploy scripts
- Redgate SQL Source Control
- Hybrid feature: SQL Source Control migration scripts
- Redgate ReadyRoll
- Hybrid feature: Programmable objects
- Database Deployment: The Bits – Database Version Drift, (build your own drift monitoring tool) Phil Factor (b|t)
- DLM Dashboard, (free drift monitoring tool)
- How Redgate Helped Define Our Process, Bob Walker (b|t)
Other talks I’ve delivered
- DevOps 101 for data professionals – how your jobs will change
- Database DevOps Anti-Patterns
- My full speaker schedule
The self promotion bit
If you are looking for database DevOps training or consultancy my company, DLM Consultants, can probably help. We offer on-site consultancy as well as online workshops. Our full public workshops schedule is here.
To read more about our services or to contact us head to www.dlmconsultants.com.