distribution sync with backup

hi,
i am not certain what is purpose when the distribution
database is configured with sync with backup. i understand
from the help online that this will not truncate the
publication database log.
however, i can't achieve this.
this is what i did in my testing.
1) configure distribution and publication db with sych
with backup.
2) Backup the distribution db.
3) Add a record in the publication db.
4) Backup the publication db.
5) Make sure that the record is replicated in the
subscriber db.
6) delete the record in the subscriber db.
7) Restore the distribution db from the backup.
8) Backup the publication db.
i expect the missing record to be replicated to the
subscriber db. but it didn't. anything i missed out? or is
my understanding wrong?
thanks for any help.
-
leonghio (leonghio@.yahoo.com.sg)
This option prevents the backup of the distribution database getting ahead
of the backup of the publishing database. You need to add the record then do
the backups - publishing db first then distributor. Next delete the record
on the subscriber then do the restores and it should work ok with the new
record going down.
Regards,
Paul Ibison
|||i tried the following steps but still can't get the
results.
1) make sure both publication and distribution are sync
with backup
2) add a record to publication.
3) log backup publication.
4) full backup distribution.
5) delete the record from subscriber.
6) stop replication agents and restore distribution.
7) start replcation agents.
8) log backup publication again to re-initiate replication.
but record is not replicated again to the subscriber.
what seems to be the problem? am i getting the steps wrong?
thanks!
-
leonghio(leonghio@.yahoo.com.sg)

>--Original Message--
>This option prevents the backup of the distribution
database getting ahead
>of the backup of the publishing database. You need to add
the record then do
>the backups - publishing db first then distributor. Next
delete the record
>on the subscriber then do the restores and it should work
ok with the new
>record going down.
>Regards,
>Paul Ibison
>
>.
>
|||If the record has already gone to the subscriber it won't
be sent again. This is because MSreplication_subscriptions
holds a column called transaction_timestamp which is the
last one sent down. This value can be obtained from
msrepl_commands (the last 8 zeros aren't always seen), so
it would be possible to force the change by updating
MSreplication_subscriptions yourself to a previous
transaction if there is one.
HTH,
Paul Ibison
|||the way the sync with backup setting works is
1) a transaction is entered in the published databases
2) the transaction is written to the transaction log
3) the transaction log is dumped and during the dump the log reader will
then read the transaction from the log and write it to the distribution
database
"leonghio" <anonymous@.discussions.microsoft.com> wrote in message
news:0a5001c4317f$efbf3630$7d02280a@.phx.gbl...
> hi,
> i am not certain what is purpose when the distribution
> database is configured with sync with backup. i understand
> from the help online that this will not truncate the
> publication database log.
> however, i can't achieve this.
> this is what i did in my testing.
> 1) configure distribution and publication db with sych
> with backup.
> 2) Backup the distribution db.
> 3) Add a record in the publication db.
> 4) Backup the publication db.
> 5) Make sure that the record is replicated in the
> subscriber db.
> 6) delete the record in the subscriber db.
> 7) Restore the distribution db from the backup.
> 8) Backup the publication db.
> i expect the missing record to be replicated to the
> subscriber db. but it didn't. anything i missed out? or is
> my understanding wrong?
> thanks for any help.
> -
> leonghio (leonghio@.yahoo.com.sg)

Distribution DB constantly grows

Hi,
I have set up transactional replication for a database from one db
server to a second. The replication is working fine but the distribution
database constantly grows. The size of the replicated db is 524 MB, the
size of the distribution db is 24 GB, growing daily.
How can I shrink my distribution database and how can I limit the growth
without damaging the replication?
Markus
you need to maintain the tlog on the distribution database. Make sure it
using the full recovery and dump the tlog every 5 minutes or so. You might
want to backup the log with truncate_only, shrink the tlog, and then backup
the database.
Then check your transaction retention period, it should be 48 hours. Make
sure your distribution clean up job is enabled and running every 10 minutes.
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
"Markus Renschler" <SP-news@.renschler.net> wrote in message
news:%23uv0qzXdFHA.2420@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have set up transactional replication for a database from one db server
> to a second. The replication is working fine but the distribution database
> constantly grows. The size of the replicated db is 524 MB, the size of the
> distribution db is 24 GB, growing daily.
> How can I shrink my distribution database and how can I limit the growth
> without damaging the replication?
> Markus
|||Hi Hilary,
thanks for the advice.

> you need to maintain the tlog on the distribution database. Make sure it
> using the full recovery and dump the tlog every 5 minutes or so. You might
> want to backup the log with truncate_only, shrink the tlog, and then backup
> the database.
The distribution database's backup model is set to simple. Is this a
problem (except the worse disaster recovery options)?

> Then check your transaction retention period, it should be 48 hours. Make
> sure your distribution clean up job is enabled and running every 10 minutes.
The transaction retention period has been set to the default value
(72h). I changed it to 48h. Then I checked the distribution cleanup job.
It is scheduled to run every 10 minutes. It had been started 5 hours ago
and was still running. I stopped it and started it again. Now it is
running since 15 Minutes. Is it possible that this job lasts more than 5
hours?
Markus
|||It is possible that it could run for more than 5 hours at first. Subsequent
runs should not take as long. Do you have anonymous subscribers? Metadata
hangs around a lot longer for them than for names subscribers.
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
"Markus Renschler" <SP-news@.renschler.net> wrote in message
news:uzVIaOZdFHA.3488@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Hi Hilary,
> thanks for the advice.
might[vbcol=seagreen]
backup[vbcol=seagreen]
> The distribution database's backup model is set to simple. Is this a
> problem (except the worse disaster recovery options)?
Make[vbcol=seagreen]
minutes.
> The transaction retention period has been set to the default value
> (72h). I changed it to 48h. Then I checked the distribution cleanup job.
> It is scheduled to run every 10 minutes. It had been started 5 hours ago
> and was still running. I stopped it and started it again. Now it is
> running since 15 Minutes. Is it possible that this job lasts more than 5
> hours?
> Markus
|||Hilary Cotter wrote:
> It is possible that it could run for more than 5 hours at first. Subsequent
> runs should not take as long.
It has been running 3:35. The next runs took less than a second, each.
After the cleanup the database was still 22GB in size. I tried to shrink
it, but without an effect.
For testing, I have set the transaction retention period to 1h. Then I
started a distribution database cleanup, but without an effect.

> Do you have anonymous subscribers? Metadata
> hangs around a lot longer for them than for names subscribers.
No, I just have one subscriber which had its subscription pushed from
the origin server (which acts as publisher and distributor).
Could it be helpful if I re-create the distribution database? Is there
any possibility to do this?
Thanks for helping,
Markus