1 2 Previous Next 26 Replies Latest reply on Nov 14, 2018 11:59 AM by vijay00u1efn99eu61Z7mS1d8 Branched to a new discussion.

    SQL Server PWX CDC architecture

    vijay00u1efn99eu61Z7mS1d8 Active 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
            vijay00u1efn99eu61Z7mS1d8 Active 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
                vijay00u1efn99eu61Z7mS1d8 Active 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.

                  • 6. Re: SQL Server PWX CDC architecture
                    vijay00u1efn99eu61Z7mS1d8 Active Member

                    Hello Dan,

                     

                    Can you please answer the following specific questions? I know some of the below are SQL server related. If you know them, pls answer. Otherwise, i will continue to do the research.

                     

                    1) Does power exchange require Database Owner authority on distribution database or publisher database to create registration groups and capture registrations?

                    2) Is PWX a Pull vs Push subscriber to distribution database?

                    3) Schema changes – will logger fail?

                    4) What happens when transactional replication is re-initialized?

                    5) What are the cases where replication needs to be re-initialized?

                    6) When we first set up PWX, transactional replication and are ready to go, will the snapshot that was taken initialize the target tables? or do we need to create one time ETLs to initialize the target tables to keep them in sync with source tables first and then start replication?

                    7) When are the snapshots generated with transactional replication publications ? For every new publication? Or For every change to existing publication?

                    8) And what does the snapshot include? snapshot for the just changed/added tables in the publication? Or Snapshot for all tables in the publication? Or snapshots for all publications?

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

                      To begin: PWX SQL Server CDC does nothing with snapshots.  They are invisible and unused, as far as PWX CDC is concerned.

                       

                      1) Does power exchange require Database Owner authority on distribution database or publisher database to create registration groups and capture registrations?

                       

                      Creating a Registration Group requires sa_role.

                      - This creates the Publication

                      Creating a Capture Registration requires dbo.

                      - This creates the Article(s), one for each table

                       

                      2) Is PWX a Pull vs Push subscriber to distribution database?

                       

                      Pull.

                      NOTE: PWX is an anonymous subscriber.

                      SQL Server doesn't even know that PWX is doing, has done, or will do, anything about retrieving rows.

                       

                      3) Schema changes – will logger fail?

                       

                      Worse.

                      You can't change table structure with capture enabled.

                      If you stop capture, change the table, and re-enable publication, the link between PWX and the article is broken.

                       

                      If you somehow get data in DistDB that doesn't match what the capture registration "knows" to be correct, yes, PWX will fail.

                       

                      If you update the Capture Registration, but do not update the Extraction Map, then PWXCCL will capture the data, but attempts to extract it will fail, because the extraction map won't match the captured data.

                       

                      4) What happens when transactional replication is re-initialized?

                       

                      For PWX? Nothing obvious, as PWX doesn't use snapshots.

                      If you destroy the existing rows in DistDB before PWX can read them, then you could cause data loss.

                       

                      5) What are the cases where replication needs to be re-initialized?

                       

                      If your DBA turns off the agent, they will need to turn it back on.

                      Note: That may mean anything already in DistDB was destroyed before PWX read it.

                      Don't Do that without making sure PWX has captured everything already in DistDB.

                       

                      Note: Some Service Packs including dropping and recreating Transactional Replication, DistDB, or the contents of DistDB.

                      All those break PWX SQL Server CDC, because the object Id of the article is stored in the capture registration, and is how PWX finds rows for each table.  Anything that changes that object ID will break the linkage between PWX SQL Server CDC and the data in DistDB.

                       

                      If you mean re-materializing the PC target from the source, that should be pretty rare - it means something went seriously wrong.

                       

                      6) When we first set up PWX, transactional replication and are ready to go, will the snapshot that was taken initialize the target tables?

                       

                      Not at all.  As noted at the top of this post.

                       

                      or do we need to create one time ETLs to initialize the target tables to keep them in sync with source tables first and then start replication?

                       

                      You need to do that.

                      Using a PC session with PC Relational connection for source is one way to do that.

                       

                      If the mapping logic is non-trivial, you might want to put it in a maplet, and then include the maplet in two mappings: one with a relational source connection for materialization, and one with a PWX CDC source for daily use.

                       

                      You might also do this with some non-INFA product, or if going SQL Server to SQL Server, with a DB backup and restore.  Or any other method that you trust.

                       

                      7) When are the snapshots generated with transactional replication publications ? For every new publication? Or For every change to existing publication?

                       

                      Doesn't matter.

                      PWX doesn't use them.

                       

                      8) And what does the snapshot include? snapshot for the just changed/added tables in the publication? Or Snapshot for all tables in the publication? Or snapshots for all publications?

                       

                      Doesn't matter.

                      PWX doesn't use them.

                      • 8. Re: SQL Server PWX CDC architecture
                        vijay00u1efn99eu61Z7mS1d8 Active Member

                        Thank you Dan for taking time and providing detail answers. Follow up on question # 1, The SA and DB owner role are needed on the publisher database. correct?

                         

                        Also, the CDC guide has the following text-  "To create registration groups and capture registrations from the PowerExchange Navigator, you must have DB_OWNER authority". Is DB_OWNER enough to create registration groups(publication)?

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

                          1) No, you also need sa_role on the source DB to enable transactional replication

                          2) No, you need sa_role to create a registration group, as I stated earlier.

                          (You do only need dbo to create a capture registration, once the group is created.)

                          • 10. Re: SQL Server PWX CDC architecture
                            vijay00u1efn99eu61Z7mS1d8 Active Member

                            Thanks again Dan. Neither sa role nor dbo role will be allowed to persons other than the DBAs here. May be i can take shot at dbo role by talking to managers but that's a very long one. So this leads to following questions.

                             

                            1) Let's say the DBA create the publication(registration group) as well as the articles(capture registration) using their tools(using the db commands or some tool) that they use. Will PWX navigator recognize both the publication(registration group) and the articles(capture registration)

                             

                            2) or do the DBA's have to use the PWX navigator to create publication(registration group) and the articles?(capture registration)

                             

                            3) or Can i give them some PWX commands that they can run to create publication(registration group) as well as the articles(capture registration) for both initial set up and maintenance afterward?

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

                              Don't go down that route.  You are for more likely to cause problems than have a workable PWX environment.

                               

                              PWX embeds the object ID of the article into the capture registration, so it really needs to be the thing creating the registrations (and the groups).

                               

                              You may want to consider creating appropriate DTLUCBRG and DTLURDMO scripts, and giving them to DBA to run, rather than trying to craft something that matches what PWX needs.

                               

                              I always recommend any PWX SQL Server CDC customer maintain a DTLUCBRG script for rebuilding the linkage between the capture registrations and the articles, but things can and do happen to the DistDB and articles that change those object IDs, or destroy the articles.

                              • 12. Re: SQL Server PWX CDC architecture
                                vijay00u1efn99eu61Z7mS1d8 Active Member

                                Thanks for the recommendation. One last question

                                 

                                1) Can the registration groups be created using the DTLUCBRG or any other PWX utility? I read the PWX utility guide but couldn't find any utility that does this job.

                                 

                                I know i asked many questions and you answered them with details and patience. I am sure the information on this entire thread of posts will be of help to others as well.

                                 

                                Thanks Dan

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

                                  We don't have a utility that only creates the registration group and extraction group.

                                   

                                  But yes, if the appropriate group does not yet exist, DTLUCBRG will create one.

                                   

                                  However, for PWX SQL Server CDC, I would recommend creating the groups manually, even if you have to get your DBA to do it via PWX Navigator.

                                   

                                  For most PWX CDC sources, the "group" isn't a real thing - just a label that PWX Navigator or other tools use to ask PWX Listener about registrations for a specific source DB.

                                   

                                  But for PWX SQL Server CDC, the "group" is a real thing, and is tied to the Publication.  Also, if you allow PWX to create the groups, it generates the INSTANCE id by taking the first 4 characters of the DB name and adding a 3digit sequencer (so you will see things like DIST000 and DIST001.)  If you allow PWX to generate the INSTANCE ids, and you are not careful about the sequence of creation in each environment, then source DB A might be DIST000 in Dev but DIST001 in QA, while source DB B might be DIST001 in Dev but DIST000 in QA, which makes migrations between environments a pain.

                                  If you manually manage the order of creation of the groups in each environment, you can avoid that problem.

                                  • 14. Re: SQL Server PWX CDC architecture
                                    vijay00u1efn99eu61Z7mS1d8 Active Member

                                    Hello Dan,

                                     

                                    i was reading through SQL server CDC guides for both 10.0 version and 10.1.1 HF2 for SQL server prerequisites. I noticed that the requirement around SQL Server Management Objects package doesn't exist in 10.1.1HF2 guide. My question is, it is not a requirement/prerequisite anymore?

                                     

                                    Thanks,

                                    Vijay

                                    1 2 Previous Next