October 1, 2003 at 5:14 pm
Greetings,
I would like to coalesce the data from a number of "satelite" databases
to a centralized database via replication. Some of those satelite databases
use MSDE and others SQL Server Standard/Enterprise edition. All databases
share the same schema and updates/inserts/deletes can only occur on the
satelite databases. In addition, the data should flow only from the satelite
databases to the centralized one. In other words, each satelite database
would show its own subset of data.
My initial idea was to use transactional replication, since it is the
model that I know and have used the most :-). I would set up a publication
for each of the satelite databases and have the centalized server to
subscribe to each of them. However, this model does not seem natural and
has clear drawbacks:
* I am not sure if I can publish a database under MSDE.
* It would be cumbersome to set up and troubleshoot all those publications.
* Some of the satelite databases reside in "laptops", which are not always
plugged in the network (How would the central database figure out when to
subscribe?).
I have heard that merge replication would be a better choice for that kind of scenario but I have some basic practical questions:
1. Where do I define the publication? In the central server or in each satelite database? Pull or push subscription?
2. How do I prevent the data to be sent back to the other satelite databases?
3. What kind of licensing would be required in each satelite database and the central server? Remember that many of the satelite database use MSDE.
Thank you
CD
October 2, 2003 at 6:26 am
Merge replication is likely the best candidate for the scenario you describe. I suggest you read everything about merge replication you can find in Books-On-Line, MSDN, etc. Merge replication is a bit more complicated than transactional. If you use a centralized publisher, then each of your satelite databases can be its clients. Also read up on push vs pull subscriptions so you can make an intelligent choice on which way to go with this.
October 2, 2003 at 9:29 am
Do you know how I can prevent the data to be sent back to the other satelite databases?
Thank you
CD
quote:
Merge replication is likely the best candidate for the scenario you describe. I suggest you read everything about merge replication you can find in Books-On-Line, MSDN, etc. Merge replication is a bit more complicated than transactional. If you use a centralized publisher, then each of your satelite databases can be its clients. Also read up on push vs pull subscriptions so you can make an intelligent choice on which way to go with this.
October 2, 2003 at 1:28 pm
You can use a subset_filter clause that goes something like this ... @subset_filterclause = N'Upper(SomeTable.TerminalID) = Upper(Host_Name())
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply