Azar smooth update when changing database schema

Question

Let's say I have a production and intermediate deployment using their own (SQL Azure) databases. If the schema in the configuration stage has changed and needs to be deployed to production, is there a certain way to achieve database updates in the production database (without downtime)?

eg. If I change the VIP setting of ↔ production (and at the same time automate the change of connection strings in some way), then what is the best way to automate updating the sql azure database.

My thought would be to detect an environmental change in RoleEnvironmentChanging (although not sure if the VIP exchange is even triggering RoleEnvironmentChanginng) and run a sql script against the current database (i.e. prod) at this point, however I need to make sure that the script runs only once , and there will be a transition of several instances.

Answer 1

So, you have a production deployment that has its own Azure SQL database and an intermediate deployment that has its own Azure SQL database. In this situation, both applications have a connection string pointing to two different databases.

Your first requirement is to change the database schema on the fly when you change the deployment or do something, and I have the following problem with this design:

• If you write any code inside a role to perform the action "ONE and only once", there is no guarantee that this will happen only once. This will happen many times, depending on several scenarios, such as

  1.1. In any situation that you must redirect to the virtual machine by the system, this CODE will do the same as during the last re-view.

  1.2 You can protect it so that it does not execute when the role starts or the VM starts using some registry key of some foreign key, but there is a complete proof mechanism that cannot happen.

• Because of this, I would suggest that when you are ready for a SWAP deployment, you can:

  2.1 Run the script to update the Azure SQL schema associated with production (this will not affect the loading of the application, since it is untouched, but when your database schema is updated, you can better know how it affects your application)

  2.2 Change the configuration in the intermediate deployment to indicate the production of SQL Azure (this will not have any application downtime at all)

  2.3 SWAP deployment (this will also not have application downtime)

Thus, even when you manually update the database schema and then deploy SWAP, there is no significant downtime other than the time it takes the database to update the schema.

Answer 2

I searched for best practices for this everywhere and did not find any. So far this is what I am doing:

• Deployment to stage (production has already started)
• Copy the app_offline.htm file to the root directory of the Production website. Thus, I block users from using the application, thereby blocking changes to the database. I use only one instance.
• Backing up the database.
• Run the DDL, DML, and SP scripts. This updates the production database to the latest schema.
• Test the application on stage.
• VIP exchange. This brings the application back online because the app_offline.htm file is not in Staging (new production).
• If something goes wrong, replace the VIP again, restore the database and delete app_offline.htm.

With this approach, I have an idle time of about 5 minutes; my database is small, which is better than waiting for the creation of Vm and users receiving errors.