A core concept of continuous delivery is build once, deploy anywhere. The idea behind it is simple, the code is built once and deployed to all the servers throughout the various environments. For example, if you had a development, testing, staging and production server, you would build once before deploying to development. Once verified, that same package would then be deployed to testing and the process would repeat itself.

This is very easy to do with code. For the longest time, I was used to building MSDeploy for websites and MSI for windows services and windows applications. All that was automated via a CI build server.

This is not so easy to do with databases. That is until we discovered Red Gate's DLM Automation Suite and SQL Source Control.

Old Database Deployment Process

As stated before we have four environments. Every company I have ever worked for has had four environments. Strange. Developers have admin rights to both development and testing SQL servers. That led to this being the development and deployment process for database changes.

1. Dev - Make necessary changes on the development SQL server.
2. Deployment - right click in SSMS editor window and change connection to testing SQL server. Run script.
3. Dev - Make code changes to make use of new SQL server changes.
4. Testers - Test changes, find bugs. Developers fix issues and re-push. Repeat.
5. Dev - Run SQL Compare and SQL Data Compare between testing and staging SQL servers. Generate scripts and supply them to DBAs to run on staging.
6. Deployment - DBAs run script files on staging.
7. Testers - Verify all changes are up in staging.
8. Dev - Run SQL Compare and SQL Data Compare between staging and production SQL servers. Generate scripts and supply them to DBAs to run on production.
9. Deployment - DBAs run script files on production.

In a perfect world, all that work would be done in a short time span, say a week, to eliminate database drift between environments. This process was full of points of failure.

• Manual generation of script files between environments.
• Database environments inevitably have drift between them, so scripts would have to be regenerated multiple times.
• The same script is not used in multiple environments. When a production deployment occurs, there is nothing more than hope that nothing will go wrong. Something always went wrong.
• Forces a human to keep track of all changes, and they must know what should and shouldn't be deployed.
• The window of time between when a database change is made and when code catches up to the database change. Fine for non-breaking change. A massive pain for a breaking change.
• Changes could be deployed to any of those servers at any point. There is no control. It becomes the wild west.

Build Once, Deploy Anywhere

With tooling, all of those manual steps are removed.

1. Dev - make necessary changes on a local SQL server. Update code to reflect database changes.
2. Dev - check in code changes and database changes. Push changes to source control server.
3. CI Server - build code and database packages. Run any automated tests to ensure validity of changes.
4. Deployment - deploy database changes to development SQL Server. Verify changes.
5. Deployment - deploy database changes to testing SQL Server. Verify changes.
6. Testers - Test changes, find bugs. Developers fix issues and re-push. Repeat.
7. Deployment - deploy database changes to staging SQL server. Verify changes.
8. Deployment - deploy database changes to production SQL server. Verify changes.

There is no manual generation of scripts. The tooling runs a compare between the database package built by the CI server and the SQL server being deployed to. By the time it has gone to production the package would have been deployed 3 previous times. There should be no surprises by that point. In fact, it really should turn into a non-event.

No More Wild West

Build Once, Deploy Anywhere eliminates a lot of the wild west mentality. Developers are still admins on the development and staging SQL servers. But if they make a change on a server and they don't check it into source control then the next time someone does a deployment those changes will be wiped out.

Fun Fact: having database changes wiped out by the automated database deployment process has happened to every developer on my team. Including me. It only takes once to learn the lesson. After spending an hour trying to figure out why a change was blown away is a good learning experience.

Conclusion

It was a bit of a mind-shift to adopt this concept. Everyone was really used to just pushing any old change whenever they wanted. But after a year using I can safely say I never want to go back. When I come across a database or application not using this process I groan.