Disable DDL replication for MSSQL

[sergsyb]

There is a database which in addition to classic (disk-based) tables includes some memory optimized tables. If this DB  add to replication, several database triggers will be created to support DDL replication such as

tr_MStran_alterschemaonly , tr_MStran_altertable ,  tr_MStran_altertrigger,  tr_MStran_alterview, tr_MStran_droptable

But if we then try to alter memory optimized table it will fail with following error - Database and server triggers on DDL statements CREATE, ALTER and DROP are not supported with memory optimized tables.

We are ready not replicate DDL for this DB at all, to be able to alter memory optimized table, but there is not such option in MSSQL endpoint like – not replicate DDL.

It will be safe to just drop these triggers and continue to use DML replication only? Or may be there is another way not replicate DDL?

[Dana_Baldwin]

Hi @sergsyb 

Triggers on source objects are not replicated to the target, only things such as dropping/truncating/altering the table (column attributes for example). You can have the task ignore these DDL activities on the source by changing the default settings in Task Settings > Change Processing > Apply Changes Settings.

Please let us know if this helps, and if so mark the reply as an accepted solution.

Thanks!

Dana

[sergsyb · Accepted solution]

Hi Dana,

Our goal to get rid of database MSSQL system triggers like following

tr_MStran_alterschemaonly , tr_MStran_altertable ,  tr_MStran_altertrigger,  tr_MStran_alterview, tr_MStran_droptable

which were created automatically when MSSQL DB has been added to replication. These triggers block using DDL for memory optimized table in DB which has been added to replication.

But it seems I have found a solution I have just disable its

DISABLE TRIGGER tr_MStran_altertable ON DATABASE;

DISABLE TRIGGER tr_MStran_alterview ON DATABASE;

DISABLE TRIGGER tr_MStran_alterschemaonly ON DATABASE;

DISABLE TRIGGER tr_MStran_altertrigger ON DATABASE;

DISABLE TRIGGER tr_MStran_droptable ON DATABASE;

Now, DDL for memory optimized table run successfully now. A Qlik replication also works well.  And the most interesting is that QlikReplicate replicates DDL as well good as DML.

I wanted to disable DDL replication before, because I supposed that QlikReplicate uses these MSSQL system triggers to support DDL replication. But it seems QR using something own for this purpose.

[john_wang]

Hello @sergsyb , copy @Dana_Baldwin ,

Glad to hear you manage it to work.

To make it more clear, I'd like to understand more about the  Memory-Optimized table  in your environment. 

There are 2 durability options for memory-optimized tables, SCHEMA_AND_DATA  &  SCHEMA_ONLY.

SCHEMA_ONLY: no transaction logging records for the DML. It's meaningless to add this type of table to replication as no way to get record changes by parsing TLOG.

SCHEMA_AND_DATA : similar to a disk-based table, transaction logging is available. To my understanding you are running this type of memory-optimized table. Please confirm.

thank you,

John.

[sergsyb]

Hi John,

In my case I have both types of tables in one Database: memory optimized tables and classic disk-based tables.
Some disk-based tables from this DB should be replicated. But memory optimized tables don't need to be repicated , but for this tables we need to provide possibilty to change its DDL .
The issue raised when replication had been enabled, several system's triggers were created and they blocked changing DDL for memory optimized tables.

[john_wang]

Hi @sergsyb ,

Thank you for your clarification.

Best Regards,

John.