-
30. Re: SQL Server PWX CDC architecture
vijay00u1efn99eu61Z7mS1d8 Dec 11, 2018 3:03 PM (in response to dan.smith@informatica.com)Yes. We plan to use PWXCCL. We will have all (PWXCCL, Listener, log files, cdct files, CND files and config files) on Power Center machine. Let's say we used windows service account "abc" for power center services. We plan to use "abc" for PWX components as well. So, this user "abc" will need to have access to dist db. correct?
-
31. Re: SQL Server PWX CDC architecture
dan.smith@informatica.com Dec 11, 2018 3:14 PM (in response to vijay00u1efn99eu61Z7mS1d8)Yes.
And SELECT on msRepl_Transactions and msRepl_Commands.
If you are using ENABLELWM, then it also needs DELETE, not just SELECT.
-
32. Re: SQL Server PWX CDC architecture
vijay00u1efn99eu61Z7mS1d8 Dec 11, 2018 3:16 PM (in response to dan.smith@informatica.com)Thank you.
-
33. Re: SQL Server PWX CDC architecture
vijay00u1efn99eu61Z7mS1d8 Dec 12, 2018 7:36 AM (in response to dan.smith@informatica.com)Quick follow up question- we could also use a SQL server login with SQL Server Authentication
using the UID or CAPTURE_NODE_UID statement and EPWD (for UID) or CAPTURE_NODE_EPWD (for CAPTURE_NODE_UID).. correct?
-
34. Re: SQL Server PWX CDC architecture
dan.smith@informatica.com Dec 12, 2018 7:55 AM (in response to vijay00u1efn99eu61Z7mS1d8)Yes
-
35. Re: SQL Server PWX CDC architecture
vijay00u1efn99eu61Z7mS1d8 Jan 24, 2019 12:35 PM (in response to vijay00u1efn99eu61Z7mS1d8)Dan,
Please answer below questions.
1) Will changing capture registrations to 'inactive' make the articles in publisher db inactive as well?
2) Following is the text from PWX command reference guide.
"If you run PowerExchange and PowerCenter on the same machine and under the same user account, you must create separate environments for PowerExchange and PowerCenter. To create the appropriate PowerExchange environment and start the PowerExchange Listener, run the pwxsettask.sh or pwxsettask.bat script. "
we will be running listener and logger on the PC machine but under a different user account. do we still have to use pwxsettask.bat? or can we just create a listener service using dtllstsi command?
3) Also, can we create a windows service for logger as well? I looked at the below KBs but i guess they help to run logger/listener in the background but not as a windows service.
-
36. Re: SQL Server PWX CDC architecture
dan.smith@informatica.com Jan 24, 2019 1:27 PM (in response to vijay00u1efn99eu61Z7mS1d8)1) Actually, I don't know the answer to this. I would expect so, but I can't state that definitively.
EDIT: I checked with a colleague on this, and the Articles should be removed from DistDB when you inactivate the capture registration.
2.a) If you are running under different accounts/logins, no.
The underlying problem here is that PWX and PC have a shared set of modules, but don't use the same code branches of those shared modules. PWX needs the PWX versions of those modules first in %PATH%, while PC needs the PC versions of those modules first in %PATH%.
pwxsettask.* rearranges the environment variables to work for PWX.
If you have different logins, each has its own set of environment variables.
2.b) Sure, as long as you are only running 1 PWX Listener that way.
3) No. PWXCCL doesn't have a service handler interface, so a Windows service can't control it.
(It could start it, but then the service would be disjoint from the process - it could not tell if PWXCCL was running, and could not sent commands to it, nor stop it.)
You really need to solve multiple problems:
1. starting PWXCCL in background
2. Windows shouldn't terminate PWXCCL when you sign off
3. You shouldn't have to login to start PWXCCL
4. You need some way to send commands to PWXCCL
That KB actually resolves 1. and 2.
1. because it runs in the background
2. because it runs as a headless process, so Windows doesn't kill it when you disconnect
You can resolve 3. by using Windows scheduler to start the script.
You can resolve 4. by using PWXCMD to send commands to PWXCCL
EDIT: I have edited that KB to make those more clear. It may take a day or so for the revised version to be visible in KB.
-
37. Re: SQL Server PWX CDC architecture
vijay00u1efn99eu61Z7mS1d8 Jan 24, 2019 1:10 PM (in response to dan.smith@informatica.com)Thanks Dan. Regarding 2b, why only one listener service. I could create many listener windows services using dtllstsi. correct?
I wish PWXCCL had a windows service handler interface.
-
38. Re: SQL Server PWX CDC architecture
dan.smith@informatica.com Jan 24, 2019 1:28 PM (in response to vijay00u1efn99eu61Z7mS1d8)Unfortunately, to really make PWX Listeners distinct, they each need to have their own DBMOVER.CFG, so that they can have separate directories (CAPT_PATH, CAPT_XTRA, LOGPATH, etc.), and distinct CAPI_CONNECTION statements.
All Windows Listener Services that you create with DTLLSTSI will share the same DBMOVER.CFG.
DTLLSTSI is legacy code from when customers ran only one PWX Listener per server, only SQL Server customers ran on Windows, and almost no SQL Server customers used PWXCCL.
The "modern" approach would be to use PowerCenter Admin Console Services for PWX Listener and PWXCCL, which do support distinct configuration files per service.
-
39. Re: SQL Server PWX CDC architecture
vijay00u1efn99eu61Z7mS1d8 Jan 24, 2019 1:41 PM (in response to dan.smith@informatica.com)Thanks again for the explanation. This is what i understand. Even though the Listeners have their own DBMOVER.CFG files, the DTLLSTSI uses the same DBMOVER.CFG file( i assume the one in PWX_HOME directory) and thus can not be used to manage these listeners.
-
40. Re: SQL Server PWX CDC architecture
dan.smith@informatica.com Jan 24, 2019 3:10 PM (in response to vijay00u1efn99eu61Z7mS1d8)When you use DTLLSTSI to create a listener service, you don't get to tell DTLLSTI what DBMOVER.CFG the PWX Listener should use.
All PWX Listener Services created by DTLLSTSI will use the default DBMOVER.CFG (the one named "dbmover.cfg" in the first PWX install directory found in Windows %PATH%).
If you want each PWX Listener to use a different DBMOVER.CFG, then you should either use the PowerCenter Admin Console Service for PWX Listener to create each, or use a combination of wscript, .bat, and Windows Scheduler, as in the KB below.
HOW TO: Run multiple PowerExchange Listeners in the background on Windows using wscript
-
41. Re: SQL Server PWX CDC architecture
vijay00u1efn99eu61Z7mS1d8 Feb 13, 2019 8:53 AM (in response to vijay00u1efn99eu61Z7mS1d8)Hi Dan,
I was reading about the below properties in pwxccl config file. Can you please help me understand which of these parameters comes into action at what point during capture process? As i understand COND_CDCT_RET_P does cleaning during startup, file switch or shutdown processing. If COND_CDCT_RET_P is taking care of clean up after expiration , when will LOGGER_DELETES_EXPIRED_CDCT_RECORDS come into action?
COND_CDCT_RET_P
LOGGER_DELETES_EXPIRED_CDCT_RECORDS
-
42. Re: SQL Server PWX CDC architecture
dan.smith@informatica.com Feb 13, 2019 9:19 AM (in response to vijay00u1efn99eu61Z7mS1d8)All LOGGER_DELETES_EXPIRED_CDCT_RECORDS does is to tell PWXCCL whether or not to actually delete the CND files when it goes through cleanup.
All COND_CDCT_RET_P does is define the retention period. When CDCT entries are older than that, they are marked expired.
If LOGGER_DELETES_EXPIRED_CDCT_RECORDS is defaulted or set to Y, then PWX also removes those records, and the related CND files.