Change the SchemaVersions location to another instance

[kevchant]

I've seen some discussions online about various attempts by others to move the SchemaVersions table to another location using DbUp. It would be great if this was possible with this module.

[nvarscar]

Hello, thanks for the feature idea.
When you say "move the SchemaVersions table to another location", do you mean move it into a different table within the same database? Or into an entirely different database/server? The first one can be resolved by renaming the table in the database and changing your configuration. I can see how it can become a command that simply renames the table inside a database.
The second one, however, introduces some major changes to the way the framework operates, where you'd have to orchestrate switching contexts and (potentially) maintaining a separate connection to the schema versions table.

[kevchant]

Hi, unfortunately I mean the second one.

Discovered this solves an issue with Azure Synapse Analytics and I think a few people would prefer it if they could change the table to be in a separate SQL database.

[nvarscar]

There are two major issues related to this functionality:

1. Switching context is something only SQL Server is doing. Other supported DBMS will require a separate connection, which the DbUp framework won't be able to do without some major refactoring.
2. If two connections are involved, this would require a two-phase commit protocol implemented. The framework would have to be flexible enough to do that after each script, which again, would require modification of core DbUp components.

Have you seen, by any chance, a working solution that I could look at? Maybe the problem isn't as big as I'm imagining.

[kevchant]

Apologies for the delay. I have not seen a working version of it. Only a couple of references online saying how it can be done in theory.

[iampeterdaniels]

For me, it's important to keep the SchemaVersions (migration log) table in the target DB so it can follow it along if it is copied/moved somewhere. I could see some benefit to ALSO having it log to another central DB on the same or another instance, though.

[kevchant]

If you want to use it with serverless SQL Pools then only option is to have be able to specify another location.

[iampeterdaniels]

Ah. Of course. Actually working with an external table or view (ie files) in serverless SQL would be a pretty tricky endeavor indeed. Not impossible, but definitely not simple like a "regular" RW SQL table. Thanks for clarifying, Kevin.