+ Start a Discussion
Juan RomeroJuan Romero 

Database Replication

Hi there,

We would like to run a local copy of the database to extend our report capabilities. Does Salesforce support any means of data replication?

We have MS SQL Server running locally. We would like to replicate the data to this server.

Thank you in advance.

niknik
The first way to do this that comes to mind is to make dumps of the data using the API at certain time periods. You can use the describe call to get a list of all fields for a table, then select out every field name and every record in a susequent query. Simply insert each record into a local table. Cool features would be to dynamically create the local tables based on field and table names from your API response, as well as checking the modified-time field on records to do incremental synchronisations as opposed to doing a full dump every x minutes.

We have developed a similar client, but it is used as a query cache for our application so that you can save on expensive API calls. We will be releasing the code to the cache in the next version of our Sforce.com Toolkit which is avaliable at www.solutionstap.com.

Regards,

Nik Cubrilovic
href="http://www.solutionstap.com">Solutionstap.
Juan RomeroJuan Romero

Thanks for the reply Nik,

Actually,  the client program I am working on is already capable of retrieving the table structure, constraints, fields and field definitions. It also generates the SQL script to create the table locally on our SQL Server. The problem is that it takes too long to parse the information when the queries return a large number of records. I was beginning to work on the replication by Modified time approach, but I was hoping there would be some other way to replicate the information than issuing queries.... sigh..... I hope there is.....

If you find out anything usefull, please let me know at juanja01@optonline.net

Thanks

 

DevAngelDevAngel

In an upcoming verison of the api there will be greater support for dbReplication.  Entities will have a systemModTimestamp field that is updated when the record has been modified in some way. 

This will allow you to avoid doing large data dumps to keep databases in sync. 

Message Edited by mike kreaden on 07-10-2003 02:29 PM

domdom

The problem with the current implementation of the "lastModifiedDate" field is that it is not guaranteed to be updated under all circumstances for some entities- which makes it unreliable for "net-change" replication. As Dave indicated there will be an API feature that supports a "system" date and timestamp will make it possible to perform - "net-change" replication.

Regardless of whether you are using "net-change" or not, you can use the existing "query" method call with the "idList" to retrieve "chunks" of records. This allows you to handle very large datasets.

The procedure is as follows...

1) Make a standard query call with the required filter and retrieve ONLY a list of ids.

2) You then iterate through this returned list and make a query call with the "idList" option and pass an array of ids (currently max 2000) and the list of fields you wish to select.

While making more round-trips - this will be mean that you will not have to make one large, expensive, and time-consuming call.

The added bonus of this is that you will be able to report progress to the user rather than just making a huge request.

Hope this helps...

Dom

 

Message Edited by mike kreaden on 07-10-2003 02:32 PM

Juan RomeroJuan Romero

Hey guys,

Thank you for your help. Your answers have been great. I will put those concepts to work today, but I will leave you with one last question:

Is there any other way to replicate the data BESIDES using XML-RPC API Calls?

Perhaps a web service or a way to connect from Server to server? (MS SQL Server supports replication)

 

Thank you

mike kreadenmike kreaden

I assume that you meant to say:

"Is there any other way to replicate the data BESIDES using XML-RPC or SOAP API Calls?"

This is a great opportunity for someone to contribute (or sell) a database replication agent that uses the upcoming replication APIs.  This will be a relatively straightforward exercise.

Message Edited by mike kreaden on 07-10-2003 02:28 PM

cwcw
Are there any plans to provide a notification mechanism in an upcoming API release which would allow an external application to subscribe to receive create/update/delete notifications on certain entities.
DevAngelDevAngel

Hi cw,

No, we have no plans right now for outbound notification beyond email.  That's not to say we won't have outbound notification in the future, but it's not on our current roadmap.

djordandjordan

I am also trying to mirror an SForce entity on a local DB (Oracle in my case). I have some code which creates tables based on the results of a Describe call to the API.

I've had trouble getting the list of IDs out of the API to perform the Query. Dominic suggested :

"1) Make a standard query call with the required filter and retrieve ONLY a list of ids.

2) You then iterate through this returned list and make a query call with the "idList" option and pass an array of ids (currently max 2000) and the list of fields you wish to select."

 

However I don't want a filter: I want to take a once-off snapshot of all the data in an entity. Calls to Query require a scope paramater. I've hit upon using the solution  filter("createdDate greater than new Date(0)) but this is a bit of a hack and is probably not the most efficient in terms of server resources. Also, not all entites have a createdDate field

Is there a better way to retrieve all the rows or all of the IDs in an entity?

David.

Juan RomeroJuan Romero

If you are looking to retrieve all the records from a given table (mirror the table locally on your server), you can simply issue a Query with an empty filter. I have building a utility for the last month to do just that, and one of the features I included is a query generator, which allows me to create extensive queries and throw them against the server. Here is a sample XML of how to get all the records from a given table (as generated by my tool, fully tested, works 100%):

<?xml version="1.0"?>
<methodCall>
 <methodName>sfdc.query</methodName>
 <params>
  <param>
   <value>
    <struct>
     <member>
      <name>select</name>
      <value>
       <array>
        <data>
         <value>
          <string>id</string>
         </value>
         <value>
          <string>username</string>
         </value>
         <value>
          <string>firstName</string>
         </value>
         <value>
          <string>lastName</string>
         </value>
         <value>
          <string>companyName</string>
         </value>
         <value>
          <string>division</string>
         </value>
         <value>
          <string>department</string>
         </value>
         <value>
          <string>title</string>
         </value>
         <value>
          <string>street</string>
         </value>
         <value>
          <string>city</string>
         </value>
         <value>
          <string>state</string>
         </value>
         <value>
          <string>postalCode</string>
         </value>
         <value>
          <string>country</string>
         </value>
         <value>
          <string>email</string>
         </value>
         <value>
          <string>phone</string>
         </value>
         <value>
          <string>fax</string>
         </value>
         <value>
          <string>cell</string>
         </value>
         <value>
          <string>alias</string>
         </value>
         <value>
          <string>active</string>
         </value>
         <value>
          <string>timeZone</string>
         </value>
         <value>
          <string>roleID</string>
         </value>
         <value>
          <string>locale</string>
         </value>
         <value>
          <string>infoEmails</string>
         </value>
         <value>
          <string>infoEmailsAdmin</string>
         </value>
         <value>
          <string>emailEncoding</string>
         </value>
         <value>
          <string>profileID</string>
         </value>
         <value>
          <string>language</string>
         </value>
         <value>
          <string>employeeNumber</string>
         </value>
         <value>
          <string>wirelessEmail</string>
         </value>
         <value>
          <string>lastLogin</string>
         </value>
         <value>
          <string>createdDate</string>
         </value>
         <value>
          <string>createdByID</string>
         </value>
         <value>
          <string>lastModifiedDate</string>
         </value>
         <value>
          <string>lastModifiedByID</string>
         </value>
         <value>
          <string>systemModstamp</string>
         </value>
        </data>
       </array>
      </value>
     </member>
     <member>
      <name>filter</name>
      <value>
       <array>
        <data>
        </data>
       </array>
      </value>
     </member>
     <member>
      <name>version</name>
      <value>
       <string>2.0</string>
      </value>
     </member>
     <member>
      <name>type</name>
      <value>
       <string>user</string>
      </value>
     </member>
     <member>
      <name>scope</name>
      <value>
       <string>filter</string>
      </value>
     </member>
     <member>
      <name>useCaseSafeIDs</name>
      <value>
       <boolean>1</boolean>
      </value>
     </member>
    </struct>
   </value>
  </param>
 </params>
</methodCall>

I am using VB to create my applications. If anyone is using the same and would like some guidance, let me know guys, I'm glad to help (After like 500 questions posted on this forum, I have been through a lot...... ask DevAngel.... he he : ) . E-mail to: juanr@richfx.com

Good Luck!

ZitizonXZitizonX
Hello Juan,
 
How are you doing? I just saw you posting at SalesForce Development discussion forum about the local data replication. I am trying to do something similer with my company data, so just thought of getting some ideas from you.
 
I basically want to replicate all my data in the SalesForce and store them locally and free the space in Salesforce. Also should be able to upload/replicate back to salesforce if needed.
 
Can you give me so hints? can you tell me how did you do your solution? Can I see some sample code if you dont mind?
 
Thanks a lot mate
 
X
SergeiSergei
To replicate Salesforce data to a database the best option is to use 3rd party providers. Take a look at Skyvia (https://skyvia.com/connectors/salesforce).

The service allows you to replicate Salesforce data in minutes and keep the copy up-to-date automatically. It also offers the full featured ETL capabilities (https://www.youtube.com/watch?v=vaLOCH-2GEc), if needed. 
Michael TomarMichael Tomar
I prefer the Skyvia cloud data integration platform too. They provide an on-premise agent for free, so I play it safe. You can find another 5 ways to connect MS SQL and Salesforce here: https://skyvia.com/blog/5-ways-to-connect-salesforce-to-sql-server