Mistake #32: treating deployment as a one-off DBA exercise. Keeping the only reliable schema copy on a development instance and sending complex scripts to production invites environment-specific failures. section 7.6 section 7.6.2
MagicChoc’s maintenance window
MagicChoc’s developers stored code on their development instance, relied on weekly backups, and manually scripted data into temporary tables before dropping and recreating objects. A CI/CD workflow packages the application and applies repeatable releases instead, reducing deployment complexity, bugs, and time to market. section 7.6 section 7.6.2
A data-tier application (DAC) supports continuous delivery by packaging database artifacts and dependencies into a uniquely versioned package. It can include tables, procedures, users, and instance-level objects such as logins. section 7.6.2
Use a safer deployment routine:
- Keep T-SQL code in source control for version history and rollback. section 7.7
- Build and publish the database project as a DAC through Visual Studio, or register the database from SSMS. section 7.6.2
- Use schema comparison to understand changes, check for drift, and assess affected functionality before deployment. section 7.6.2 section 7.7
- Add unit tests for programmable objects before releasing incremental changes. section 7.7
Figure 7.9 CI/CD process
Use the CI/CD process figure to orient the workflow from packaged changes through controlled deployment. section 7.6.2
