Change server database connection at runtime

[VagueGit]

Great have a forum for discussion. Thanks @Mimetis

I raised this topic as Issue #359. As can be seen in the issue I did manage to find a solution.

In brief, our app puts a SQLite database on the client machines. Each client (or group of clients within a company) syncs to that company's database in an Azure Elastic Pool. At login the app determines which server database to connect to and configures the DMS SyncAgent

I would appreciate input from the community regarding my approach. Are there weaknesses in my approach? Is there a better way? Is there interest in DMS supporting switching server databases at runtime, out-of-the-box?

[gentledepp]

well the way you switch databases seems fine. Others do a similar thing using cookies or information in the bearer token.

However, your approach to "migrate" databases won't work. Please be aware that dotmim.sync does currently not support evolving sync schemas over time.
So if you change the schema, your clients would have to

1. send pending changes (hopefully your new schema version is backward compatible!)
2. run sqlite migrations
3. re-synchronize

[VagueGit]

Thanks for your input @gentledepp. This app is quite mature; it has been evolving in the market since 1996 and so the schema is quite stable. Having said that ... who knows what the future might bring.

This app had a conventional client-server architecture for most of it's life. We ran migrations with DDL statements, iterating through versions on the client to the current version. This migration is only necessary on one client as it updates the server database.

This app supports core business for our customers globally so we need to minimise disruption during migrations. Our customers are used to some delay, or delay on one machine while the server is upgraded, but a significant disruption on all client machines within a company would be problematic.

I wonder if one possible solution might be for one machine in a company to run the migration then upload a copy of it's local SQLite database to storage? Then when the app detects on other machines that a migration is required, it first checks storage for an uptodate SQLite database before running the migration locally.