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

Details

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.

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.

Affected Releases

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

Comments (4)

  1. Arndt says:

    Hello,
    one question. On the KB article in the section “This issue is fixed in the following cumulative updates of SQL Server.”
    are the same releases mentioned, which you describe would breake the CDC
    example:
    Cumulative Update 5 for SQL Server 2016 RTM
    Cumulative Update 2 for SQL Server 2016 SP1
    Cumulative Update 4 for SQL Server 2014 SP2
    Cumulative Update 10 for SQL Server 2014 SP1
    Cumulative Update 9 for SQL Server 2014
    Cumulative Update 8 for SQL Server 2012 SP3
    Cumulative Update 7 for SQL Server 2012 SP2

    who is right?

    Sorry for my terrible Englisch

    Kind regards

    1. Hi Arndt, Thanks for pointing this out. I have updated the blog.

  2. Duarte Canuto says:

    Hi People,
    Thanks for the information
    Are there any fix to be released with this issue on SQL 2012 SP3 CU8 on the next months?
    Thanks,
    Duarte Canuto

    1. Hi Duarte, there is no fix being planned for the Always On Availability Group scenario as this is an expected behavior for read-only secondary databases. For the scenario related to Replication components we are working on a change, though we do not have a release date finalized yet.

Skip to main content