Heres a breakdown by example of what SYNC WITH BACKUP does with Transactional Replication.

Whats it is:

Copied from BOL

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

syncWithBackup1

Open Replication Monitor and run a Tracer Token

syncWithBackup2

The token will now remain pending until we do a backup of the publishing database

syncWithBackup3

Second. Run a BACKUP on the Publishing Database.

syncWithBackup4

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

syncWithBackup5

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

syncWithBackup6

Note that the credit has a 91% full transaction log.

Next run a LOG BACKUP of the Credit database

syncWithBackup7

Re-run the DBCC SQLPERF

syncWithBackup8

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

syncWithBackup9

Re-run DBCC SQLPERF again!

syncWithBackup10

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.

Advertisements