Deploying SSDT and web app with breaking changes

Question

I'm just wondering if anyone has come across this issue before.

For simplicity we have 2 projects

• SSDT Project
• ASP.net MVC app

Both are hosted on AWS, the MVC app is load balanced with 50% instance deployments.

Deploying these apps via a CI server separately may cause a timing window that can cause the application to break if there are breaking schema changes in the SSDT deployment.

A new Not Null column gets added to a table, users are still using the old version of the web app and it may break because the table expects a value.

Or the reverse, the web app has updated but tries to insert data into that column but does not exist in the table yet.

One possible way to solve this is create the column as to allow nulls, deploy SSDT changes, deploy web app, alter column to not null in SSDT, redeploy SSDT. This process is a manual process unfortunately.

Answer 1

I have worked on a web site similar to this, we deployed the db separately to the web site - so changes would follow this pattern...

Change DB to include new code, making sure it was backwards compatible so...

-deploy new procs rather than modify old ones or add default parameters etc -significantly changing tables means new names and putting views in place to replicate old structure

Deploy DB changes and then web site, in a future deployment tidy up and get back to the state you actually want to be in.

It sounds like a waste of time but in actuality all it means is just every iteration also tidying up some pieces from the last iteration and including them in the next release.

It also takes discipline to make sure you remove the intermediate code and actually do update the website but it is all doable.

Have a look at this which basically discusses what challenges there are:

https://www.simple-talk.com/sql/database-delivery/non-breaking-online-database-deployments/

Good luck - it is a great feeling when you do this as you can really move to a continuous deployment model.

ed