Answered by:
MSF: Sync among databases question.

Question
-
Hi,
Can you give me your feeling about whether MSF will be applicable in our project:
The software we are building has a global database (master data) and local databases (replicates some global master data and mantain its own local data)
The separation between global and local is purely logical, as the DB model follows SaaS conventions (all of the databases can coexist on one physical DB). So local DBs will be located at the same or different boxes based on performance , geography, or other reasons.
Changes made to Master Data at the global DB should be replicated at some of the local databases. Which local DBs receive the data depends on info mantained at tables in the global DB.
The "information delivery" would not be done directly from the Database, but from Data Centric Objets located at AppServers, that receive all changes from the BR tier and persist them.
The project is planned to be finished by march/april 2008.
My questions:
1) Do you think MSF should be used to do the "information distribution" from Global to Local DBs?
2) Is there any example that I can use to start playing and familiarize myself with what has to be done?
3) Any rough estimates for the date of final release of MSF?
Thanks
Daniel
- Moved by Max Wang_1983 Thursday, April 21, 2011 10:21 PM forum consolidation (From:SyncFx - Technical Discussion [ReadOnly])
Friday, November 23, 2007 8:27 PM
Answers
-
Daniel,
My appologies for the delay.
Are you open to using SQL Server 2008 as the store? If so, there is a new feature in it called SQL Server Change Tracking. This would allow you to make a change in SQL Server and the change is automatically tracked by the server. Using this ChangeTable mechanism you could create a mechanism to send those changes to each of the stores.
If you are simply looking to do one way sync of these changes to each of the other data stores, I can tell you, you may not need the Microsoft Sync Framework, since using the ChangeTable mechanism it is pretty easy to tell the changes that have been made and pass them to each of the servers as long as you have a guaranteed connection to each of these servers.
Just as a side note, there is a forum on this Change Tracking feature here:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1623&SiteID=1
If these remote servers are occasionally connected you will probably want to implement something like Microsoft Sync Services for ADO.NET or Merge Replication that would allow these remote databases to periodically request changes from the main store.
I hope this helps.
Liam
Thursday, November 29, 2007 5:34 PM
All replies
-
Hi Daniel,
I think the Microsoft Sync Framework would work for your implementation as long as I am understanding you correctly. I think you are saying that you would like to create a Sync Service Provider in your "Information Delivery" system that would store all of the item changes from each of the databases. It would also control the sync between each of the master and local databases. I see no reason why you could not build this, but I have to admit that this is fairly disconnected from the traditional "hub-and-spoke" or "peer to peer" sync architecture we would typically be seeing.
Typically we would see a number of local databases sync directly to the master data to allow them to download the information they require. This would be a hub-and-spoke style implementation. The advantage here is that the local databases store the changes locally and then can quickly and efficiently exchange the information with the master database.
In your case, if I am understanding it correctly, you are looking to store the changes in some "information distribution" system. As such, I question the performance of such a solution since you would first need to populate the changes into the information distribution system before it could send them out to each of the local databases.
Is there a reason why you do not want to implement a traditional hub-and-spoke solution here?
Liam
Monday, November 26, 2007 10:56 PM -
Hi Liam,
Sorry for not being clear (English is my second language). Let me restate the situation as follows:
To de-abstract a little bit, suppose we have a class Customer, an instance of which has the following properties:
1) Customer Name: Paul.
2) It's defined in the global store 0.
2) it should be known (replicated) in stores 1, 3 and 5.
1) The UI or WebServices tier, where BusinessRules objects live, confirms some change made to Paul, and after validations the changes are packaged as a DTO (data transfer object) and sent to an AppServer.
2) Upon receiving the DTO, the AppServer will do the following ( S: synchronously, AS: asynchronously):
2.1) (S) Do some server side validation.
2.2) (S) Save the changed to the global store 0.
2.3) (AS) Do some post-processing stuff, like sending e-mails/communicator msgs to subscribers.
2.4) (AS) Deliver the changes to stores 1,3 and 5.
2.4.1) some of the stores, say 1 and 3, are located in the same db as store 0 (i.e., there is a num_store column in every table -multi-tenant architecture-)
2.4.2) some stores, say 5, are remote, accessible via low WAN connections.
What we're looking for is a technological way to accomplish item 2.4, and based upon your reply I see two different paths:
Path 1) Hub-and-spoke: The AppServer saves the information about the changes in some storage area; there are listeners working on behalf of each local store that will start sync sessions to receive the changes. I think this will be a scenario appropriate for MSF.
Path 2) Event-Based: The AppServer pushes the data to the local stores. If the local store is available, it commits the changes. If not, it will put the changes in a queue for later processing. This will involve more work and I don't know if MSF would be fit here.
I hope this is more understandable. Any Suggestions/Comments?
Thxs
Daniel
Monday, November 26, 2007 11:39 PM -
Daniel,
My appologies for the delay.
Are you open to using SQL Server 2008 as the store? If so, there is a new feature in it called SQL Server Change Tracking. This would allow you to make a change in SQL Server and the change is automatically tracked by the server. Using this ChangeTable mechanism you could create a mechanism to send those changes to each of the stores.
If you are simply looking to do one way sync of these changes to each of the other data stores, I can tell you, you may not need the Microsoft Sync Framework, since using the ChangeTable mechanism it is pretty easy to tell the changes that have been made and pass them to each of the servers as long as you have a guaranteed connection to each of these servers.
Just as a side note, there is a forum on this Change Tracking feature here:
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1623&SiteID=1
If these remote servers are occasionally connected you will probably want to implement something like Microsoft Sync Services for ADO.NET or Merge Replication that would allow these remote databases to periodically request changes from the main store.
I hope this helps.
Liam
Thursday, November 29, 2007 5:34 PM -
Thanks Liam,
I'll take a look at SqlServer2008. It could produce a selling point for the customer (currently using Oracle 10i).
I'll post the results of our research on this thread just in case it's useful for any other member.
Daniel
Thursday, November 29, 2007 6:08 PM