I’m a consultant, so I see a variety of answers to Mala’s question.
But her question isn’t exactly “where does the database code live?”, it’s “where do you keep your database code?” – and I’m not going to take the bait about whose code it really is (because I think the answer to this is “it belongs to the developer or customer, the DBA is just the custodian”).
So let’s assume it’s “my” code, that is, I’m responsible for it and possibly created it in the first place. Perhaps it’s the code for indexes that are set to be created. Then there’s the question about whether it’s been deployed yet and whether it gets managed differently before it’s in test/production compared to after.
Fundamentally, the answer to this is that it fits in with whatever processes are used in my client’s organisation. I can encourage their DevOps-culture, their likelihood to implement good practice around source control and deployment, and all that, but ultimately, I have to fit in with them.
Sometimes, there simply is no source control, or at least, none that I have access to. The database is the only place that some code lives, and I find myself querying catalog views like sys.all_sql_modules to understand the environment better. But there are often other items at play, such as reports, and these aren’t tracked either.
If I have permission (which isn’t always – many of my clients don’t let me have any of their data or code on my own device), I like to schedule a process (PowerShell using SMO) to script out all the objects I can (if they’re encrypted then that might be harder and shouldn’t be done on a production environment, but it’s still doable), including reports, jobs, pipelines, basically anything I can script – and use this to create a repository so that at least I can track changes. My PowerShell script can pull all that, and then a few quick git commands can push changes into a repo.
A scripted repository can be hugely valuable, for spotting what’s changed when bespoke software was updated, or to be able to find which things (even reports) access some tables. If something starts going slower – has an extra column been added to some query without being added to an index perhaps?
At this point, I’ve introduced them to the benefits of source control, and their culture can start to shift towards using it for all their code, whether it’s application code, database code, analytics code, whatever.
Plus, this repository can be a place where my own changes can also be tracked, and we can start having conversations about deployment methods too.
