Help get this topic noticed by sharing it on Twitter, Facebook, or email.

How can I optimize the copie of SF

Hello,
I begin to work with DBAMP to replicate data from SF to my Database.
My job takes 18 hours to finish.
it drops a old table and create a new one with all data for every table.

How can I optimize it?

It's possible to get just the last new data from SF instead all data every day?
Thanks for help.
1 person has
this problem
+1
Reply
  • It looks like you are running SF_ReplicateAll. This stored procedure replicates every object in your Salesforce org locally. Most businesses do not need every single Salesforce object locally. Do you know if your business requires you to need every single Salesforce object locally, or do they really only care about certain objects to be replicated locally?

    Also, take a look at the SF_Refresh and SF_RefreshAll stored procedures in the DBAmp Doc: http://forceamp.com/hats/DBAmpDoc.pdf

    These two stored procedures are incremental updates of the local table, instead of a full backup.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned

  • Thanks for you answer.
    I need to replicate most of the SF tables.
    I tried to execute SF_refrechAll instead SF_replaceAll, and I had some errors like this below,
    Thank you for you help:
    Error1 :
    16:54:56: Removing deleted rows from AcceptedEventRelation
    Error: SF_Refresh failed for table AcceptedEventRelation
    Error 242, Severity 16, State 3, Line 1
    La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.
    Error2 :
    17:04:30: Error occurred creating primary key for table.
    17:04:30: Error: Impossible de créer la contrainte. Voir les erreurs précédentes.
    --- Ending SF_Replicate. Operation FAILED.
    Error: SF_Replicate failed for table ApexPageInfo
    Error 50000, Severity 16, State 1, Line 252
    Error3 :

    17:04:48: Create ApexTestResultLimits_Previous with new structure.
    Le fournisseur OLE DB "DBAmp.DBAmp" du serveur lié "SALESFORCE" a retourné le message "Error 13005 : Error translating SQL statement: line 1:205: unexpected token: Soql".
    Error: SF_Refresh failed for table ApexTestResultLimits
    Error 7306, Severity 16, State 2, Line 1
    Impossible d'ouvrir la table "ApexTestResultLimits" à partir du fournisseur OLE DB "DBAmp.DBAmp" du serveur lié "SALESFORCE". La table ou la vue spécifiée n'existe pas ou comporte des erreurs.

    Error4 :
    - Starting SF_Refresh for EmailMessage
    17:28:02: Using Schema Error Action of yes
    17:28:12: Using last run time of 2016-02-10 23:51:00
    Le fournisseur OLE DB "DBAmp.DBAmp" du serveur lié "SALESFORCE" a retourné le message "Error 1 : OPERATION_TOO_LARGE: exceeded 100000 distinct ids".
    Error: SF_Refresh failed for table EmailMessage
    Error 7320, Severity 16, State 2, Line 1
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned

  • Error 1: "Converting a varchar data type to a datetime datatype has created a value out of range."

    Can you try running an SF_Replicate on that table, followed by an SF_Refresh and tell us the results of those:

    exec SF_Replicate 'SALESFORCE', 'AcceptedEventRelation' then,
    exec SF_Refresh 'SALESFORCE', 'AcceptedEventRelation'

    Error 2: "Can not create the constraint. See previous errors."

    What version of DBAmp are you currently running? Can you also post the complete message output from ApexPageInfo error?

    Error 3: "Error translating SQL statement: line 1: 205: unexpected token: Soql".

    Can you try replicating this table (ApexTestResultLimits) and post the complete message output?

    Error 4: "Error 1 : OPERATION_TOO_LARGE: exceeded 100000 distinct ids"

    This error is from the Salesforce server and occurs when a non-admin SF User runs a query and the Salesforce server cannot resolve the row level security for each row.

    The solution is to change the SF User on the linked server security page to be an SF User who is a System Admin. That way the salesforce server does not have to resolve security issues since admin users can view any row.

    Finally, do you need all four of the tables above locally (AcceptedEventRelation, ApexPageInfo, ApexTestResultLimits, EmailMessage) or just one or a few of them?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned

  • I’m happy
    Error1:
    Ok, so I have to modify SF_RefreshAll to include you proposition for every table with the same error.

    Error2:

    The version of DBAMP is 3.3.1.0
    this is the complete message :

    --- Starting SF_Replicate for ApexPageInfo
    17:04:28: Drop ApexPageInfo_Previous if it exists.
    17:04:28: Create ApexPageInfo_Previous with new structure.
    17:04:28: Run the DBAmp.exe program.
    17:04:28: DBAmp Bulk Operations. V3.3.1 (c) Copyright 2006-2016 forceAmp.com LLC
    17:04:28: Populating local table ApexPageInfo_Previous , TC-MSSQL2PPD-DZ / Salesforce Backups .
    17:04:29: DBAmp is using the SQL Native Client.
    17:04:29: Opening SQL Server rowset
    17:04:30: 307 rows copied.
    17:04:30: Drop ApexPageInfo if it exists.
    17:04:30: Rename previous table from ApexPageInfo_Previous to ApexPageInfo
    Attention : changer une partie du nom de l'objet peut inhiber les scripts et les procédures stockées.
    17:04:30: Create primary key on ApexPageInfo
    17:04:30: Error occurred creating primary key for table.
    17:04:30: Error: Impossible de créer la contrainte. Voir les erreurs précédentes.
    --- Ending SF_Replicate. Operation FAILED.
    Error: SF_Replicate failed for table ApexPageInfo
    Error 50000, Severity 16, State 1, Line 252
    --- Ending SF_Replicate. Operation FAILED.
    --- Starting SF_Refresh for ApexTestQueueItem

    Error4:
    I need to have a copy of all data in SF except 10 tables.
    My problem is the replicateall takes a lot of hours so that's way I try to use refreshAll.

    Thank you for your help
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned

  • Error 1: did you run the following in a new query window:

    exec SF_Replicate 'SALESFORCE', 'AcceptedEventRelation' then,
    exec SF_Refresh 'SALESFORCE', 'AcceptedEventRelation'

    If so, can I see the complete message output from both.

    Error 2: 3.3.1 is an old version of DBAmp and that error will go away in the latest version. I highly suggest you upgrade to the latest version of DBAmp. To do so, follow ALL of the instructions at the following link: http://forceamp.com/upgrade.htm

    Error 4: Is the SF User being used by the linked server a System Admin and have the View All Data privilege?

    Also, when you first ran the SF_ReplicateAll that took 18 hours, did you let it complete or stop it before it completed?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned

  • Error 1:
    Yes I run it and it works.
    Error 2:
    I will try to upgrade it next week.
    Error 4:
    - I think I have all privilege,But I have to check it with SF team.
    - yes , when I run it without job , it worked with some errors but it completed
    And when I run it with job it stoped alone with errors like that:
    ...
    du nom de l'objet peut inhiber les scripts et les procédures stockées. [SQLSTATE 01000] (message 15477) 20:02:15: Create primary key on AccountContactRole [SQLSTATE 01000] (message 0) --- Ending SF_Replicate. Operation successful. [SQLSTATE 01000] (message 0) --- Starting SF_Replicate for AccountHistory [SQLSTATE 01000] (message 0) 20:02:15: Drop AccountHistor... L'étape a échoué.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned

  • Here is what we recommend:

    Make sure the SF User being used by the SALESFORCE linked server is a System Admin and has the View All Data privilege. Then, upgrade DBAmp to the latest version using the link posted above, make sure you follow all directions. Once upgraded, run SF_ReplicateAll in a new query window and send us the complete message output once it finishes so we can take a look at any errors that may have occurred.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned

  • Hello ,

    After I upgrade dbamp with version 3.6.3 I ran replicateAll and it took 14h hours and after 5 days I ran refreshAll and it took 5 hours and I had 9 errors, do you have any idea to resolve it?
    Thanks.
    error1:
    --- Starting SF_Replicate for Attachment V3.6.3
    15:55:36: Parameters: SALESFORCE Attachment Version: V3.6.3
    15:55:36: Drop Attachment_Previous if it exists.
    15:55:36: Create Attachment_Previous with new structure.
    15:55:36: DBAmpNet2 3.6.3.0 (c) Copyright 2015-2017 forceAmp.com LLC
    15:55:36: Parameters: replicate Attachment_Previous TC-MSSQL2PPD-DZ Salesforce Backups_old SALESFORCE
    15:55:46: Error System.Web.Services.Protocols.SoapException: OPERATION_TOO_LARGE: exceeded 100000 distinct ids
    at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
    at DBAmpNet2.sforce.SforceService.query(String queryString)
    at DBAmpNet2.SFDataLayer3.QuerySF()
    15:55:46: DBAmpNet2 Operation FAILED.
    15:55:48: Error: Replicate program was unsuccessful.
    15:55:48: Error: Command string is C:\"Program Files"\DBAmp\DBAmpNet2.exe Exportsoap "Replicate" "Attachment_Previous" "TC-MSSQL2PPD-DZ" "Salesforce Backups_old" "SALESFORCE"
    --- Ending SF_Replicate. Operation FAILED.
    Error: SF_Refresh failed for table Attachment
    Error 50000, Severity 16, State 1, Line 370

    error2:---------------------------------------------------------------------------------

    Create CaseHistory_Previous with new structure.
    16:05:11: DBAmpNet2 3.6.3.0 (c) Copyright 2015-2017 forceAmp.com LLC
    16:05:11: Parameters: replicate CaseHistory_Previous TC-MSSQL2PPD-DZ Salesforce Backups_old SALESFORCE
    16:45:13: Error System.Net.WebException: The operation has timed out
    at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
    at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
    at DBAmpNet2.sforce.SforceService.query(String queryString)
    at DBAmpNet2.SFDataLayer3.QuerySF()
    16:45:13: DBAmpNet2 Operation FAILED.
    16:45:14: Error: Replicate program was unsuccessful.
    16:45:14: Error: Command string is C:\"Program Files"\DBAmp\DBAmpNet2.exe Exportsoap "Replicate" "CaseHistory_Previous" "TC-MSSQL2PPD-DZ" "Salesforce Backups_old" "SALESFORCE"
    error3:--------------------------------------------------------------
    20:06:50: Create Task_Previous with new structure.
    20:06:51: DBAmpNet2 3.6.3.0 (c) Copyright 2015-2017 forceAmp.com LLC
    20:06:51: Parameters: replicate Task_Previous TC-MSSQL2PPD-DZ Salesforce Backups_old SALESFORCE
    20:08:55: Error System.Web.Services.Protocols.SoapException: QUERY_TIMEOUT: Your query request was running for too long.
    at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
    at DBAmpNet2.sforce.SforceService.query(String queryString)
    at DBAmpNet2.SFDataLayer3.QuerySF()
    20:08:55: DBAmpNet2 Operation FAILED.
    20:08:57: Error: Replicate program was unsuccessful.
    20:08:57: Error: Command string is C:\"Program Files"\DBAmp\DBAmpNet2.exe Exportsoap "Replicate" "Task_Previous" "TC-MSSQL2PPD-DZ" "Salesforce Backups_old" "SALESFORCE"
    --- Ending SF_Replicate. Operation FAILED.

    error4:----------------------------------------------------------------------------
    20:10:57: Drop TopicAssignment_Previous if it exists.
    20:10:57: Create TopicAssignment_Previous with new structure.
    20:10:57: DBAmpNet2 3.6.3.0 (c) Copyright 2015-2017 forceAmp.com LLC
    20:10:57: Parameters: replicate TopicAssignment_Previous TC-MSSQL2PPD-DZ Salesforce Backups_old SALESFORCE
    20:10:58: Error System.Web.Services.Protocols.SoapException: MALFORMED_QUERY: Implementation restriction: TopicAssignment only allows security evaluation for non-admin users when either (1) LIMIT is specified and at most 1000 or (2) WHERE is specified and
    filter on Id or Entity
    at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
    at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
    at DBAmpNet2.sforce.SforceService.query(String queryString)
    at DBAmpNet2.SFDataLayer3.QuerySF()
    20:10:58: DBAmpNet2 Operation FAILED.
    20:11:00: Error: Replicate program was unsuccessful.
    20:11:00: Error: Command string is C:\"Program Files"\DBAmp\DBAmpNet2.exe Exportsoap "Replicate" "TopicAssignment_Previous" "TC-MSSQL2PPD-DZ" "Salesforce Backups_old" "SALESFORCE"
    --- Ending SF_Replicate. Operation FAILED.
    Error: SF_Refresh failed for table TopicAssignment
    Error 50000, Severity 16, State 1, Line 370
    --- Ending SF_Replicate. Operation FAILED.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned

  • Is the SF User being used by the linked server a System Admin and have the View All Data privilege? Also, how big (how many records) are your 10 biggest tables? You can use the Storage Page on Salesforce to determine this.

    Remember that SF_ReplicateAll and SF_RefreshAll attempt to bring down and refresh every single table in your Salesforce org, that can be over 400 objects. If you don't need all 400+ objects locally, I would suggest moving away from those two stored procedures.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly happy, confident, thankful, excited indifferent, undecided, unconcerned