Enabling CDC on an Azure SQL replica

Michael Mehrtens 1 Reputation point
2022-04-27T20:24:58.817+00:00

Is there a way to enable Change Data Capture (CDC) on an Azure SQL replica node, rather than enabling it on the primary node?

When attempting to enable on my replica I get the following error:

   Msg 22830, Level 16, State 1, Procedure sys.sp_cdc_enable_db_internal, Line 215 [Batch Start Line 13]  
   Could not update the metadata that indicates database DATABASENAME is enabled for Change Data Capture. The failure occurred when executing the command '(null)'. The error returned was 3906: 'Failed to update database "DATABASENAME" because the database is read-only.'. Use the action and error to determine the cause of the failure and resubmit the request.  

However, the SQL Server Management Studio properties for the replica show that the Read Only property is set to False.

197144-image.png

I was hoping to enable CDC on the replica to avoid any performance impact on the primary node. In theory this seems like it should be possible, and I haven't found any documentation indicating that it's not supported:

Azure SQL Database
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Erland Sommarskog 105.9K Reputation points MVP
    2022-04-27T21:38:03.027+00:00

    No, you cannot enable CDC on a readable secondary, since enabling CDC results in objects being created to support CDC.

    The ReadOnly property you found in SSMS is a different setting. It is possible to set a standalone database as ReadOnly.