MS SQL Server Replication - Transaction replication,

Author

Transaction replication,

Veasna Much

2006-02-14, 3:23 am

Hello,

I plan to setup an transaction replication for my project but I am very
new to this. I have read some documents but still not so clear on how do it.
I know that some of you have been sucessfull implement this type of
replication. Just setting up the replication I think that is not so hard but
the way we do for backing up and restore the replication data base is the
problem.

My configure is Publisher/Distributor in one server and 2 subsribers. How to
configure replication so that we can restore the backup data in minimum
time. Guide or sample would be very please.

Best reagrds,

Veasna

Paul Ibison

2006-02-14, 7:23 am

Veasna,
this guide should help a bit:
http://msdn2.microsoft.com/en-us/library/ms152560.aspx
Apart from that, I tend to enable anonymous subscribers and have a high
retention period to ensure there is a decent history of commands in the
distribution database. That way I don't worry about the subscriber backup
being a little old. Apart from that I just ensure that the publisher log
backup occurs before the distribution one (and the distributor one just
before the subscriber one). This way the publisher is always more upto date
and I don't have to bother skipping errors.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Veasna Much

2006-02-14, 8:25 pm

Thanks for your advise, throught what you have guide me to do , I have
notice that in the reference document they have forcused on setting sync
with backup to true for publisher and distributor but as what the configure
I have had (publisher and subscriber is in single server), how would I do
for that?

If I use transaction replication and I backup master, msdb, and my_db along
with dailly full backup and hours log backup of(my_db), is that enough to
recover the data if any failure accur?

Your help is very important for me...

Best regards,

Veasna

Paul Ibison

2006-02-15, 3:24 am

Veasna,
you'll also need to backup the distribution database.
The sync with backup flag on the publisher is something I've never been able
to set because of the latency this introduced when delivering the commands
from the publisher to the subscriber. Provided you know how to do a
point-in-time restore, backup regularly and can accept that on restore the
publication database might have to 'go back in time' a bit, this shouldn't
be a problem. Actually this is usually an inevitable situation anyway as
when the publication server goes down you can't get hold of the live log, so
some production data will be lost anyway. You just have to be able to
restore all the other databases to a point in time before the publication
backup and then let replication catch up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Veasna Much

2006-02-17, 7:25 am

Could u tell me the guide how to set the set "sync with backup" flag on
distributor to true? As you have known my configuratuin is publisher and
disctibutor is in a single SQL Server.

If we dont set "sync with backup" to true, what might have accur while we
restore the database?

Best regards,

Veasna

Paul Ibison

2006-02-17, 7:25 am

Veasna,
here is the link you'll need:
http://msdn.microsoft.com/library/d...backup_4cku.asp
From BOL: "Unlike the publication database, setting the sync with backup
option on the distribution database has no effect on replication latency,
but it will delay the truncation of the log on the publication database
until the corresponding transactions in the distribution database have been
backed up."
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Sponsored Links