T-SQL Tuesday: Managing Database Code

My answer to this month’s T-SQL Tuesday topic is rather dependent on my workplace. So long as the database code is covered by version control at minimum, I’m usually happy to take my lead from the existing company &/or team processes so long as they work well in terms of code coverage, security & ease of use.

However, if there’s no version control for the database(s) or I’m given the choice, my preferred option for managing database code is a git repo. My logic is that application code is usually (or should) be stored in a version control system like git, so why shouldn’t the database code be treated similarly?

Of the two main approaches to version control for the database, state (or model)-based and migration-based, I prefer state-based because I like to be able to see what the desired state of the database should be at any time & this approach makes refactoring (changing the database schema) much easier in my opinion. If you’re new to these approaches, have a quick search for “state vs migration version control for databases” & you’ll find a number of resources, opinions & conversations on the matter.

Since I tend to use the state-based approach, I create my database code repo using Visual Studio’s Database Project or SQL Server Data Tools (SSDT). I then start to add my database objects – for an existing database, this involves importing the schema into the project from the SQL Server, whereas objects for a new database can be created directly in the database project & deployed to the SQL Server from there.

Managing git integration for the database project is easy to do with Visual Studio so that each & every change can be committed to git before it is deployed. Common git actions & features (such as commits, pushing/pulling, branches, etc) can be easily handled from within Visual Studio or a third-party UI tool if you prefer.

I don’t currently use a CI/CD pipeline for deploying code changes (although I have in the past). My basic very high-level approach to code changes is to:

1. Make the changes in the git repo.
2. Build the project & commit the changes to git. These changes should be pushed from your local machine to a central repo (e.g. Github, Bitbucket, etc) so your device dying doesn’t end up with you losing the whole codebase.
3. Deploy the project to a dedicated development or test environment (not production!) by publishing the project or scripting the changes & running the script directly on the SQL Server. The deployment process for database projects involves VS comparing the the database code the the state of the actual database & creating a “diff” script to bring the database in line with the codebase.
4. Test, test, test… ideally using an automated testing platform, like tSQLt for unit testing.
5. Follow the company’s agreed-upon change control process for deploying changes to the production environment, co-ordinating with any necessary application changes, scheduled downtime requirements, etc.