This post describes how to run safely database migration from on-prem instance of SAP Commerce system to SAP Commerce Cloud infrastructure with use of SAP Commerce DB Sync tool.
Main purpose of following content is to describe a practical use case of "staged copy approach", with proper examples, hence at least basic knowledge of data migration aspects is required here. For more details please refer to official User Guide for Data Migration documentation.
Include DB Sync extensions in Customers repository, either as direct copy to custom extensions location, or with Git Submodule setup. Adjust accordingly localextension.xml configuration to activate added extensions, in case of extensions managed by manifest.json refer to SAP Commerce Cloud documentation.
To use staged copy, application has to be configured in a certain way, main requirement is to configure database tables prefix property (
Minimal required properties to be adjusted for staged copy approach:
It's recommended to use Service Properties of Background Processing aspect to manage this type of temporary configuration.
Create a regular build from a configured code branch in SAP Commerce Cloud Portal. Execute deployment of finished build to target environment, with database initialization option selected
Data migration process assumes that source DB schema is compatible with target DB schema, at least to the level that it can be either handled by DB Sync internal ETL logic or can/might be adapted later.
If target DB schema can be already fully and correctly populated with standard SAP Commerce logic, after accessing HAC, Schema Differences and Schema Migration can be used as provided by DB Sync, to create all required target DB tables.
This case should be selected if, either source and target solutions are already on same SAP Commerce versions, or source SAP Commerce was upgraded to expected target version.
Migration with DB Sync in staged copy approach, can be considered as kind of a "blind" migration, as application is only used to execute ETL operations and is not aware/dependent on underlying type of a data being copied. Such state can be useful on early stage of migration project, especially in case of combined SAP Commerce upgrade with migration, it might be useful to be able to run migration without fully operational custom extensions. If for example some of custom extensions, especially ones containing custom item types definitions cannot be yet deployed in SAP Commerce Cloud, it will not be possible to prepare target DB schema in a standard way, with regular initialization process. To mitigate such limitation for data migration run, target DB schema can be created based on a current source SAP Commerce system. For example in on-prem instance of HAC, or on local environment, application can be configured to use MSSQL database and generate initialization DDL scripts, which then can be used as-is on SAP Commerce Cloud DB.
In order to generate init SQL scripts, after adjusting
Generated script will be stored in hybris/temp/hybris/init_master_schema.sql file. After brief reviewing of generated DDLs, script can be imported to SAP Commerce Cloud DB with use of Groovy script. For more details please refer to DDL Executor section.
In case of successful schema population, Schema Differences and Schema Migration functionality of DB Sync can be also used to further ensure structure consistency.
Execution of migration process itself, does not require any further adjustments. Please refer to User Guide documentation.
When data copy is finished, DB Sync will provide overall status summary, additionally with more detailed JSON report which can be downloaded and reviewed, to gather migration statistics. Such data can be used for further fine-tuning of a migration process before running an actual production system cutover.
For more information about validation prese refer to User Guide documentation.
In case of a non-standard schema differences between source and target version of SAP Commerce, additional adjustments might be needed.
Examples of such changes might be orders table PK reference (
Once gathered during trial run migration attempts all neccessarry schema and data adjustments, collected script can be applied on database with Groovy script via HAC Scripting Console. For more details please refer to DDL Executor section.
Before execution of first deployment,
If previously Service Properties were used to prepare initial configuration, after adding this change, make sure to "Skip" immediate apply, to postpone services restart:
Previously prepared build, not having DB Sync extensions included, can be now deployed with selection of "Migrate data" for "Update Mode":
Last step of process is to execute database cleanup. All system tables used during migration can now be simply removed. As full database access is not allowed, this can be achieved again with Groovy script.
List of DB tables prefixed with
This section contains resources useful or simplifying described process
Following Groovy script simplifies execution of any kind of DDL scripts for database schema modification and preparation that might be required additionally to regular migration approach and recommendations
This script can be executed from HAC Scripting Language Console, its main logic utilizes Spring JDBC
Based on a type and size of particular script, DDL content can be provided in two ways:
Following Groovy script simplifies removal of DB tables created to support staged migration process. In order to generate all required DDL statements table filter condition has to be adapted with
With only this change, script can be executed. Obvious recommendation is firstly to run it in dry-run mode for proper validation. After review of generated DROP statements script can be re-executed with
Main purpose of following content is to describe a practical use case of "staged copy approach", with proper examples, hence at least basic knowledge of data migration aspects is required here. For more details please refer to official User Guide for Data Migration documentation.
Data Migration Process
Preparation
Include DB Sync extensions in Customers repository, either as direct copy to custom extensions location, or with Git Submodule setup. Adjust accordingly localextension.xml configuration to activate added extensions, in case of extensions managed by manifest.json refer to SAP Commerce Cloud documentation.
Configuration
To use staged copy, application has to be configured in a certain way, main requirement is to configure database tables prefix property (
db.tableprefix), which will coordinate SAP Commerce schema initialization to generate DB tables with names prefixed with defined value. This will allow to run application safely during data migration and will not cause overwrite or modification of "active" type system structure. In case of a migration failure or unexpected problems during post-migration deployment, it will be possible to either resume/re-run migration or analyze root cause of issues, without a need for reinitialization of entire system.Minimal required properties to be adjusted for staged copy approach:
db.tableprefix=m0_
migration.ds.target.db.tableprefix=
It's recommended to use Service Properties of Background Processing aspect to manage this type of temporary configuration.
Initial deployment
Create a regular build from a configured code branch in SAP Commerce Cloud Portal. Execute deployment of finished build to target environment, with database initialization option selected
Schema preparation
Data migration process assumes that source DB schema is compatible with target DB schema, at least to the level that it can be either handled by DB Sync internal ETL logic or can/might be adapted later.
No upgrade required
If target DB schema can be already fully and correctly populated with standard SAP Commerce logic, after accessing HAC, Schema Differences and Schema Migration can be used as provided by DB Sync, to create all required target DB tables.
This case should be selected if, either source and target solutions are already on same SAP Commerce versions, or source SAP Commerce was upgraded to expected target version.
"Blind" migration
Migration with DB Sync in staged copy approach, can be considered as kind of a "blind" migration, as application is only used to execute ETL operations and is not aware/dependent on underlying type of a data being copied. Such state can be useful on early stage of migration project, especially in case of combined SAP Commerce upgrade with migration, it might be useful to be able to run migration without fully operational custom extensions. If for example some of custom extensions, especially ones containing custom item types definitions cannot be yet deployed in SAP Commerce Cloud, it will not be possible to prepare target DB schema in a standard way, with regular initialization process. To mitigate such limitation for data migration run, target DB schema can be created based on a current source SAP Commerce system. For example in on-prem instance of HAC, or on local environment, application can be configured to use MSSQL database and generate initialization DDL scripts, which then can be used as-is on SAP Commerce Cloud DB.
In order to generate init SQL scripts, after adjusting
db.* properties run following command:
ant initialize -DdryRun=true-DtypeSystemOnly=true
Generated script will be stored in hybris/temp/hybris/init_master_schema.sql file. After brief reviewing of generated DDLs, script can be imported to SAP Commerce Cloud DB with use of Groovy script. For more details please refer to DDL Executor section.
In case of successful schema population, Schema Differences and Schema Migration functionality of DB Sync can be also used to further ensure structure consistency.
Migration execution
Execution of migration process itself, does not require any further adjustments. Please refer to User Guide documentation.
Validation
When data copy is finished, DB Sync will provide overall status summary, additionally with more detailed JSON report which can be downloaded and reviewed, to gather migration statistics. Such data can be used for further fine-tuning of a migration process before running an actual production system cutover.
For more information about validation prese refer to User Guide documentation.
Post-migration adjustments
In case of a non-standard schema differences between source and target version of SAP Commerce, additional adjustments might be needed.
Examples of such changes might be orders table PK reference (
EXEC SP_RENAME 'orderentries.OrderPK', 'p_order', 'COLUMN') or update of execution node for cronjobs (UPDATE cronjobs SET p_nodegroup = 'integration', p_nodeid = NULL).Once gathered during trial run migration attempts all neccessarry schema and data adjustments, collected script can be applied on database with Groovy script via HAC Scripting Console. For more details please refer to DDL Executor section.
Post-migration deployment
Before execution of first deployment,
db.tableprefix property has to be restored to empty value:
db.tableprefix=
If previously Service Properties were used to prepare initial configuration, after adding this change, make sure to "Skip" immediate apply, to postpone services restart:
Previously prepared build, not having DB Sync extensions included, can be now deployed with selection of "Migrate data" for "Update Mode":
Cleanup
Last step of process is to execute database cleanup. All system tables used during migration can now be simply removed. As full database access is not allowed, this can be achieved again with Groovy script.
List of DB tables prefixed with
db.tableprefix value can be extracted from MSSQL sys objects with SQL query, for each returned table DROP statement must me executed. Please refer to Tables Remover section, for more details.Additional Resources
This section contains resources useful or simplifying described process
Please ensure that you are fully aware of purpose of each item here and familiarized with a potential risks that might cause misusage
Helper Scripts
DDL Executor
Following Groovy script simplifies execution of any kind of DDL scripts for database schema modification and preparation that might be required additionally to regular migration approach and recommendations
This script can be executed from HAC Scripting Language Console, its main logic utilizes Spring JDBC
DatabasePopulator API, so the same as the one used by default in SAP Commerce HybrisSchemaGenerator via Apache DdlUtils.Based on a type and size of particular script, DDL content can be provided in two ways:
- Internal string variable
SQL_DDL- recommended for ad-hoc SQL DDL content for non-extensive changes (add/remove/rename columns, simple DML to adjust type system data post-migration, etc.) - Hot-folder blob path set via
SQL_BLOB_PATH- for large size DDL scripts (like SAP Commerce init schema, complex schema manipulation, etc.), or alternatively for ease of storage and distribution through Azure Blob Storage default hot-folder container
run-ddl.groovy
/*
* true = fetch from blob hot-folder storage path `SQL_BLOB_PATH`
* false = run DDL from `SQL_DDL` variable
*/
DDL_FROM_BLOB = true
/*
* only show DDL SQL and finish (use with caution for large scripts)
*/
DRY_RUN = true
SQL_BLOB_PATH = '_migration/test-ddl.sql'
SQL_DDL =
"""
DROP TABLE IF EXISTS m0_test;
CREATE TABLE m0_test (PK int not null, value varchar(20), PRIMARY KEY (PK));
DROP TABLE m0_test;
"""
// ***
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator
import org.springframework.core.io.ByteArrayResource
import org.springframework.core.io.Resource
def sqlBytes
if (DDL_FROM_BLOB) {
def blobSession = azureBlobSessionFactory.session
if (!blobSession.exists(SQL_BLOB_PATH)) {
throw new IllegalArgumentException("DDL not found at: ${SQL_BLOB_PATH}")
}
def baos = new ByteArrayOutputStream()
try {
blobSession.read(SQL_BLOB_PATH, baos)
sqlBytes = baos.toByteArray()
}
catch (IOException e) {
throw new IllegalStateException("Failed to read DDL script: ${e}", e)
}
}
else {
sqlBytes = SQL_DDL.bytes
}
if (DRY_RUN) {
println new String(sqlBytes)
return
}
def populator = new ResourceDatabasePopulator(false, true, (String) null, [ new ByteArrayResource(sqlBytes) ] as Resource[])
try {
populator.execute(de.hybris.platform.core.Registry.currentTenant.dataSource)
println "DDL executed"
}
catch (Exception e) {
println "DDL execution error: ${e.message}"
}Tables Remover
Following Groovy script simplifies removal of DB tables created to support staged migration process. In order to generate all required DDL statements table filter condition has to be adapted with
TABLE_PREFIX property.With only this change, script can be executed. Obvious recommendation is firstly to run it in dry-run mode for proper validation. After review of generated DROP statements script can be re-executed with
DRY_RUN flag set to falsedrop-tables-mssql.groovy
TABLE_PREFIX = 'm0_%'
DRY_RUN = true
de.hybris.platform.core.Registry.currentTenant.dataSource.getConnection(true).with { c ->
c.prepareStatement("""
SELECT
CONCAT('DROP TABLE ', t.NAME, ';') AS dropSQL
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME LIKE '${TABLE_PREFIX}'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
AND s.name = 'dbo'
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY t.Name
""").with { s ->
def rs = s.executeQuery()
while (rs.next()) {
def tableDropQuery = rs.getString(1)
println tableDropQuery
if (!DRY_RUN) {
c.createStatement().with { d ->
d.executeUpdate(tableDropQuery)
}
}
}
}
}- SAP Managed Tags:
