How to Configure the Catalog and Inventory Databases on Different SQL Servers
You can install the Commerce Server 2009 catalog and inventory resources on different computers that are running SQL Server. Commerce Server 2009 uses SQL Server linked servers to join the resulting data. Although this configuration is possible, there will be some significant performance costs associated with doing this.
Note
For performance reasons, the catalog and inventory databases should reside on the same computer that is running SQL Server. The Catalog System frequently accesses inventory data from the inventory database, depending on your configuration. As a best practice, these databases should reside on the same computer that is running SQL Server.
To deploy the catalog and inventory databases on different computers that are running SQL Server, you must give Microsoft Distributed Transaction Coordinator (MSDTC) network access. You must do this on both computers.
Follow these steps to configure the SQL Server computers as linked servers.
For more information about how to link computers that are running SQL Server, see https://go.microsoft.com/fwlink/?LinkId=74207.
To add the catalog server as a linked server to the inventory server
On the inventory server, open Microsoft SQL Server 2005 or SQL Server 2008.
Select the inventory database, and then click New Query on the toolbar.
In the query pane, type the following query:
EXEC sp_addlinkedserver '<CatalogServerName>'Where <CatalogServerName> is the name of the computer that is the catalog server.
Click Execute on the toolbar.
To add the inventory server as a linked server to the catalog server
On the catalog server, open Microsoft SQL Server 2005 or SQL Server 2008.
Select the catalog database, and then click New Query on the toolbar.
In the query pane, type the following query:
EXEC sp_addlinkedserver '<InventoryServerName>'Where <InventoryServerName> is the name of the computer that is the inventory server.
Click Execute on the toolbar.
To verify network access for MSDTC
Click Start, click Control Panel, double-click Administrative Tools, and then double-click Component Services.
In the Component Services console, expand Component Services, expand Computers, right-click My Computer, and then click Properties.
In the My Computer Properties dialog box, on the MSDTC tab, in the Transaction Configuration section, click Security Configuration.
In the Security Configuration dialog box, in the Security Settings section, verify that Network DTC Access is selected.
Click OK two times.
Close the Component Services console.