Deployment scripts
Published 31 July 2019
SQL Change Automation can produce two types of SQLCMD-compatible artifacts which allow you to handle the deployment of your migrations in different ways:
- Patch deployment script
- Package deployment script
For a full list of build artifacts see continuous integration.
Patch deployment scripts
Patch deployment scripts are single-use scripts which can be used to bring a given database environment up-to-date.
Patch scripts are produced based on a specific SQL Server instance and database name combination, so they will only contain a delta of migration scripts that have yet to be deployed to the specified database. Any migrations that have already been deployed to the database will not be included in the patch script.
A patch script is generated when you perform a release against a database and will be contained within the release artifact. This script will be used for deployment when deploying a release.
You can also generate a patch script locally using Visual Studio. Whenever you perform a build, a patch script will be generated against your development database.
Package deployment scripts
Package deployment scripts are multiuse scripts which can be used to bring all of your database environments up-to-date.
Instead of containing just a delta of migrations that have yet to be deployed, these scripts contain a concatenated set of all of the migrations within your project.
When the package script is executed against a given database, it will determine which migrations have yet to be applied and execute the appropriate scripts on-the-fly.
Because package scripts contain the content of all migrations, they can become quite large. They also incur a performance hit over patch scripts because they make a database call per migration to find out whether that migration has been deployed to the target database, whereas for patch scripts a single query for all scripts is run up front when the script is generated.
Package scripts also wrap the contents of your migrations in EXECUTE() blocks within conditional statements (using SET NOEXEC ON to prevent already deployed migrations from running). This can cause issues with temporary tables.
