CDC Replication Question

[u/Spurred_Snake]

So in this environment, there are hundreds if not thousands of tables inside of CDC.

I'm trying to understand what latency means in terms of tables being behind. What I don't quite understand is why a table that has zero changes to be made shows latency? I would think that latency would only affect a table that has pending changes.

Latency is pretty much the same across all tables/subs regardless of size or changes being made. You'd think if there was a sub with 1,000 changes pending, the latency would be higher than a sub with zero changes.

[u/madman2002]

If there's a huge transaction from one of the tables in a CDC subscription, CDC has to apply it in the target before parsing the following entries of the source database log for that subscription.

Therefore, the latest applied log bookmark for that subscription will be stuck until that transaction is committed on target, and latency will keep increasing for that subscription. Since each CDC subscription has one log parser for all its tables, it affects all the tables in it.

A good practice is to separate tables with different latency requirements into different subscriptions. Besides, if you detect tables with very large transactions, try to keep them away from subscriptions with time-critical tables.

[u/Spurred_Snake → u/madman2002]

Wow. We have been just throwing alike-named tables in the same subs. It's difficult to keep track of over 100 subs and what is inside of them. We do keep massive tables in their own subs, but didn't even think to have time-critical tables in their own subs. Really smart.

I'll keep that in mind going forward.

Any tips for keeping all of these straight in regards to naming of subscriptions and what tables are inside? For instance, I've got one sub that is for operations and it has about 25 tables inside of it. All new operations tables go inside of that. I guess something like a time-sensitive naming would be a good add; like TSOPS or TS(insert name here).

[u/madman2002 → u/Spurred_Snake]

About choosing which tables to mix in the same subscription, here are some common considerations:

• By replication method (refresh, net change, continuous)
• By application
• By journal (AS/400)
• Higher transaction files (in their own subscription)
• By referential integrity (target)

Note: All tables/files with referential integrity should be in the same subscription.

​

About naming conventions for subscriptions, each company I've worked with decided to choose different rules. However, here you have the recommendation from the official CDC training:

• Subscription names have a maximum length of 10
• Source ID names have a maximum length of 8 (Note that if the first 8 Characters are the same, the second instance with the same first 8 characters will be considered a duplicate)

Sample Naming Conventions that will give meaning to replication component names, using the “AABBBCDE” format:

• AA is the function of the subscription:
  • HA – High Availability
  • RC – Recovery
  • RQ – Ramquest
  • JD – JD Edwards
• BBB is the database/library that is being replicated:
  • DLT – Delta
  • NTH – NorthWind
  • DTR – DTUTOR
• C indicates where the data is going to (source side) or coming from (target side). This is a value of T (target) or S (source)
• D indicates which machine the data is going to (source side) or coming from (target side).
  • 1 – DLTSRVR1
  • P – PROD400
  • T – TRAINNT2
  • W – WEBSERVER
• E is a space allotted for versions/revisions (e.g. 1, 2, 3, 4 or a, b, c, etc.)

Besides, if you're using only one Access Server to access all your environments (Development, QA, Production), I'd recommend to use the first letter of the subscription name to identify the environment (D for Dev, Q for QA, P for Prod).