Database versionized with SQLCA - deploying to customers using sql compare?
We use SQL Change automation to versionize our database to track changes.
Via Azure DevOps Server (internally hosted) we deploy changes to a staging sql server instance (also in our company network), where the database sits with it's final state before delivering to our customers.
Currently we have an own developed update wizard (.NET application) which is checking changes from the staging database (in a xml file) via SMO .NET with the database from a customer (the update application runs on the customers sql server). All changes (new/edited) will be then updated and so the customer gets the updated database changes we developed locally.
We now want to integrate all the benefits from the sql toolbelt to use SQL Compare and SQL Data Compare to deploy our staging database to our customers.
The tricky part is, that we cannot do this from our local network. We need to deploy an application / powershell script or anything else directly to the customer server, then we can execute this.
So the application/script on the customer server needs to check the current customer database with our staging database (which we need to deploy with the app/script). All changes must then be deployed - or be rolled back if any errors occur.
Side note: We currently have > 350 customers and with our SLA the customer could deside which version he wants. Therefore we do not deploy updates at the same time to all customers so every customer has the same version. It can be possible that we have 100 different versions running.
Hopefully I have explained our situation in detail.
Now does anyone know how we can use SQL Compare and SQL Data Compare (because we have some static data tables which needs to be deployed to our customers on every update) via an .NET application or powershell script.
Is this possible, does anyone has already experience with this kind of update mechanism?
Any hints or recommendations are warmly welcomed, if I was not clear enough of our process please let me know.