Database Deployment Best Practices and Observability in DevOps

2. o Introduction o Feature Flagging o Rollback/Undo o Release Branches (Cherry Picking) o Observability o Best Practices

4. DevOps Advcate/Architect Microsoft Data Platform MVP Founder, SQLServerCentral he/him @way0utwest /in/way0utwest www.voiceofthedba.com

5. • We want changes to flow smoothly to our databases • Things will go wrong • We will learn new things about our environment during deployments • We need to have a (n ever-growing) variety of tools to deploy successfully

6. • Flyway is a migrations framework • OSS • Paid versions available • This allows you to run database scripts in order • There are various switches to change behaviors • There are other similar frameworks, this is the example framework to discuss behaviors we want

8. • Database changes can be destructive Deployments are often not backward-compatible Changes can break applications • Coordination with applications can be problematic • • Updates take time May require multiple app deployments Each may be to different platforms (web, server, desktop, mobile) • • •

9. • Use the Expand/Contract model (Martin Fowler) Database changes can be destructive • Break deployments into steps • Each deployment must be backward-compatible • Each step is backward-compatible with the previous step • Refactor app code to handle additive changes • We use multiple deployments to buy time • Use this time to update applications with feature flags •

10. • Feature Flags control code behaviour • Usually, these are an IF or SWITCH in app code Can be CASE in SQL • • Use defaults and optional parameters • Use other good coding practices INSERT column list No SELECT * • •

11. DBDeploy 1 – Add new column and triggers • DB Deploy 2 – Move data • App Deploy 1 – Add flag to use new column • App deploy 2..n – Flip feature flag • DB Deploy 3 – Remove old column, update code •

12. DBDeploy 1 – Add NULL column • DB Deploy 2 – Move data • App Deploy 1 – Add flag to use new column • App deploy 2..n – Flip feature flag • DB Deploy 3 – Alter col to NOT NULL •

13. As an example, Flyway is a database deployment framework • Flyway breaks deployments into migration scripts • Use multiple migration scripts to split deployments Separate expand and contract scripts • • Deploy Expand scripts early • Hold Contract scripts in PRs • Use name/date to signify these • Use new Deployment Rules to manage when scripts deploy to environments •

14. Rollback / Undo Rollback / Undo When things do go wrong, we need a new deployment

15. o If things can go wrong, they (sometimes) will o Despite our best intentions, we may o deploy broken code o find our code didn’t compile in the deployment o We can roll-forward or rollback Forward – patch our system Back – undo the deployment • •

16. Ensure alerts notify you of errors • Ops and/or developers Learn to comb through logs quickly • • Evaluate the risk of data loss • Is this 10s or 10 hours after deployment Have scripts ready to save data if needed • • Slow is fast and fast is slow • Think through options (with others) •

17. Flyway undo lets you reverse migration scripts Need prewritten undo (Uxx__desc) script ready • • Test these scripts in QA (or another db) • Use automated tests to ensure the changes are undone • Don’t use script success as a metric! • Alter scripts if you need to save data (auto-gen doesn’t do this) • Use a separate pipeline for rollbacks/undo • Avoid confusing anyone • Avoid accidental undos •

18. Choosing what to deploy

19. o We merge in work before it’s completely tested o If we find issues, we may need to reorder work o Commit a, then b, then c o Deploy [b, c] or [a, c] o Some changes are problematic (large indexes) o Ops may want to separate some changes o To deploy, easy to build a release branch than cherry-pick out files

20. In your VCS, use a branch to collect what is to be deployed • Can use tags • I find branches cleaner and simpler • Use a PR to move code to the branch • We can delete scripts to customize this branch • Often this is something that Ops might do with input from Dev •

21. Cherry pick is choosing specific commits to include in a branch or PR • I find this confusing and error-prone • Easy to forget a script • This customizes the release (branch) •

22. Flyway has a cherryPick parameter • Use this to choose migration scripts to include • Comma-separated list Can be the migration description or number i.e. flyway migrate –cherryPick=“1,3” • • • This can be a parameter in the pipeline • Dangerous. Can also be in the config file (better) • •

24. What Is Observability? If a system is said to be highly observable then it means that businesses can promptly analyze the root cause of an identified performance issue, without any need for testing or coding. https://www.opsera.io/blog/devops-observability-what-is-it-and-how-to-implement-it

25. One area of focus in DevOps is observability • This is an area that helps improve efficiency • This is the correlation of lots of data to find a root cause • The goal is to improve performance •

26. Monitoring is for an overall view of the system Resource utilization Trends • • • Observability is a deep dive into an application (entire stack) • Observability used for highly technical analysis Root cause analysis Debugging • • •

27. Capture all logs from systems (manual or automatic) • Build skills to correlate different logs based on time • Ensure you can turn on (or up) logging • Use logging for critical calls inside a database • Can be to a table Export to text for correlation • • Learn CLI tools (lots in the security space) •

28. • Flyway logs to stdout (--verbose / -X) • Exit and error codes should be noted • Flyway Reports are a good source of tracking what changed Ensure these are kept for at least 2 deployments •

29. Recommendations to build repeatable, reliable deployments

30. • The Main Areas Decouple deployment from release Remove the human Practice, practice, practice Use Smoke Tests Regular Retros • Management Advice • • • • •

31. • Humans are creative and solve problems well • They are not reliable across time • Driving automation is a tenet of DevOps • Use automation wherever possible (with reviews) • Avoid typing anything in a deployment • Keep everything in code • Use PRs

32. • If you want to be better at something, practice it • Regularly deploy DB changes and verify your pipeline • If you don’t have a deploy, • Let DBAs do maintenance • Deploy dummy procs/functions • If you have multiple pipelines, test

33. • There are always critical functions (app or DB) • Double check these after deployments • automatically • This is an early warning for issues • Regularly add/remove smoke tests • App smoke tests verify user functionality • DB smoke tests ensure DB code is correct

34. • Review all issues for RCA • Write up findings and recommendations • No blame, just learning • Periodically review the performance of deployments • Periodically review the performance of code in prod • Adopt patterns and avoid anti-patterns

35. • Separate deployment from release Allow teams to deploy code, check it, and then release Give staff minutes/hours/days to verify things • Use Feature Flags everywhere as a default • Allocate time for Contract/cleanup work • • Put pressure on dependent code to change • Encourage retros and publish learnings • Train people •

36. A quick recap

37. • Feature flagging • Rollbacks and undo should be rare (test early) • Release branches • Observability • Best Practices

38. • Learn about Expand/Contract • Separate deployment from release • Drive automation • Embrace Observability

39. Thank you for coming www.voiceofthedba.com sjones@sqlservercentral.com @way0utwest /in/way0utwest