Qlik Replicate: When are DDL changes needed on an SQL Server source?

[Dana_Baldwin]

This article outlines how to handle DDL changes on a SQL Server table as part of the publication. 

Resolution

The steps in this article assume you use the task's default settings: full load and apply changes are enabled, full load is set to drop and recreate target tables, and DDL Handling Policy is set to apply alter statements to the target.

To achieve something simple, such as increasing the length of a column (without changing the data type), run an ALTER TABLE command on the source while the task is running, and it will be pushed to the target.

For example: alter table dbo.address alter column city varchar(70) 

To make more complicated changes to the table, such as: 

• Changing the Allow Nulls setting for a column
• Reordering columns in the table
• Changing the column data type
• Adding a new column
• Changing the filegroup of a table or its text/image data

Follow this procedure: 

1. Stop the task
2. Remove the table from the task
3. Remove the table from the publication.
   Some changes do not require this, and some do. Removing it from the publication will work in all cases. 
   1. Right-click the publication (starts with AR_) and select Properties
   2. Click to select the Articles tab
   3. Uncheck the box next to the table and click OK
   4. Copy all the data to a temp table
4. Recreate the table with the needed modifications
5. Copy the data back into the table
6. Add the table back to the task and resume. This will automatically add the table back to the publication and reload the table on the target. Reference: Saving changes is not permitted error message in SSMS

Environment

• Qlik Replicate