CODEWITHSUNDEEP
.netsql-servervb6
kelvinfix
kelvinfix

Reputation: 3075

How to sync two sql-server tables from different location?

I have a database at HQ and another one at branch. Both database is identical, all the schema is the same. It could be more than one branch.

For example, the branch has a local database with a table called Sales, all transaction will be inserted into the table. Every night, when the shop is close, all the sales information from the branch will be synchronize with HQ database. Only the record not in the HQ database will be inserted or updated.

How can I do it in a very efficient method, small and light, reconnect when the connection is down, because the internet bandwidth is limited and constantly dropping.

How can I do synchronize from HQ to all branches with the latest products and price?

Should I write a web services?

Thank you.

Upvotes: 0

Views: 9273

Answers (4)

Tom Collins
Tom Collins

Reputation: 4069

I've use xSQL Software in the past and was very pleased with the results. You can replicate the schema and/or the data in either direction, or even both directions.

Upvotes: 0

java4script
java4script

Reputation: 557

Are the two locations inside the same local network? And do you require the tables to be in sync only once in a while (e.g. nightly instead of real-time)?

If the answers are both yes, then you can use sql server's tablediff.exe command line utility. It can do table-by-table, one-off compare between two tables and generate the sql automatically for you to sync the dest to the source.

There's also a GUI wrapper around it http://code.google.com/p/sqltablediff/ which makes the job even easier.

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32697

I came here to say "use merge replication" as what you've described is more or less a textbook example of when you'd implement it.

Upvotes: 0

Jakub Konecki
Jakub Konecki

Reputation: 46008

I would suggest SQL Server replication. You won't have to write any code.

http://msdn.microsoft.com/en-us/library/ms151198.aspx

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Update:

Since replication is not possible as the db is deployed to the client, than I would use Sync Framework.

http://msdn.microsoft.com/en-us/sync/bb821992

Microsoft Sync Framework is a comprehensive synchronization platform enabling collaboration and offline for applications, services and devices. Developers can build synchronization ecosystems that integrate any application, any data from any store using any protocol over any network. Sync Framework features technologies and tools that enable roaming, sharing, and taking data offline.

Upvotes: 3

Related Questions