search

dataplat / dbops

⚙ dbops - Powershell module that provides continuous database deployments on any scale
MIT License
163 stars 39 forks source link

Change the SchemaVersions location to another instance #127

Open kevchant opened 3 years ago

kevchant commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

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 commented 2 years ago

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 commented 1 year ago

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 commented 1 year ago

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

iampeterdaniels commented 1 year ago

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.