Having your database in source control and included in your automated deployment is considered a true CI/CD configuration. Getting to this point can be challenging but well worth the dedication and perseverance. We are going to walk through some of the key items you need to consider before taking the steps.
When making changes to your database schema or simple data changes, you must write these scripts in a way that will preserve the database and store them in source control for the automated deployment process to pick up. Say you want to add a new column to a table. You would write the script in a way that will run if the column does not exist. Having these checks will ensure your environment is pristine and the deployment is successful.
Testing Your Scripts
Advanced configurations will include multiple dynamic environment configurations that will load and test the database deployment scripts before making it to the true Test environment. Having created several of these, my favorite configuration has been with Docker containers and PowerShell. It is extremely fast to stand up a new SQL Server environment from a predefined image, run all your scripts against it, report script failures and teardown the container. This process can be automated upon a git merge to master and could test as many different environment backups. In fact, this same type of process could run smoke tests inside of a Kubernetes container. But that’s for a different day.
Pending on your business requirements and security standards, this type of sensitive data can be scripted but would live in a separate location only to be accessed by very few people in the organization. I prefer security to be set up before the application deployment process and in some cases, could be configured via PowerShell automation installation script.
Clock is Ticking
Say you found a bug in production after you pushed your code and might have crippled your application. After troubleshooting you find you can make a quick code change and redeploy to solve the outage. How long does it take to run a complete deployment cycle? This should include all the typical steps before it is pushed in production. A typical configuration will have two Docker loads, a test environment, UAT environment and then a production environment. Each deployment time must be calculated for each environment and summed for your total deployment time.
Now its decision time. Is it faster to rollback your changes (in some cases by hand) or make the code change, run code review and redeploy your code? Emergency’s dictate exceptions. With that said, if you have long deployment times, maybe bypassing a few environments to get the correction out is warranted. You just need to make sure your change is eventually pushed across all environments.
There are many different platforms for Source Control. I personally use Azure DevOps VSTS to manage all my repositories and deployments. The idea of source control and deployment is to make sure all your scripts are executed in the correct order. This is not as difficult as you may think. Once you decide how to layout your source structure (scripts, tables, procs, a bunch other…) you will need to develop some PowerShell to extract and sort in a certain order.
If you have not included your database in your automated deployment, now is the time. If you are pushing code by hand or batch scripts, take the time to learn CI/CD. Take the extra steps to ensure all your environments are up to date and your deployments are seamless. Benchmark your deployment times and understand each environment you have configured.
Once you have completed all of this, you will sleep better knowing the process has your back.