Search

SF_Mirror

SF_Mirror

Usage

The SF_Mirror stored procedure is a hybrid of the SF_Replicate and SF_Refresh stored procedures. It automatically determines whether to perform a full copy (equivalent to SF_Replicate) or a delta copy (equivalent to SF_Refresh) of the table based on the following criteria:

  • If the table does not exist in the remote database, SF_Mirror creates the table by using the contents of the corresponding Salesforce.com object in the remote database of the remote SQL Server instance.

  • If the table exists, SF_Mirror determines whether to perform a full copy or a delta copy based on when the table was last replicated. If the table was replicated more than seven days ago, SF_Mirror performs a full copy. If the table was replicated less than seven days ago, SF_Mirror performs a delta copy.

  • If any schema changes are detected, SF_Mirror performs a full copy of the table.

The replicated table uses the same name as the Salesforce object (for example, Account). By default, SF_Mirror uses the Bulk API (with the PK chunking header, where applicable) when you perform a full copy of the table.

Note: If the table has already been mirrored to the remote database, SF_Mirror uses the SOAP API when the local table contains fewer than 20,000 rows and uses the Bulk API when the row count exceeds 20,000 during a full copy. In addition, SF_Mirror creates a primary key on the ID field of the table.

Syntax

  EXEC SF_Mirror 'linked-server','object-name','options', 'use-remote'

In this syntax:

  • The linked-server parameter specifies the name of your linked server.

  • The object-name parameter specifies the object name.

  • The use-remote parameter specifies that you are targeting a database with this remote stored procedure. This parameter must be set to 1.

Several other options are available for you to use as well. For details, see Options.

Example

This example creates either a full copy or a delta copy of the Account table by using the current data from Salesforce.com. It uses the SALESFORCE linked server to replicate the data to the remote database of the remote SQL Server instance.

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

Options

The SF_Mirror stored procedure determines whether to make a full or delta copy of the table. You can override the options it uses in two ways:

  • by using the options parameter

  • by using the DBAmpTableOptions table

SF_Mirror uses the following order of precedence when it evaluates options for replicating a Salesforce object:

  1. Options that are passed to the options parameter of SF_Mirror.

  2. Options that are specified in the Options field of the DBAmpTableOptions table for the corresponding table.

If no options are specified in the options parameter of SF_Mirror or in the Options field of the DBAmpTableOptions table, SF_Mirror determines automatically whether to perform a full copy of the Salesforce object.

For more information on using the DBAmpTableOptions table see Using the DBAmpTableOptions Table.

Options Parameters

This section describes options that you can use with the SF_Mirror remote stored procedure.

  • batchsize(n): SF_Mirror uses a default batch size of 100,000 rows when it makes a full copy. If you need to reduce the batch size to accommodate APEX code on the Salesforce.com server, use the batchsize(n) option, as shown in this example:

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

  • pkchunk: SF_Mirror uses the Bulk API when it creates a full copy. If Salesforce allows the object to use the PK chunking header, SF_Mirror adds it to the job. To force SF_Mirror to use the Salesforce Bulk API with the PK chunking header, add the optional pkchunk switch. SF_Mirror then submits a Bulk API job using the PK chunking header and polls every minute for completion, as shown in the following example:

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

    Use this option only for large tables, where chunking improves performance and reduces the likelihood of timeouts.

    The default batch size is 100,000 records. You can change this value by using the batchsize(n) parameter, as shown in this example:

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

  • bulkapi: SF_Mirror uses this option by default when it creates a full copy, so specifying this option is not required. SF_Mirror submits a Bulk API job and polls every minute for completion. For example, to force SF_Mirror to use bulkapi and poll every one minute for completion, submit this statement:

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

  • queryall: By default, SF_Mirror does not include archived or deleted records from the Salesforce.com object when it creates a full copy of the Salesforce data. To include the archived and deleted records in the SOQL query results, use the queryall switch, as shown below:

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

  • soap: By default, SF_Mirror uses the Salesforce Bulk API when it creates a full copy. To use the Salesforce.com Web Services (SOAP) API to create a full copy of Salesforce data, add the soap switch, as shown below:

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

  • subset: SF_Mirror creates a full copy of the table in the remote database if there is a schema change to the Salesforce object. When you add the subset option, SF_Mirror attempts 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 performs a delta copy of the table in the remote database based on that column subset. Using subset means that new fields that are added to the Salesforce object are not captured by SF_Mirror. However, SF_Mirror performs a full copy of the table every seven days regardless of the subset option. Deleted fields also remain in the table.

    The following example runs SF_Mirror with the subset option enabled for the Account table:

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

  • full( ): SF_Mirror creates a full copy of a table with the contents of the corresponding object on Salesforce.com every seven days. You can change this interval by using the full( ) option, as shown in this example:

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

Notes

  • A primary index on the ID column is created automatically 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. This behavior improves performance when you replicate large datasets. If you require the actual values, change the Include Binary Field Values setting to True on the Settings page of the DBAmp Configuration program.

  • SF_Mirror always creates fields as nullable in the table.