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: Sunday, February 08, 2009 8:28:53 PM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
Commerce Server | SQL Server

Use RunAs to Connect to SQL Servers in Another Domain

Doh! Another simple thing I wish I knew (or thought of) previously: Using RunAs to connect to SQL Servers in other domains

Posted: Friday, September 07, 2007 4:31:07 PM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
SQL Server

Sql Server Fix Logins

Sometimes when copying databases, for instance from production down to development, the user accounts become dysfunctional. The database login no longer matches the overall server login. To re-synchronize them use:

exec sp_change_users_login auto_fix, 'someusername'

Posted: Tuesday, June 05, 2007 10:12:17 PM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
SQL Server

More SelfSSL Issues

I blogged previously about some issues with SelfSSL and multiple web sites. A colleague of mine, Charles Medcoff, blogs about a related problem with SelfSSL and SQL Server.
Posted: Wednesday, March 28, 2007 4:39:17 AM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
Security | SQL Server

How ADO.Net 2.0 Batch Updates Really Work

An excellent post by Pablo Castro of  the ADO.NET team explaining the mechanics of how batch updates work with ADO.NET 2.0 and Sql Server 2005.
Posted: Friday, July 07, 2006 3:50:09 PM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
SQL Server

Batch execute SQL files in a directory

Having to excecute hundreds of .sql files in an environment without port 1433 open led me to this nugget of a Usenet post: How to execute multi-file.

Here is the gist of it, the command line for executing all those .sql files with osql:

FOR %i IN (*.sql) DO OSQL -n -U sa -P sapassword -d databasename -i %i

I am sure I could have merged them all into one file or made some sort of batch vbs, but this did the trick nicely.

Posted: Monday, July 25, 2005 5:21:07 AM (Eastern Standard Time, UTC-05:00)  #    Comments - Trackback
SQL Server