Let's say that I have an azure sql (paas) that has in front of it a scale set of VM's, each VM containing a website hosted in IIS. In front of the scaleset i have a traffic manager for website updates purpose only ( whenever i need an update, i create a second scaleset with VM's having the latest version and after the second scaleset deploy, i change the traffic manager to route traffic to the newly created scaleset). The website is 100% stateless. The problem raises for me just after i create the second scaleset: how would i run rollout scripts on sql azure db without disturbing clients that consume the old website version?
I am thinking of using mirroring or sort of for the db to replicate transactions on a second azure db, while running on it in the same time rollout scripts. I just have then to cut traffic from the live scaleset and wait for sessions to drain and then switch to the new scaleset. is this a good aproach? I see that i lose the "always on" capability that i really need. I really don' t know the best practice, a book or a link would be highly apreciated.
In short, i would like to remain as "highly available as possible" even for planned application updates. How can I reach this?
Try or consider having 1 more SQL Azure database. Your traffic manager could indicate also which of the 2 SQL Azure database is the active. Deploy changes to the passive database, then update the traffic manager database to make the passive database the new active database.