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 (18)

  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.

      1. Sam says:

        Workaround mentioned for CDC on a database in an AG isn’t working.

        1. Sam says:

          Our environment is running on SQL Server 2016 SP1 CU1

        2. Hi Sam, if you are on SQL Server 2016 SP1 CU1, then you would not be running into the issue. The issue would only surface if you upgrade the instance to SQL Server 2016 SP1 CU2 and if the ALL the following conditions are met.
          1. User Databases have CDC Enabled.
          2. The CDC enabled database are part of an Always ON Availability Group
          3. The patch is first applied on a secondary replica.

      2. Arndt says:

        Hello,

        sp_vupgrade_replication isn’t mention on this website: https://msdn.microsoft.com/en-us/library/mt163864.aspx the English version is broken, but the the German still available.

        Kind regards

  3. Chris Wood says:

    Looks like it didn’t make it into the SQL 2014 CU’s released yesterday did it?

  4. shruti says:

    Hi,

    We use SQL server 2016 and it’s up-to date with the latest CU. After we updated it, CDC packages are failing due to metadata issue with the new column “_$command_id” being added . After the packages are refreshed with this column it executes fine. But what I observed is when there is change in the source data, then the package fails saying invalid column “_$command_id”. I think this belongs to the second scenario in this blog https://blogs.msdn.microsoft.com/sql_server_team/cdc-functionality-may-break-after-upgrading-to-the-latest-cu-for-sql-server-2012-2014-and-2016/

    But I don’t see you mentioning any resolution for that. Would be great if you can tell more about this.

    1. Hi Shruti,
      I am assuming this are SSIS packages and you using the CDC Flow Components in your packages? This is not the second scenario in the blog. This is currently being investigated by the team and we would update as soon as there is a resolution.

      1. For SSIS CDC Splitter component the simplest workaround could be to bridge execution of fn_cdc_get_<all|net>_changes_<tracking_instance> functions:

        1. Rename upgraded fn_cdc_get_<all|net>_changes_<tracking_instance> to fn_cdc_get_<all|net>_changes_<tracking_instance>_original, e.g.
        exec sp_rename @objname = N’cdc.fn_cdc_get_<all|net>_changes_<tracking_instance>’, @newname = N’fn_cdc_get_<all|net>_changes_<tracking_instance>_original’
        2. Create new stored procedure with the name, how it was before renaming and call original function from it, along with returning all values plus the extra null as __$command_id, e.g.
        create function [cdc].[fn_cdc_get_<all|net>_changes_<tracking_instance>] (@from_lsn binary(10), @to_lsn binary(10), @row_filter_option nvarchar(30))
        returns table
        return select *, null as __$command_id from [cdc].[fn_cdc_get_<all|net>_changes_<tracking_instance>_original](@from_lsn,@to_lsn,@row_filter_option)

        1. Jason says:

          Alexander Vorobyov,
          Editing the cdc.fn_cdc_get functions did the trick for me. Thanks.

  5. Mike says:

    I have a 5-node AG with CDC that has already been upgraded. CDC is currently broken. Are you saying that I have to run “sp_cdc_vupgrade” on each replica while it is Primary? That is a massive outage. We have secondary replicas that are not designed to be the Primary. Total size of all AG databases is about 2 TB.

    1. Hi Mike, you have to run it only once, on any one Replica.

  6. SQLGlenn says:

    How do I check to see if CDC is enabled on a database? Thank you.

    1. Hi, you could use the query
      select * from sys.databases where is_cdc_enabled = 1

Skip to main content