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 follows:
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, we may also separate each sites’ profile resource. Thus each site will have 6 databases if you do not create a datawarehouse, and use a separate profile for each one.
Every MSCS installation creates two DBs, namely “
MSCS_Admin” and “
MSCS_Admin DB stores settings of the MSCS.
MSCS_CatalogScratch DB stores temporary tables of the system.
Microsoft Commerce Server 2002/2007/2009 development experience is required in order to understand this article.
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 Microsoft provides. If you use web services, we call this approach
ServiceAgent approach, otherwise if you use commerce DLLs in order to CRUD underlying 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
ServiceAgent use 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 information from the underlying data stores.
Using More Than 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
productCatalog2 by changing the connectionstrings from Commerce Server Manager windows application, after changing the connectionstrings 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.
- 11th September, 2009: Initial post