...
Traditional packaging requires users to maintain a reference database, but often this is expensive 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 higherEphemeral database connection require the same Roles as required for Datical Packager Permissions,
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.
Limitations
You cannot place the temporary copied database in a pipeline or run a status on it because technically it does not exist in the pipeline).
Usage
Using a SQL Server Ephemeral Database eliminates the need to manage an additional database on the pipeline. The ephemeral packaging feature creates a clone or copy copy of the first database on the pipeline when the packager runs, then packages all the changes on it, and drops the database copy after . 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 desired first database (this is the DEV database in most cases)on the pipeline.
| Info |
|---|
It’s important to note that the database to be copied should always be the first database in the pipeline. |
...
Update the deployPackager.properties file to set databaseBackupRestoreMethod=BackupRestoreEphemeralSqlServer
2.
...
Ensure the first database in the pipeline(s) has the db_backupoperator and dbcreator permissions for the user.
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.
3. 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.
...
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.
Property Name | Description |
|---|---|
ephemeralCopyName | [Optional] Defines a name for a DB copy (has impact on SQL Server Ephemeral copy only). Default name for the ephemeral copy will be source database name with Property Validation: Copy name should be up to 30 characters long, contain no spaces [ ], slashes [/] and semicolons [;] and cannot begin with a number. |
ephemeralCopyWithData | [Optional] By default Liquibase will copy databases without data. (*) If you would like to copy both the structure and data, use this boolean property. (*) There is one exception in that the data from the Property Validation: true/false |
ephemeralSkipFinalDeploy | [Optional] The default value for this setting is false. Any changes being packaged are deployed to the first database in the pipeline during the Deploy Packager operation. Setting this value to true allows changes to be packaged and changesets to be created and stored in the changelog.xml, but the deployment to the first database in the pipeline does not occur. Property Validation: true/false |
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.