A few years ago when I started delivering presentations about automating database delivery with Grant Fritchey, I was surprised by something. We were rehearsing a talk and Grant mentioned that there were really only two places where we knew the state of our code: in a VCS and in production. I hadn’t really thought about that before, but realized it was true.
While the development environments are always in flux, by using a VCS, you can determine the state of your code at any point in time. However since changes can occur in production (DBAs adding indexes, changing security, etc), you can’t count on a release branch of some other designation as being a guarantee of the state of your live environment. Production is always its own version of the truth.
Ideally all of the changes being made on your production system are tested in development, QA, and pre-production environments before being deployed, but very few companies have a mature enough deployment pipeline to allow them to push hot fixes and patches rapidly enough, especially when they need to be made in an emergency.
More than likely some changes will always be made in production directly, especially small ones that affect data. You can’t prevent this, but you should be able to deal with the aftermath and ensure that your system doesn’t destabilize from the enhancements. Without a doubt DBAs should log any scripts they run, but it’s easy to forget things, especially when changes are made in firefighting mode or at 3am.
That’s why having some sort of monitoring system to watch production for any changes becomes an important logging mechanism for DBAs. Whether you build one yourself, or buy some software, you use the native tools or bolt something onto SQL Server, make sure that you put something in place. Work to push your ad hoc changes back to development, and ensure they get into a VCS at some point.