locked
realtime synchronization of databases RRS feed

  • Question

  • im kind of new to ADO.NET thing and the synchornization framework...

    so im asking kind of a basic question.. please tell me the possible apporaches...

    the scenario is:

    SQL Server 2000 has the original database. it gets updated in realtime as its connected with a transactional source...

    now the data needs to be updated on realtime to another remote SQL server [can be any version].. this remote SQL server servers as the data layer of a stereo-type n-tier application..

    both servers are connected over the internet...

    can microsoft sync framework help in this case? if yes, how? what are the performance implications? how reliable this solution will be?


    if no, what are other possible solutions? like making a web-service on the web-server's database and invoking it after a specific time?

    looking forward to the responses and thanks in advance.
    Tuesday, November 4, 2008 4:22 AM

Answers

  • I dont think any solution over the internet will be able to provide you with very less latency because of the flakiness of the connections. I am not sure how performant the web service approach would be, but you should prototype it.

    Transaction replication is good on LAN and can provide you with that latency requirement, but no over internet.

    I think you should prototype the web service approach and the Sync frameowrk approach and see which one is better and go with that.

     

    Friday, November 7, 2008 7:01 AM

All replies

  • Does your original server need to be a SQL Server 2000?

    If it can be SQL Server 2005 or SQL Server 2008, you could try the collaboration scenario of Sync services: http://msdn.microsoft.com/en-us/library/bb726002.aspx

     

    Tuesday, November 4, 2008 6:59 AM
  • thanks mahesh for the reply.

     

     

    yup, the original server needs to be on SQL Server 2000 service pack 3.. actually its integrated with WinCC; so it will be kind of hard to change it.

     

     

     

     

    Wednesday, November 5, 2008 8:22 AM
  • If you want low latency solution and that you cannot change the SQL 2000, you may want to look at transactional replication. However you cannot expect to replicate over the internet as there is no out of the box support for transaction .replication
    Thursday, November 6, 2008 6:23 AM
  • yeah.. i was also thinking on the same lines..

     

    actually im doing a proto-type to simulate the environment over the internet...

     

    in the start, databases can be synchronized by copying files manually.. but after the deployment, it need to be syncrhonized in real time over the internet..

     

    do you think that transactional replication will be a good-enough solution in that case?

     

    how about exposing a web-service on the destination computer and the source computer calls that webservice after 1-2 seconds and updates the data there?

     

    how would you compare both these options?

     

    -- destination computer will actually be a webserver.

     

    Thursday, November 6, 2008 8:14 AM
  • I dont think any solution over the internet will be able to provide you with very less latency because of the flakiness of the connections. I am not sure how performant the web service approach would be, but you should prototype it.

    Transaction replication is good on LAN and can provide you with that latency requirement, but no over internet.

    I think you should prototype the web service approach and the Sync frameowrk approach and see which one is better and go with that.

     

    Friday, November 7, 2008 7:01 AM