The source is SQL Server 2016 and Target is Netezza 7.2 When a source table is being mapped to the target, The below message appears,
ERROR: An error has occurred while setting the replication method for dbo.CCM [An error occurred while turning on supplemental logging for dbo.CCM. Failed to get publication ID.]. Check the event log for related events and a possible cause.
SQL Server Replication is enabled with a local distributor database. We have checked the CDC event logs and the same error is logged, nothing much in detail. Any help on this would be appreciated.
You need to check the trace files. These are located in whichever folder you selected for instance data in the install. If you do not know this you can look at /conf/userfolder.vmargs - /instance/log
If you cannot find any useful information, then turn on detailed traces
1.) Management Console, configuration perspective, select the MS SQL Server datastore, properties, system parameters
2.) Add a new parameter global_trace_hours and specify a numeric value say 4
3.) Save
4.) The tracing is enabled dynamically - tracing will be set on for the number of hours you specify. The value will be automatically decremented every minute and then when it gets to 0 tracing is automatically and dynamically disabled
5.) Attempt to change the replication method to mirror again
6.) In the folder /instance/log/on you should find some files with data in
7.) Copy the trace file to a location with a short path (e.g. C:\TEMP) - or if it has already been zipped, unzip to C:\TEMP
8.) Open a command prompt as administrator
9.) Change directory to /bin
10.) Execute dmdecodetrace C:\TEMP\ | more
Note that the additional trace files are not full text, to minimize the impact of writing them, so need to be decoded
If you still do not get any pointers open a support ticket.
One potential cause could be that the table does not have a primary key. SQL replication requires a primary key, and as CDC is using the SQL replication to ensure that the full row images are logged in the transaction log, it is also a prereq for CDC as well.