This article explains how CopyStorm optimally decides which records are copied from Salesforce.

The Most Important Point

CopyStorm’s default behavior is to perform an incremental copy from Salesforce every time it runs. There are no special parameters to set — just use the defaults and CopyStorm will incrementally update your Salesforce backup.

How CopyStorm Selects Records to Copy

CopyStorm minimizes the number of records processed during each run by using record timestamps stored in the CopyStorm database from previous runs.

For example, if the most recent timestamp for an Account in the CopyStorm database is 14-Jul-2013 at 12:00:00.000 then there is no need to read records from Salesforce that were modified before this timestamp.

The following diagram illustrates how CopyStorm reads records for each selected table:

The details are a bit more complex than the above diagram — for example, records are not read one at a time in the code. However, this is the exact logical process followed by CopyStorm.

Advanced Timestamp Details

This section provides a bit more detail on exactly how timestamp values are used by CopyStorm. You do not need to grasp this section to understand how CopyStorm works.

Salesforce has an interesting quirk — records are not committed to a Salesforce database in timestamp order. The implications of this are:

• At 13:01 a SOQL query indicates that there are 1000 records created earlier than 13:00.

• At 13:02 the same SOQL query may indicate that there are 1001 records created earlier than 13:00.

It seems that records written to Salesforce “take a little while” to be committed to the database AND are committed in no particular order.

CopyStorm overcomes this Salesforce quirk using two parameters on the Advanced tab:

• The SFDC Commit Latency parameter indicates how long to wait for Salesforce to commit records. The default is 300 seconds.

  • This means that, by default, CopyStorm will not read a record from Salesforce until it has a timestamp at least 300 seconds old.

• The Timestamp Latency parameter indicates how many seconds to subtract from the most recent timestamp found in CopyStorm.

To sum up the details, the SOQL used by CopyStorm really looks like:

• SELECT * FROM TT WHERE TT.systemmodstamp>=(MostRecent – “TimestampLatency”) AND TT.systemdstamp <=(CURRENT_TIME – “SFDC Commit Latency”)

In Salesforce a query of the form “SELECT * FROM TT” will often fail with a timeout error on large tables. To avoid this problem CopyStorm runs every SOQL query with a LIMIT. For most tables the LIMIT value is set to 20,000 — but is configured to be smaller for a handful of tables known to have problems with this high of a limit.

The default LIMIT value can be overridden on the Advanced tab using the “Row LIMIT per Query” option — however, changing this parameter generally results in non-optimal behavior.

In the case where all records in a batch have exactly the same timestamp, CopyStorm dynamically increases the LIMIT value until the end of records is reached or not all records have the same timestamp. When this type of adjustment is required it is rarely noticed unless a Salesforce table has 100,000’s of records with exactly the same timestamp.

If you would like to know about further Salesforce quirks CopyStorm has to manage (and be convinced that you don’t want to write your own CopyStorm) take a look at this blog post: .

Metadata tables in a CopyStorm database are primarily copies of the equivalent Salesforce metadata calls plus mappings from Salesforce to CopyStorm names for tables and fields. These tables are the “CopyForce” tables found in the CopyStorm database.

CopyForceTable maps the Salesforce API table name to the CopyStorm created database table name. It contains the same fields as a Salesforce DescribeSObjectResult. Where possible CopyStorm uses the same name, but there are several reasons why CopyStorm may use a different table name:

• Names that are the same as reserved words in the target database will be modified.

  • For example, User is renamed to User2 in SQL/Server.

In general, CopyStorm uses the minimal number of API calls possible to do a backup.

Detailed information can be found in this table:

Yes you can — CopyStorm supports log4j2. This article contains the instructions you will need.

CopyStorm comes packaged with log4j2 — configuring configuring CopyStorm to use it is a two-step process:

• Select the “Use Apache Logging” option on the configuration section of CopyStorm’s Configuration tab.

  • When checked, CopyStorm will use the Apache Commons Logging system rather than the default.

The short answer is “Yes, CopyStorm backs up all Salesforce metadata.”

In Salesforce, metadata is stored in two different ways:

• In Tables (SObject) — much of the key Salesforce metadata is stored in tables.

  • Examples include Apex Classes, Apex Pages, Apex Triggers, and Workflows/Processes

• Not In Tables — this type of data is only accessible as zip archives via the Salesforce Metadata API.

CopyStorm backs up all metadata stored in Tables and, just as importantly, maintains revision history as the records change.

For example:

• If an ApexClass changes, the old version of the class is saved in a field history tracking table.

• If an ApexPage is deleted, the definition of the ApexPage just before it was deleted is saved in a field history tracking table.

Since metadata tables used by CopyStorm are defined by Salesforce’s ERD, the data in them is readily accessible within the CopyStorm database. For example, the definition of the ApexClass “MyFavoriteClass” can be found with the following SQL:

• SELECT body FROM ApexClass WHERE name=”MyFavoriteClass”

Starting with CopyStorm 7.37.2, metadata accessible only via the Salesforce metadata API is backed up to the CopyStorm table SFMetaFileProperties and can be restored using CopyStorm/Restore.

Since CopyStorm keeps a copy of Salesforce metadata in local database tables it is a fairly trivial task to compare metadata from multiple CopyStorm backups.

Problem 1: Compare Table and Field definitions from Two Backups

The key CopyStorm tables required to perform table and field comparisons are CopyForceTable and CopyForceTableField. These are the tables CopyStorm uses to keep the CopyStorm database structure in sync with Salesforce. Rather than bore you with details, download the following SQL script for an example of how to exhaustively search for table and field differences.

• Search for Table and Field Differences Script.

Problem: Compare Salesforce Metadata Objects from Two Backups

This solution compares elements like Apex Classes, Page Layouts, Settings, etc. from two difference CopyStorm backups. The key table in CopyStorm is SFMetaFileProperties since this table contains the most recently backed version of each metadata type. Note that the companion table, SFMetaFilePropertiesHistory, contains historical values for each metadata type. Download the following script for an example of how to exhaustively compare metadata.

When a new version of CopyStorm is released, adjustments to the internal CopyStorm data model may be required. By default, CopyStorm automatically handles database version upgrades. To disable automatic schema upgrades, refer to the documentation.

Schema Upgrade Notice Dialog

CopyStorm’s default behavior is to display a Schema Upgrade Notice dialog to the user:

From this dialog, you can:

• View all pending schema upgrades.

• View specific details about a schema upgrade, including the underling SQL to be executed.

• Approve or Disapprove of the changes.

  • Approving the changes will cause CopyStorm to execute all upgrade SQL.

  • Disapproving the changes will cause CopyStorm to terminate.

  • Note: By default, CopyStorm automatically applies the upgrades.

• View the progress of the approved changes.

CopyStorm offers users the ability to view details about an individual internal schema upgrade.

Users have the ability to see:

• Schema Upgrade Description

  • High level information about the purpose of the upgrade.

• SQLassociated with the Schema Upgrade

  • This provides users with the ability to copy & run the SQL manually.

  • Note: For some upgrades, SQL can also be generated dynamically by CopyStorm. In these cases, it is not appropriate to run upgrade SQL manually. Please contact before proceeding to run any upgrade SQL manually.

*Note:*The Schema Upgrade shown in the above screenshots are for an update to introduce support for off-database partitions. To learn more about how off-database partitions work, refer to the .

When Schema Upgrades are not approved, the following dialog will be shown:

From this point, users have the options to:

• Exit

  • Terminate the CopyStorm application

• Cancel

  • Return to the Schema Upgrade Notice Dialog

In Salesforce all Date/Time data is stored in UTC — Coordinated Universal Time. UTC is not a time zone but rather a time standard used commonly around the world.

For practical purposes, we can assume that UTC is the same as the older GMT (Greenwich Mean Time) standard and is the current time in London, UK.

CopyStorm also stores all Date/Time values in UTC and it is up to an application to convert UTC values to the timezone of each application using the data. This makes a lot of sense for systems with users accessing the same data from wildly different timezones. Each user expects to see Date/Time values displayed in their local time zone!

How Do I Convert From UTC to My Local Time Zone?

This section lists a few techniques for converting from UTC to the local timezone of the computer hosting the CopyStorm database.

Microsoft SQL/Server

Example query:

SELECT systemmodstamp ,DATEADD(hh,((DATEDIFF(ss,systemmodstamp,GETDATE())-DATEDIFF(ss,systemmodstamp,GETUTCDATE()))/3600), systemmodstamp) AS LocalTimestamp FROM Account

There are two things to note with this solution:

• SQL/Server 2016 has a direct solution to this problem ().

• This example does not take daylight savings time into consideration.

  • See for a solution that takes daylight savings time into consideration.

Example query:

SELECT systemmodstamp, systemmodstamp + (systimestamp – SYS_EXTRACT_UTC(systimestamp)) LocalTime FROM Account

Example query:

SELECT systemmodstamp, systemmodstamp + INTERVAL (TIMESTAMPDIFF(SECOND, UTC_TIMESTAMP() , NOW())) SECOND FROM Account

Example query:

SELECT systemmodstamp, systemmodstamp AT TIME ZONE ‘UTC’ FROM Account

This article explains how Capstorm license management works and provides tips on diagnosing license management problems.

When a Capstorm product connects to Salesforce it also checks for a valid product license in the file “license.txt”. The “license.txt” file will be located in the same directory as the script used to launch the Capstorm product. If a license.txt file is not found or a valid non-expired license is not found in the file, the Capstorm product will issue a request to the Capstorm license service to retrieve (or generate) a license.

When issuing the license request to the Capstorm license server, the file will be used to identify the installation. If the CapstormCustomerKey.txt file is not valid, a message indicating the issue will be shown.

If a Capstorm product fails to retrieve/generate a license it is usually because the connection is blocked by a firewall rule. Diagnosing and confirming this condition can be done using a web browser. Here’s how.

To retrieve or generate a license, a Capstorm product makes a https request in the following form:

Having multiple CopyStorm jobs run on the same database at the same time is perfectly OK if the two jobs are processing different Salesforce tables. However, what if two CopyStorm jobs are running at the same time on the same set of tables? How can this happen?

• CopyStorm is scheduled to run every 30 minutes.

• A CopyStorm job starts at 12:00.

• At 12:30 the first CopyStorm job is still running because there were a LOT of updates in Salesforce.

CopyStorm copies both active and deleted records from Salesforce to the target database by inspecting the Salesforce recycle bin. When a deleted record is discovered its “isDeleted” column is set to true in the target database.

The retention policy for deleted records in the target database is set using the Advanced tab parameter “Delete Older Than”. Available values can range from:

• Remove deleted records immediately.

• Remove deleted records after they have been deleted in Salesforce for 7 days.

All programming languages, databases, and most applications have limits on the maximum size of values they can store or manipulate. For example, Salesforce has a 2 GB limit the size of an uploaded file, and PostgreSQL has a 1 GB limit on the size of a column value.

When CopyStorm downloads large files from Salesforce, CopyStorm streams the Base64-encoded file body to the file system (which has no size limits other than available disk space). Without using CopyStorm’s Base64 Chunking feature the Base64-encoded file body will be written directly to the database, subject to the size limitations (e.g. 1 GB in PostgreSQL).

The CopyStorm Base64 Chunking feature allows large files to be broken apart into chunks, storing these chunks in a separate table named CopyForceTableFieldChunk. By default, files will be broken into 100 MB chunks – but this is configurable. For example, if the Body field of a ContentVersion record is 500 MB, and the Base64 Chunking feature is enabled, then the 500 MB file will be broken into 5 parts that are stored as 5 separate entries in the CopyForceTableFieldChunk table.

With Base64 Chunking enabled, there are no practical database storage limits to the size of field values in SalesForce objects that can be brought into the CopyStorm database, and CopyStorm is not subject to the maximum size limitations of the database.

Base64 Chunk Usage

The Base64 Chunking feature is disabled by default when CopyStorm is initially configured – to enable this feature, check the Chunk Base64 Values checkbox on the configuration editor.

When this checkbox is:

• Unchecked, then Base64 field values are always stored in-line in the primary CopyStorm data tables.

• Checked, then Base64 field values that are larger than the chunk size will be stored in the CopyForceTableFieldChunk table.

  • In this case, a pointer value will be stored in the primary CopyStorm data table.

The pointer is the string representation of a JSON object containing field metadata. For example:

All chunked values will be prefixed with the value “#AAA:”. Inlined Base64 values will never begin with this value because the “#” character is not valid in Base64 strings.

All of the Base64 chunking information within this page also applies to CopyStorm Snapshots, with two differences:

1. CopyStorm Snapshots store Base64 values in a secondary chunked data table named CopyForceArchiveFieldChunk.

2. CopyStorm Snapshots will always chunk Base64 values, regardless of whether Base64 chunking is enabled in the primary CopyStorm database.

To override the system default chunk size, create a Base64ChunkParameters.xml file and place it in the [CopyStorm]/config/ directory. This file is used to provide the chunk size to CopyStorm. In the example below, the default chunk size has been changed to 5 MB (5000000):

All chunks created after updating this file will be created using the newly provided chunk size.

CopyStorm will automatically delete records from the CopyForceTableFieldChunk table as parent records are deleted from the primary CopyStorm data tables. If the CopyStorm tables are truncated, or if records are deleted from the CopyStorm tables without CopyStorm performing the deletions, then the CopyStorm/Medic Delete Orphaned Base64 Chunks tool can be used to scan for and delete any CopyForceTableFieldChunk records that point to records that are no longer present in the primary CopyStorm data tables.

Snapshot chunked records contained in the CopyForceArchiveFieldChunk table will be automatically cascade-deleted when their parent records are deleted.

The Salesforce API presents all SObjects (Account,Contact, etc.) as tables to the outside world even though quite a number of SObjects are really subsets/views of other SObjects. The most common example is Salesforce feed tables — there are dozens of feed tables (AccountFeed, ContactFeed, etc.) that are simply views of the table that contains all feeds: FeedItem.

• The AccountFeed table is really a view on the base table FeedItem.

  • SELECT * FROM FeedItem WHERE parentId IS an Account Record.

There are also cases where the Salesforce API does not support a direct query on an SObject but the SObject can be constructed from other SObjects.

• The ActivityHistory table is a union of the Event and Task SObjects but cannot be queried directly using SOQL.

There are other issues of this kind, but lets get to the important part. When Database Views are enabled in CopyStorm, then CopyStorm will use views in the target database rather than copying a table. This can lead to much faster backups for each table that can be represented as a view.

Database Views are enabled by default in CopyStorm and when enabled:

• If a Salesforce table does not exist in the target database and can be represented as a view, then CopyStorm will create it as a view.

• If a Salesforce table already exists in the target database, then CopyStorm will not re-create it as a view but will continue to treat it as a table.

If Database Views are disabled in CopyStorm:

• Database Views will not be created for new tables.

• If a Database View already exists in the target database, then CopyStorm will not change it.

The toggle that enables/disables Database Views is found in the configuration section of the CopyStorm Configuration tab.

This section is for anyone wanting to know exactly how the logic for determining whether a Database View should be used works.

If your CopyStorm database was created before Database Views were introduced then are likely many database tables in your backup that could be represented as database views instead. For safety, CopyStorm will not convert an existing table to a view UNLESS you follow the instructions in this article.

The technique to force CopyStorm to switch to a database view for a table is simple:

• Manually drop the table in the CopyStorm database that should be a view. The next time CopyStorm runs it will create the view.

For example:

• DROP TABLE AccountFeed

When metadata backup is enabled for CopyStorm a table named SFMetaFileProperties will be created in the target database to hold metadata backups. In addition a table named SFMetaFilePropertiesHistory will be created to hold past revisions of metadata.

The columns in SFMetaFileProperties mimic the data structure returned from the Salesforce Metadata API:

There are a number of Salesforce quirks that make creating incremental backup software more difficult than a person might expect. The content of this article will help you understand some of the quirks and give you information that makes you happy that Capstorm backs up Salesforce for you. Note, the text may look familiar to an article published originally on blogspot in 2014 — that’s OK since the blogspot author is one of the founders of Capstorm.

SOQL’s Seven Deadly Sins

This article is not about how to be a better SOQL programmer. It is about pitfalls in SOQL that are obscure and often difficult to diagnose and understand when encountered.

1. The SOQL WHERE clause cannot be trusted.

2. The SOQL LIMIT parameter is not always optional.

3. Timestamps returned by SOQL believe in time travel.

4. SOQL can return field data that is invalid based on the field definition.

5. SOQL has a love/hate relationship with tables containing lots of custom fields.

6. SOQL does not treat all table equally – not knowing the exceptions can take down your org.

7. SOQL chokes on tables with lots of functional columns.

Here is a trick question. Will the following SOQL statement return any records that were modified today?

SELECT id, name FROM Account WHERE systemModStamp < LAST_YEAR

The answer is……usually not. However it will return a record modified today when:

• The record fit the WHERE criteria when the record set to return was determined.

• The record is modified before it was fetched from Salesforce.

This “sin” is easy to reproduce with a Python script and a web browser.

1. In the web browser open the Salesforce GUI on the record that will be the most recent one to match the LAST_YEAR criteria.

2. Start a Python script that fetches records using:

SELECT id,name,systemModStamp FROM Account WHERE systemModStamp < LAST_YEAR ORDER BY systemModStamp ASC

1. Modify the record in the Salesforce GUI after the Python script starts fetching records.

2. One of the last records you read from Salesforce will have a modification date of today

Though the SOQL documentation says that the LIMIT parameter is optional there are a handful of Salesforce tables where it is nearly impossible to run a large query without the dreaded Query Timeout error. If you are not a believer then try running these queries on a mature Salesforce instance.

1. SELECT id, accountId, description FROM Task ORDER BY SystemModStamp ASC

2. SELECT application,browser,loginTime FROM LoginHistory ORDER BY loginTime ASC

Short of begging Salesforce for a max timeout value longer than the default, a solution has to “know” good limit values for problem tables. Here are good values for a handful of the more troublesome table.

Here’s another SOQL quiz. In the following code snippet, is it possible for the Exception to be thrown? For the quiz assume that all Account records have unique timestamp values and no records are deleted.

Account accts1[] = [SELECT id, systemmodstamp FROM Account ORDER BY systemmodstamp ASC;

Account maxAcct = accts1[acct1.size() – 1]

Account accts2[] = [SELECT id, systemmodstamp FROM Account WHERE systemmodstamp <=:maxAcct.systemmodstamp ORDER BY systemmodstamp ASC;

if( accts2.size() > accts2.size()) {

throw new Exception(‘Timewarp – new account created in the past!’);

}

The answer….usually no. However, Salesforce does not guarantee that records are committed in timestamp order.

It is possible for a record with a SystemModStamp of 2014-12-31T00:12:12.000Z to be committed to Salesforce after a record in the same table with a SystemModStamp of 2014-12-31T00:13:12.000Z.

This is a hard “sin” to reproduce because it depends on the current load of the Salesforce servers. We discovered the problem by monitoring a high volume production instance for three weeks – the problem occurred just a couple of times during this period. It was sort of like searching for a Higgs particle.

The moral: Do not assume that the MAX(SystemModStamp) or MAX(LastModifiedDate) value is a good constraint to find all values that are older.

The solution: Do not rely on timestamps that are fresh. Give Salesforce a little while to commit all recent transactions. In practice this odd state seems to settle out on Salesforce servers after 10-120 seconds.

This quiz should be easy for experienced SOQL users.

1. Is it possible for an invalid email address to be stored and returned in a Salesforce Email field?

2. Is is possible to store a 132 character string in a 128 character Salesforce field?

The answer to #1 is, of course, possible. Check out contact Ms. Barbara Levy in any developer force instance. Her email is: b.levy@expressl&t.net Try saving the record and Salesforce will tell you that the email format is invalid (using the API will throw the same error).

The answer to #2 is also “possible.” In fact Salesforce creates an email template with an invalid length subject when Sites is enabled.

There is other “invalid” data (related to currency values) lurking in the developer force instance – you will have to find this one yourself.

Thing to remember:

1. Just because Salesforce meta data says a field is 80 characters long does not mean it may have a longer value.

2. Just because Salesforce meta data says a number is DECIMAL(5,2) do not trust it. An API program may have inserted data that does not match the spec.

3. Do not be surprised if you read data from one Salesforce instance and it fails to upsert into another instance – the source data may be bad.

Though Salesforce allows many custom fields to be created on a table, using too many columns in a SOQL statement will cause a QUERY_TOO_COMPLICATED error. At least this “sin” is one Salesforce documents but after 10 years one would assume they would have a work-around in the back-end.

If you really need to do the equivalent of “SELECT * FROM Account” and SOQL will not allow the read in a single statement what needs to be done? There are two problem to solve.

1. Figure out the minimum number of SOQL queries to run. If none of the columns are formulas then this can usually be done by using 500 columns for each query. If formula columns are involved the problem is much harder – in this case see Sin #7.[

2. Join the results of multiple queries together.

Realistically, grab the data from a Salesforce dump file or use a tool like Capstorm’s CopyStorm to extract the table into a relational database. Doing it yourself is hard.

The transaction cost of querying a table with SOQL varies a lot. The first time you query a high cost table and cause your production org to run out of transactions will probably be the last time.

Here a SOQL quiz. How many Salesforce transactions does it take to run the following SOQL statement?

SELECT id, name FROM TableName LIMIT 2000

The answer, of course, depends on TableName and is either 2000, 200, 40, 20, 10, 4, 2, or 1.

The “1 transaction per record” rule for Attachment records is documented. The same rule applies to QuoteDocument and ContentVersion but we have learned this from experience rather than documentation. In general 2000 and 1000 are the common number of records per transaction but the only way to know for certain is the write a bit of code and see how many come back in a bunch.

When Salesforce runs a SOQL query it turns it into SQL first. Once in SQL there is a 64K character query limit. If the 64K SQL query limit is passed then SOQL will return a QUERY_TOO_COMPLICATED exception.

Here a SOQL quiz: Given a SOQL statement how can you accurately (or inaccurately even) guess that it will generated a SQL statement less than 64 characters?

The answer is……if table has a lot of formula columns you probably cannot guess accurately.

In practice, the addition of formula columns in a SOQL statement can cause the size of the resultant SQL to explode – sometimes. It all depends on the complexity and number of tables referenced by the formula.

• The complexity of the formula seems to matter. My guess is that Saleforce is in-lining the formula calculation into the generated SQL.

• A formula that references other tables likely causes the table expression and predicate portion of the generated SQL to grow rapidly.

The solution to this problem is nearly the same as Sin #5 but the first step, determining the minimum number of SOQL query to run can difficult. The only solution we have found that works reliably is iterative.

1. Sort column in ascending order by the length of their name or length of their corresponding formula.

2. Set maxColumn = total # of columns

3. Run a query run using columns 1 thru maxColumn with a LIMIT 1.

• If the query succeeds then save the query, remove the query’s columns from the list of available columns.

• If the query fails then set maxColumn = maxColumn – 1 and start step 3 again.

1. Set maxColumn = # remaining columns

2. If maxColumn > 0 goto Step #3, otherwise all queries have been determined.

The “SOQL Sins” described in this article were accumulated over a number years while developing:

• – keep a hot backup of your Salesforce instance in SQL/Server, Oracle, MySQL, PostgreSQL, and H2

• – restore a backup to any production, development, or sandbox instance. This is a restore tool even your manager could use.

• – a standard Python module for Salesforce. Works with Python 2.8. 3.3, and 3.4. Use SQLForce when Apex is just too hard.

For more information see

The following table describes how Salesforce column data types are mapped to database data types:

Each time CopyStorm runs it records a variety of data into tables in the CopyStorm database:

• A summary of records written to the database and any exceptions.

  • The content and structure of the tables will be familiar because they mimic what is displayed as progress within the CopyStorm GUI.

• Changes to field values (see CopyForceFieldHistory in this article).

• DDL statements (e.g. ALTER TABLE) that were issued against the CopyStorm database.

CopyStorm database logging tables were introduced starting with the 7.*.* version of CopyStorm.

The following tables are used for logging:

These logging tables are created automatically by CopyStorm. The only changes that should ever be performed by a customer is the deletion of log records — this should be done using .

The simplest way to view and manage CopyStorm log data is often the tool in CopyStorm/Medic.

The remainder of this article describes the column structure for each table.

The CopyForceJob table contains one record for each time CopyStorm successfully connects to Salesforce, connects to the target database, and starts a copy.

The table contains these columns:

The CopyForceJobTable table contains one record for each table that was modified during a CopyStorm run.

The table contains these columns:

The CopyForceJobException table contains one record for each exception that occurred during a CopyStorm run.

The table contains these columns:

The CopyForceDDLLog table contains a record of each DDL statement ran against the CopyStorm database.

The table contains these columns:

The CopyForceFieldHistory table records changes to field value. This table will be empty unless field history tracking is explicitly enabled.

History records will be maintained even if the referenced table / field is modified or deleted in Salesforce.

The table contains these columns:

The CopyForceMedicJob table contains one record for each time a CopyStorm/Medic job runs.

The table contains these columns:

The CopyForceMedicJobException table contains one record for each exception that occurred during a CopyStorm/Medic run.

The table contains these columns:

The CopyStorm “Copying…” tab has real-time metrics which help identify a components that are limiting the speed of a database backup. The metrics, located in the lower right hand corner of a “Copying…” tab are described in this article.

There are two primary factors which limit the speed of a CopyStorm job:

• The rate at which Salesforce delivers data to CopyStorm.

• The rate at which the target database accepts data from CopyStorm.

Metrics highlighted in the following image provide data indicating which component is the bottleneck at your site:

Metrics are divided into three sections:

In a balanced system:

• The “S” threads will be blinking blue almost all of the time.

  • This means that a steady stream of data is being delivered from Salesforce.

• The “D” threads will be grey most of the time but blinking blue rapidly.

• The data throughput values will be less than 50ms and the database write speed will be less than 20ms.

In a system where the database is the bottleneck:

• The “S” threads will often turn grey.

• The “D” threads will be blue most of the time.

• The database write speed will be large.

  • This value is elapsed time for a single request to the database to write a block of records.

In a system where Salesforce is the bottleneck:

• The “S” threads will be solid blue most of the time.

  • This means that they are waiting for a response from Salesforce.

• The “D” threads will be grey most of the time.

• The first data throughput number will be large.

The most common issue we see is that CopyStorm and the database are not in the same data center. The underlying issue is that the protocols used to talk to relational databases require close proximity between the application and the database for great performance. Until we discover a way to increase the speed of light, CopyStorm running in India and talking to a database in Canada will give slow performance. You can solve this problem by running CopyStorm and its corresponding database in the same data center.

CopyStorm will write data to a database as quickly as Salesforce delivers it. If the “S” threads are grey, the “D” threads are blue, the data throughput numbers are large, and you are sure that CopyStorm and the database are in the same data center; then the database is likely overloaded. Using system monitoring tools on the database computer will quickly pinpoint the issue — often the database computer will be page-faulting like crazy.

If the CopyStorm “S” threads are solid blue, the “D” threads are almost always grey, and the first data throughput number is large; then Salesforce is not delivering data rapidly. Even with a modest internet bandwidth of 20M/s, CopyStorm can easily transfer 20,000 – 30,000 accounts per minute. If you are sure that your available bandwidth is high then see your system administrators. There may be network software inspecting and slowing packets from Salesforce.

Though CopyStorm’s runtime requirements are modest, it cannot control demands made by other applications. Use a local system monitor to see which applications are using a high amount of CPU time. On a balanced system CopyStorm will always be consuming CPU — reading from Salesforce and writing to the target database.

CopyStorm includes logic specific to Daylight Savings or Summer Time changeovers.

This logic accommodates both time changes and Salesforce record commit latency issues.

When a CopyStorm job finishes within a few hours of a time change, the next job will re-query for records modified before the time change.

The next job will re-scan an extra 90 minutes of record changes prior to the time change. This ensures that Salesforce commit latency issues and time change issues do not cause missing records in the database.

Jobs that run near a time change will take longer to complete.

This is because the job intentionally reads extra data to ensure that all records are copied. As a result, you will likely see a higher number of “Skipped” records – the job is re-checking data that is already up to date in the CopyStorm database.