Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
joanne
Starting Member
46 Posts |
 Posted - 2005-08-05 : 08:27:36
|
| Hello, My company plan to use an application that will use a transactional replication (on the same server!!!).For me is for the first time when I use the replication. Can someone explain me how work a disaster recovery plan in this case? I know that in the replication are 3 important databases: publisher, subscriber and distribution. Which database is necessary to restore if I have a problem? There are other things to do for synchronization? Somebody can explain me the ' sync with backup' option (when this option should be used and how)?Many thanks in advance |
|
|
mblythe
Starting Member
16 Posts |
Posted - 2005-08-05 : 16:20:03
|
The backup restore guidance in SQL Server 2000 BOL is not complete. I have updated docs that I can send you. If you would like these, send me email (mblythe at microsoft.com). In the meantime, here is some introductory info that should help:Backing up DatabasesFor snapshot and transactional replication, you should back up the following databases regularly:- The publication database at the Publisher.
- The distribution database at the Distributor.
- The subscription database at each Subscriber.
- The master and msdb system databases at the Publisher, Distributor and all Subscribers. These databases should be backed up at the same time as each other and the relevant replication database. For example, back up the master and msdb databases at the Publisher at the same time you back up the publication database. If the publication database is restored, ensure that the master and msdb databases are consistent with the publication database in terms of replication configuration and settings.
If you perform regular log backups, any replication-related changes should be captured in the log backups. If you don't perform log backups, a backup should be performed whenever a setting relevant to replication is changed.Backup Settings for Transactional ReplicationTransactional replication includes the sync with backup option, which can be set on the distribution database and the publication database:It is recommended to set this option on the distribution database in all cases.Setting this option on the distribution database ensures that transactions in the log of the publication database will not be truncated until they have been backed up at the distribution database. The distribution database can be restored to the last backup, and any missing transactions are delivered from the publication database to the distribution database; replication continues unaffected.Setting this option on the distribution database has no effect on replication latency. However, it will delay the truncation of the log on the publication database until the corresponding transactions in the distribution database have been backed up (which can result in a larger transaction log in the publication database).It is recommended to set this option on the publication database if your application can tolerate additional latency.Setting this option on the publication database ensures that transactions are not delivered to the distribution database until they are backed up at the publication database. The last publication database backup can then be restored at the Publisher without any possibility of the distribution database having transactions that the restored publication database does not have.Latency and throughput are affected because transactions cannot be delivered to the distribution database until they have been backed up at the Publisher. For example, if the transaction log is backed up every five minutes, there is an additional five minutes of latency between a transaction being committed at the Publisher and the transaction being delivered to the distribution database and subsequently the Subscriber.Note: The sync with backup option ensures consistency between the publication database and the distribution database, but it does not guarantee against data loss. For example, if the transaction log is lost, transactions committed since the last transaction log backup will not be available in the publication database or the distribution database. This is the same behavior as a non-replicated database. Michael Blythe Technical WriterSQL Server Replication - Microsoft --------------------------------------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. |
 |
|
|
|
|
|
|
|
transactional replication DRP