ReadyRoll 1

Deployment scripts

Page last updated 27 September 2017

ReadyRoll 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.

Patch scripts are produced whenever you deploy your database within Visual Studio (e.g. when you build your solution after updating from source control), and can only be executed on the server/database that the script was produced against.

To find out how to deploy by patch, see Command Line Deployment (MSBuild).

Package deployment scripts

Package deployment scripts are multi-use 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. Given that package scripts only need to be produced once, this makes them suitable for continuous delivery scenarios: rather than needing to produce a separate script for each environment (Dev/Test/Prod), a single script can be generated at build time and applied to all of your target databases.

To find out how to deploy by package, see PowerShell Deployment.

 

Package deployment script batch execution strategy

ReadyRoll can create package deployment in two different ways, that differ by how the batches are executed:

  • wrapping batches in EXECUTE() with single SET NOEXEC ON
  • wrapping batches in EXECUTE() conditionally

The differences between the above methods can be most easily understood using an example. Let's assume that we have a migration file with the below content:

Sample content of sql migration file

-- <Migration ID="42f0706e-b084-4da8-afe6-357b653c7bf9" />
GO
USE MyDatabase
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON
CREATE TABLE tab1(id INT IDENTITY(1,1))
SET IDENTITY_INSERT tab1 ON
GO
INSERT INTO tab1 (id) VALUES (1)
SET IDENTITY_INSERT tab1 OFF
UPDATE tab1 SET id = 2 WHERE id = 1
GO
GO
USE MyDatabase
GO
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON
GO
CREATE TABLE tab2(id INT IDENTITY(1,1))
GO
SET IDENTITY_INSERT tab2 ON
GO
INSERT INTO tab2 (id) VALUES (1)
GO
SET IDENTITY_INSERT tab2 OFF
GO
UPDATE tab2 SET id = 2 WHERE id = 1
GO
USE MyDatabase
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON
GO

 

Wrapping batches in EXECUTE() with single SET NOEXEC ON

This strategy was introduced in ReadyRoll ver. 1.14.10 and is a default one for new projects. 

If you want to use this option in your previously created projects, edit your .sqlproj file and change or add the PackageScriptBatchExecutionStrategy node with the below value:

<PropertyGroup>  
 <PackageScriptBatchExecutionStrategy>WrapInExecuteWithSingleSetNoExec</PackageScriptBatchExecutionStrategy>
</PropertyGroup>

 

This strategy produces the below result:

Sample package deployment script with batches wrapped in EXECUTE() with single SET NOEXEC ON

IF DB_NAME() != '$(DatabaseName)'
    USE [$(DatabaseName)];

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    PRINT '
***** EXECUTING MIGRATION "Y:\fakePath\myMigration.sql", ID: {42f0706e-b084-4da8-afe6-357b653c7bf9} *****';

GO
IF EXISTS (SELECT 1
           FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
           WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    SET NOEXEC ON;

GO
EXECUTE ('USE MyDatabase
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON
CREATE TABLE tab1(id INT IDENTITY(1,1))
SET IDENTITY_INSERT tab1 ON
');

GO
EXECUTE ('INSERT INTO tab1 (id) VALUES (1)
SET IDENTITY_INSERT tab1 OFF
UPDATE tab1 SET id = 2 WHERE id = 1
');

GO
USE MyDatabase;

GO
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON;

GO
EXECUTE ('CREATE TABLE tab2(id INT IDENTITY(1,1))
');

GO
SET IDENTITY_INSERT tab2 ON;

GO
EXECUTE ('INSERT INTO tab2 (id) VALUES (1)
');

GO
SET IDENTITY_INSERT tab2 OFF;

GO
EXECUTE ('UPDATE tab2 SET id = 2 WHERE id = 1
');

GO
USE MyDatabase;
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON;

GO
SET NOEXEC OFF;

GO
IF N'$(IsSqlCmdEnabled)' <> N'True'
    SET NOEXEC ON;

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    PRINT '***** FINISHED EXECUTING MIGRATION "Y:\fakePath\myMigration.sql", ID: {42f0706e-b084-4da8-afe6-357b653c7bf9} *****
';

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    INSERT  [$(DatabaseName)].[dbo].[__MigrationLog] ([migration_id], [script_checksum], [script_filename], [complete_dt], [applied_by], [deployed], [version], [package_version], [release_version])
    VALUES                                          (CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER), 'myChecksum', 'myMigration.sql', SYSDATETIME(), SYSTEM_USER, 1, NULL, '$(PackageVersion)', CASE '$(ReleaseVersion)' WHEN '' THEN NULL ELSE '$(ReleaseVersion)' END);

 

Please note that:

  • For the whole migration there is only one check whether a migration was already run. If it was run, the SET NOEXEC ON statement is executed, which prevents executing this migration. After the last batch of the migration, the NOEXEC option is turned off.
  • All batches are wrapped into the EXECUTE() statement, unless a batch contains only USE database or any of the SET option ON/OFF statements, including setting IDENTITY_INSERT, offset and statistics.
  • In case you're receiving an error stating that "Cannot insert explicit value for identity column in table 'testTable' when IDENTITY_INSERT is set to OFF.", make sure that the SET IDENTITY_INSERT testTable ON is the only statement in the batch or that you don't insert rows to this table in a separate batch than setting the IDENTITY_INSERT option.

 

Wrapping batches in EXECUTE() conditionally

This strategy was the only one in ReadyRoll before ver. 1.14.10.

If you want to use this option in your projects, edit your .sqlproj file and change the PackageScriptBatchExecutionStrategy node to the below value:

<PropertyGroup>  
 <PackageScriptBatchExecutionStrategy>WrapInExecuteConditionally</PackageScriptBatchExecutionStrategy>
</PropertyGroup>

 

This strategy produces the below result:

Sample package deployment script with batches wrapped in EXECUTE() conditionally

IF DB_NAME() != '$(DatabaseName)'
    USE [$(DatabaseName)];

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    PRINT '
***** EXECUTING MIGRATION "Y:\fakePath\myMigration.sql", ID: {42f0706e-b084-4da8-afe6-357b653c7bf9} *****';

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    EXECUTE ('USE MyDatabase
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON
CREATE TABLE tab1(id INT IDENTITY(1,1))
SET IDENTITY_INSERT tab1 ON
');

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    EXECUTE ('INSERT INTO tab1 (id) VALUES (1)
SET IDENTITY_INSERT tab1 OFF
UPDATE tab1 SET id = 2 WHERE id = 1
');

GO
USE MyDatabase;

GO
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON;

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    EXECUTE ('CREATE TABLE tab2(id INT IDENTITY(1,1))
');

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    EXECUTE ('SET IDENTITY_INSERT tab2 ON
');

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    EXECUTE ('INSERT INTO tab2 (id) VALUES (1)
');

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    EXECUTE ('SET IDENTITY_INSERT tab2 OFF
');

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    EXECUTE ('UPDATE tab2 SET id = 2 WHERE id = 1
');

GO
USE MyDatabase;
SET ANSI_DEFAULTS, NUMERIC_ROUNDABORT ON;

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    PRINT '***** FINISHED EXECUTING MIGRATION "Y:\fakePath\myMigration.sql", ID: {42f0706e-b084-4da8-afe6-357b653c7bf9} *****
';

GO
IF NOT EXISTS (SELECT 1
               FROM   [$(DatabaseName)].[dbo].[__MigrationLogCurrent]
               WHERE  [migration_id] = CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER))
    INSERT  [$(DatabaseName)].[dbo].[__MigrationLog] ([migration_id], [script_checksum], [script_filename], [complete_dt], [applied_by], [deployed], [version], [package_version], [release_version])
    VALUES                                          (CAST ('42f0706e-b084-4da8-afe6-357b653c7bf9' AS UNIQUEIDENTIFIER), 'myChecksum', 'myMigration.sql', SYSDATETIME(), SYSTEM_USER, 1, NULL, '$(PackageVersion)', CASE '$(ReleaseVersion)' WHEN '' THEN NULL ELSE '$(ReleaseVersion)' END);

 

Please note that:

  • For every batch there is a separate check whether a migration was already run. It may cause the script to run slower than a script generated using the other strategy.
  • All batches are wrapped into the EXECUTE() statement, unless a batch contains only USE database or the simple SET option ON/OFF statements, excluding setting IDENTITY_INSERT, offset and statistics.
  • In case you're receiving an error stating that "Cannot insert explicit value for identity column in table 'testTable' when IDENTITY_INSERT is set to OFF.", make sure that the SET IDENTITY_INSERT testTable ON is in the same batch as inserting rows to this table.

Didn't find what you were looking for?

Product Articles

Tips and how-to guides for Redgate products

University

Easy to follow video courses

Community Forums

Ask, discuss, and solve questions about Redgate's tools

Events & Friends

Meet us at an event, get sponsored, and join our Friends of Redgate

Simple Talk

In-depth articles and opinion from Redgate's technical journal