Is Sql Server Migration Assistant dead?

What are the best practices to update the database schema in a SaaS environment?

• Sometimes in a Scrum sprint, one discovers that it is necessary to add database tables or columns. This could, of course, cause potential problems in production. In a Software-as-a-Service environment the problem can be even bigger. Apparently you are only supposed to have one version of the software. It's a pretty long story, but it all boils down to Preferably  we would like to have automated migration, but the people responsible  for the production environment, would like to review the migration SQL  before releasing the changes. Also the QA department prefers to update  the acceptance test server manually themselves. So we can generate the  migration SQL and even send it automatically. Another  issue is that there are no automated tests for the migration, since we are  using special in memory databases and mocks for the unit tests and  integration tests.

• Answer:

  I believe you answer your own question - lack of automated testing. When you are rolling out changes in any production environment (SaaS or not), you will have scenarios where there is a point of no return - changes that cannot be rolled back. Lets go over the methodology first:  Since you have a SaaS model, typically you will not have more than three releases in the pipeline (N, N+1 and N+2 code base). For each release, you will typically have a perf-test platform, a system/integration test platform and other unit testing platforms (sometimes these may just be developer laptops). In the SaaS world, you could look at integrating your development tasks with operations - DevOps. So you would have a process that daily (or weekly) check out the code from svn / repository, compiles it, builds the app, deploys the required virtual machines, deploy the app on the VM's, starts up the app, runs the tests, logs the results in the bugzilla / bug-tracker, optionally takes a snapshot of the VM's and destroys the VMs.. You gotta have very strict code control policy (In one of the shops I worked, beyond a point in the dev cycle, only 1 person in the team was allowed to check-in the code. The code could only be checked in if it compiled)... Now on to the app design: You may want to think working around the "point of no return" problems. This is where you would need someone who can look across the code base / app architecture and find issues (though there may not be that many, especially since you now have the ability to drop table columns without having to recreate indexes or constraints)..