Traditional packaging requires users to maintain a reference database, but this can be time consuming and cumbersome to manage. Using a SQL Server Ephemeral Database eliminates the need to manage an additional database on the pipeline. You simply configure Liquibase to create a temporary copy of a database in the pipeline to be used in place of the Reference Database. Once the copy is made, you will ensure that all of your changes work on the copy, implement them on your original database’s changelog, and then destroy the database copy. Follow this guide to learn how to implement this workflow.

MSSQL Ephemeral Modes

There are two modes associated with MSSQL Ephemeral Database, copies without data (default mode) and copies with data. This mode is controlled by the ephemeralCopyWithData property in the deployPackager.properties file.

• Copies without data use Microsoft’s DBCC CLONEDATABASE command. This command creates a schema-only copy of a database. This mode should have performance benefits over copies with data. However, the Liquibase user associated with the first database on the pipeline must have sysadmin server role in order to use the command.

• Copies with data use the BACKUP and RESTORE SqlServer commands to create a complete copy of the database. This mode requires the Datical Packager Permissions (db_backupoperator and dbcreator) on the first database in the pipeline.

Pre-requisites

• Requires Liquibase Enterprise 8.8 and higher

• For ephemeral copies without data, the Liquibase user must have sysadmin server role

• For ephemeral copies with data, the Liquibase user requires the roles of Datical Packager Permissions (db_backupoperator and dbcreator)

Usage

Using a SQL Server Ephemeral Database eliminates the need to manage an additional database on the pipeline. The ephemeral packaging feature creates a copy of the first database on the pipeline when the packager runs. All changes are performed on the copy and the copy is dropped when packaging is complete. If the final deploy is not skipped - it will deploy newly packaged changes on the first database on the pipeline.

Open

Backup and Restore Package Method

In order to implement an Ephemeral Database, the databaseBackupRestoreMethod package method on the deployPackager.properties needs to be configured.

A new package method called BackupRestoreEphemeralSqlServer has been added.

See: Use the Required deployPackager.properties File

1. Update the deployPackager.properties

Update the deployPackager.properties file to set databaseBackupRestoreMethod=BackupRestoreEphemeralSqlServer

2. Optional Step: Associated deployPackager.properties

These are packaging properties available specifically for the ephemeral database packaging method. They are optional if you would like to override the default values.

See: Use the Required deployPackager.properties File

3. Setup database permissions

If using copies without data, ensure the Liquibase user for the first database in the pipeline has sysadmin server role.

If using copies without data, ensure the Liquibase user for the first database in the pipeline has dbcreator server role and db_backupoperator user permission.

Integrated Security option
Liquibase will connect to the ephemeral database using the same connection method as the first database in the pipeline. If the first database is using Integrated Security connection method, then the ephemeral database will also use Integrated Security connection method.

4. Remove existing REF dbDefs and references in the datical.project

In the datical.project file, remove any dbDef definitions for REF databases and also remove the references to these dbDefs in the plans/pipelines.