Compressing Tables on SQL Server

[billmcevoy]

Is there a way to specify in QLIK Replication that when it creates target tables in SQL Server that it also enable compression?  It would be preferred to do it on a table by table basis.

What happens now is that we enabled compression in the target after QLIK created the tables, but when the task was restarted, the old tables were dropped and new tables added without compression.

We needed to restart the task to bring over new columns from the Oracle source, otherwise we would normally just truncate and import.

[MartinBurgos]

Hi Billmcevoy,

Hope all is good on your end.

Unfortunately, that won’t be possible because it’d slower the task RW process, resulting in bad performance.

Hope this helps!

Martin

[Dana_Baldwin]

Hi @billmcevoy 

If you would like to pursue this further, you can submit this as a feature request here for our Product Management team to evaluate:

https://community.qlik.com/t5/Ideas/idb-p/qlik-ideas

Thanks,

Dana

[john_wang · Accepted solution]

Hi @billmcevoy ,

Totally agree with @Dana_Baldwin . In general we may custom the target endpoint CREATE TABLE SQL statements to add some special settings eg COMPRESSION, NLS language, CODEPAGE, Storage setting etc. However there are a few of exceptions, SQL Server target is one of the exception unfortunately . That's why we need a FR.

In current Replicate version the available options:

1. We need to create the table with COMPRESSION properties manually prior to the task FULl Load by setting "If target table already exists" to "TRUNCATE before loading" in Replicate. With this setting Replicate will keep the COMPRESSION properties of the table.

2. Or, you may let Replicate create the target table automatically without COMPRESSION (so far we need not to create the table manually), after the Full Load done then compress the table manually, then resume the task.

alter table table_name
rebuild with (datacompression = page | row | none);

Hope this helps.

Regards,

John.

[billmcevoy]

Thank you for your answers everyone.  Even with compression on (I added it after the tables were created) performance is not an issue.  In fact, we are all very impressed with the performance.  

[john_wang]

Hello @billmcevoy ,

Thank you for you opening the FR:

https://community.qlik.com/t5/Suggest-an-Idea/Add-option-to-run-a-SQL-script-when-a-Replicate-task-i...

Regards,

John.