Sql-server – SQL Server replication pending transactions

It depends. ha ha.

You also need to think about what data-loss requirements the customer wants for the publisher, and whether you're already taking log backups (I'm guessing you are).

Database mirroring can be set to have zero data-loss (as long as the mirror stays synchronized), but depending on the transaction log generation rate and the network bandwidth available, waiting for the log records to be hardened on the mirror before the transactions can commit on the principal may slow the workload down. Depends on what kind of transactions you're doing (long or short) whether this will have a noticeable effect on the overall response time.

With log shipping, it's just backup-copy-restore, repeat. So if you're already taking log backups, you're not going to be impacting performance at all. If you're not used to taking log backups, you may run into issues with transaction log size management.

Be aware that mirroring requires the FULL recovery model, so it could impact your database maintenance, especially if you're used to using the BULK_LOGGED recovery model. Depending on the network bandwidth available, this could also lead to log size management issues.

Both require network bandwidth, but in different ways. Log shipping is a burst every time a log backup is copied, database mirroring is more sustained, obviously depending on the log generation rate again. I'd need ot know a lot more to be able to tell whether the amount of extra bandwidth required for either would impact the movement of data in the replication stream and so affect latency there.

With log shipping, you'd have to manually failover to the log shipping secondary in the event of a failure, and there's the potential for data loss (all data since last log backup that was copied from the primary). And then you'd need to kick-start repl again.

With database mirroring, you can set it to failover automatically and you can specifically set the failover partner in the repl agent jobs to startup automatically on the new principal (which is also the new publisher). The trickiness is making sure that the datbase mirroring failover doesn't occur before the local cluster failover gets a chance to happen. You can do this by changing the mirroring partner timeout value. I blogged about this at http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-3-Database-mirroring-failover-types-and-partner-timeouts.aspx.

I wrote a whitepaper for Microsoft that describes how to use mirroring and tranasactional replication together: see http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server2008-New-whitepaper-on-combining-transactional-replication-and-database-mirroring.aspx.

All other things being equal, I'd recommend database mirroring because of the ease of management with potential for less dataloss. You may have some other requirements I'm not aware of that would prevent that though.

Hope this helps.

Check the Publication Properties-->Snapshot-->Location of snapshot Files-- Make sure put files in the default folder is checked, in my case it was't check marked instead put files in the following folder was checked with a location address(C:\REPL) but i had my distributor in a different server other than publisher and the location address was misleading to distributor agent .