HAS_REPLICATION_FILTER on published table

[Orlandis]

Hello, community.
We encountered the following difficulties while migrating data from SQL Server to Attunity.
Our version is SQL Server 2022 Enterprise CU 17
The problem is this: when we try to perform a column change, it could be changing the type from varchar to nvarchar or increasing the length of the field, changing null to not null, we get the message - 
Msg 4928, Level 16, State 1, Line 6
Cannot alter column 'Name' because it is 'REPLICATED'
In this case, the column being changed is not included in the primary key

We noticed this feature, on almost all tables that are present in the publication on the SQL Server side, the has_replication_filter property is set to 1, but in fact, in the publication properties, no filters are set for any table.

It is because of this property that we cannot make valid changes and are forced to delete the table from the publication and add it again each time.

Perhaps someone has encountered this problem and understands the reason.

[OritA]

Hi, 

The description of your problem seems to be related to MS-SQL replication rather then to Qlik Replicate. In any case is you wish, please open a case with the details of the problem and attach to it the task disagnostic package so our support team can review your settings and try to help you. 

Regards,

Orit

[Orlandis]

Good day.
My question is related to Qlik replicate, the consumer that takes data from SQL Server is Attunity.
The status has_replication_filter on the table changes to 1 when a new table is added to the Attunity task.

[DesmondWOO]

Hi @Orlandis ,

Are you using MS-Replication or MS-CDC? What is your DDL statement?

I ran the following statement on SQL Server 2022, but I was unable to reproduce your error.

alter table [src_latin1].[dbo].[test02] ALTER COLUMN NAME NVARCHAR(30)

CDC on my table was enabled by Qlik Replicate.

Regards,
Desmond

[Orlandis]

Hi.
We are using MS-Replication with Atunity.
If you execute the query select name, is_replicated, has_replication_filter from sys.tables, where name= 'test02' and has_replication_filter field value is 0, you will not get an error when executing the DDL operation. The problem is that as soon as the Atunity admin simply adds a table to his task, it is added to the publication and gets the status 1 for the has_replication_filter field.

[john_wang · Accepted solution]

Hi @Orlandis ,

In SQL Server, the column has_replication_filter in sys.tables is a flag indicating whether a table is participating in replication with a row filter applied.

When Qlik Replicate adds an article, it sets a default filter (@filter_clause = N'(1=0)'). This filter technically excludes all rows from being replicated to subscribers, but for Qlik Replicate it serves no functional purpose. Its only role is to ensure that sufficient information is recorded in the TLOG for Qlik Replicate tasks.

Observed behavior:

1. With has_replication_filter = 1, regular DDL commands such as the following can still be executed successfully:

   ALTER TABLE testrepfilter ALTER COLUMN name CHAR(30);
   ALTER TABLE testrepfilter ALTER COLUMN name CHAR(40) NOT NULL;
   ALTER TABLE testrepfilter ALTER COLUMN name VARCHAR(40);

2. Errors occur when using system stored procedures like:

   EXEC sp_rename 'dbo.testrepfilter.name', 'name2', 'COLUMN';

   Notably, this error persists even if has_replication_filter = 0.

3. Summary:
   SQL Server restricts certain schema changes (for example, renaming a column) to protect replication consistency. If the DDL operation is supported by Qlik Replicate, it will be replicated as-is (with some limitations). If the DDL is blocked by SQL Server, and you want to keep the filtered replication in place, the safest approach is:

   1. Drop the table from replication.

   2. Apply the schema change.

   3. Re-add the table back to replication articles list.

   Be aware that this may also require the table to be reloaded in the Qlik Replicate task.

Hope this helps.

John.

[Orlandis]

Hi @john_wang 
Thank you for your comment.

As for sp_rename, this is a classic MS_replication behavior, I do not argue with this.
But the question remains open, you claim that the DDL operations specified in point 1 of your comment with the value has_replication_filter = 1 for the table should be performed without errors. 
We listened to your words and checked it out. In classic replication we manually created a transactional publication, added a filter to the table, added a subscriber to another MS SQL Server database, made a snapshot and performed a DDL operation on the publisher, this operation was successful and was displayed on the subscriber. 

What is the reason then?

We managed to simulate such behavior only in one case: it is necessary to manually create a publication on SQL Server, start the snapshot creation and stop the snapshot creation while the agent is running, i.e. not allow the agent to complete the snapshot. After this action, has_replication_filter = 1 on all tables and the execution of the DDL operation becomes impossible. Perhaps something similar somehow arose from Attunity and thus affected the publications and the tables in them?

[john_wang]

Hello @Orlandis ,

Thanks for the detailed explanation.

In classic replication we manually created a transactional publication, added a filter to the table …

This is very similar to what Qlik Replicate does when preparing a table for CDC. The difference is that Qlik Replicate does not require a subscriber to be added.

It is necessary to manually create a publication on SQL Server, start the snapshot creation, and stop the snapshot creation while the agent is running …

Qlik Replicate does not require snapshot creation. The behavior you’re describing and the resulting error occur entirely within SQL Server itself, outside of Qlik Replicate’s scope. I would suggest consulting Microsoft for deeper insight on this behavior.

If you’d like to see exactly what Qlik Replicate does when preparing a table for CDC, you can set SOURCE_CAPTURE to Verbose, re-run the task, and then review the task log files for the backend SQL statements executed.

For example, here is a sample of an article creation statement recorded in the verbose task log. I hope this helps.

if not exists
(
select a.name from sysarticles a,sysobjects o
where a.[objid]=o.[id]
and schema_name(o.[uid]) =N'dbo'
and object_name(a.[objid])=N'testrepfilter'
and a.[type] in(1,3,5,7)
and a.status & 1 = 1
)
begin

-- Adding the transactional articles
exec sp_addarticle
@publication = N'AR_PUBLICATION_00005', --Publication Name
@article = N'AR_ARTICLE_00005_267147997', --Article Name
@source_owner = N'dbo', --Owner
@source_object = N'testrepfilter', --Table
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'drop',
/*@schema_option = 0x00000000000000F3,*/
@schema_option = 0x050D3,
@identityrangemanagementoption = N'none',
@destination_table = N'testrepfilter', --Table
@destination_owner = N'dbo', --Owner
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL [sp_MSins_testrepfilter]', --Table
@del_cmd = N'CALL [sp_MSdel_testrepfilter]', --Table
@upd_cmd = N'MCALL [sp_MSupd_testrepfilter]',--Table
@filter_clause = N'(1=0)'

-- Adding the article filter
exec sp_articlefilter
@publication = N'AR_PUBLICATION_00005', --Publication Name
@article = N'AR_ARTICLE_00005_267147997', --Article Name
@filter_name = N'AR_FILTER_00005_267147997', --Filter name
@filter_clause = N'(1=0)',
@force_invalidate_snapshot = 1,
@force_reinit_subscription = 1
end

Once again, thank you for your outstanding support and the effort you’ve put into analyzing this at such a detailed level.

My Best Regards,

John.