SF_Mirror
SF_Mirror
Usage
SF_Mirror
is a hybrid of the SF_Replicate
and SF_Refresh
stored procedures. It automatically chooses whether to do a full copy (equivalent to SF_Replicate
) or a delta copy (equivalent to SF_Refresh
) of the table. SF_Mirror
decides this based on these criteria:
-
If the table does not exist locally,
SF_Mirror
creates a local table with the contents of the same object at Salesforce.com. -
If the table exists locally,
SF_Mirror
decides whether to do a full copy or a delta copy of the table. It makes this decision based on the created date of the local table (the last time the table was replicated). -
If the created date of the local table is more than 7 days old,
SF_Mirror
makes a full copy of the table. -
If the created date of the local table is less than 7 days old,
SF_Mirror
creates a delta copy of the table. -
If there are any schema changes detected,
SF_Mirror
will make a full copy of the table.
The name of the local table is the same name as the Salesforce object (e.g., Account). By default, SF_Mirror
uses the BulkAPI (with a PKChunk header where applicable) when making a full copy of the table locally.
NOTE: If the table has been mirrored locally, SF_Mirror
uses the SOAP API when the row count of the local table is below 20,000, and the BulkAPI when the row count is above 20,000 when doing a full copy. In addition, it creates a primary key on the ID field of the local table.
Syntax
EXEC SF_Mirror '𝘭𝘪𝘯𝘬𝘦𝘥_𝘴𝘦𝘳𝘷𝘦𝘳','object_name','options'
where linked_server is your linked server name and object_name is the object name.
There are several additional options you may include as well.
Example
This example makes a full or delta copy of the local Account table with the current data on Salesforce.com using the SALESFORCE linked server.
EXEC SF_Mirror 'SALESFORCE', 'Account'
Options
SF_Mirror
decides whether to make a full or delta copy of the table. You can override the options it uses in two ways:
-
the
Options
parameter -
the DBAmpTableOptions table
There is an order of precedence for using options with SF_Mirror
when replicating a Salesforce object locally. The order is as follows:
-
Options passed into the
Options
parameter ofSF_Mirror
. -
Options provided in the Options field of the DBAmpTableOptions table, for the specified table.
-
If no options are passed into the
Options
parameter of SF_Mirror or the Options field of the DBAmpTableOptions table for the specified table,SF_Mirror
determines whether to make a full copy of the Salesforce object locally.
For more information on using the DBAmpTableOptions table see Using the DBAmpTableOptions Table.
Options Parameters
-
batchsize
—SF_Mirror
uses a default batch size of 100,000 rows when making a full copy. If you need to reduce the batch size to accommodate APEX code on theSalesforce.com server, use thebatchsize(𝘹𝘹)
option. For example, to set the batch size to 50,000:EXEC SF_Mirror ‘Salesforce’,’Account’,’batchsize(50000)’
-
pkchunk
—SF_Mirror
uses the BulkAPI when making a full copy. If Salesforce allows the object to use the pkchunking header,SF_Mirror
adds it to the job. To forceSF_Mirror
to use the Salesforce BulkAPI with the pkchunking header, use thepkchunk
switch.SF_Mirror
then submits a BulkAPI job using the pkchunking header and polls every minute for completion. Use this option for large tables. For example, to use thepkchunk
option and poll every one minute for completion:EXEC SF_Mirror 'Salesforce','Account','pkchunk'
The default batch size is 100,000. You can alter this using the batchsize option:
SF_Mirror 'Salesforce','Account','pkchunk,batchsize(50000)'
-
bulkapi
—SF_Mirror
uses this option by default when making a full copy, so specifying this as an option is not required.SF_Mirror
submits a BulkAPI job and polls every minute for completion. For example, to force it to usebulkapi
and poll every one minute for completion:EXEC SF_Mirror ‘Salesforce’, ‘Account’, ‘bulkapi’
-
Queryall
—SF_Mirror
does not include archived and deleted records of the Salesforce.com object when making a full copy of Salesforce data. To include the archived and deleted records of the Salesforce.com object in the full copy of Salesforce data, use thequeryall
switch.
For example, to use the queryall
option with SF_Mirror
:
EXEC SF_Mirror 'Salesforce', 'Account', 'queryall'
soap
—SF_Mirror
uses the Salesforce BulkAPI by default when making a full copy. To use the Salesforce.com Web Services (SOAP) API to make a full copy of Salesforce data, add thesoap
switch.
For example, to use the soap option with SF_Mirror:
EXEC SF_Mirror 'Salesforce', 'Account', 'soap'
temporal
—creates a full copy of the table as a system-versioned table designed to keep a full history of data changes and allow easy point in time analysis. For more information, see Making Local Copies as Temporal Tables.
For example, to use the temporal
option with SF_Mirror
:
EXEC SF_Mirror 'Salesforce', 'Account', 'temporal'
Subset
—SF_Mirror
makes a full copy the table locally if there is a schema change to the Salesforce object. If you add the subset option,SF_Mirror
tries to determine a valid subset of columns that exist in both the local table and the table on Salesforce.com. It then does a delta copy of the local table based on that column subset.Subset
implies that new fields added to the Salesforce object are not captured bySF_Mirror
. However, it does make a full copy of the table every seven days regardless of the subset option. In addition, deleted fields remain in the local table.
For example, to use theSF_Mirror
subset
option:
EXEC SF_Mirror 'Salesforce', 'Account', 'subset'
-
full
—SF_Mirror
creates a full copied table with the contents of the same object at Salesforce.com every seven days. You can change the period by using the full(xx) option. For example, to make a full copy of the local table once a day:EXEC SF_Replicate 'Salesforce', 'Account', 'full(1)'
Notes
-
SF_Mirror
is a hybrid ofSF_Replicate
andSF_Refresh
. -
A primary index on the ID columnis automatically created when the table is replicated.
SF_Mirror
also preserves any secondary indexes on the local table. -
By default, DBAmp does not download the values of Base64 fields. Instead, it sets the value to
NULL
. This is done for performance reasons. If you need actual values, change theInclude Binary Field Values
setting toTrue
in the Settings page of the DBAmp Configuration program. -
SF_Mirror
always creates fields as nullable in the local table.