HomeDataOpsDevOps for databases: “DataOps”

Comments

DevOps for databases: “DataOps” — 4 Comments

  1. In my opinion the way forward is a combination of the two. Idempotent pre- and post-deployment script sandwiching a state-based deployment. There are actions that will fail due to state if you rely on state-based deployment only.

    • Tuan, I’m trying to understand your comment. Could you give an example of when an idempotent script would _not_ work for a state-based deployment? Which actions will fail due to state if you rely on state-based deployments only?

      It seems like one should be able to find a creative way to make _any_ SQL script idempotent, which means definitionally there should be no side effects if I press F5 a second time accidentally.

  2. I love this post for so many reasons. IMO, state-based is the way to go… especially for team development!

    Each member of the team should have their own DEV instance and be able to build/deploy the current copy of the db. Shared DEV instances are frustrating with multiple developers stepping on each other’s toes.

    In some cases, testing can be tricky and special considerations are required for large datasets or regulatory privacy issues (e.g. HIPAA/GDPR/etc). Source control policies (e.g. gated checkins) can help ensure no member of the team “breaks the build” (is this possible with migration-based scripts?)

    As for deployment, yes, that can also be tricky with state-based… but I’d argue the same for migration-based. One important thing to point out is that with state-based deployment (e.g. SSDT/SQLPackage.exe) you can generate a deployment script and then review/modify as needed and finally test-again in a separate non-production (e.g. Test or Integration) environment before final (automated) deployment to prod. This is very helpful with large databases where data movement is a concern and some state-based tools don’t generate the most efficient code.

  3. It seems like both approaches suffer when there is drift. In the state-based deployment, if an index is added to the database after a snapshot is captured, then the deployment would presumably drop that index to enforce the state in source control. In the migration-based deployment, drift seems like it might only matter in cases where a migration refers to another object (e.g. a new foreign key to a pre-existing table and column) that’s been deleted. In the state-based case you would likely notice drift on the first deployment after the drift occurs. In migration-based case you could go for 100s of deployments before someone writes a new migration that relies on a deleted referent.

    As in continuous delivery of applications, it seems that potentially the most critical change management activity is weening teams off of problem solving in production.

Leave a Reply

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

HTML tags allowed in your comment: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>