Introduction

After Level 3, a changeset has been designed, structured, and thoroughly rehearsed across environments so that execution can always start from a known baseline and return to it when needed. Yet rehearsal alone does not make a deployment production-ready. The scripts used during development and validation can be executed as they are. However, consolidating them into explicit deployment artifacts helps preserve the execution semantics already verified during rehearsal.

This level focuses precisely on this final transition: converting a rehearsed changeset into production-ready deployment artifacts and defining how those artifacts are executed safely in production. The approach is intentionally pragmatic. The goal is not to introduce new concepts, but to ensure that everything proven during rehearsal remains intact as the changeset moves from the development workflow toward production execution.

During development and rehearsal, Create and Rollback scripts are executed individually. This allows each script to be inspected, validated, and rehearsed before being safely executed in the target environment. Production deployment, however, requires a different form: something that can be executed with minimal room for interpretation. This concern becomes even more relevant when a changeset includes a large number of scripts, especially when the activity cannot be performed directly and must be handed over to DBAs or release managers for execution. Passing a full folder of scripts, together with execution instructions, can be a delicate and error-prone practice. In cases like this, the outcome should be a small, explicit set of deployment artifacts designed for a single execution step: ideally, one forward deployment script and the corresponding rollback script.

Although the need to produce explicit deployment artifacts becomes more evident in environments where developers cannot access the production system directly, this practice should not be seen merely as an operational constraint. Even in less structured environments—such as two-tier architectures where developers may deploy directly—producing explicit deployment artifacts remains a valuable discipline. It ensures that production execution mirrors the behavior already validated during rehearsal.

Naturally, this approach becomes redundant in personal databases or single-tier setups where development and production coincide, as scripts are executed directly in the only available environment.

Preparing scripts for merging

When assembling a deployment artifact, all relevant scripts must be included, not only those in the Create and Rollback folders. If the changeset includes _schema or Update scripts, their content must be incorporated into the final artifacts in the same order already validated during rehearsal.

Nothing new is added at this stage. The production scripts are a composition of known, rehearsed parts. Nonetheless, before merging individual scripts into a single deployment artifact, a couple of preparatory checks are strongly recommended.

The first concerns transactions. During development, it is common to wrap individual scripts in explicit transaction blocks to support testing and rollback during iteration. Before merging, these transaction wrappers should be removed from individual scripts when present. If a transaction is required—and tolerated by the handover process— its scope should be defined explicitly at the artifact level. Leaving nested or overlapping transaction blocks in a merged script can lead to unexpected behavior or execution failures in production, especially if a temporary ROLLBACK statement from a previous test is inadvertently left in the script.

The other one is batch separation. Before being merged, each script should terminate cleanly with a GO batch separator. This ensures that batch-scoped statements behave consistently and object creation and alteration occur in the intended sequence. Preserving the same execution semantics as the individual scripts across the entire artifact is crucial. A missing GO may seem like a small detail, but it can cause disproportionately large problems once scripts are merged:

-- Create script 1
CREATE PROCEDURE etl.addCompany
(
     @CompanyCode nchar(5)
    ,@CompanyShortName nvarchar(15)
    ,@CompanyName nvarchar(255)
)
AS
INSERT dim.Companies (CompanyCode, CompanyShortName, CompanyName)
SELECT @CompanyCode, @CompanyShortName, @CompanyName

-- Create script 2
DROP TABLE dim.Companies
GO

CREATE TABLE dim.Companies
(
     CompanyCode nchar(5) NOT NULL CONSTRAINT PK_Companies PRIMARY KEY
    ,CompanyShortName nvarchar(30) NOT NULL CONSTRAINT UQ_Companies UNIQUE
    ,CompanyName nvarchar(255) NOT NULL
)
GO

-- Update script
EXEC etl.addCompany 'C0345', 'Contoso', 'Contoso Technology Solutions Ltd.'

Merging these scripts without adding a GO after each one would produce the following outcome:

-- Merged script without the required GO separator
CREATE PROCEDURE etl.addCompany
(
     @CompanyCode nchar(5)
    ,@CompanyShortName nvarchar(15)
    ,@CompanyName nvarchar(255)
)
AS
INSERT dim.Companies (CompanyCode, CompanyShortName, CompanyName)
SELECT @CompanyCode, @CompanyShortName, @CompanyName

DROP TABLE dim.Companies
GO

CREATE TABLE dim.Companies
(
     CompanyCode nchar(5) NOT NULL CONSTRAINT PK_Companies PRIMARY KEY
    ,CompanyShortName nvarchar(30) NOT NULL CONSTRAINT UQ_Companies UNIQUE
    ,CompanyName nvarchar(255) NOT NULL
)
GO

EXEC etl.addCompany 'C0345', 'Contoso', 'Contoso Technology Solutions Ltd.'

In this case, the missing GO after the procedure definition changes the meaning of the merged artifact. The DROP TABLE statement is no longer a separate deployment step; it becomes part of the procedure body.

As a result, the script may fail when attempting to create the table dim.Companies, since it already exists. If the table does not exist, the script may still complete successfully, but it no longer performs the operation that was validated during rehearsal. In either case, at the end of execution, the table dim.Companies would not exist, as it would be dropped by the stored procedure.

Merging scripts safely

Merging scripts should follow a few simple principles:

1. Scripts are merged only after rehearsal is complete
2. The merge preserves the exact execution order already validated
3. No new logic is introduced during the merge
4. The merged rollback script mirrors the merged Create script in reverse order

Although this may appear to be a straightforward operation, it is a delicate step that requires care—especially when dealing with multiple scripts coming from different folders. It is easy to paste scripts in the wrong order, accidentally omit one of them, or copy content from a rollback script into the forward script, or vice versa.

Using a capable text editor or merge tool could be a good idea to reduce the risk of accidental errors during this step.

Validating merged artifacts

Once merged, the deployment artifacts should be treated as new executables, even though they are composed of known parts. Before production execution:

• run the merged Rollback script in UAT,
• confirm UAT returns to its original baseline,
• run the merged Create script in UAT,
• validate successful execution.

This sequence assumes that the changeset has already been applied in UAT during rehearsal and ensures that the merged artifacts preserve the same rollback and forward behavior already validated.

Only after this step should the artifacts be considered production-ready.

Executing in production

The goal of the merging activity is to keep production execution straightforward, whether it is handled directly or handed over to someone else (release manager, DBA, or colleague).

As the final step following rehearsal, the deployment artifact is designed to eliminate common issues that typically arise during execution, such as interactive debugging or environment-specific adjustments. Production deployment should become a controlled operation rather than a troubleshooting exercise.

In practice, production deployment should consist of three simple steps:

• run the forward script
• verify successful completion
• know exactly which rollback script to run if needed

The deployment process is designed to work even under restricted permissions, supported by rehearsal in UAT—possibly simulating restricted privileges—to ensure consistent and repeatable execution. This separation of responsibility allows developers to design and validate changesets without requiring direct access to production systems.

Handling failure in production

Even with careful rehearsal, failures can occur. The difference at this stage is not whether failure is possible, but how it is handled. If a failure occurs:

• the rollback artifact is executed,
• the database is restored to a known structural state,
• investigation can proceed without leaving the system in an unknown condition.

Rollback is no longer a contingency plan—it is a normal, expected capability.

Once deployments are structured this way, each changeset becomes a well-defined and predictable unit of release. However, even a successful deployment may expose new issues. This typically occurs in more complex environments, where multiple developers or teams contribute changes in parallel. This introduces a new challenge, which will be the focus of the next level: concurrency.