Open Forum

Expand all | Collapse all

SQL Server and Change Data Capture (CDC)

  • 1.  SQL Server and Change Data Capture (CDC)

    GOLD CONTRIBUTOR
    Posted 9 days ago
    Hi All,

    I'm wondering if there are any SQL DBA's with any knowledge of Change Data Capture (CDC) for SQL Server 2017 ?

    My company has brought on a Datawarehouse consulting company that would like to turn on CDC for All companies in GP for the most used tables (modules) in Microsoft Dynamics GP.

    We currently already have an Always On failover configured to 2 different data centers that we use for High Availability and at the same time have configured the secondary site as the reporting site for any SSRS or outside reporting we perform against the GP company databases.

    We have listed below:
       - 79 Users
       - Mekorma MICR
       - MEM
       - Nolan ABR
       - Rockton's GP Toolbox
       - PaperSave
       - SmartConnect
       - SmartView both Internal and External

    My question is, should we be worried turning on CDC with the above configuration ?



    ------------------------------
    Curtis Cruz
    Database Administrator
    Apex Healthcare Partners LLC
    Montebello NY
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: SQL Server and Change Data Capture (CDC)

    GPUG ALL STAR
    Posted 9 days ago
    Essentially, this company you've brought on is relying on CDC to do all the heavy lifting for them to populate the data warehouse.  They don't have to build the ETL process to populate the data warehouse, they can just grab it from the CDC repository.

    Here's the part where you might run into challenges: CDC is going to be tracking all the changes to your main data tables: SOP30200, RM20101, etc.  Depending on how many transactions you've got going at any given time, tracking those changes is going to hit your database with that overhead.  It may or may not be noticeable at low transaction volumes, but its certainly going to be noticeable during high-volume periods such as the posting of batches, etc.  Whether or not it generates a load on the database which degrades normal processing to the point of interfering with daily business is going to depend on how many resources you have dedicated to your SQL Server itself.

    You mention that you have 79 users.  Is that licenses or total authorized users?  (We have 24 licenses but ~70 users.)  Can you provide any more specifics about the server hosting Dynamics GP?  RAM?  # of GP Companies?  Average size of GP company DB's?  Are there other applications using the DB server which may place load on the server which should be taken into account?  (From the list of add-ons you have here only PaperSave is intensive enough IMHO to worry about.)

    You also mention that you have High Availability turned on.  That in and of itself is resource intensive because that process is also looking at the transaction logs to determine what change information to pass down the line to your "reporting" server/clone.  Adding CDC on top of that is going to slow down the replication process because now you have two major processes wanting the same data all the time.  It might be worth asking the data warehousing company if you can turn on CDC only in the "reporting" database rather than on the main production server.  That would limit the load to the one server instead of your entire infrastructure.

    I think one of the things I would research is the basic requirements of CDC vs what you currently have available for your SQL Server instance.  RAM is your friend and I would max out the RAM before you implement CDC.  SQL Server will use all available RAM (unless you limit it specifically) as needed.  There are some tweaks you can do dependent on whether or not you are going with a dedicated memory design or a shared pool design as well, but I don't want to get off-topic too much before we cover the basics.

    In general, you should always be concerned about adding load to a server.  The question is really how to manage the additional load without it taking away from day-to-day business.  That is going to be largely a case-by-case analysis because no two businesses use the same things the same way.  Hopefully this gives you some things to look at.


    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise Idaho GPUG Chapter Leader
    GPUG All-Star
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: SQL Server and Change Data Capture (CDC)

    GOLD CONTRIBUTOR
    Posted 8 days ago
    Hi Curtis,

    I read this yesterday and considered a reply and then didn't.  I'm hesitant, because this could be a very complex discussion.  But after seeing it again today, my main thought is that after being involved with hundreds of GP environments I can't think of any that CDC would be my primary option to be used for the possible usages and especially for a data warehouse.  Without knowing all the details though, maybe yours is different.

    Like Blair mentioned, I would ask a lot of questions about this.  I immediately had thoughts about resources.  In addition to the pieces mentioned, if CDC is running you still will likely populate a data warehouse in another table structure so in addition to making a copy of the data you will have process to actually populate the data warehouse.  Also, would this need to be near real-time or could it be done at intervals at night or throughout the day.  There are many ways to schedule or limit the resource usage.

    Again, you may have a complex reason for doing this but wanted to also give you the feedback asked here to review this information completely.

    ------------------------------
    John Kirsch
    GP Product Lead
    Dynamic Consulting
    Green Bay WI
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role