Migrating Commerce databases to Amazon

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