This topic describes how to migrate an EPiServer Commerce Database (version 8.0 and higher) to an Amazon RDS SQL instance. In this scenario we assume that you have a working EPiServer Commerce site. Refer to Deploying Commerce to Amazon websites for more information.
Migration procedure
The migration is done through the generation of scripts with data.
Create the database, certificate and symmetric key
- On the Amazone RDS, create a database and provide owner access for the user you want to use.
Note that the database name should be exactly the same as your database name.
- In order to use the data encryption feature in your database, create a master key with private password by executing the script:
CREATE MASTER KEY ENCRYPTION BY PASSWORD= N'{your_private_password}'
Create a certificate Mediachase_ECF50_MDP: (start date and expiry date could be changeable)
CREATE CERTIFICATE [Mediachase_ECF50_MDP] AUTHORIZATION [{your_user_name}] WITH SUBJECT = N'Mediachase Certificate', START_DATE = N'2013-12-06T14:27:05', EXPIRY_DATE = N'2014-12-06T14:27:05';
Create a symmetric key Mediachase_ECF50_MDP_Key:
CREATE SYMMETRIC KEY [Mediachase_ECF50_MDP_Key] AUTHORIZATION [{your_user_name}] WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE [Mediachase_ECF50_MDP];
Decryption Credit card and Metadata on local database
Executing these script on your local commerce database to decrypt Credit card and Metadata:
exec mdpsp_sys_OpenSymmetricKey UPDATE dbo.cls_CreditCard SET [CreditCardNumber] = CCD.CardNumber_string, [SecurityCode] = CCD.SecurityCode_string FROM (SELECT CONVERT(VARCHAR(max), DecryptByKey(cast(N'' AS XML).value('xs:base64Binary(sql:column("CC.CreditCardNumber"))', 'varbinary(max)'))) AS [CardNumber_string], CONVERT(VARCHAR(max), DecryptByKey(cast(N'' AS XML).value('xs:base64Binary(sql:column("CC.SecurityCode"))','varbinary(max)'))) AS [SecurityCode_string],
CreditCardId FROM cls_CreditCard CC WHERE CC.CreditCardNumber is not NULL) CCD WHERE CCD.CreditCardId = cls_CreditCard.CreditCardId exec mdpsp_sys_CloseSymmetricKey GO exec mdpsp_sys_OpenSymmetricKey DECLARE @MetaClassTable NVARCHAR(256), @MetaFieldName NVARCHAR(256), @MultiLanguageValue BIT, @MetaQuery_tmp nvarchar(max) DECLARE classall_cursor CURSOR FOR SELECT MF.Name, MF.MultiLanguageValue, MC.TableName FROM MetaField MF INNER JOIN MetaClassMetaFieldRelation MCFR ON MCFR.MetaFieldId = MF.MetaFieldId INNER JOIN MetaClass MC ON MC.MetaClassId = MCFR.MetaClassId WHERE MF.IsEncrypted = 1 OPEN classall_cursor FETCH NEXT FROM classall_cursor INTO @MetaFieldName, @MultiLanguageValue, @MetaClassTable WHILE(@@FETCH_STATUS = 0) BEGIN IF @MultiLanguageValue = 0 SET @MetaQuery_tmp = ' UPDATE '+@MetaClassTable+' SET ['+@MetaFieldName+'] = dbo.mdpfn_sys_EncryptDecryptString(['+@MetaFieldName+'], 0) WHERE [' + @MetaFieldName + '] IS NOT NULL' ELSE SET @MetaQuery_tmp = ' UPDATE '+@MetaClassTable+'_Localization SET ['+@MetaFieldName+'] = dbo.mdpfn_sys_EncryptDecryptString(['+@MetaFieldName+'], 0) WHERE [' + @MetaFieldName + '] IS NOT NULL' EXEC(@MetaQuery_tmp) FETCH NEXT FROM classall_cursor INTO @MetaFieldName, @MultiLanguageValue, @MetaClassTable END CLOSE classall_cursor DEALLOCATE classall_cursor exec mdpsp_sys_CloseSymmetricKey GO EXECUTE dbo.mdpsp_sys_CreateMetaClassProcedureAll GO
Generating Database schema scripts
Refer to How to: Generate a Script (SQL Server Management Studio) for more information.
Follow the steps below to generate the Database schema scripts.
- In Microsoft SQL Server Management Studio, right-click on Database.
- Select Task > Generate Scripts...
Follow the instructions displayed in Generate and Publish Scripts, note that you need to check Select specific database objects.
- Set the advanced scripting options as below, make sure that the "Types of data to script" is "Schema only".
When finalized, the script will be created on the path that you have defined.
Executing scripts on Amazon SQL instance
Before importing database schema, refer to Importing Data into SQL Server on Amazon RDS for the database preparation, to disable Foreign Key Constraints and Triggers before importing data. Thses will be enabled later.
The generated script file might be executed by using both Microsoft SQL Server Management Studio or "sqlcmd" command. Refer to sqlcmd Utility in MSDN for more information.
A simple command could be:
sqlcmd -S servername -U username -P password -i script_path [> log_path]
Note that you will have two databases that need to be migrated to the Amazon SQL instance.
Transferring data to Amazon Database
You can use the SQL Database Migration Wizard from Codeplex to tranfer data, ensure to set the Target Server as SQL Server, and Script Table/Data as Data Only in the Advanced Settings options.
Complete the wizard to transfer data to Amazon Database.
Encryption Credit card and Metadata on Amazon DB instance
Executing these script on commerce database to encrypt Credit card and Metadata:
exec mdpsp_sys_OpenSymmetricKey UPDATE cls_CreditCard SET CreditCardNumber = CONVERT(nvarchar(512), CAST(N'' AS xml).value('xs:base64Binary(sql:column("CC.CreditCardNumber_string"))', 'varchar(4000)') ) , SecurityCode = CONVERT(nvarchar(255), CAST(N'' AS xml).value('xs:base64Binary(sql:column("CC.SecurityCode_string"))', 'varchar(4000)') ) FROM ( SELECT EncryptByKey(Key_GUID('Mediachase_ECF50_MDP_Key'), (CONVERT(varchar(4000), CreditCardNumber))) CreditCardNumber_string , EncryptByKey(Key_GUID('Mediachase_ECF50_MDP_Key'), (CONVERT(varchar(4000), SecurityCode))) SecurityCode_string , CreditCardId FROM [cls_CreditCard]) CC WHERE cls_CreditCard.CreditCardId = CC.CreditCardId exec mdpsp_sys_CloseSymmetricKey GO exec mdpsp_sys_OpenSymmetricKey DECLARE @MetaClassTable NVARCHAR(256), @MetaFieldName NVARCHAR(256), @MultiLanguageValue BIT, @MetaQuery_tmp nvarchar(max) DECLARE classall_cursor CURSOR FOR SELECT MF.Name, MF.MultiLanguageValue, MC.TableName FROM MetaField MF INNER JOIN MetaClassMetaFieldRelation MCFR ON MCFR.MetaFieldId = MF.MetaFieldId INNER JOIN MetaClass MC ON MC.MetaClassId = MCFR.MetaClassId WHERE MF.IsEncrypted = 1 OPEN classall_cursor FETCH NEXT FROM classall_cursor INTO @MetaFieldName, @MultiLanguageValue, @MetaClassTable WHILE(@@FETCH_STATUS = 0) BEGIN IF @MultiLanguageValue = 0 SET @MetaQuery_tmp = ' UPDATE '+@MetaClassTable+' SET ['+@MetaFieldName+'] = dbo.mdpfn_sys_EncryptDecryptString(['+@MetaFieldName+'], 1) WHERE [' + @MetaFieldName + '] IS NOT NULL' ELSE SET @MetaQuery_tmp = ' UPDATE '+@MetaClassTable+'_Localization SET ['+@MetaFieldName+'] = dbo.mdpfn_sys_EncryptDecryptString(['+@MetaFieldName+'], 1) WHERE [' + @MetaFieldName + '] IS NOT NULL' EXEC(@MetaQuery_tmp) FETCH NEXT FROM classall_cursor INTO @MetaFieldName, @MultiLanguageValue, @MetaClassTable END CLOSE classall_cursor DEALLOCATE classall_cursor exec mdpsp_sys_CloseSymmetricKey GO EXECUTE dbo.mdpsp_sys_CreateMetaClassProcedureAll
See also
Deploying Commerce to Amazon websites
Last updated: Oct 20, 2016