T-SQL Tuesday 177: Managing database code

This month’s #tsql2sday is hosted by Malathi, a.k.a Mala, a.k.a diligentdba (b), and asks us about how we manage our database code.

Well, as the de-facto DB DevOps expert over at Madeira Data Solutions, I felt obligated (and obliged) to participate and contribute my piece.

Let’s start with answering Mala’s questions:

1. Where do you keep your database code? Is it in a GIT-based repo, or just in the database the old-fashioned way?
   • I’m definitely a long-time GIT enjoyer. I had the displeasure of working with TFS-based repositories a few times, and I hated it a lot. Git is definitely more convenient for collaboration, branching, and merging. Many of our customers are also using GIT, and we recommend them to do so if they ask for our advice. We’re also using GIT (specifically GitHub and Azure Repos) for our own private and public repositories, and it’s very convenient.
2. If it is in a repo, how is it created? Do you script out individual scripts and add them, or do you use tools to script out the entire database, like SSMS scripter, SMO-based scripts, or third-party tools? This could apply to any relational database, not just SQL Server.
   • We’re using SSDT (SQL Server Data Tools) for our customers and our own internal projects. We’re big proponents of offline development methodologies, and state-based deployments.
3. How do you keep your repo up to date when code changes? Depending on what your shop does, you may or may not have a well evolved CI/CD pipeline. If you do, explain how it works. If you don’t, that’s fine – we just want to know where the code resides, if it is outside the database.
   • Our favorite CI/CD platform for automating deployments of SSDT is naturally Azure DevOps, as it is the only CI/CD platform (as of yet) that has built-in native support for deployment of DACPAC files. It’s remarkably easy to configure these automations using the graphical user interface. Just fill out a form and you’re done!

Let’s review some of the main takeaways:

SSDT as a development tool for SQL databases

There are numerous database development tools out there for databases. Some are expensive and complicated (e.g. Redgate or ApexSQL), some are open-source and overtly simplified (e.g. Liquibase or DBGeni).

But SSDT is in a perfect spot of being both completely freeware, as well as very robust and powerful.

The only real downside to SSDT, if you ask me, is the fact that it exclusively supports SQL Server databases and nothing else.

SSDT has a natural home in Microsoft Visual Studio as one of the available “workloads” that you can install (under “Data and Storage”).

It’s also supported in both Azure Data Studio as well as VS Code. So, that gives you cross-platform support if you need it (i.e. not limited to Windows machines only).

However, the Visual Studio version of it is definitely more robust and powerful and supports features that the ADS/VSCode variant does not (as of yet, anyway). For example, Database Unit Test projects, the Refactor tool, native CLR support, and more.

Offline Database Development

Microsoft have created SSDT to be used in a “Project-Oriented Offline Development” sort of methodology, where the SQL developer is making changes not to any “live” database (be it a production database, shared development database, or even an isolated development database), but instead to a simple “project”.

Obviously, you can combine your development with an actual living database, but your actual changes would still need to be applied in the SSDT project before moving onto the source control, and from there to testing and/or production.

Doing so would be possible either by making the change manually in the project, or using SQL Schema Compare, or using the Refactor tool, as needed.

This project-oriented development is identical to how development is done by application developers, and fits much better with Agile and DevOps best practices.

State-Based Deployments

As of today, SSDT is the only database development tool that supports state-based deployments exclusively.

There are many migration-based deployment tools, and a couple of tools that can do some kind of combination of both. But SSDT is the only one doing state-based deployments only.

State-based deployment basically means that what you’re developing and storing in source control is only the “desired end-state” of your database as a whole. And only during the deployment itself, in real-time, the DACPAC and target database are compared and a “diff” script is automagically generated to be used as the “publish script”.

This methodology basically guarantees that you’d be avoiding “version drift” in your database schemas. Even though it’s not a hard rule, as you could add all kinds of settings to your deployment to customize its behavior, such as not dropping any objects in the target database, or excluding certain object types, avoiding potential data loss and more.

You can even take it another step forward and register your target database as a “Data-tier application”, using which you can completely block the deployment if any sort of “version drift” was detected on your target database since its previous deployment.

This is just scratching the surface

There’s a lot to learn about SSDT, how it works, and how best to be working with it.

In case you didn’t know that yet, I have delivered a series of free public webinars called CI and CD with SSDT.

As of today, there are 5 sessions in this series:

• CI and CD with SSDT Part 0 – Database DevOps for Leaders: Explains the topic of DevOps in general and DB DevOps specifically, its importance, ROI, and impact on an organization, and some best practice guidelines in implementing these practices in your organization.
• CI and CD with SSDT Part 1 – Development Lifecycle Basics for DBAs: This session gives you the basic knowledge that you need as a starting point for working with source control and DevOps.
• CI and CD with SSDT Part 2 – SSDT Methodologies for DB DevOps: Diving into SSDT specifically, this session explains the inner workings of SSDT, how it differs from other tools, and what’s the best methodology of working with it.
• CI and CD with SSDT Part 3 – SSDT Tools and Features for DB DevOps: Diving even deeper into SSDT, this session is basically a “tour” of the tools and features available to us in Visual Studio when we install and use SSDT. What each tool and feature does and where to find it.
• CI and CD with SSDT Part 4 – Troubleshooting Common SSDT Errors: Taking SSDT another step forward, we start digging into the juicy edge cases and common issues often encountered by people only starting to work with SSDT. The issues we should expect, and how best to resolve them.

There are recordings available publicly for each of the above sessions, and I expect more to be coming as time goes by.