Replication to multiple subscribers question

We have an application that runs with MS SQL 2000 (SP3a) which is used for
tracking information from multiple sources. We are shortly going to be
setting up replication of this database with a secondary location, with a
third location planned by mid year. We know that there will be other
locations added over the course of the next few years. Our concern is that
if we have the snapshot expire, we will have to regenerate the snapshot and
reapply it to ALL subscribers before we can add a new subscriber. This is a
problem for 2 reasons. First because the foreign key relationships we have
between many of the tables appears to require that we delete the database on
the subscriber before we can apply the snapshot, and second because the
nature of our subscribers will mean that most of them will be disconnected
for long periods and as the number of subscribers grows it will be
increasingly unlikely that we will have them all connected at the same time.
It appears that our other option is to not have the snapshot expire, but MS
warns that this may cause performance problems but I haven't seen anything
that quantifies the potential performance problems.
Can anyone tell me what the "right" way of doing this is? What sort of
performance problems are we looking at if we don't have the snapshot expire?
Our database consists of approximately 100 or so user tables and currently
is about 300MB of data.
TIA
Ron L.
Ron,
I suspect the performance problems being talked about are proportional to
the amount of changes taking place to the data once the snapshot has been
made. EG if your snapshot contained static lookup tables, then having a
long-lived snapshot file wouldn't really be an issue. However if your
snapshot was of a productlisting table containing 1 million rows, and
product prices were changed on a regular basis (my case) then after
application of the snapshot you might find your merge agent (or distributor)
would struggle to send down the accumulated changes. BTW, in this case the
problem would be least for merge assuming records could be repeatedly
changed, and it avoids the issue of not being able to run the distribution
cleanup agent that you'll have.
HTH,
Paul Ibison
|||Paul
Thanks for the response. My data tends to be less changes and more
additions - we have logging records for text logs and records tracking
movement of parts within systems. At the moment, our largest table has just
over 100,000 records and the next largest is in the 20,000 record range.
One other option we were looking at was to have seperate publications
for each (or a small group of) additional server, thus making the snapshot
regeneration have less impact. Do you have any feeling as to whether this
is a better approach?
Thanks,
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23FVkMhWLEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Ron,
> I suspect the performance problems being talked about are proportional to
> the amount of changes taking place to the data once the snapshot has been
> made. EG if your snapshot contained static lookup tables, then having a
> long-lived snapshot file wouldn't really be an issue. However if your
> snapshot was of a productlisting table containing 1 million rows, and
> product prices were changed on a regular basis (my case) then after
> application of the snapshot you might find your merge agent (or
distributor)
> would struggle to send down the accumulated changes. BTW, in this case the
> problem would be least for merge assuming records could be repeatedly
> changed, and it avoids the issue of not being able to run the distribution
> cleanup agent that you'll have.
> HTH,
> Paul Ibison
>
|||Ron,
I agree that this approach leads to more granularity and therefore is more
versatile for your needs, As long as the publications are distinct units
then it should be OK - eg if there are PK-FK relationships across
publications then it's a no-go, so it depends how related the tables are.
Regards,
Paul
|||Paul
What we are thinking is multiple publications covering the same set of
tables. Is this not going to work?
Ron L
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e454$eeLEHA.1484@.tk2msftngp13.phx.gbl...
> Ron,
> I agree that this approach leads to more granularity and therefore is more
> versatile for your needs, As long as the publications are distinct units
> then it should be OK - eg if there are PK-FK relationships across
> publications then it's a no-go, so it depends how related the tables are.
> Regards,
> Paul
>
|||Ron,
I've not heard of this arrangement before, but it sounds feasible with merge
replication.
I guess the only downside is increased maintenance.
Cheers,
Paul

replication over a wan

Howdy kids. We are moving our servers to a new location here shortly. One of
the servers is a replicated reporting box. There is an internal ongoing
debate on if we should move this box or not. I have a 25 gig database. The
two buildings are about 40 miles apart from each other. The replication
configuration is as follows:
Transactional.
Remote Distributor. (on the Subscriber)
NOT immediate updating.
Are there any issues with this type of configuration over a WAN? Any gotchas
that wouldn't be had on a local network that could happen here?
SQL2K SP3
TIA, ChrisR
If your subscriber (and consequently your distributor) goes down your tlog
on your publisher will balloon like a balloon. This ballooning tlog will
cause performance problems on the publisher, and can eat up all your space
on the publisher. I would advise you to use a local distributor - preferably
a distributor on the same box as the publisher.
Also you have to check your effective bandwidth of your WAN. Low bandwidth
can cause log reader performance problems leading to ballooning tlogs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <bla@.noemail.com> wrote in message
news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
> Howdy kids. We are moving our servers to a new location here shortly. One
of
> the servers is a replicated reporting box. There is an internal ongoing
> debate on if we should move this box or not. I have a 25 gig database. The
> two buildings are about 40 miles apart from each other. The replication
> configuration is as follows:
> Transactional.
> Remote Distributor. (on the Subscriber)
> NOT immediate updating.
> Are there any issues with this type of configuration over a WAN? Any
gotchas
> that wouldn't be had on a local network that could happen here?
> --
> SQL2K SP3
> TIA, ChrisR
>
|||Thanks Hillary. If I switch to a Local Distributor, should I switch from a
Pull to Push Subscription as well? It would make sense to me, what do you
think?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
> If your subscriber (and consequently your distributor) goes down your tlog
> on your publisher will balloon like a balloon. This ballooning tlog will
> cause performance problems on the publisher, and can eat up all your space
> on the publisher. I would advise you to use a local distributor -
preferably[vbcol=seagreen]
> a distributor on the same box as the publisher.
> Also you have to check your effective bandwidth of your WAN. Low bandwidth
> can cause log reader performance problems leading to ballooning tlogs.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ChrisR" <bla@.noemail.com> wrote in message
> news:uwfPwhiCFHA.328@.tk2msftngp13.phx.gbl...
One[vbcol=seagreen]
> of
The
> gotchas
>
|||Your choice of push or pull is irrelevant in this case.
Use push when you have small numbers of subscribers, you want a central
point of administration on your publisher, and your subscribers are normally
online and well connected.
Use pull when you have large numbers of subscribers, you don't care about a
central point of administration because you are using replication monitor
group, and your subscribers are not always connected.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ChrisR" <bla@.noemail.com> wrote in message
news:%23PqR59jCFHA.4052@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Thanks Hillary. If I switch to a Local Distributor, should I switch from a
> Pull to Push Subscription as well? It would make sense to me, what do you
> think?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e#XCZQjCFHA.3936@.TK2MSFTNGP09.phx.gbl...
tlog[vbcol=seagreen]
space[vbcol=seagreen]
> preferably
bandwidth[vbcol=seagreen]
> One
ongoing[vbcol=seagreen]
> The
replication
>

Replication mechanism

Hello,
Can anybody here explain me shortly how the mechanism of replication
generally works? (with SQL Server CE Agent, SQL Server Agent, etc.)
thx
robert
Robert,
the 'SQL Server CE documentation' has a good set of descriptions and BOL
will explain standard replication. Is there something specific you have in
mind?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||With merger replication each row has a tracking column called a rowguid
column. Each row has a different value for this rowguid column, but
globally tables in the publisher and all subcribers which contain this
row will have the same value for this guid. This enables the merge
replication process to track changes to this row. This of it as a
Social Security Number, or Social Insurance Number (not sure what the
non American, non Canadian equivalents are).
Each table which is part of a merge publication has triggers of it, so
if one of the rows is modified tracking information is written to
tracking tables.
When the merge agent runs (run by SQL Server Agent) it consults these
tracking tables to determine what has changed since the last time it
has run. It has a counter called a generation which increments each
time the merge agent runs. It then gets a list of the rows which has
changed since the last time it ran and brings them to the publisher in
batches.
When the list of rows arrives at the publisher the publisher compares
its list with the list of changes it has and figures out what goes
where. If changes have to be made on the subsciber it fires procs there
to make these changes, if changes have to be made locally it uses sql
statements. If it detects that the same rows have changed on both the
subscriber and publisher it figures out who wins by using the conflict
resolution policy and logs the conflict info to conflict tracking
tables.
It then gets the next batch.
SQL CE does much the same thing - only it is done programmatically.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com