By: Jitschak Rosenbloom, Principal Customer Success Architect, Salesforce.org and Tom Leddy, Senior Principal Customer Success Architect, Salesforce.org
As much as we’d love to store all of our data in Salesforce, doing so is not always practical. Storage limits are one reason for this, but keeping data that’s no longer needed in your Salesforce org can also cause other issues, including:
- Performance degradation
- Reports that don’t truly reflect the current state of your business because they’re peppered with old, irrelevant data
- User frustration when people have to repeatedly dig through old records to find what they’re looking for
If you read our hot-warm-cold blog post, you’ll be familiar with the process to put together a data archiving strategy.
Archiving isn’t the only reason to store data externally either. Certain types of data simply don’t make sense to load into Salesforce. Log files or Syndicated Data whose record counts can be in the millions and have no practical use within a CRM system are examples of this.
In this post, we’ll go over a way to set up a robust architecture that can be used for archiving and purging, or even simply storing data externally, while also making it easy to access data stored off-platform from within Salesforce.
Below is a list of the components that are used in this example:
|Component Name||What it is||Why it’s needed|
|Heroku||Heroku is a cloud platform that allows you to build external applications and databases and seamlessly sync data between them and your Salesforce org, allowing you to encapsulate certain functionality.||This is the external platform that will host our databases and handle our synchronization and purge processes.|
|Postgres – Sync Tables||PostgreSQL is a relational database management system that Heroku uses to store synced Salesforce data.||This set of tables will contain an exact copy of all the records in any Salesforce objects we select and can be used for additional off-platform processing.|
|Heroku Connect||Allows you to set up uni- or bi-directional sync between Heroku Applications and Salesforce Org(s).||Heroku Connect will create a 1-1 relationship between database tables in Heroku and objects in Salesforce. It will then sync the data at specified intervals. An exact copy of the data will exist in both databases after each sync.
Note that Heroku Connect is not subject to Salesforce API Limits but there are some limitations on the types of objects that are supported.
|Postgres – Archive Tables||A set of tables to store data that’s been purged from Salesforce.||Here’s where things get tricky. Setting up a database in Heroku will work for external processing (which we’ll cover in an upcoming blog post), but it isn’t sufficient for archiving and purging. Heroku Connect will always keep the Heroku database in sync with the Salesforce database, so purging old records from Salesforce when you’re ready to archive them will also purge them from your Heroku database (and vice-versa). Adding a second set of tables will allow you to store archived data in another location that won’t be affected by data purges.|
|Stored Procedures||Database commands to replicate and purge data based on archiving criteria (dates, etc..)||Transfer records from the Sync tables to the Archive tables prior to purging them from Salesforce. There’s a free scheduler add-on available within Heroku that will allow you to execute these on time based intervals.
Optional Bonus feature: create an additional set of stored procedures that will move individual records from the archive tables back to the sync tables on demand, which will then push them back into Salesforce and “unarchive” them (note that doing this will create a new ID for the record in Salesforce).
|Salesforce Connect / External Objects (Optional)||Heroku provides an OData wrapper for any Postgres Database you build on the platform. Salesforce Connect allows you to display this data within Salesforce, as though it lives there natively.||If you want to access archived records without having to fully restore them, you can use Salesforce Connect to retrieve them from the archive database in Heroku and work with them as external objects.
Note that this is subject to OData Callout Rate Limits
Other Data Storage Options
It should be noted that Heroku isn’t the only option for storing and accessing Salesforce data externally. There are a number of third party applications and databases that can be used as well. If your organization is already using another platform for data archiving, Salesforce offers a variety of options for connecting, so you should still be able to make it work. Connecting to external platforms will typically be subject to Salesforce API limits, though, so make sure to take this into consideration.
A Customer Success Architect can help with decisions related to external data storage options as well as helping to put together an overall data management strategy for your organization.
**This blog is part 2 of a 3 part series on Data Storage, and part of our larger “Ask an Architect” content series. See part 1. To learn more about engaging a Salesforce.org Customer Success Architect in your organization, please contact your Account Executive.
About the Authors
Jitschak Rosenbloom is a Principal Customer Success Architect at Salesforce.org, based in Coral Springs, Florida. He assists NGOs and Universities in evaluating their current strengths and aspirations, and then building roadmaps towards realization. He is an active Salesforce and Jewish community member and a father of five. You can find Jitschak on LinkedIn and Twitter.
Tom Leddy is a Senior Principal Customer Success Architect at Salesforce.org based in the Chicago area. He helps nonprofit and higher education organizations integrate Salesforce into their IT landscapes so they can serve their communities more effectively. He is also an author, public speaker, marathon runner and the president of Pawsitively Famous, Inc. You can connect with Tom on LinkedIn or Twitter.