Support staging/production scenario as in Windows Azure
This could be done with the future cloning technique or perhaps a more sophisticated one?
This is not currently planned at this time. Will keep this item open for voting and comments.
One option for this is to use a different schema for each environment, so you can have more than one environment running on a single SQL Database, which saves money.
For example, you can have all your non-Production environments (dev, test, QA) on a single database, with a schema for each, and your Production environment in a database on its own.
I have also run across a need for having production/staging for databases in Azure. Our scenario is using release pipeline and builds created in VSTS with staging environments and slot switching of a Azure Web App.
Using Azure CLI task it is very easy to create the staging databases either empty, copy from live DB or from backup. Using Cake or Powershell we can also manipulate the staging database if there is something that needs to be reconfigured for staging.
So for us most of our requirements just worked in the VSTS release pipeline straight away. The only issue we ran into was https://feedback.azure.com/forums/217321-sql-database/suggestions/17367109-allow-database-to-reside-in-different-resource-gro
For production we want to have a delete lock at the resource group level for everything but that doesn't work since the sql server and the databases need to reside in the same resource-group we cannot use azure locks on only some databases in a server to protect them from being deleted. And if we put the db server in the production group which is locked then we cannot scrap entire staging or test environments easily.
Dan Siegel commented
The process should be fairly straight forward like deployment slots are for Azure Websites (App Services).
1) I should be able to manage one or more versions of the database for scenarios like Production, Staging, Development
2) I should be able to choose between user requested and an automatic process. This could be daily, weekly, etc., and perform a clone anytime I need.
3) I should be able to choose schema only, full db, or select tables to sync
4) I should be able to run any Sql files, PS scripts, or compiled apps I may need. For example I may want to change user passwords to a known password for testing, or generate a random password and email the developer.
5) I should be able to kick off this process using any stored backup and not just the live database.
Shannon Lowder commented
Yeah, the built-in db copy could be used to clone the db, just restore it to your db_staging like mxrss suggests. You could even use powershell to automate the restores with some work.
Chris Schaller commented
I'm not sure how much of this is "Azure's" responsibility.
It is not the platform's role to identify all of the services that your solution might be using, nor are you confined to only consuming services in Azure.
Firstly, there are common staging vs production paradigms that we as developers have been implementing long before Azure, I have to ask:
Why are you treating your deployment to Azure differently to your normal in-house or client test vs live environments.
Secondly, Visual Studio has provided awesome support for deployment to staging vs production.
Our App uses two separate databases, multiple services outside of azure and 3 roles within.
In our Visual Studio solution, we have the two Azure deployment profiles, one for staging, one for production, we then have two solution configurations... you may have heard of them, Debug and Release.
We have two rules in-house. Staging deployments MUST be debug builds, Production deployments MUST be release builds. The Deployment profiles handle this for us so it's not something that you need to be overly aware of.
We toyed with the idea of changing the solution configuration names to Staging and Production but the name is arbitrary.
We use mainly web.config transformations (visit http://go.microsoft.com/fwlink/?LinkId=125889) to handle the fact that settings need to be changed between the two environments and some compiler directives.
To Handle our Azure DB changes we could use DAT packs, however we have an in-house solution were the service roles detect changes and verify DB schema on version upgrades, as this is the solution we have been using for many years now, it all still functions the same in Azure.
Because we integrate with a number of on-premise services, there is no way that Azure could automatically migrate or indeed affect the state of these services. So either way we would have to manage this ourselves, as you would expect, however the web.config transformations really are instrumental in managing our service proxy configuration.
Cant this be done by either
1) Creating a new DB Server
2) Creating a copy of the databases like stagging_db