Share via

Database Synchronisation/Replication

Tammen 26 Reputation points
2023-09-18T04:52:35.56+00:00

Hi, I was wondering what some of the best ways to synchronise data stored on a table in a local database with an online server. As I am developing an application that will be ran on multiple devices and will store the data collected in a local database, I will then need to send the data from each database to one main database with each device having its own table. I was wondering what the best way would be to go about this either using SQL or if there is a better way. Advie would be very appreciated

Thank You.

Developer technologies | Windows Forms
Azure SQL Database
SQL Server | Other
Accepted answer
  1. GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator
    2023-09-21T14:27:52.8833333+00:00

    @Tammen I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

    Issue:

    • looking for ways to synchronize data stored on a table in a local database with an online server.you are developing an application that will be run on multiple devices, and each device will store the data collected in a local database. you need to send the data from each database to one main database with each device having its table. you are sking for advice on the best way to go about this, either using SQL or if there is a better way.

    Solution:

    • To achieve the desired outcome, I stored my local database table in a WinForms dataset. Since my database will only have a few hundred rows, this approach worked well. Then, I inserted the data into the online database using a different connection.

    If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

    Regards

    Geetha

    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2023-09-18T21:10:35.6566667+00:00

    Service Broker could be a solution, but there is certainly a hurdle to get going. With Service Broker you can send asynchronous messages, and you can also get acknowledgements that data has been received.

    This book is great for getting started with Service Broker: https://www.amazon.com/Rational-Server-Service-Broker-Guides/dp/1932577270/ref=sr_1_1?crid=27TMRU5RRZSA1&keywords=roger+wolter+service+broker&qid=1695071297&sprefix=roger+wollter+service+broker%2Caps%2C152&sr=8-1

    It's just over 200 pages, and it is written by the Program Manager who was responsible for the development of the feature. Yes, the book is a tad old, but there have only minor enhancements to Service Broker since.

    And I like to emphasise that could. Take a look and then decide if it fits you.

    1 person found this answer helpful.
  2. Alberto Morillo 34,676 Reputation points MVP Volunteer Moderator
    2023-09-19T01:22:48.6+00:00

    If you can use SQL Server Express on your devices, then you can use SQL Data Sync where you can configure synchronization between each device with SQL Server installed (we called a member database) and a hub central database that will be an Azure SQL Database. The setup you need to perform between each device and the hub database is this one.

    The synchronization can be unidirectional or bi-directional with a frequency of seconds, minutes, hours, days or manual.

    1 person found this answer helpful.
    0 comments
  3. Olaf Helper 47,516 Reputation points
    2023-09-18T05:15:29.73+00:00

    I was wondering what the best way

    "The best" never exists, if there would be just one way: The best.

    Have a look at SQL Server Replication, Transaction or Merge Replication could be a solution for you.

  4. SSingh-MSFT 16,371 Reputation points Moderator
    2023-09-18T06:51:47.7466667+00:00

    Hi
    Tammen     •,

    Welcome to Microsoft Q&A forum.

    Could you please help us with below mentioned details:

    1). Are you using Azure SQL Database?

    2). What are the different types of Database used?

    3). Did you check SQL Data Sync and Transactional Replication options in Azure SQL DB?

    If yes, let us know where you are stuck so that we can help you.

    Thanks

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.