Setting up a Salesforce Data Migration using DBAmp DBAmp / SQL Server
Background
With the rapid growth of Salesforce.com as a core CRM solution to many organizations, the need for migration to the Salesforce platform is rapidly increasing.
In addition, the need to consolidate Sales databases as organizations merge and re-organize is leading to greatly increased demand for data migration services.
The following notes describe an iterative process for using the DBAmp Toolset to set up a repeatable migrating process into Salesforce.com from a variety of data sources (such as Lotus Notes/ACT).
The level of detail should be sufficient to build the core migration database allowing iterative extension to be performed throughout the project and beyond.
Process Overview
The key elements of the suggested data migration process are as follows
· The SQL server SSIS to be used to import data export from Lotus Notes (possibly using direct integration / plug-ins).
· A SQL Server Database being used to translate this data into a form suitable for import into Salesforce.com
· DBAmp toolset being used to import and cross-reference this data into Salesforce.com using the bulk import API.
· Automation of the core data migration steps using T-SQL (including rollback of previously imported data if required). This process to be fully repeatable.
· Potential use of the SQL server agent to fully automate the import process (allowing parallel running of source and target systems if required).
Reasons for using the SQL Server/ DBAmp toolset
· ETL Tools - SQL Server has excellent capabilities for importing data from various data formats into local data tables. Various 3rd party plug-ins already exist for many legacy systems (including Lotus Notes / Sage / Dynamics).
· Programmability – The full capabilities of the SQL server toolset are available for building any data transformations or procedural extensions. This extends to full use of SQL DML and SOQL extensions to manipulate data (if the bulk API is not used).
· Source and target connectivity – DBAmp exposes data on the target system after import, enabling it to be used to filter data future data sources (including later versions of the same data).
· Automation – All migration processes can be automated using the SQL Server Agent. This can make real time integration between Source and target systems possible, easing the transition between systems.
· Database Size - Access Databases (which are the main interchange format for alternatives like DemandTools) have a maximum size of 2GB, which may prevent manipulation of all of the source data from Lotus Notes
· Support for Ad-hoc imports - Data can be exported in tabular format, which can then be manually imported using the Apex Data loader.
· Rollback Capability - DBAmp can also delete data from Salesforce if required, enabling corrective action and re-use of environments.
· Reporting Tools - can help prototyping of source data by enabling various data sources to be joined on the fly. This can enabling fast validation of core KPIs against imported data.
Suggested Migration Approach
The following steps should be used as part of an iterative enhancement and development cycle for building the migration toolset.
While these are milestones for the initial database development, they also form checkpoints for any subsequent modification.
Once a repeatable, automated process is established, user feedback can be very quickly incorporated with minor changes enabling datasets to be incorporated.
Step 1 - Migration Analysis
· Identify & prioritize key CRM scenarios / user journeys
· Produce examples (suggestion would be one client record for each stage of the customer journey).
· Data Triage - Identify & priorities main data / fields to be migrated for each scenario (initially mandatory fields and data for core KPIs)
· Identify high level field / entity mappings (i.e. which source data will feed into Accounts? Contacts? Opportunities – note that these may be defined in terms of forms on a legacy system rather than data entities)
· Confirm rules for record ownership (i.e. who will own imported records if previous owners no longer have access).
· Identify any required data transformation steps (i.e. Process stages, pick list values, user mappings, ownership rules)
· Identify validation rules / required import checks (i.e. record ownership on target system by team / role / individual)
Step 2 - Salesforce Configuration
· Ensure all required users are set up in the target system
· Create custom objects in the target SFDC Org
· Add fields to track Legacy IDs (Custom and Standard Objects)
· Add data fields / pick-list values for core data (i.e. task fields)
· Disable any validation rules / workflows / Apex Triggers affecting data import (may be trial and error)
Step 3 - Data Preparation
· Use the SSIS / SQL Server Toolset to import RAW data into SQL server (i.e. from external Databases, CSV files, spreadsheets e.t.c) - where possible create re-usable import jobs
· Create database views matching the required import format (i.e. CSV Files where columns match SDFC API Names) - one view per target entity.
- For guidance use the data format from Apex data loader
- SQL Reporting tools can be useful for prototyping
- Create database functions to perform any data transformations (i.e. swapping source user IDs with target Ids)
· Copy views into tables (to be imported using DBAmp Bulk API)
Step 4 - Core Data Imports
Use the DBAmp Bulk API to Import Each Entity in order of reference
- Accounts
- Contacts
- Products
- Opportunities
- Tasks (multiple assignees to be copied to a separate custom field)
(NB - for example, before Activity Data can be imported, all task dependent entities and users must exist in the target system)
Step 5 - Updates to cross reference parent relationships
Once the initial data is imported, Salesforce IDs will exist for all imported records, with references available within the staging tables used to import data via the bulk API.
These can be used to create complex relationships between records, which would have been lost during migration. Examples:
· Parents Accounts
· Contact Hierarchies
· Task Ownership
Generally the best way of identifying and updating these relationships is to ensure legacy ID fields are present and populated within Salesforce, such that these can be cross referenced against the Source data. Updates using the bulk API or SQL can then be performed en masse.
Step 7 - Testing and Verification
Validation of the imported data can be performed in a number of ways:
· Using the key UAT scenarios mapped from the source system(s),
· Using Salesforce reporting (i.e. numbers of source records meeting specific KPIs)
· SQL queries (checking relationships between source data and that on the target system). Ideally these should be part of the automated import process (allowing the need for rollbacks to be flagged early).
NB - It may be useful to build up a migration dashboard so that any regular imports can be validated.
Step 6 – Rollbacks (as required)
If required, data can be deleted directly from Salesforce entities using the DBAmp Data Sources and the staging tables used for bulk importing. It is advised that these are backed up as part of the import process.
Key technical risks and mitigation strategies
· IP Ranges – The database hosting DBAmp needs to be in the allowed IP range of target Salesforce Org (likely to be restricted in a banking environment)
· DBAmp Web proxies must be correctly set (usually the same proxy settings as desktops within the user environment)
· Validation Rules / Apex Triggers can affect imports - it is recommended that these are disabled prior to imports
· Replication of Migration Databases – this can prevent Linked Servers being refreshed / updated to reflect recent Salesforce customizations.
· Test Data Sets – it is advisable to test with a small subset of import data (i.e. 5-10 accounts + related contacts e.t.c) - this enables greater use of Developer Sandboxes (which have a 14MB limit)
· Full Sandbox Refreshes - Can only be updated every 29 days, so should only be used after rollback procedures have been introduced and validated.
· Version control of data – Any static data to be imported should be kept under version control in order to reduce the chance of erroneous updates and to ensure specific versions can be retrieved for any data maintenance.
Annex 1 - General Resources
Best Practice Presentation on Salesforce Org Merges (some useful migration advice)
http://salesforce.vo.llnwd.net/o1/us/community/ppt/ADM006_Sherman.ppt
DBAmp Toolset - Sales contact - Bill Emerson (one license covers a single Salesforce Org and all sandboxes).
(Free) Lotus Notes ODBC Drivers (enabling direct connection from SQL Server)
Annex 2 – High Level Migration Process Flow (Salesforce to Salesforce)
Source: Kevin Sherman (see Annex 1)
Annex 3 – Recommended update order for core objects
| Order | Object to Update | Related To |
| 1 |
| |
| 2 |
|
|
| 3 |
|
|
| 4 |
| |
| 5 |
|
|
| 6 |
|
|
| 7 |
| |
| 8 |
| |
| 9 |
|
|
| 10 |
|
|
| 11 |
|
|
| 12 |
|
|
| Depends |
|
|
| Last |
|
|
Source: Kevin Sherman (see Annex 1)
