MS-SQL Server migration (with a siding of WTF)
-
So I'm trying to automate deployment for this (internal “ERP” kind app) project. It has a moderately complex database schema that evolves a lot (too many managers raising requests to support their quirky existing workflows).
The guy who maintains the database uses Visual Stuido SQL project to do it. He is used to a workflow where he modifies the database schema, then asks Visual Stupido to generate the upgrade script by comparing it to the test instance, adds any data update logic that might be needed, commits the script under suitable version, tests it, manually, on the test instance—and throws it over the wall to operations.
Now operations is trying to automate the deployment. Which is almost done, except those upgrade scripts that currently need to be applied by hand. Why? These days there are heaps of tools like flywaydb, yuniql or alembic that could take care of that except:
- The scripts generated by Visual Stupido contain sqlcmd commands. Which means they can't be executed by any tool that uses plain old SqlClient or ODBC diver or such. And while the commands are understood by three Microsoft tools—Visual Stupido, SQL Server Management Stupido and sqlcmd (and probably sqlpackage internally as it deploys the output of Visual Stupido)—library implementing them does not appear to be available, so there is no easy way to add the support to other tools.
- And unfortunately the DB developer uses the sqlcmd commands as he has test data in the database project that are conditionally deployed. Sorting that out would be quite a bit of work.
- The scripts generated by Visual Stupido contain transactions (
). Ok, fortunately SQL Server does seems to support nested transactions, so wrapping the whole thing in another transaction should be an option.
The DB developer does not want to clean up all that junk from the scripts. And it is not that surprising given that the project accumulated three million lines of database upgrade scripts in around a year. And it's not even that big project. It's just that there are quite many requests that need changes to the schema, and Visual Studio is very verbose in what it generates.
So, before I go and cobble together some kludge in shell or python calling sqlcmd, is there something already implemented that we could take advantage of?
And of course feel free to make fun of Microsoft for not having any tool for this in the SQL project toolbox—except the sqlpackage PoS that implements the database syncing anti-pattern.
- The scripts generated by Visual Stupido contain sqlcmd commands. Which means they can't be executed by any tool that uses plain old SqlClient or ODBC diver or such. And while the commands are understood by three Microsoft tools—Visual Stupido, SQL Server Management Stupido and sqlcmd (and probably sqlpackage internally as it deploys the output of Visual Stupido)—library implementing them does not appear to be available, so there is no easy way to add the support to other tools.
