Accessing Microsoft Commerce Server 2007/2009 ConnectionStrings

Introduction

If you are using Microsoft Commerce Server 2007/2009 (MSCS) and if you have one commerce starter site then you will have 7 databases in your Sql Server. The databases are listed as following:

1. SiteName_ProductCatalog
2. SiteName_Profiles
3. SiteName_Marketing
4. SiteName_MarketingLists
5. SiteName_Transactions
6. SiteName_TransactionConfig
7. SiteName_DataWareHouse

If you have multiple sites datawarehouse resource may be shared among these sites. If we leave datawarehouse alone, each site may have 6 databases. Although you may share your profile db among your sites, but we may also seperate each sites’ profile resource. Thus each site will have 6 databases if you do not create datawarehouse, and use seperate profile for each one.

Every MSCS installation create two dbs namely “MSCS_Admin” and “MSCS_CatalogScratch”. MSCS_Admin db stores settings of the MSCS. MSCS_CatalogScratch db stores temprorary tables of the system.

Data Retrival

Having read all the information above now we know that MSCS has an internal storage for connection strings for the active resources that the system uses. The commerce infrastructure’s CRUD operations is done through web services or the dlls that MS provides. If you use web services we call this approach ServiceAgent approach, otherwise if you use commerce dlls in order to CRUD undelying data model we call it SiteAgent approach.

Another approach to query the underlying data model is directly connecting to the database and executing data retrieval queries for fast responses. Since SiteAgent and ServiceAgent uses commerce server dlls or services before querying the data model, these approaches are not as fast as directly connecting and executing queries to database. That’s why sometimes you may want to query the db directly in order to achieve best performance in your applications. Thus direct query is another option to get info from the underlying data stores.

Using more then one product catalog repository for testing/staging purposes

You may have two productCatalog databases in order to use one for testing or staging purposes, active site may use productCatalog2 database. If the staging db is ready to go for live you may switch the active product catalog resource from productCatalog to productCatalog2 by changing the connectionstrings from Commerce Server Manager windows application, after changing the connection strings you should recycle the application pool in order to refresh the caches for commerce system.

Consequences of changing the connectionStrings frequently

As a result of always changing the connectionstrings of productCatalog resource your code may not be aware of the active product catalog. Thus you cannot hard code your conStr in your web.config file and always use the same conStr. Instead of using the same conStr you have to go and search for the active conStr in your MSCS_Admin db and find the active productCatalog conStr at that moment.

By finding the active productCatalog conStr from the MSCS_Admin db you may execute data retrieval queries fast. As an additional suggestion you may use “(NOLOCK)” statement in your SELECT queries for the non-blocking queries, this will make your queries run faster.