How to sync data in multiple instances of a database?

Hi,
I have a question about how to keep the data in multiple instances of a
database in sync.
Our application is a stand alone desktop application, being used in all
of our branches - the exact same app running on the exact same
database, the only difference being the data in the database. The
backend is a SQL Server 2000 database, which is relatively small with
about 50 tables. Each branch would have its own data, and they would
generate reports at regular intervals and send it across to the main
office, and everything was going great. Now, a requirement that the
data of all branches be available to all other branches has come up.
One central database, though very much preferred, is ruled out because
of various reasons - operational, financial... but more importantly to
us developers, the application was designed to talk to the database
very frequently, so remoting will slow down the app which is not
acceptable to the accounting guys who use the app.
I would like to know the options available to us for getting the data
in one database to another. PK columns are all made of UUIDs, so the
problem of duplicate PKs won't be there while importing data of one
branch into another.
I read about publishing/subscription, but can't quite figure out how to
implement: each database needs to be a publisher as well as a
subscriber...
I also thought of using the bcp utility to export the data from each
branch and send it to all other branches, where we could use bcp to
import the data from all other branches, but some problems there: can
only new data be exported, or if not, can bcp be adviced to not copy
duplicate rows while importing? And then there's the question of
getting the exported file across to all the branches automatically...
Any ideas, suggestions are greatly appreciated. Additional info:
branches are quite far away from one another, worst case is about 2100
kms! And there's not a great deal of data: I would guess about a few
hundred rows of data a day.
- RameshThis sounds like a merge replication scenario. Each branch could be a
publisher to the central DB. The central DB could then re-publish to the
branches. You'd have a filter to ensure that a branch is not subscribing to
its own data.
You may want to post this in the .replication newsgroup. Ask for Hilary.
;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ramesh" <dramesh@.rushmorent.com> wrote in message
news:1141154664.627611.186750@.v46g2000cwv.googlegroups.com...
Hi,
I have a question about how to keep the data in multiple instances of a
database in sync.
Our application is a stand alone desktop application, being used in all
of our branches - the exact same app running on the exact same
database, the only difference being the data in the database. The
backend is a SQL Server 2000 database, which is relatively small with
about 50 tables. Each branch would have its own data, and they would
generate reports at regular intervals and send it across to the main
office, and everything was going great. Now, a requirement that the
data of all branches be available to all other branches has come up.
One central database, though very much preferred, is ruled out because
of various reasons - operational, financial... but more importantly to
us developers, the application was designed to talk to the database
very frequently, so remoting will slow down the app which is not
acceptable to the accounting guys who use the app.
I would like to know the options available to us for getting the data
in one database to another. PK columns are all made of UUIDs, so the
problem of duplicate PKs won't be there while importing data of one
branch into another.
I read about publishing/subscription, but can't quite figure out how to
implement: each database needs to be a publisher as well as a
subscriber...
I also thought of using the bcp utility to export the data from each
branch and send it to all other branches, where we could use bcp to
import the data from all other branches, but some problems there: can
only new data be exported, or if not, can bcp be adviced to not copy
duplicate rows while importing? And then there's the question of
getting the exported file across to all the branches automatically...
Any ideas, suggestions are greatly appreciated. Additional info:
branches are quite far away from one another, worst case is about 2100
kms! And there's not a great deal of data: I would guess about a few
hundred rows of data a day.
- Ramesh|||Does each brach really need a complete copy of transactional data from all
other branches, or do they simply need summary data for inclusion in
soncolidated reports?
"Ramesh" <dramesh@.rushmorent.com> wrote in message
news:1141154664.627611.186750@.v46g2000cwv.googlegroups.com...
> Hi,
> I have a question about how to keep the data in multiple instances of a
> database in sync.
> Our application is a stand alone desktop application, being used in all
> of our branches - the exact same app running on the exact same
> database, the only difference being the data in the database. The
> backend is a SQL Server 2000 database, which is relatively small with
> about 50 tables. Each branch would have its own data, and they would
> generate reports at regular intervals and send it across to the main
> office, and everything was going great. Now, a requirement that the
> data of all branches be available to all other branches has come up.
> One central database, though very much preferred, is ruled out because
> of various reasons - operational, financial... but more importantly to
> us developers, the application was designed to talk to the database
> very frequently, so remoting will slow down the app which is not
> acceptable to the accounting guys who use the app.
> I would like to know the options available to us for getting the data
> in one database to another. PK columns are all made of UUIDs, so the
> problem of duplicate PKs won't be there while importing data of one
> branch into another.
> I read about publishing/subscription, but can't quite figure out how to
> implement: each database needs to be a publisher as well as a
> subscriber...
> I also thought of using the bcp utility to export the data from each
> branch and send it to all other branches, where we could use bcp to
> import the data from all other branches, but some problems there: can
> only new data be exported, or if not, can bcp be adviced to not copy
> duplicate rows while importing? And then there's the question of
> getting the exported file across to all the branches automatically...
> Any ideas, suggestions are greatly appreciated. Additional info:
> branches are quite far away from one another, worst case is about 2100
> kms! And there's not a great deal of data: I would guess about a few
> hundred rows of data a day.
> - Ramesh
>|||Hi JT,
Each branch needs a complete copy of all the data from all the
branches, please don't ask me why. It is as if there is one central
database and every branch is connected to it, except that there are
multiple copies of the data, one at each branch. (:
- Ramesh|||Hi Tom,
May be you are right; I should post this question in the other group.
Let me read about merge replication a bit before posting it there.
Thanks anyway.
- Ramesh