In situations where deployment of database script might be delayed due to review process or when multiple project branches are automatically deployed to a single stage environment there is always a possibility that target schema was changed in relation to prepared deployment script. This might be the cause for script execution failure and consequential database issues.
To prevent described situations a schema validation would be desirable. Naturally, with automated database CI and CD processes validation should also be automated to check target schema before deployment script is executed.
Schema validation setup
The ApexSQL DevOps toolkit uses a Deploy step to execute a database script and has the option to perform target database schema validation before and after script execution.
Basic configuration of the Deploy step requires choosing deployment source, which can be a NuGet package containing synchronization script created and packed in Sync and Sync data steps, or a specific schema and data synchronization scripts can be selected as a source. For the Target a database that requires script execution should be selected.
The following Validation options allow user to set Pre-deployment and Post-deployment schema validation. For this option to work a NuGet created by Sync step is required as deployment source. A brief overview of the Sync step execution will explain this requirement and mechanisms that make this feature possible.
The Sync step has the essential task to compare a database script folder created with the Package step and a target database to create synchronization script. When Include output in package is checked, which is another requirement for schema validation, the Sync step will store created synchronization script in NuGet package.
However, when the package is examined two additional artifacts can be found beside the synchronization script.
The “SchemaSync_source_scripts” is a database script folder that represents the database project source. This is a snapshot of a database version that should be present on target after updates are done. Basically, it is the same version contained in the package created with the Package step and previously used as source for comparison.
The “SchemaSync_target_scripts” is a database script folder that represents target database version at the moment of synchronization script creation. This is basically a version snapshot of a target database schema scripted out as script folder with appropriate structure.
When the NuGet package is used as source for the Deploy step and Pre-deployment validation is checked, the step will perform comparison between “SchemaSync_target_scripts” database script folder and a target database. In case there is a change in target database schema post synchronization script creation the validation will detect differences and prevent synchronization script execution thus preventing undesirable effects on target.
An additional result of validation the difference report will be generated which can be reviewed and according to situation a new synchronization cycle can be started or target database schema can be reverted to previous version or synchronization script execution can be forced with the same package version.
Following example will demonstrate the actions that pre-deployment validation will take and expected results. During the production uptime a synchronization package was created, for example, to introduce new table to a production database with a continuous integration pipeline. This package will contain the snapshot of production database at that moment.
Another pipeline with the Deploy step was created to schedule the deployment of previously created synchronization package “overnight” during the production downtime and pre-deployment validation was checked.
In the meantime an index was introduced to a table directly on a live database:
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20190702-123720] ON [dbo].[Phone_book] ( [Name] ASC, [Surname] ASC, [Home_phone] ASC )
On scheduled execution the pre-deployment validation will perform comparison between production database snapshot and current database (with index added) which will result in the Deploy step and pipeline failure with appropriate message.
The step failure on this action means that the synchronization script was not executed.
As execution output artifact the difference report can be found and examined. The report contains scripts of compared options so clicking on an object that is indicated as different will display scripts with marked differences. On the screenshot below the index that was created in this example is shown:
The Post-deployment validation can be use as redundant insurance that deployment was performed successfully. After the execution of synchronization script the Deploy step will perform comparison between “SchemaSync_source_scripts” script folder and updated target database. If no differences were detected the synchronization can be considered successful. If there are some differences found the step will generate a report on differences which can be reviewed for further troubleshooting.
Pre-deployment database backup
Generation of “SchemaSync_target_scripts” script folder can be considered as a bonus for archiving a database schema version. But beside that, the Deploy step has another safety feature with the Backup before deployment option. Checking this option will create a full target database backup before synchronization script execution. This option executes standard Backup-SqlDatabase PowerShell command using default SQL Server backup settings for arguments. Checking the option will populate the Additional parameters field with used arguments which can be changed manually before execution.
In conclusion, continuous delivery concept in ApexSQL DevOps toolkit has moved to next level of automation with addition of deployment safety features.
