While many components of Epicor CPQ can be moved from one environment to another through deployment, any custom database tables you create must be migrated manually. Follow these steps to use our current best practice.
Before you begin...
- Be familiar with the difference between local tables and database tables.
- Connect successfully to both your source and destination databases with SSMS.
As part of setting up those connections, you'll ensure your current IP address is added to the firewall allowlist in both your source and destination environments. Learn more about setting up and troubleshooting a database connection. Ensure your workstation has up-to-date database drivers.
The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server Management Studio 19 (SSMS). Both the SQL Server Native Client OLE DB provider (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server going forward.Anticipate a warning message.
During some parts of this process, you'll see a yellow warning message informing you that since you are not the owner of the database, "this might cause some operations with this database to fail." You are not performing any security-elevated operations, and can safely ignore this message when it appears.
Overview
In this example, we'll be migrating the structure and data of a table from one organization's DEV environment to their TEST environment using SSMS. You can follow these same steps to migrate data in other ways, such as within an organization (from TEST to PROD), or between organizations (from one organization's DEV database to another's).
Use SSMS to move data
Open SSMS, and log in to the source environment database using the credentials you've confirmed in the "Before you Begin" section. You'll see the object explorer appear, with your source database appearing under the "Databases" node. Here, we are logging into our DEV environment.
- Right click the database kbmax_prod_[your_organization]_dev. A pop-up menu appears.
- Select Tasks > import/export data
- The import/export wizard appears.
Choose a data source as SQL Server native client
-
Enter your source database authentication information.
Type or paste the server name, username and password for the source environment.Server name:
kbmax-us.database.windows.net or
kbmax-eu.database.windows.net, depending on your geography.User name: xuser
Password: as provided to you when you requested access to databases. This is not your usual user login password. If you are unsure of the database password for any of your environments, submit a support ticket with us.
-
Enter your source database name.
The naming convention is one of the following (where companyname is your organization's name):- kbmax_prod_companyname_dev
- kbmax_prod_companyname_test
- kbmax_prod_companyname_prod
(Here, we are moving data from our organization's DEV instance into TEST, so we'll use the source database name kbmax_prod_companyname_dev.)
-
You've mapped the source connection. Click Next and you'll choose a destination.
- Repeat the previous connection step using the server name, user name, and password for your destination connection. In most cases, the servername is the same, the username is also xuser, but the password for the destination is different than that for the source.
- Enter the destination database name. (Here, we are moving data from the foo company's DEV instance into TEST, so we'll use the target database name kbmax_prod_companyname_test.)
Your source and destination have been defined. Now you need to specify what you want to move. Begin by selecting "Copy data from one or more tables."
A listing appears, showing the tables and views from your source and destination.
For each table in the list you want to move,
- Select the source table to copy by marking it in the list with a checkmark.
- Define how that table's data will move by clicking the Edit Mappings button. In the pop-up window that appears,
- If the table does not exist in the destination, select "Create Destination Table".
- If the table already exists, and you are simply updating the data within it, select "Delete rows in destination table". This will purge any existing data from that table, and completely replace it with a copy from the source.
- If the table already exists, but you are updating both the data and the structure of the table (such as adding or removing columns), we suggest you delete the destination table first for simplicity's sake. Other techniques for more experienced SQL administrators are also available: learn more in the documentation for SSMS and Microsoft SQL databases.
- Click "OK" to close the pop-up window and return to the list.
Repeat the previous step to select and configure all the tables you want to move.
After selecting and configuring all the tables you want to move, select Run Immediately > Next > Finish.
The migration process starts. Watch for any errors or warning messages. When complete, query the updated table to ensure that it matches the source data as expected.
You're done in SSMS. Use your web browser to log on to your Epicor CPQ destination environment, and confirm the application there performs as anticipated with the new data.