Sql-server – Change Data Capture not running when Mirror is Suspended

change-data-capturemirroringsql-server-2012

We have 2 SQL 2012 SP2 Enterprise servers that have some mirrored databases. One of these databases also has CDC enabled on a number of tables so that we can run some business logic when there are changes to the table.

Yesterday we did some maintenance on the mirror server (the primary was running exposed). A few hours later we re-enabled the mirror and it synchronised as expected.

What we found out was that the CDC data that we normally process close to real time didn't get collected until after the mirror was resumed.

2 questions out of this:

When you have a mirror configured, is CDC data only collected when the mirror is synchronized?
Is there anyway to make CDC capture run when a mirror is paused?

Best Answer

CDC uses the log reader, same as replication. When mirroring or availability groups are used, by default the log reader will not process log records that have not already been hardened at all mirroring / availability group secondary replicas.

This can be changed with Trace Flag 1448, which enables the replication log reader to move forward even if the asynchronous secondary replicas have not acknowledged the reception of a change. Even with this trace flag enabled, the log reader always waits for the synchronous secondary replicas.

References:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/replicate-track-change-data-capture-always-on-availability#CDC

https://support.microsoft.com/en-us/help/937041/fix-changes-in-the-publisher-database-are-not-replicated-to-the-subscr

Related Solutions

Sql-server – send a string over TCP using T-SQL

The article linked uses the dreaded WMI middle-man for delivering the notifications. That is so error prone that is not worth talking about. Cut out the WMI middle man (that also looses events...) and go straight to the source: create an event notification for the Database Mirroring State Change Event. Deliver the notifications locally to your [msdb] database. Then you can react in your activation procedure directly to the state change notification. Yo do not need any TCP or other communication, each mirroring partner can do this monitoring independently and each partner reacts to the local state change.

For a C# application to react to the notification simply have it monitor a queue (WAITFOR(RECEIVE...)) where, again, you have the system deliver event notifications for mirroring state change event. You can even use the external activation app.

The Pro SQL Server 2008 Mirroring book has an example showing exactly this technique.

Just don't use WMI... and absolutely don't connect to client back from server via TCP.

Sql-server – Transaction log growing on a mirrored principal

Mirror, Synchronized / Restoring... is the typical state for the mirror instance.

You can determine why the log file is growing by issuing the following command in a SQL Server Management Studio query window:

SELECT name, LOG_REUSE_WAIT_DESC 
FROM sys.databases 
WHERE name='your_database_here';

This will show several possible choices, such as:

NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT

You may need to perform at least one transaction log backup, among other actions depending on the LOG_REUSE_WAIT_DESCRIPTION shown in the query above. For instance, there may be a long-running transaction that is forcing the log files to not be re-used.

Best Answer

Related Solutions

Sql-server – send a string over TCP using T-SQL

Sql-server – Transaction log growing on a mirrored principal

Related Question