Archive for February, 2009

Moving Commerce Server 2007 Databases

I needed to move the set of Commerce Server databases from one server to another, so I developed and tested this procedure.



  1. Script the database users to file. Our site is using domain users, so I performed this step first to keep the users in sync with a backup/restore of the databases.
  2. Backup each of the Commerce Server databases:

    • Marketing
    • Marketing_Lists
    • ProductCatalog
    • Profiles
    • TransactionConfig
    • Transactions
    • MSCS_Admin
    • MSCS_CatalogScratch

  3. Transfer the scripts and backup files to the new server.
  4. Run user scripts against the new server
  5. Restore each database on the new server
  6. On the web server, open Commerce Server Manager to change connection strings:

    1. Admin Database:

      1. Click the Commerce Server Manager node
      2. Click the Administration Database link in the right panel
      3. Change the Connection String, click OK

    2. Profiles


      1. Expand this node:
        Global Resources/Profiles

      2. Right-click and change the following 3 connection strings:
        s_BizDataStoreConnectionString
        s_CommerceProviderConnectionString
        s_ProfileServiceConnectionString

    3. Profile Database

      1. Expand this node:
        Global Resources/Profiles/ProfileCatalog/DataSources/ProfileService_SQLSource/Partitions
      2. Right-click SQLSource in the right pane and choose properties
      3. Change the Connection String, click OK

    4. Inventory Database

      1. Expand this node:
        Commerce Sites/CSharpSite/Site Resources/Inventory
      2. Right-click Inventory Database in the right pane and choose properties
      3. Change the Connection String, click OK

    5. Marketing Database

      1. Expand this node:
        Commerce Sites/CSharpSite/Site Resources/Marketing
      2. Right-click Mailing List Connection String in the right pane and choose properties
      3. Change the Connection String, click OK
      4. iv. Right-click Marketing Database Connection String in the right pane and choose properties
      5. Change the Connection String, click OK

    6. Product Catalog

      1. Expand this node:
        Commerce Sites/CSharpSite/Site Resources/Product Catalog
      2. Right-click Catalog Database in the right pane and choose properties
      3. Change the Connection String, click OK

    7. Transaction Config

      1. Expand this node:
        Commerce Sites/CSharpSite/Site Resources/Transaction Config
      2. Right-click Transactions Config Database in the right pane and choose properties
      3. Change the Connection String, click OK

    8. Transactions

      1. Expand this node:
        Commerce Sites/CSharpSite/Site Resources/Transaction Config
      2. Right-click Transactions Database in the right pane and choose properties
      3. Change the Connection String, click OK

  7. Change connection strings in the web.config for site (if any using API calls)
  8. Restart IIS

Posted on:
Posted in Commerce Server, SQL Server | Comments Off on Moving Commerce Server 2007 Databases

Commerce Server 2007 Customer and Orders Payment Method Error

Right off I just want to say it sucks when the error message is correct but you don’t understand why.

We needed to add a new payment method to a Commerce Server 2007 implementation. The Customer and Orders UI threw an error immediately when going to manage Payment methods:

The underlying table was full of data. The site has been in production for over a year, collecting credit card payments the whole time without issues. Obviously the payment methods weren’t missing.

Of course, Googling was no help whatsoever. I played with the Commerce API, wrote some code, ran SQL traces, all to no avail.

So on the a non-production version of the site (with the same problem luckily), I decided to work on the database table directly. I scripted off the original data, and truncated the table. Now the UI doesn’t throw the error. So I re-added the data manually, and again the UI is fine, no errors.

So I compared the newly entered data with the old data, and there was a difference (outside of the obvious differences like keys). There is a bit column in the table called IsDefault. In the old data, all rows except one were set to False. In the new data, all rows were set to True. To test that this was the problem, I truncated the table again, and re-inserted the original data. Then I changed the IsDefault values to True on all the rows. Bingo. The UI now works again, and I could add the new data. So apparently someone did modify the data in the past, and the error message was correct about the database being tampered with. Luckily this modification had not affected Commerce Server’s ability to process credit cards.

I think the IsDefault column refers to the language setting for the row in the table, as you can have multiple language variants for a credit card and choose which one is the default language. So for the UI to function properly, at least one row for each language needs to have IsDefault set to True.

Posted on:
Posted in Commerce Server, Strange Problems | Comments Off on Commerce Server 2007 Customer and Orders Payment Method Error