Let's say we have a continuous integration server. When I check in, the post-hook pulls the latest code, runs the tests, packages everything. What is the best way to also automate the database changes?
Ideally, I'd build an installer that could either build a database from scratch or update an existing one using some automated syncing method.
What is CI CD in SQL Server?
Integrate SQL Server databases in your CI/CD pipelinesContinuous Integration and Continuous Deployment allows teams to be more efficient and SQL Server fits seamlessly into this pipeline. SQL Server Data Tools (SSDT) allows you to effortlessly design and deploy any SQL Server content type.
What is database continuous integration?
Database continuous integration (CI) is the rapid integration of database schema and logic changes into application development efforts and to provide immediate feedback to developers on any issues that arise.
Can you automate with SQL?
Automating the executing of SQL queries can be handled by any client tool which can be scheduled. Typical solutions are in place by the database vendor already. Like Microsoft SQL server has the SQL Server Agent and MySQL has the MySQL Event Scheduler.
What is a continuous integration server?
A continuous integration server (sometimes known as a build server) essentially manages the shared repository and acts as a referee for the code coming in. When developers commit to the repository, the CI server initiates a build and documents the results of the build.
I've recently bumped into an article, that might be of use.
The author explained some of the best continuous integration practices including testing, processing and automation.
Here are some of the key takeaways:
- In many shops code is unit tested at the point of commit. For databases, it is preferred running all unit tests at once and in sequence against a QA database, vs development, as a part of the Test step
- The test step is a critical part of any CI/CD process. Test scripts, including unit tests themselves, should also be versioned in source control, extracted at the point of the Build step and executed
- Pulling data from production is appealing as a quick expedient, but is never a good idea
- The best approach is using a tool or script to quickly, repeatedly and reliably create synthetic test data for your transactional tables
- Running unit tests to produce manual summary results for human consumption defeats the purpose of automation. We need machine readable results, that can allow an automated process to abort, branch and/or continue.
- Running a CI process, which requires 100% of all tests to pass, is akin to not having CI at all, if the workflow pipeline is set up atomically to stop on failure, which it should. To thread the needle, tests should have built in thresholds, that will raise an error based on either the % of tests failing or in some cases, if certain high priority tests fail.
- All processes should ultimately produce a Boolean result of pass or fail, but some non-automated processes can easily find their way into your CI workflow pipeline (e.g. unit testing). Software should be plug-n-play into any workflow pipeline, taking known inputs and producing expected outputs – like pass, fail.
- CI/CD process should be aborted on failure and a notification email should be immediately sent vs continuing to cycle the pipeline.
- The CI process should not cycle again until any errors in the last build are fixed. On failure, the entire team should get the failure notification, including as many details as to what failed as possible.
- If a pipeline takes 1 hour, from start to finish, to complete, including all the testing, then all the build intervals should be set to no less than one hour and all new commits should be queued, and applied to the next build.
- No plain text passwords should exist in automation scripts
Menu
466
