1 2 3 Previous Next 42 Replies Latest reply on Feb 13, 2019 9:19 AM by dan.smith@informatica.com Go to original post Branched to a new discussion.
      • 15. Re: SQL Server PWX CDC architecture
        dan.smith@informatica.com Guru


        PWX SQL Server CDC no longer uses SMO/RMO.

        • 16. Re: SQL Server PWX CDC architecture
          vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

          Hello Dan,


          please answer below questions.


          1) What happens when a lower environment sql server db is refreshed? like a full database copy. Do we have to recreate publications, articles etc. for PWX to continue replicating? of course this activity will be a coordinated one with source system owners but trying to figure out what needs to be fixed/done to get PWX going.


          2) the CDC guide says PWX will need a user with read access to dist db to read changed data from distribution database. is read access enough?


          on other note, we are also starting out on PWX CDC Express for Oracle. I haven't read the entire guide but one thing i noticed is i do not see "every source table needs to have primary key" as a requirement for oracle like in the case of sql server.




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

            1) Correct.  anything that changes object IDs or turns off replication will break PWX SQL Server CDC.

            If that happens, you have to rebuild the articles and capture registrations.

            As noted elsewhere, I recommend that each PWX SQL Server CDC customer maintain a set of DTLUCBRG scripts to do that.


            2) Like most things PWX, that depends.

            If you are not using ENABLELWM, then the SQL Server Login PWX uses needs SELECT on msRepl_transactions and msRepl_commands.

            If you are using ENABLELWM, then it also needs DELETE.


            The requirement for a PKey is actually an SQL Server Transactional Replication requirement, not a PWX requirement.


            Speaking as a DBA, I recognize that heap tables serve some purposes, but they should be rare.

            Most tables should have a PKey - but PWX doesn't really care if it does, or not.

            • 18. Re: SQL Server PWX CDC architecture
              vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

              Hello Dan, we are making progress with PWX for SQL Server replication and this leads to following questions. Please answer. Here is the setup. The source system has multiple dev instances which get refreshed from prod db periodically. So, the tables(capture registrations) are same across these multiple dev publishing databases on a single SQL server. Also, we have one DistDB on the same server.


              1) Should we create a separate registration group(publication) for each publication database? I was thinking that this approach helps in not touching replication from the other instances when one instance is refreshed?

              2) If yes, will PWXCCL create one set of log files per publication or one set of log files for all publications?

              3)  For a source table, Can we create one power center mapping and point it to desired capture registration/extraction map to source data from log files by parameterizing the capture registration/extraction map name? So that we redirect the mapping to read data from desired extraction map.


              I understand that this approach is not ideal and when switching to different database, there will be several other tasks that need to be performed. We also understand that migration of code to higher envs will cause some challenges.

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

                1) Good thinking, and yes, that is probably what I would recommend.

                I would use a separate set of (PWX Listener + PWXCCL + config files (with MULTIPUB explicitly set to N)) for each source DB.


                2) Each PWXCCL will create its own set of CND files, only for the sources you tell that PWXCCL to capture.

                If you have each PWXCCL capturing for a single source DB (publication DB), then each one's CND files will only have data for its source/publication DB


                3) No, this isn't available for PWX SQL Server CDC.

                You would need multiple sets of mappings/sessions.

                Please note that one session can source from only one PWX Listener, so this would further separate the sources, operationally.


                If you are certain that the sources will remain in sync (structurally) across the source databases, and if your PC mapping logic is non-trivial, then I would suggest coding that in a mapplet, and including the mapplet in multiple sessions as needed.

                If you do that, I would suggest a pair of such sessions/workflows, with one set having PWX CDC source connections, and the other PC relational connections.  The former would be daily-use, and the latter would be for any reconciliation / materialization requirements.


                Given that you mentioned refreshing Dev instances from Prod, I would strongly suggest creating and maintaining a set of DTLUCBRG scripts to recreate the article object ID <-> PWX Capture registration linkages.


                HOW TO: Run the PowerExchange DTLUCBRG utility using the MSSOPTS UPDATESTATUS parameter in PowerExchange


                • 20. Re: SQL Server PWX CDC architecture
                  vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

                  Thanks Dan.


                  1) I was actually saying using one set of listener + PWXCCL + config files and creating multiple publications(one per source database). Do you think this is not a viable approach?


                  2) From your reply, i figured out that there will be only one set of CND for each PWXCCL


                  3) I did this for oracle in the past . So i assumed this would work for sql server. Can you please explain why it works for oracle but not for sql server? Also, if the source in power center is so tightly coupled with actual source table in source sql server database, how would this power center mapping work in higher environments when it is migrated?

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

                    MULTIPUB=Y vs. MULTIPUB=N is a real decision point, as it affects everything you do downstream of that.


                    I make a habit of recommending solution architectures that tend towards stable and isolatable, as that improves operational resiliency, and that means less cases raised with GCS because things went wrong.

                    That's really a bit part of my goals in running the PWX forum - trying to help customers avoid problems.


                    As a result, I don't recommend MULTIPUB.


                    A PC Session can source from one and only one Listener, so having a session source from multiple actual source DBs means having all those source DBs use the same PWX Listener.

                    I don't recommend mixing sources in the same PWX Listener, for reasons of operational stability.

                    I recommend that each source have its own (PWXCCL + PWX Listener + config files).


                    *IF* you use MULTIPUB=Y, then you can have one PWXCCL for all sources flowing through the same DistDB.

                    Then you can have one PWX Listener extracting data from all that PWXCCL's CND files.


                    PWX DB2 i5 CDC directly supports journal/library name overrides, and wildcarding.

                    That is a very clean implementation.

                    PWX SQL Server CDC doesn't have equivalent functionality.


                    If you can accomplish it via PC parameterization, then yes, you can do that with any PWX CDC source, including PWX SQL Server CDC.


                    I think I've answered the question about SMO/RMO multiple times, on this forum, but I know not everyone reads all the posts.

                    No, PWX SQL Server CDC doesn't use SMO/RMO.

                    That changed at PWX 10.1.0.

                    MS announced that they were deprecating SMO/RMO, and we re-wrote our SQL Server CDC code to use ODBC, and then MS retracted their statements about SMO/RMO.

                    Kindof annoying at the time, but it means a cleaner implementation in many ways. Getting all the SMO/RMO related packages installed in the right places and right order could be a pain, and now none of that is needed.
                    • 22. Re: SQL Server PWX CDC architecture
                      vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

                      Thanks for the detailed explanation. if we created separate set of Listener + PWXCCL+ config files for each source dev database instance, can they all use one DistDB ? or a separate DistDB for each source db needs to be created to take full advantage of this architecture?




                      regarding # 3, i am confused with your suggestions from your last 2 replies. Sorry. May be i did not understand what you implied.  On the first one, you said, it is not possible. On the second one you said, "If you can accomplish it via PC parameterization, then yes........". My question is- let's say i have a table named same across 2 dev dbs and i create two pubilcations(registration group), one per source db,  that includes these tables(articles/capture registrations). DistDB has changed data from both the publications. PWXCCL extracts and condenses the changed data to CND files for both the tables. Can i have one single mapping with one source definition to extract data from either extraction map,one at a time , by parameterizing the extraction map name?

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

                        If you set up a distinct (PWX Listener + PWXCCL + Config files) for each source, as I recommended, then no, you can't do parameterization because a session talks with one and only one PWX Listener, and can source from one and only one set of CND files.


                        If you do what I suggest that you don't do, and use one PWX Listener for multiple source DB, and one PWXCCL for multiple source DB (you will need to set MULTIPUB=Y), then you can do parameterization.


                        Please understand that means any changes to any tables in any of the source DBs, will result in needing to stop and restart PWXCCL (since it is only one, for all of them).

                        If you arrange to manage that appropriately, then you should be OK.


                        If you work in a shop where different departments don't play well together, and your department does not have control over the source DBs, then you probably will encounter problems, and having only one PWXCCL may exacerbate the effects of those problems.

                        • 24. Re: SQL Server PWX CDC architecture
                          vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

                          Thanks again. I understand and am in agreement with you. But let me also provide more information around how the source system application and databases are. They have one prod db and multiple dev dbs for reasons only known to them. I can not change that. No QA dbs  at all. These dev dbs get refreshed periodically from prod. So in prod, we will be replicating from only one db via one Distdb  and one set of listener + PWXCCL + Config files. No issues there. when it comes to replicating from dev dbs, we will be replicating from only one dev db at any given time. but we may have to switch from one source dev db to another dev db from time to time depending on the data needs that arise from the projects that are going on in these dev dbs. What i wanted to achieve was not create one set of PWX components for each dev DB as 1) we will not replicate from all dev dbs at the same time 2) PWX processes and PC mappings sitting idle probably timing out, etc 3) this multiple dbs concept doesn't exist in production anyway 4) one set of PC mappings that can be parameterized so that they can source from any dev DB and we will only have this one set of mappings to migrate to production. But i also wanted PWX set up to account for refreshing and switching to different source dev dbs. So having different publications for each dev db but only one Distdb, Listener,PWXCCL, etc helps with 1) parameterization of PC mappings 2) Rebuild only that articles that are refreshed.In short, the goal is to keep the entire replication architecture(PWX + PC)  same across dev and prod given the unusual setup of dev dbs, And i think the said architecture meets in the middle.

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

                            OK.  That's a relief.


                            I am a lot less concerned about less-than-ideal practices for Dev than I would be for Prod.


                            I would suggest thoroughly documenting what you set up for Dev, and how to swap from one Dev source to another, in case someone else has to work on it in the future.

                            • 26. Re: SQL Server PWX CDC architecture
                              vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

                              Yes. We plan to do that. It will be part of our operational support documentation.


                              Thanks for your time and wisdom.

                              • 27. Re: SQL Server PWX CDC architecture
                                vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

                                We use windows authentication for SQL server. I am trying to figure out where do we mention this to connect to DistDB to capture changed data. DBMOVER or PWXCCL files? Below is the text from CDC Guide


                                For Microsoft SQL Server instances that use SQL Server Authentication, enter a database user ID that permits access to the SQL Server distribution database.

                                For SQL Server instances that use Windows Authentication, PowerExchange uses the user ID under which the PowerExchange Listener was started. In this case, do not specify

                                this parameter unless you want to specify another user.


                                I couldn't find a parameter that accepts the username in the DBMOVER file.

                                • 28. Re: SQL Server PWX CDC architecture
                                  vijay00u1efn99eu61Z7mS1d8 Seasoned Veteran

                                  i think i understood it. It's the windows service account using which Listener task was started. Before i ask a follow up question- let me remind you of our set up. Distribution DB is on the source system db server. PWX will be on the Power Center Server machine. We plan to use the same windows account for PWX that was used for Power center services. Now the question is, the windows service account that will be used for PWX tasks and that was used for Power Center will need to have read access to Distribution Database using windows authentication?

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

                                    If you are using PWXCCL, then you can code a login using the UID or CAPTURE_NODE_UID statement.

                                    - If you do this, code a password using the EPWD (for UID) or CAPTURE_NODE_EPWD (for CAPTURE_NODE_UID).


                                    Otherwise, it will be the OS login running whatever is performing capture.

                                    If you are using PWXCCL

                                    - and are not doing Remote Logging, that will be the login running PWXCCL

                                    - and are doing Remote Logging, that will be the login running the Capture side PWX Listener.

                                    If you are not using PWXCCL

                                    - that will be the login running PWX Listener.