Pointing existing R2 eCommerce to a different CRT DB

Sometimes you might need to point your existing R2 ecommerce deployment to a different CRT DB (which might be connected with a different instance of AX). To achieve that we would need to change CRT DB connection string in 2 components (only those 2 access CRT) - RetailPublishingJob and RetailPublishingPortal site collection. Below are the steps:

1. RetailPublishingJob

As described in the article Update the Publishing Job Configuration [AX 2012] the configuration of the job is stored in SharePoint's configuration DB, so, if you are comfortable with programmatic way to change the ConnectionString you can do so by using the technique described in the article to modify CommerceRuntimeConnectionString. 

Another way to update the job's configuration is to directly use SQL statements against SharePoint_Config database:

a) Query existing configuration by executing the statement below in SQL Management Studio:

SELECT Properties

FROM SharePoint_Config..Objects

WHERE NAME = 'RetailPublishingJobSettings'

As a result you will see a column Properties with the XML in it. Copy its value (right click and then select 'Copy') into clipboard and then save it into a text file (use Notepad.exe for instance) just as a backup in case you need to revert back to an original state.

b) Open new window in SQL Management Studio and paste (but don't execute yet) there the following template:

UPDATE SharePoint_Config..Objects

SET Properties = ''

 WHERE NAME = 'RetailPublishingJobSettings'

Place cursor between quotes (near SET Properties line) and paste there the content of the property you extracted in the step (a), do not apply any formatting, keep the text and formatting as is.

Now search (you can use CTRL+F key combination) for "CommerceRuntimeConnectionString" - you will find the CRT DB connection string, modify what you need to modify, for instance Server's name. One more time - do not apply any auto format because it might make the XML not usable. Once you modified the connection string, or, basically, you can modify anything else in the configuration you need, execute the UPDATE statement.

Once the steps above are done you don't need to restart anything - the changes will automatically be picked up next time the job is executing (it loads the configuration each time prior to executing).

2. RetailPublishingPortal - this Site Collection is technically ASP.NET web site with its own web.config file, the CRT DB configuration is stored there in standard section ConnectionStrings, the name of the string is CommerceRuntimeConnectionString, so, update it to reflect your changes. No any restart is needed. Please note that there are 2 web sites for the Publishing Portal - internal and external, update both of them, this is how you can access the web.config:

This completes all required steps to repoint your deployment to a different CRT DB.

Note that in R3 the SDK contains an GUI utility which greatly simplifies changing RetailPublishingJob configuration, details can be found in this post: Pointing existing R3 eCommerce to a different CRT DB