We’ve been using OctopusDeploy for approximately 3 years now for mainly on-premises deployments (SQL/APP/WEB).
For SQL deployments we have a standardised Deploy.ps1 which takes our build’s dacpac(s) and uses sqlpackage/dacFX to publish/script/report etc. against target SQL instances.
We’d like to adopt a similar approach to Azure SQL (PaaS) databases - i.e. use the same basic Deploy.ps1 to publish our dacpacs to target SQL PaaS instances in Azure.
We can achieve this by having a dedicated tentacle server on-premises and allowing that tentacle access to Azure on port 1433 so that this tentacle can connect to Azure instances using a standard connection string.
The problem we then face is, we lose the segregation of environments - i.e. the firewall rule which allows our tentacle server access to Azure exposes all of Azure (e.g. dev, test, pre-production and Production instances all accessible from this one tentacle)…
It would therefore be possible for a deployment in the “test” environment to have a mis-configured connection string which would inadvertently (or deliberately!) target the production SQL instance.
it is possible to add a prerequisite check step to our Octopus process that would validate the connection string against current environment (e.g. environment = “test” and $connection contains Azure equivalent of “test”)…The problem here being, there is currently no way to make a deploy step mandatory.
- Is there a best-practice approach to deploying from on-premises Octopus to SQL PaaS (which retains segregation of environment)
- Are there plans to introduce mandatory process steps (which cannot be disabled at deployment time)
Any advice on this would be much appreciated,