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?
PowerExchange CDC for Linux/UNIX/Windows sources: How do the PowerExchange and PowerCenter components connect?
PowerExchange CDC for SQL Server: Components and Data Flow
Where should PowerExchange CDC for SQL Server components run?
PowerExchange CDC for SQL Server: Example Configuration Files for use with PWXCCL
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.
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.
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.
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.
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?
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.