5 Replies Latest reply on Sep 14, 2018 7:41 AM by dan.smith@informatica.com

    SQL Server PWX CDC architecture

    vijay p New Member

      will the following set up work? We really can not install any PWC component on the source data base system at our company except for any SQL server specific components and configuration.

       

      1) Source DB with transactional replication enabled.

      2) A remote machine with both Logger(remote logging) and Listener installed and configured.

      3) Start Logger

      4) Start Listener

      5) Power Center machine(sessions) extracting data from logger files from remote logger +machine.

       

      i understand there are several other tasks and configurations that  need to be done but on a high level, will the above set up work?

       

      Also, does the logger capture changed data from distribution database logs or directly from the articles with in distribution database?

       

      Thanks for the help.

        • 1. Re: SQL Server PWX CDC architecture
          dan.smith@informatica.com Guru

          The architecture is viable.

          The process flow is not.

           

          Unless capture registrations already exist, the logger has nothing to do, so it will shutdown immediately - and tell you that there are no active capture registrations.

           

          Start PWX Listener.

          Create one or more capture registrations.

          Cold Start PWXCCL (only have to cold start the first time).

           

          Once you have capture registrations, you also have extraction maps.

          Do an "import from PWX" in PC Designer to create Sources from PWX CDC Extraction Maps.

          Then you can include those sources in Mappings as Application SQs.

           

          Please refer to the following KB:

           

          When implementing PowerExchange, where does PowerExchange software have to be installed?

          https://kb.informatica.com/whitepapers/4/Pages/1/326253.aspx

           

          PowerExchange CDC for Linux/UNIX/Windows sources: How do the PowerExchange and PowerCenter components connect?

          https://kb.informatica.com/whitepapers/4/Pages/1/326245.aspx

           

          PowerExchange CDC for SQL Server: Components and Data Flow

          https://kb.informatica.com/whitepapers/4/Pages/1/151277.aspx

           

          Where should PowerExchange CDC for SQL Server components run?

          https://kb.informatica.com/whitepapers/4/Pages/1/157484.aspx

           

          PowerExchange CDC for SQL Server: Example Configuration Files for use with PWXCCL

          https://kb.informatica.com/whitepapers/2/Pages/141253.aspx

           

          Please remember that Windows will kill foreground windows if you logoff.

          If you want to run PWX on Windows, you need to run PWX Listener as a service or PC admin console service, or as a headless window, and you need to run PWXCCL as a PC Admin Console service, or as a headless window.

          • 2. Re: SQL Server PWX CDC architecture
            vijay p New Member

            Thanks Dan for the inputs.

             

            Sounds like i do not have to install any PWX components on the source SQL server database system and i can have both logger and listener run on the remote machine. Please confirm.

             

            Coming to my other question, whether the logger reads from source database logs or distribution database, the documentation says the logger reads from distribution database. I am not a SQL server expert. So i do not know what exactly a distribution database contains and how it is different from publisher database. i would highly appreciate if you can answer what exactly logger reads from distribution database.

             

            Thank you.

            • 3. Re: SQL Server PWX CDC architecture
              dan.smith@informatica.com Guru

              Correct, you do not have to.

              As noted in the earlier KBs, and my earlier comments, that is one of the viable implementation architectures.

               

              For details of how SQL Server Transactional Replication works, please talk with your SQL Server DBA.

              In general:

              You enable transactional replication (PWX does this for you when you create a capture registration group, but if it  is later disabled, it has to be manually re-enabled by DBA)

              A publication is created in the Distribution Database (defined in the previous step)

              You select specific tables to be replicated (PWX does this for you when you create capture registrations)

              An Article is created ("under" the publication created earlier) (PWX does this for you when you create capture registrations)

              The SQL Server agent detects changes on the SQL Server transaction logs for tables that are being replicated, and puts rows in the Articles in DistDB for committed transactions.

              • 4. Re: SQL Server PWX CDC architecture
                vijay p New Member

                Thanks again Dan.

                 

                I will talk to SQL Server DBA. However, i need to understand what PWX logger interacts with. Let's assume the following set up. We have a publisher DB on  one server with transaction replication topology and we have created a publication with a set of articles on this database. The distribution database is on another server. The SQL server agent will read transaction logs on the publication DB for articles that are being replicated and puts the data changes in the Distribution DB. The question is - will PWX logger read the changes directly from distribution database or from the logs on distribution database?

                • 5. Re: SQL Server PWX CDC architecture
                  dan.smith@informatica.com Guru

                  PWX SQL Server CDC does not read transaction logs.

                  It operates as an anonymous subscriber of the articles in the DistDB.

                  It queries msrepl_commands and msrepl_transactions.

                   

                  If you use PWXCCL and have PWX configured to do so, it can also remove the rows that it has read and hardened to disk.