Results 1 to 2 of 2

Thread: SQL Server - uniformly mapped across partitions

  1. #1
    robrechts is offline Junior Member
    Join Date
    Jun 2018
    Rep Power

    SQL Server - uniformly mapped across partitions


    I have trouble with the cdc of some tables, as demo environment i'm using the test db from microsoft: WideWorldImporters.
    Some tables won't capture any data and give the error: Table 'Sales.InvoiceLines' is not uniformly mapped across partitions. Therefore - it is excluded from CDC

    Is there any way to solve this, i don't really understand what the problem is.

    Kind regards and thanks,

  2. #2
    stevenguyen is offline Senior Member
    Join Date
    May 2014
    Rep Power
    The reason you got this table suspended, is because it has some special structure, that Replicate will have a problem with doing CDC on.
    Please see below details , and possible solutions.
    BTW, this is a rare warning, and we saw it so far, with a small number of tables.
    Also, note that Replicate does support partitions in SQL server, this is very special case of using partitions in SQL Server.

    Problem description:

    In order to run CDC on a SQL server table, Replicate parses the SQL server Tlogs.
    On each tlog record, to get the values that were in that INSERT/UPDATE/DELETE, Replicate parses on it read an hexadecimal value that contains the data of the columns that got inserted/updated/deleted on that change.
    In order to parse this hexadecimal record, it read the table metadata from SQL server system tables.
    Those tables say what are the table columns, and some internal properties ,like their "xoffset" and "null bit position", that will tell you how to parse the value of each column from that hexadecimal value.

    This metadata ( what columns are in the table, their position, "xoffset" and "null bit position") should be the same for all raw partitions of the table.
    Now, the problem with this specific table, is that , for some reason , it doesn't have the same metadata on all of its partitions, so Replicate might parse changes wrongly,
    And applied changes on target will have wrong data.

    As I wrote, this is a rare condition, that we are not sure how it happened.
    We only saw it on a few specific table.

    Possible Solutions:

    1. If the table has a clustered index, then an index rebuild would be enough to fix the issue.
    However, this is not the case for this table
    2. Another solution is to add a clustered index to the table (you can drop it later if you want). This should also fix the issue.

    These solutions should help, as adding a clustered index or rebuilding it will causes all the partitions in the table to have the same "metadata" , and so fixes the issue.

    Please let us know if the above solution work for you.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts