This morning, at work, we released an update to one of our internal applications. It involved database schema changes, data migration, and ASP.NET MVC application changes. The release went mostly well, except for the deployment of the database changes which failed when the script generated by SSDT dropped user objects. We are now revising our deployment process to prevent this failure in the future.
Despite having a good data-centre Operations team, we developers are responsible for application releases. For code deployment, we are given an FTP account to copy files to the production server. For database changes, we are limited to a db_owner account to run scripts on the relevant databases. Code deployment is usually straightforward, and in the rare cases when we have to roll-back files, it is just a matter of replacing the new files with the old ones. But, when a database deployment fails, we have to engage the Operations DBA in a rather bureaucratic way in order to get backups restored. To minimise the chance of failure, our process already included a mock deployment on a copy of the database in a staging environment before the actual production deployment.
However, when we ran the script against the production database this morning, we were horrified when it dropped the db_owner account that we were using. The rest of the script failed, as expected, and we were forced to request the Operations DBA to restore the database. Whilst that was in progress, we investigated the failure and found that SSDT had been too clever in generating the deployment script. Because the logins differed between the staging server and the production server, SSDT had added statements to drop users before recreating them with the correct logins.
We re-generated the script, removed the lines that dropped the users, and attempted the deployment again. This time, it completed successfully.
The failed deployment was frustrating because we had rehearsed it many times without noticing that users were dropped. We were fairly confident that we could publish database changes directly from SSDT. However, having experienced this scenario, we have changed our process to include a review of the deployment script by at least two people before executing it on the production database. We are also considering submitting the deployment script to the DBA to review and run on our behalf, but that is unlikely to happen because their responsibilities do not include DevOps function.