CDC functionality may break after upgrading to the latest CU for SQL Server 2012, 2014 and 2016
Microsoft SQL Server Product team has identified a potential issue with the latest Servicing Releases for SQL 2012, 2014 and 2016, where in Change Data Capture functionality might break if
- The databases enabled for CDC are part of Always On availability group, OR
- SQL Server replication components are not installed on the server
Microsoft introduced a CDC related fix in below mentioned releases (see section, affected releases): KB 3030352. As part of the fix, a new column was introduced to the change tables to correctly order the operations within the change table. This schema change is applied to the change tables through the sp_vupgrade_replication stored procedure, which is executed during the CU upgrade.
The following scenarios will cause the change tables to be NOT updated after CU upgrade.
- If a CDC enabled database is part of an Always On availability group and users follow the general recommendations of upgrading the secondary replica first, sp_vupgrade_replication will not run in such databases during upgrade because secondary replica databases are not in read/write mode. This is a known behavior and is by design.
- If the server does not have replication component installed, sp_vupgrade_replication will not be executed at CU upgrade time. Microsoft is working on a potential fix for this situation.
Additionally, this issue may also impact SSIS packages which use the CDC flow components (CDC Source component) to extract changes from the CDC enabled database. Microsoft SQL Server product team is currently investigating the impact on such packages, and will be updating the blog post with the findings and potential fix or workaround.
- As a recommended resolution for the first scenario, users can perform either of the following:
- After a secondary replica is upgraded, perform a failover to make it the primary, and run sp_vupgrade_replication.
- Disable automatic failover and perform upgrade at the primary replica. If automatic failover is needed, it can be re-enabled after upgrade. Please note that this approach will result in database unavailability during upgrade.
- To work around the second scenario, users can run "sp_cdc_vupgrade" or "sp_vupgrade_replication" against the database(s) enabled for CDC after the upgrade.
The following SQL Server servicing releases can cause the CDC functionality to break.
- SQL Server 2016 RTM CU5
- SQL Server 2016 SP1 CU2
- SQL Server 2014 RTM CU9
- SQL Server 2014 SP1 CU10
- SQL Server 2014 SP2 CU4
- SQL Server 2012 SP3 CU8
- SQL Server 2012 SP2 CU7