Post Migration Data Management

  • Article

This section describes the steps that you should perform to remove nonessential data after you complete the migration of your Commerce Server site. Use SQL Enterprise Manager (for SQL Server 2000) or SQL Server Management Studio (for SQL Server 2005) to remove any database tables, stored procedures or triggers that you no longer need. For more information about Transact-SQL, see the SQL Server 2005 Books Online Web site at https://go.microsoft.com/fwlink/?LinkId=69569

Before removing any data from Commerce Server SQL databases that have been migrated to Commerce Server 2007, we strongly recommend that you perform a complete database backup of your existing data. The lists provided in this topic contain the database objects that are no longer used by Commerce Server 2007, individual data usage or requirements may vary by specific implementation.

Removing Old SQL Tables, Stored Procedures, and Triggers

Commerce Server Orders Data

Run the following SQL commands to remove the old tables and stored procedures from the Transaction resource. By default, this data would be contained within the “<SiteName>_Transactions” database or the “<SiteName>_Commerce” database if all resources were originally unpacked to a single database.

Drop Table BasketGroup

Drop Table OrderGroupAddresses

Drop Table OrderFormLineItems

Drop Table OrderFormHeader

Drop Table OrderGroup

Drop Table CatalogProducts_AuctionBid

Drop Table CatalogProducts_AuctionItem

Drop Table Counters

Drop Table UserFilter

Drop Procedure OrderSummaryProc

Run the following SQL commands to remove the old tables and stored procedures from the Transaction Configuration resource. By default, this data would be contained within the “<SiteName>_Transactionconfig” database or the “<SiteName>_Commerce” database if all resources were originally unpacked to a single database.

Drop Table ShippingConfig

Drop Table TableShippingRates

Drop Table Decode

Drop Table Region

Drop Table RegionalTax

Drop Table CatalogCache_Virtual_Directory

Drop Procedure sp_GetShippingRateProc

Commerce Server Campaigns Data

The Commerce Server 2007 SDK folder contains a script “drop_legacy_campaigns_schema.sql” that can be used to remove the old tables, stored procedures, and triggers from the campaigns database. This script can be found in the folder %COMMERCE_SERVER_ROOT%\SDK\Samples\SiteCreate.

By default, this data would be contained within the “<SiteName>_Campaigns” database or the “<SiteName>_Commerce” database if all resources were originally unpacked to a single database. Optionally, you can use the following SQL commands to remove the old tables, stored procedures, and triggers from the campaigns resource:

Drop Table ad_item

Drop Table campaign

Drop Table campaign_item

Drop Table campaign_item_types

Drop Table campaigns_version

Drop Table creative

Drop Table creative_property

Drop Table creative_property_value

Drop Table creative_size

Drop Table creative_type

Drop Table creative_type_xref

Drop Table customer

Drop Table dm_item

Drop Table event_type

Drop Table industry_code

Drop Table order_discount

Drop Table order_discount_expression

Drop Table order_discount_misc

Drop Table page_group

Drop Table page_group_xref

Drop Table performance_total

Drop Table target

Drop Table target_group

Drop Table target_group_xref

Drop Procedure sp_camp_item_archive

Drop Procedure sp_clean_archived_campaigns

Drop Procedure sp_clean_delete_campaign

Drop Procedure sp_clean_delete_campitem

Drop Procedure sp_clean_delete_customer

Drop Procedure sp_cs_campitem_add

Drop Procedure sp_cs_campitem_delete

Drop Procedure sp_cs_campitem_update

Drop Procedure sp_cs_mail_add

Drop Procedure sp_cs_mail_delete

Drop Procedure sp_cs_mail_schedid

Drop Procedure sp_cs_mail_update

Drop Procedure sp_DeleteCampaign

Drop Procedure sp_DeleteCampaignItem

Drop Procedure sp_DeleteCustomer

Drop Procedure sp_dm_item_delete

Drop Procedure sp_dm_item_edit

Drop Procedure sp_dm_item_new

Drop Procedure sp_GetCampItemGUID

Drop Procedure sp_InsertAdItem

Drop Procedure sp_InsertCampaign

Drop Procedure sp_InsertCampaignItem

Drop Procedure sp_InsertCPValue

Drop Procedure sp_InsertCreative

Drop Procedure sp_InsertCustomer

Drop Procedure sp_InsertDiscount

Drop Procedure sp_InsertDiscount2

Drop Procedure sp_InsertDiscountMisc

Drop Procedure sp_InsertPageGroup

Drop Procedure sp_InsertPageGroupMain

Drop Procedure sp_InsertTargetGroup

Drop Procedure sp_InsertTargetGroupMain

Drop Procedure sp_InsertTargetMain

Drop Procedure sp_UndeleteCampaign

Drop Procedure sp_UndeleteCampaignItem

Drop Procedure sp_UndeleteCustomer

Drop Procedure sp_update_performance_total

Drop Procedure sp_update_vdir

Drop Procedure sp_UpdateAdItem

Drop Procedure sp_UpdateCampaign

Drop Procedure sp_UpdateCampaignItem

Drop Procedure sp_UpdateCreative

Drop Procedure sp_UpdateCustomer

Drop Procedure sp_UpdateDiscount

Drop Procedure sp_UpdateDiscount2

Drop Procedure sp_UpdateDiscountMisc

Drop Procedure sp_UpdateTargetGroupMain

Drop Procedure sp_UpdateTargetMain

DROP Trigger tr_aditem_eff_goals

DROP Trigger tr_campitem_goal_changed

DROP Trigger tr_aditem_goal_changed

DROP Trigger tr_camp_goal_changed

Commerce Server Business Desk Security

Run the following SQL commands to remove the old tables and stored procedures from the Business Desk resource. By default, this data would be contained within the “<SiteName>_BDSecurity” database or the “<SiteName>_Commerce” database if all resources were originally unpacked to a single database.

Drop Table BDSecurityDefinition

Drop Table BDSecurityIdentifiers

Drop Procedure sp_AddAccount

Drop Procedure sp_AddSIDsToTempTable

Drop Procedure sp_GetAccounts

Drop Procedure sp_GetAccountsForRight

Drop Procedure sp_GetPermissions

Drop Procedure sp_GetUID

Drop Procedure sp_RemoveAccount

Drop Procedure sp_RemoveSecurityDefinitions

Drop Procedure sp_SetPermissions

Commerce Server Predictor

Run the following SQL commands to remove the old tables and stored procedures from the Predictor resource. By default, this data would be contained within the “<SiteName>_dw” database, if the predictor was installed and configured.

Note

You must substitute the name of your site for Sitename in the following SQL commands.

Drop Table PredictorAttributes_Sitename_AdClick

Drop Table PredictorAttributes_Sitename_Combined

Drop Table PredictorAttributes_Sitename_Demographics

Drop Table PredictorDataTables

Drop Table PredictorEventEnum

Drop Table PredictorModelCfgs

Drop Table PredictorModels

Individual Databases no longer used by Commerce Server 2007

The following SQL database may have been created by using an older version of Commerce Server. This database is not used by Commerce Server 2007. The database name may vary based on decisions made when the original site and resource was unpacked.

<SiteName>_BDSecurity