Year: 2010

CDC does not require Unique Index

Change Data Capture feature of SQL 2008 is based on Transactional Replication.  CDC and Transactional Replication used the same internal stored procedure sp_replcmds to retrieve data updates from the transaction log.  However, unlike Transactional Replication, CDC does NOT require the tables to have a unique index (or Primary Key). In fact CDC can be use
Read More »

Snapshot Agent Performance –MaxBCPThreads

We noticed a large published database was taking a long time for the Snapshot to be applied to the subscriber.  Looking further in sp_who and sys.sysprocesses we noticed only one Distribution Agent thread executing the bulk insert statements. It took a while but we discovered the publication was created using “concurrent snapshot” option. -MaxBcpThreads number_of_threads
Read More »

Distribution Agent fails with: Error Locating Server/Instance Specified [xFFFFFFFF].

Sateesh Yele | Microsoft SQL Server Support Symptom Distribution Agent is failing when connecting to subscriber server when replicating between cross domain servers. Consider this, you are setting up replication between 64 bit edition servers that are cross domain and the Distribution Agent is failing with the following error in Replication Monitor. The process could
Read More »

What happens if I select Create a snapshot immediately in the Publication Wizard?

When creating a Transactional Replication publication, what happens if I check Create a snapshot immediately and keep the snapshot available to initialize subscriptions? This sets the publication property @property=‘immediate_sync’,@value=‘TRUE’ which instructs Replication to keep all the transactions cached in the Distribution database for the entire Retention Period.  Normally these rows are purged as soon as
Read More »