In order to perform any continuous integration or delivery process, an important requirement – to be able to pull out the database (which you previously versioned in source control), automatically and repeatedly, to recreate the database in a QA environment at a click a button (or scheduled on a commit).
In this article, the second Build step of the CI workflow will be described. The Build step is a step in which a database is built using the latest changes in the source control repository and once the build process is finished, a feedback of success/failure is provided to developers.
A SQL database source control “label” or “tag” (aka revision tag) (name depends on the particular source control system) represents a snapshot in time of the source control repository and its contents. It can be saved as a reference for the future use. When the database development cycles reach a particular milestone e.g. a new build, a source control label can be created as a checkpoint. The team can continue to work on the database but revert to the source control label at any time.
Whenever building a SQL Server database, it must determine which objects to include in it. ApexSQL Build provides a detailed object analysis for building a database or for a database update deployment, with an ability to customize the SQL database objects and include dependent objects automatically. Selecting objects is easy and allows to easily customize SQL database objects.
For users that need to build deployment packages across environments from SQL scripts, either by using the C# project or .NET executable, ApexSQL Build offers an efficient way to accomplish this task in a few simple steps. This applies, for example, to situations where development changes to the structure and content of a production database need to be pushed as an upgrade
Application deployment is not an easy task. For more complex updates, besides a new version of a database that should be deployed, a new version of the application should also be deployed, and even the environment configuration changes (e.g. IIS settings for a web application or some other server settings) and post-deployment testing might be necessary.
Problems with complex deployments
A complex deployment often involves a lot of manual work, which makes it slow and error prone. While these characteristics are not a big problem in a test or development environment, they are certainly unacceptable in production.
One of the caveats of having your SQL database under a source control system, is the overhead when the time comes to deploy a new database build. Even if a single copy of the database isn’t shared among the developers, but rather each developer has its own local copy of the database objects’ scripts which are synchronized with source control on a regular basis, building a deployment SQL script may prove to be a rather challenging task.