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

  • Anonymous
    April 11, 2017
    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 EnglischKind regards
    • Anonymous
      April 12, 2017
      Hi Arndt, Thanks for pointing this out. I have updated the blog.
  • Anonymous
    April 17, 2017
    Hi People, Thanks for the informationAre there any fix to be released with this issue on SQL 2012 SP3 CU8 on the next months?Thanks, Duarte Canuto
    • Anonymous
      April 17, 2017
      The comment has been removed
      • Anonymous
        April 21, 2017
        Workaround mentioned for CDC on a database in an AG isn't working.
        • Anonymous
          April 21, 2017
          Our environment is running on SQL Server 2016 SP1 CU1
        • Anonymous
          April 27, 2017
          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 Group3. The patch is first applied on a secondary replica.
      • Anonymous
        April 25, 2017
        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
  • Anonymous
    April 18, 2017
    Looks like it didn't make it into the SQL 2014 CU's released yesterday did it?
  • Anonymous
    April 18, 2017
    The comment has been removed
    • Anonymous
      April 27, 2017
      The comment has been removed
      • Anonymous
        May 18, 2017
        The comment has been removed
        • Anonymous
          June 23, 2017
          Alexander Vorobyov,Editing the cdc.fn_cdc_get functions did the trick for me. Thanks.
  • Anonymous
    April 24, 2017
    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.
    • Anonymous
      April 27, 2017
      Hi Mike, you have to run it only once, on any one Replica.
  • Anonymous
    April 27, 2017
    How do I check to see if CDC is enabled on a database? Thank you.
    • Anonymous
      April 27, 2017
      Hi, you could use the query select * from sys.databases where is_cdc_enabled = 1
  • Anonymous
    August 17, 2017
    Thanks for this info.If you'd like know what the error message text looks like when this issue exists, here it is (related to a request to enable CDC for a table):Could not update the metadata that indicates table [].[] is enabled for Change Data Capture. The failure occurred when executing the command ‘insert into [cdc].[captured_columns]’. The error returned was 213: ‘Column name or number of supplied values does not match table definition.’. Use the action and error to determine the cause of the failure and resubmit the request.
  • Anonymous
    February 18, 2018
    The comment has been removed
    • Anonymous
      March 06, 2018
      Hi, the issue you have mentioned above seems like a Primary key violation error and does not seem to be related to the scenario mentioned in the blog. Would it be possible for you to work with Microsoft CSS to troubleshoot the issue further.