Integrating with Force.com platform using DbAmp: Part-01: Introduction

Introduction
The Force.com platform is becoming a ubiquitous platform of choice in the cloud for many organizations to move their Sales, Service and custom cloud applications. These applications or solutions cannot exist in vacuum and in almost all cases, they need to tightly integrate with the enterprise data that is housed local on-premise network or even with other cloud services such as Windows Azure or AWS. For e.g. if an organization implements a sales cloud, then they need their user information from their internal active directory to load in the Contact object, the customer lists from their SAP or other system for Account or Contact objects. Similarly custom Force.com applications might require data from the organization’s internal systems. Integration is a big concern for the organizations as they grow their presence in the cloud and it’s numerous tools and services are flooding the market to integrate with any systems.
This article series will cover one such tool called DbAmp from the ForceAmp.com. The goal here is to demonstrate the capabilities of this tool and show how the common tasks such as Insert, Delete, Upsert, Update operations can be done using this tool to accomplish the integration between a SQL Server deployed in the on-premise network with the Force.com platform. All of these topics are discussed elaborately in the following articles:
  • Integrating with Force.com Platform using DbAmp: Part-01: Introduction
  • Integrating with Force.com Platform using DbAmp: Part-02: Basics
  • Integrating with Force.com Platform using DbAmp: Part-03: Insert
  • Integrating with Force.com Platform using DbAmp: Part-04: Update
  • Integrating with Force.com Platform using DbAmp: Part-05: Upsert
  • Integrating with Force.com Platform using DbAmp: Part-06: Delete
  • Integrating with Force.com Platform using DbAmp: Part-07: Using SSIS
  • Integrating with Force.com Platform using DbAmp: Part-08: SF_REPLICATE
  • Integrating with Force.com Platform using DbAmp: Part-09: SF_REFRESH
  • Integrating with Force.com Platform using DbAmp: Part-10: SF_BULKOPS
  • Integrating with Force.com Platform using DbAmp: Part-11: Under the hood
  • Integrating with Force.com Platform using DbAmp: Part-12: Common integration techniques
DbAmp
DbAmp is a simple, yet very powerful tool, that exposes the Force.com as another database to your SQL Server. It allows the developers to use their familiar SQL (and SOQL as part of Open Query) to do all the CRUD operations on the Salesforce objects. The data on the Force.com platform can be backed up completely to an on-premise SQL Server using DbAmp with very little programming.
Benefits
Complete backup of Force.com data
DbAmp can be used to backup the data from the Force.com platform completely to an SQL Server database located on-premise. It is not limited to any specific Salesforce edition or application or objects; it can backup standard and custom objects and even custom settings for all types of Force.com cloud, such as Sales, Service, Marketing, Custom clouds, etc. However it is not a universal Force.com backup tool, meaning, it cannot backup other Force.com features such as profiles, permission sets, groups, roles, etc., because it is only a data backup tool. But it does support both full backup and incremental backup for the data. Full backups enable to completely backup all the objects with all the records, while incremental backups refreshes only the delta changes from Force.com to local on-premise database.
Build reports
DbAmp allows developers to build reports that can use data both from local repository and Salesforce in both real-time and offline fashion using simple SQL SELECT queries. For offline, DbAmp provides stored procedures (which will be covered shortly) to bring the data from Force.com platform to on-premise. Developers
Integrate local and cloud
DbAmp can be used to integrate local on-premise applications with Force.com data in both directions. That means that data can be inserted or upserted or deleted in Force.com from the on-premise SQL database.
How it works?
As outlined previously, DbAmp exposes Force.com as another database to your SQL Server. It does this by providing a OLE DB Provider to Force.com. As you might know, the SQL Server can connect to other data sources (that can support OLE DB, such as another SQL Server or Oracle, and expose them as another database using Linked Server. The following diagram (taken from the above link) illustrates this idea:
 
A3-P1-001-LinkedServer
This allows the developers to use the DbAmp OLE DB Provider to create a linked server that can connect to Force.com. Once this linked server is created, now developers can use regular SQL queries to manipulate the data on the Force.com platform. Developers can also use SOQL directly with the SQL using the Open Query, but keep in mind that there are some limitations in using the SOQL with the Open Query. Check the DbAmp documentation for more details.  One important thing to highlight here is that DbAmp takes care of all governer limits, so a developer need not to worry about governer limit exceptions when he/she tries to upsert with 5000 rows/records. DbAmp even allows developer to specify the limit per operation so that the developer can precisely control how the data should be batched.
Components
Now that we have understood how DbAmp works, let’s see the components of the DbAmp to better understand about how DbAmp works.
DbAmp OLE Db Provider
The DbAmp OLE Db Provider is used to connect to the Force.com by the linked server configuration. This is the engine of the DbAmp and takes care of all the background processes such as converting the SQL to appropriate Force.com web services API, sending the data from on-premise to Force.com and bringing the data back to on-premise from Force.com, etc.
DbAmp System Stored Procedures
The DbAmp system stored procedures does the bulk work to backup completely or incrementally. It also does the bulk inserts, deletes, updates and upserts. Here are those stored procedures.
  • SF_REPLICATE – The SF_Replicate stored procedure creates a new table which is an exact replica of a Force.com object and imports every single record from that object into the local, similarly named table. If this table already exists, it takes a temporary backup and drops and recreates this table.
  • SF_REFRESH – The SF_Refresh stored procedure synchronizes the local table with it’s corresponding Salesforce object. It deletes the records in the local table if these records got deleted in the Salesforce and gets any all the records that was added in Salesforce and doesn’t exist locally and the records whose fields got updated in the Salesforce.
  • SF_BULKOPS – The SF_Bulkops stored procedure is the key stored procedure that is used to send any new records or updated records in Force.com and even to delete any existing records.
There are few other stored procedures that ships with DbAmp, but these are the most widely used stored procedures. In a later article in this article series, we will explore these three stored procedures in depth to gain deep understanding about how it affects the way a developer designs/develops the integration job.
Anatomy of a DbAmp Job
Using DbAmp to integrate on-premise data with Force.com requires SQL skills, as the developers will write their integration entirely using SQL. The DbAmp integration can be either done using regular SQL jobs or SSIS packages. For the most part, this article series will be using only SQL jobs to implement the integration.
SQL Jobs
The anatomy of a SQL job that uses DbAmp slightly varies depending on the task involved and the time it runs. For uploads, we first refresh the local tables and populate a load table and then perform the upload operation (CUD). The load table is a temporary (physical, but temporary in nature) table that mimics the Salesforce object. The load table name should start with the name that is similar to the object that it deals; for e.g. to perform upload operation on ‘Case’ object, the load table name can be ‘Case_Load’ or ‘Case_BillingIssues_Load’, but it can’t be ‘ABC_Load’, etc. The load table need not have every column as in the Salesforce object and can have fields/columns that the job is going to affect. The only other column that is required is named ‘Error’ which will be used by the DbAmp to update the status information after the SF_BULKOPS call. The load table can be dropped or left as it is after the SF_BULKOPS call. In general, it’s a best practice to first check for its existence and create it if it doesn’t exist and to drop it just before the end of the logic in the SQL job.
At a high level the anatomy of a DbAmp SQL job look like as follows:
To upload data from local on-premise SQL database to Force.com platform for frequent jobs.
  • Refresh the local table(s) from the Salesforce objects using SF_REFRESH (or it’s other variants)
  • Create the load table
  • Populate the load table with the data that needs to be sent to Salesforce
  • Use SF_BULKOPS to upload the data. The upload operation can be ‘Insert’, ‘Update’, ‘Upsert’, ‘Delete’
  • Check ‘Error’ column in the load table to manage the error handling scenarios.
To upload data from local on-premise SQL database to Force.com platform for nightly jobs.
  • Replicate the local table(s) from the Salesforce objects using SF_REPLICATE (or it’s other variants)
  • Create the load table
  • Populate the load table with the data that needs to be sent to Salesforce
  • Use SF_BULKOPS to upload the data. The upload operation can be ‘Insert’, ‘Update’, ‘Upsert’, ‘Delete’.
  • Check ‘Error’ column in the load table to manage the error handling scenarios.
To upload data from Force.com platform to local on-premise SQL database for frequent jobs.
  • Refresh the local table from the Salesforce objects using SF_REFRESH (or it’s other variants)
  • Perform CUD (Create, Update, Delete) operation(s) on your other databases retrieving data from the refreshed table(s)
To upload data from local on-premise SQL database to Force.com platform for frequent jobs.
  • Replicate the local table(s) from the Salesforce objects using SF_REPLICATE (or it’s other variants)
  • Perform CUD operation(s) operation(s) on your other database retrieving data from the refreshed table(s).
Conclusion
This article introduced the DbAmp, a tool to integrate local on-premise databases with Force.com data and discussed about the benefits and the underlying mechanism of how it works. It also discussed the basic constructs of the DbAmp tool and the anatomy of a DbAmp SQL job. In the next part, we will explore how the data can be inserted from local SQL database to Force.com using a DbAmp SQL job.
Stay tuned.
Advertisements

Tagged: , , , , , , ,

6 thoughts on “Integrating with Force.com platform using DbAmp: Part-01: Introduction

  1. Yamini Makhija March 18, 2015 at 11:31 am Reply

    Hi Hari,

    We are working on POC for the DBAmp for replicating the SFDC data from source SFDC environment to target SFDC environment. Can you please guide and let me the know the steps to acheive it.

    • Hari Krishnan March 19, 2015 at 5:55 pm Reply

      Hello Yamini,
      I’m not sure if I can answer your questions in the comment, but here are the high level steps:
      1. Download and install the DbAmp setup package in your SQL server
      2. Configure a linked server pointing to your Salesforce instance.
      3. Use the appropriate DbAmp stored procedures to replicate/refresh Salesforce objects. Use SF_REPLICATE to replicate the Salesforce object (e.g.when there is a schema change) and use SF_REFRESH to refresh the Salesforce object (e.g. to bring the delta changes from Salesforce to local).

  2. Kat_Z November 9, 2015 at 6:23 pm Reply

    Do you also recommend installing DBAmp on a SQL Server in the DMZ, much like your castiron model or is it secure enough to put it on a production SQL server in the LAN.

    • Hari Krishnan November 10, 2015 at 5:40 am Reply

      Hello Katz,
      Your DbAmp server doesn’t need to be in DMZ as the DbAmp is who is going to initiate the calls with Salesforce and as far as your network policy allows internet access, it should work. You need to put something in DMZ only when you call your service from Salesforce; in your case, DbAmp is not exposing anything to be called.

      Best Regards,
      Hari Krishnan.

  3. Prashanth Mathew December 1, 2016 at 7:27 am Reply

    Hi Hari,

    This is an interesting article, do you have the complete topics published anywhere else ?
    Not able to find links to topics after part 3.

    Regards,
    Prashanth Mathew

    • Hari Krishnan December 14, 2016 at 12:47 am Reply

      Hello Prashanth,
      I originally planned to complete the series when I started writing this, however I couldn’t complete as I moved on and didn’t have access to DbAmp any more. Nevertheless, it’s a very powerful tool and very much suited for certain scenarios (e.g. SFDC data backups, moving data from prod to developer sandboxes, scheduled jobs, etc.).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: