September 9, 2003 at 4:10 pm
Dear Friends,
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, I do not want data to move back to each satelite database (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 was wondering if other models, e.g. merge replication, would be more
suitable.
I would very much appreciate your opinon and comments regarding the best
replication model to achieve this goal. Also, I would appreciate if you
could comment on what kind of licensing it will be required as far as SQL
server goes.
Thank you
CD
September 10, 2003 at 5:41 am
Hi there,
Some factors that you need to keep in mind are the number of users that can use MSDE at any time ( I think it's five )
and the database size limit of 2 GB ? MSDE also does not support Transactional Replication.
I would maybe look at Merge Replication for your topology. Merge replication is very scalable when it comes to laptops and disconnected users. Conflicst can be centralized and you can also specify/create your own custom resolvers.
You could maybe look at setting up Push Subscriptions as opposed to pulling the data, that way when the laptop publisher is online it will push the data to your centralized server.
To ensure that the data flows only inbound from the satellite publishers to the centralized server you could look at using filtering and dynamic snapshots ? Possibly even an -ExchangeType switch on the merge agent specifying that replication must only download data to the subscribing Centralized Server.
Hope this helps
CHEERS
Jon
CHEERS
Jon 🙂
September 10, 2003 at 5:37 pm
Hi Jon,
Thank you very much for your reply.
The 2GB database size limit and the 5 concurrent users under MSDE do not pose a limitation to our application.
It is strange how the terms publisher and subscriber take an interesting switch when we are dealing with merge replication. In transactional replication the publisher is the originator of the data and the subscriber is the reader, i.e. the data moves from the publisher to the subscriber.
Using merge replication (and please correct me if I am out of line here) the central database would be the publisher but the data would flow from the satelite databases via push subscriptions.
I am most interested in how to ensure that the data flows from the satelite databases to the central one. Could you ellaborate a little more on filtering and dynamic snapshots and how to accomplish that? Just for your information - and I don't know if it makes any difference - the data in one satelite database is completelly different from the others.
Thanks
CD
quote:
Hi there,Some factors that you need to keep in mind are the number of users that can use MSDE at any time ( I think it's five )
and the database size limit of 2 GB ? MSDE also does not support Transactional Replication.
I would maybe look at Merge Replication for your topology. Merge replication is very scalable when it comes to laptops and disconnected users. Conflicst can be centralized and you can also specify/create your own custom resolvers.
You could maybe look at setting up Push Subscriptions as opposed to pulling the data, that way when the laptop publisher is online it will push the data to your centralized server.
To ensure that the data flows only inbound from the satellite publishers to the centralized server you could look at using filtering and dynamic snapshots ? Possibly even an -ExchangeType switch on the merge agent specifying that replication must only download data to the subscribing Centralized Server.
Hope this helps
CHEERS
Jon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply