This is quite a common question when developing a solution, how do I get my catalogs/catalogues from my staging environment to the live environment? (Note how I have used both the American spelling and English spelling of catalog to not cause confusion when searching help files)
With Commerce Server 2002 as I see it you have 1 of 5 options available to you.
Option 1 - Compelete Database restore
A complete database backup and restore of your product database. While this is the least complicated it's not the best when you are working with a live system or the users that take on the solution are not technically minded. Taking down your product database will cause errors on your site while it is not available.
Option 2 - MS SQL Server Replication
While this is recommended in the Microsoft documentation on Commerce Server, we discovered a bug in SQL replication when the amount of columns/fields in the product catalog/catalogue table went over a certain number. Because we had so many attributes for our products we found we were unable to use SQL server replication, note this is only a bug in SQL Server 2000. The long and the short of it is, if it works for you and you don't have as many attributes as we did this could be a better choice for you and could be kicked off from a stored procedure or a SQL server Agent job.
Option 3 - XML Import of Catalogs/Catalogues using Catalog Manager
A nice option is using the XML import functionality of the Catalog Manager, this enables you to create your own XML (schema available in Commerce Server Feature Pack 1 help files) from legacy systems which can then easily be imported. Or to export your catalogs as XML from your Staging environment and then re-import using the Catalog Manager to your live environment.
A few things to note about this method
- XML export of Catalog Manager does not preserve your Virtual Catalogues
- If you are exporting and importing a lot of data XML is a very bulky format for this data and could take a considerably long time.
- After importing data you will need to rebuild your full text indexs
- Your site may be unavailable during a lengthy import procedure.
- Consider using it for initial product catalog creation on your staging environment or if you only use base catalogs and do not have a large product database.
Option 4 - Catalog API
The Catalog API is not a bad choice when initialy importing data into Commerce Server 2002 or 2000, however as the XML import it can get slow when you have lots of product data to import. Another thing to note is the error handling of the Catalog API can be a bit strange to say the least as the majority of it is still sitting on COM wrapped in .NET. As an export method it probably would not be the best method but should probably only be considered when importing product data into your staging environment before it is sent to the live environment.
A few notes:
- Consider only using this method for initial imports into your Staging Environment of product data.
- Be prepared for errors in your import and log these so you can check which data did not get imported.
Option 5 - Custom DTS Package
I found this was by far the best option, to use a DTS package that was kicked off by a SQL Server Agent job. To identify the tables you need to export look inside the Commerce Server 2002 Feature Pack 1 help file for the phrase "Components of the Catalog Database" this will provide a list of the table you will need to look for and use in your import.
A few notes:
- Develop a method that will export all your base catalogs and virtual catalogs without the need for a code change so for example base catalog tables will end in the following [CatalogName]_CatalogProducts, [CatalogName]_CatalogReationships, [CatalogName]_CatalogHierachy, [CatalogName]_CatalogDeletedProducts etc.(as taken from help file).
- If a non technical user needs to kick off the package consider using a web app that calls a stored procedure that will calla SQL Server Agent job which in turn will run your DTS package. This way your web app will not timeout when the DTS package is run.
- This method is one of the fastest in my opinion.
- Test this method over and over again to make sure it works.
Choosing the correct option should be based on factors such as
- How many products and catalogs you have
- How regularly you update your products on the live environment.
- How your environment is structured (do you use a staging environment?)
- Is speed important to you?
- Is it acceptable for your site to be down for a period of time while using a slower method of product import?