You need to sign in to do that
Don't have an account?
Juan 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.
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.
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
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
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
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
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
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.
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.
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!
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.