Search

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_Mirrordecides this based on these criteria:

• If the table does not exist in the remote database, SF_Mirror creates a table with the contents of the same object at Salesforce.com in the remote database of the remote SQL Server Instance. • If the table exists, 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 table (the last time the table was replicated). • If the created date of the table is more than 7 days old, SF_Mirror makes a full copy of the table. • If the created date of the 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 table is the same name as the Salesforce object (e.g., Account). By default, SF_Mirroruses the BulkAPI (with a PKChunk header where applicable) when making a full copy of the table locally.

NOTE: If the table has been mirrored to the remote database, 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 table.

Syntax

 EXEC SF_Mirror '𝘭𝘪𝘯𝘬𝘦𝘥_𝘴𝘦𝘳𝘷𝘦𝘳','object_name','options', 'use_remote'

where linked_server is your linked server name and object_name is the object name. use_remote must be set to 1.

There are several additional options you can include as well.

Example

This example makes a full or delta copy of the Account table with the current data on Salesforce.com using the SALESFORCE linked server to the remote database of the remote SQL Server Instance.

 EXEC SF_Mirror 'SALESFORCE', 'Account', '', 1

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. The order is as follows:

  1. Options passed into the Options parameter of SF_Mirror.
  2. Options provided in the Options field of the DBAmpTableOptions table, for the specified table.
  3. 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.

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 the batchsize(𝘹𝘹)option. For example, to set the batch size to 50,000: EXEC SF_Mirror ‘Salesforce’,’Account’,’batchsize(50000)’, 1 • 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 force SF_Mirror to use the Salesforce BulkAPI with the pkchunking header, use the pkchunk 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 the pkchunk option and poll every one minute for completion:

EXEC SF_Mirror 'Salesforce','Account','pkchunk', 1

The default batch size is 100,000. You can alter this using the batchsize option:

SF_Mirror 'Salesforce','Account','pkchunk,batchsize(50000)', 1

• 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 use bulkapi and poll every one minute for completion:

EXEC SF_Mirror 'Salesforce', 'Account', 'bulkapi', 1

• 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 the queryall switch. For example, to use the queryall option with SF_Mirror:

  EXEC SF_Mirror 'Salesforce', 'Account', 'queryall', 1

• 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 the soap switch. For example, to use the soap option with SF_Mirror:

  EXEC SF_Mirror 'Salesforce', 'Account', 'soap', 1

• Subset: SF_Mirror makes a full copy the table in the remote database 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 table in the remote database and the table on Salesforce.com. It then does a delta copy of the table in the remote database based on that column subset. Subset implies that new fields added to the Salesforce object are not captured by SF_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 table. For example, to use theSF_Mirror subset option:

  EXEC SF_Mirror 'Salesforce', 'Account', 'subset', 1

• 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 table once a day:

EXEC SF_Replicate 'Salesforce', 'Account', 'full(1)', 1

Notes

• A primary index on the ID column is automatically created when the table is replicated. SF_Mirror also preserves any secondary indexes on the 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 the Include Binary Field Valuessetting to True in the Settings page of the DBAmp Configuration program. • SF_Mirror always creates fields as nullable in the table.