By Example : Transactional Replication Sync with Backup
Heres a breakdown by example of what SYNC WITH BACKUP does with Transactional Replication.
Whats it is:
When enabling a database for transactional replication, you can specify that all transactions must be backed up before being delivered to the distribution database. You can also enable coordinated backup on the distribution database so that the transaction log for the publication database is not truncated until transactions that have been propagated to the Distributor have been backed up.
How To Turn it ON / OFF Check settings:
Run the below query changing the DB to the relevant database (Either your publisher or subscriber)
-- is SYNC WITH BACKUP on 1 = ON 0 = OFF SELECT DATABASEPROPERTYEX ('credit', 'IsSyncWithBackup') How do you turn it ON / OFF: -- Turn on SYNC WITH BACKUP EXEC sp_replicationdboption @dbname = 'Credit', @optname = 'sync with backup', @value = 'TRUE' -- Turn on SYNC WITH BACKUP EXEC sp_replicationdboption @dbname = 'Credit', @optname = 'sync with backup', @value = 'FALSE'
Example on a Publishing Database:
The easiest way to understand what it does is see it in action. I have Transaction Replication enabled on database Credit which is in full recovery mode and all backup jobs have been disabled.
Enable SYNC WITH BACKUP
Open Replication Monitor and run a Tracer Token
The token will now remain pending until we do a backup of the publishing database
Second. Run a BACKUP on the Publishing Database.
You then see the token go through to the Distributer to the Subscriber.
Pretty simple but i think a nice way to show how enabling SYNC WITH BACKUP works on the Publisher.
Example on a Distribution Database:
First lets enable SYNC WITH BACKUP on the Distributor
Now push some data into the publisher, for me I setup an agent job to run every second adding some data to a table.
After letting it run for a while check the log space with DBCC SQLPERF
Note that the credit has a 91% full transaction log.
Next run a LOG BACKUP of the Credit database
Re-run the DBCC SQLPERF
Note that the Credit DB is now even fuller (The transactions have not been cleared off log because we are still waiting for a distribution backup and its now bigger as we are still writing to that table every second)
Run a LOG BACKUP of the Distribution database
Re-run DBCC SQLPERF again!
Now we see Credit has finally cleared its transactions.
Hopefully these little experiments help show what SYNC WITH BACKUP does and you found it useful.