As I am planning to implement replication at one of our client. Since I need
to replicate only once per day from each server located across the globe. I
figured out that Snapshot replication is best.
I would like to know what kind of replication is feasible for this scenario.
I need to replicate from multiple servers across different parts of the
world to one central server. The central server is the subscriber and the
remaining are different publishers, would it be better to have Pull
subscription or push subscription?
In pull subscription I have an advantage and disadvantage. The disadvantage
is that in the pull subscription, the subscriber requires a shared folder
from the publisher to fetch the data from the distributor (publisher &
distributor in one PC), I was not successful in fetching the data from the
shared folder in one of our test environment. Advantage is that pull
subscription can be controlled (Time flexibility to pull the data) at one
central place i.e from the central subscriber.
I was successful in push subscription in my test environment. Advantage,
subscriber doesnt require a shared folder from the publisher(distributor).
disadvantage, No central control of the replication.
Plz suggest
Plz suggest
Hello Affy,
I would suggest central subscriber Vs multiple publisher model. As your
requirement is limited to once in a day Snapshot would be fine depending on
the size of the database. Now I suggest Push subscription as you do have
multiple site and in Push replication SQL Server use resource of Publisher
and not subscriber. Hence you will have less burden on the central
subscriber. In Pull case central server has to pull the data from all the
subscriber and there might be some performance issue else you need to
consider the replication load and hardware of the central subscriber must be
sufficient to take the load.
Still you need to evaluate your requirement and study various replication
topology to set up replication.
Cheers,
MB
"Affy" <Affy@.discussions.microsoft.com> wrote in message
news:99A896C8-80C9-4E8D-8B6B-5C9B390FA11E@.microsoft.com...
> As I am planning to implement replication at one of our client. Since I
> need
> to replicate only once per day from each server located across the globe.
> I
> figured out that Snapshot replication is best.
> I would like to know what kind of replication is feasible for this
> scenario.
> I need to replicate from multiple servers across different parts of the
> world to one central server. The central server is the subscriber and the
> remaining are different publishers, would it be better to have Pull
> subscription or push subscription?
> In pull subscription I have an advantage and disadvantage. The
> disadvantage
> is that in the pull subscription, the subscriber requires a shared folder
> from the publisher to fetch the data from the distributor (publisher &
> distributor in one PC), I was not successful in fetching the data from the
> shared folder in one of our test environment. Advantage is that pull
> subscription can be controlled (Time flexibility to pull the data) at one
> central place i.e from the central subscriber.
> I was successful in push subscription in my test environment. Advantage,
> subscriber doesnt require a shared folder from the publisher(distributor).
> disadvantage, No central control of the replication.
> Plz suggest
> Plz suggest
|||Are you replicating to the same table? If so, snapshot won't work and you'll
need transactional. Transactional with multiple publishers and one central
subscribers is ok if the data is partitioned - is that the case? If so then
this article might help:
http://www.replicationanswers.com/CentralSubscriberArticle.asp
As for push or pull, I'd consider where you want to do the work and where
you want to do the maintenance, which are the competing factors.
BTW there is another topology that could be used in this case - have the
reporting server set up as a merge publisher and the other computers set up
as merge subscribers. If the data load is light, the performance problems of
using this method are outweighed by the fact that administration is more
simplified.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for the reply MB. I happen to study different topolgies but central
subscriber is the closer one where as republisher, central publisher,CP with
remote distributor. Its multiple publishers to a single subscriber with
publishers located in geographically different locations like eg
Shanghai,Moscow,Malaysia,Australia. The data is just a table that is being
sent to the subscriber on a daily basis. Maximum records per country could
come around 6000 records (but they are proned to increase but slowly). but
using Push will have all the configuration available at the publisher, so the
subscriber has no control over it right? But I am scared about Pull because
there can be firewall issues when the distributors are located at multiple
locations and fetching the data from the shared folder(distributor) will be
most toughest thing? am i right with this?
"MB" wrote:
> Hello Affy,
> I would suggest central subscriber Vs multiple publisher model. As your
> requirement is limited to once in a day Snapshot would be fine depending on
> the size of the database. Now I suggest Push subscription as you do have
> multiple site and in Push replication SQL Server use resource of Publisher
> and not subscriber. Hence you will have less burden on the central
> subscriber. In Pull case central server has to pull the data from all the
> subscriber and there might be some performance issue else you need to
> consider the replication load and hardware of the central subscriber must be
> sufficient to take the load.
> Still you need to evaluate your requirement and study various replication
> topology to set up replication.
> Cheers,
> MB
>
> "Affy" <Affy@.discussions.microsoft.com> wrote in message
> news:99A896C8-80C9-4E8D-8B6B-5C9B390FA11E@.microsoft.com...
>
>
|||Thanks Paul for your reply. I happen to read the topology article which you
referred. It was pretty neatly explained. Since my requirement doesnt meet
with amalgamation of data from multiple publisher. But the transactional
replication will happen even if there is a small update or insert happend,
which is not required in my scenario as well, its just one time per day dump.
Why would the snapshot replication from multiple destinations to the same
table will not work? Let me explain the plan which i have, All the publishers
to the central subscriber are subscribed at different times and it will
happen once in a day for each publisher to push the data to a table and then
there will be a job (at the subscriber end) which will process the data in
the table and once processed and inserted in to another database, after which
entire table is cleared, so the next country's data will be pushed by another
publisher. Will this scenario would not fit for Snapshot? I would like to ask
one more question Paul. If I need control on the subscriber end, like
when(time adjustment) the publisher pushes the data, then which one to chose
Push or Pull? But Pull has some disadvantages with firewall and other issues
with sharing data from the distributor right? Also Paul can you send me any
reference article on Merge publisher & Merge subscriber topology which could
explain me neatly just like the one you sent before.
Thanks
Aftab
"Paul Ibison" wrote:
> Are you replicating to the same table? If so, snapshot won't work and you'll
> need transactional. Transactional with multiple publishers and one central
> subscribers is ok if the data is partitioned - is that the case? If so then
> this article might help:
> http://www.replicationanswers.com/CentralSubscriberArticle.asp
> As for push or pull, I'd consider where you want to do the work and where
> you want to do the maintenance, which are the competing factors.
> BTW there is another topology that could be used in this case - have the
> reporting server set up as a merge publisher and the other computers set up
> as merge subscribers. If the data load is light, the performance problems of
> using this method are outweighed by the fact that administration is more
> simplified.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||What I'm wondering is how you will prevent duplicates - data being
snapshotted once one day and again the next day. Are you filtering somehow
and then updating the filter column after replication on the publisher. Or
are you dropping the table in the first publisher which will create the
table on the subscriber? I'm not too sure why there are complications with
pull through the firewall - just more configuration of IP addresses I
suppose. For merge I wouldn't set up this topology - I'd have the central
server as the publisher and not the subscriber. As it doesn't really follow
the publisher/subscriber metaphor this will work ok.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Actually in order to avoid duplication, We have a unique code which is sort
of universal combined with country code also we are actually deleting the
records after one publsiher has issued one snapshot of the table. So by the
next publisher publishes a copy of the records, the previous set of records
would have got updated. Is there are any article for Pull subscription, with
extra IP settings for Firewall?
"Paul Ibison" wrote:
> What I'm wondering is how you will prevent duplicates - data being
> snapshotted once one day and again the next day. Are you filtering somehow
> and then updating the filter column after replication on the publisher. Or
> are you dropping the table in the first publisher which will create the
> table on the subscriber? I'm not too sure why there are complications with
> pull through the firewall - just more configuration of IP addresses I
> suppose. For merge I wouldn't set up this topology - I'd have the central
> server as the publisher and not the subscriber. As it doesn't really follow
> the publisher/subscriber metaphor this will work ok.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||OK - I 'think' I understand your topology. I'd personally use transactional
in preference to this setup though as you are continually sending some of
the same data. Am not too sure what the last question means: "Is there are
any article for Pull subscription, with extra IP settings for Firewall?".
Ports 1433 (for default instance) and 21 (FTP) are usually sufficient, then
the IP addresses need to be fixed and registered in the firewall.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
