Terri Hurley shares one path:
Back in the day when I received an email from a developer who requested a Production Change, I would copy and paste SQL code from an email to SQL Server to run against a production database. I never bothered to save that code. But later when I was asked for a history of database changes, it was awkward to locate all the previous email requests asking for production changes. Fast forward to present day, most businesses have embraced Change Management Separation of Duties in companies of all sizes.
My preferred path to change management is:
- All changes go into source control. You can either have the people making changes create the actual scripts to run, or generate them from base changes in source control, dealer’s choice.
- Each changeset goes to a specific environment when it gets PR’d into the appropriate branch—that is, dev, QA, test, pre-prod, prod, post-prod, next-gen-post-dev-unprod, whatever.
- DBAs are on pull requests, at least after a certain point. PR to dev? Let the team handle that code review. Once you move nearer to prod, at least one DBA performs a code review.
- After PR gating happens, your CI/CD system automatically deploys the changes, assuming it passes any continuous integration tests.
This takes time to get right, but it’s very much worth the effort. I worked at a company where they built the release management processes on top of Git + Azure DevOps. You can also use tools like Flyway.