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.

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:

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:

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:

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

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:

1. 1. 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.)

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

/*
 * 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 false

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)
        }
      }
    }
  }
}