Yep it’s kind of as gross as it sounds. But there’s a good chance that you have implemented a very nice CI\CD pipeline for part of a vendor database that you are allowed to customize. Unfortunately you don’t always know when they might log in to do a hotfix or patch and you may have dependencies on objects that you don’t own. Automation to the rescue:
Previously on this site I outlined how you could audit your SQL instance using free/open source tools. I ended up leveraging that same process to run a daily check to see if there where unexpected changes in the production database and (thanks to this blog post from Jason Sider) creating a Github issue to ensure time gets set aside to review those changes and pull them into our local codebase.
The details are pretty simple so I’ll leave the specific implementation details to you dear reader but in a nutshell you’ll need the following resources:
- an automation user with access/permissions to the appropriate Github repos in order to create an issue,
- an automation tool like Octopus Deploy or something similar.
The entire process is:
- Query for unexpected changes (whatever that means in your environment). In our case it’s any changes made to the production database that wasn’t done by our automation tools. Any other changes should (at the least) be reviewed to see if they are legitimate DDL.
- If unexpected changes exist for the last $timeFrame bundle them together in to the issue description and submit it to Github.
If you get fancy (or already fancy) this may trigger other useful workflows. Our repos are also tied into our Kanban board and a Github issue will automatically create a work item for the DBA team to work on, so it really raises the visibility of that goes into maintaining the vendor side of the code base and keeping internal projects in synch with those changes.
Please note: You should have a general use git login that can be used by automation tools. This will prevent you from breaking processes that depend on a user access token when the user leaves the organization and their access to the repos goes away. Generate user access tokens for the automation user and then store those secrets in a secure, shared location for anyone working on automation (along with the login credentials etc. for this shared user).