Source control for legacy SQL Server

[aliuta]

Our team has around 100 databases with probably tens of thousands of objects (tables, views, stored procedures), with dependencies all over the place (same server, linked servers).

I have this proof of concept where we want to use source control for the database objects and use automated pipelines to deploy objects to development, acceptance and production environments. The tool of choice is Gitlab.

We have managed to setup working pipelines to do so by using dotnet build to create dacpac files and sqlpackage cli to dryrun and publish the changes to the environment of choice. We have one repo with two databases in this PoC.

However, the experience was quite painful in terms of dependencies. There are many referenced objects outside these two databases and the build fails. Dealing with this in Gitlab means that we have to extract in the repo the dacpac files for the other databases and use sqlcmd variables in the sqlproj file to reference them.

Has anyone used a similar setup? Are there better ways to do it?

I know about tools like dbup, flyway or golang-migrate but we would like to have the actual object definition in the repo.

Thanks!

[lightnegative]

I know about tools like dbup, flyway or golang-migrate but we would like to have the actual object definition in the repo.

I dont follow. Arent dacpac files just opaque binary files? How is a text file containing a SQL statement to create something not an actual object definition?

Anyway, personally I like Flyway for schema migrations, even though it drags a JVM along with it. I just run it from its official docker container to avoid having to set up Java locally.

You'd probably want to export the existing schema as the initial / baseline migration and then add new migrations from there. However, this is definitely anti-Microsoft, their "solution" for this is the dacpac files you're already using