How to configure Commerce Server Staging to log to SQL database

  • Article

By default Commerce Server 2007 Staging (CSS) logs event and project data to an Access database.  This works fine but for those administrators that won't to be able to better manage and make use of the reporting history usage of a SQL Server database is the best choice.  In this post I will outline the steps needed to re-configure CSS to log to a SQL Server database.

CSS uses the ODBC System DSN 'CSSLocalEvents' to define where to perform logging which by default is an Access database named events.mdb. The DSN can be changed to use either a local or remote SQL Server database.

Use the steps below to change the database that the 'CSSLocalEvents' DSN will use for CSS logging.  For these steps we will be using a SQL Server 2005 database and administration tools.

> Using the SQL Server Management Studio, create a new empty database named "Events" on the SQL Server you will use to host the database.

> Set the account that the Commerce Server Staging service is running as (<domain>\CSStageSvc) to be db_owner on the Events SQL database just created.

> Open the Commerce Server Staging MMC and stop the CSS service

> From Start\Administrative Tools select Data Sources (ODBC)

> In the ODBC Data Source Administrator select the System DSN tab

> Select the CSSLocalEvents data source and click the Configure button

> Rename the data source to CSSLocalEventsOriginal and then select OK to save the change (this step allows a user to easily go back to logging to the Access database if needed)

> Click the Add button to create a new ODBC System DSN. This will start the wizard and on the first page scroll through the list of drivers and select SQL Server.

> Complete the wizard by entering the SQL server name and selecting the Events database already created.

> Restart the Commerce Server Staging service from the Commerce Server Staging MMC

To verify that the service was able to initialize the new Events SQL database use SQL Server Management Studio to view the tables in the database. You should see that two tables named 'Events' and 'TranslatedEvents' were created and populated. If the tables do not exist check the application event log for any errors.

Note: When viewing the project reports in the Commerce Server Staging MMC if you receive an error that the DSN could not be found or something similar you should look at your IIS permission settings to resolve the issue.