How to create new (missing) columns for entities in your Data Export Service Azure SQL database

Usually the Data Export Service (DES) automatically picks up changes on entities and creates new fields/columns in the Azure SQL database that is connected to your DES profile.

Our experience was that this automatism is not always reliable thus we had to come up with a workaround.

Microsoft Support suggested us to create missing columns on the export database ourselves manually, which I did. As there is currently no other official documentation on this topic I thought I should share this article with the rest of our awesome community.

1. Completely recreate the entity table

The 1st approach to solve this issue would be to go inside your DES profile and deselect the target entity from the list of entities to sync and save the changes. Next delete the target entities tables and user defined table types with the script provided here:

https://docs.microsoft.com/en-us/power-platform/admin/replicate-data-microsoft-azure-sql-database#how-to-delete-data-export-profile-tables-and-stored-procedures-for-a-specific-entity

Once deleted you can go back in your DES profile and re-enable the target entity. Once saved in the next sync run the entity table and table type will get recreated.

The Big BUT for this approach...

From my prospective this approach is only acceptable for DEV or TEST environments with little data, where it does not hurt to loose that data and resync everything again.

For production environments this way is a big NO GO. You could have further integrations that are dependent on that data, e.g. reports, ERP integrations or interfaces in 3rd party systems.

For this reason there needs to be another way to solve this problem...

2. Only amend the existing entity table and recreation of the related user defined table type

ALTER TABLE statement for the entity table that is missing the new columns

ALTER TABLE ov.account
ADD [ak_detergentsmanualtoolspy] [decimal](38, 4) NULL,
[ak_detergentsmanualtoolspy_base] [decimal](38, 4) NULL,
[ak_detergentsmanualtoolsytd] [decimal](38, 4) NULL,
[ak_detergentsmanualtoolsytd_base] [decimal](38, 4) NULL,
[ak_aftermarketservicesytd] [decimal](38, 4) NULL,
[ak_aftermarketservicesytd_base] [decimal](38, 4) NULL,
[ak_aftermarketservicespy] [decimal](38, 4) NULL,
[ak_aftermarketservicespy_base] [decimal](38, 4) NULL

Recreate User-Defined Table Type

The DES requires to have the same columns definitions for both the UDT table type and the entity table, otherwise it will throw an error while syncing.
As the UDT-Table types cannot be altered we need to recreate them.

On your data export database in the left navigation tree, open the Programmability folder, navigate to Types and search for your target entity type:

Select the type, right-click on it and create a script to create a new UDT type:

Keep the new script query open and add the missing lines to the bottom of the query:

CREATE TYPE [ov].[accountType] AS TABLE(
    [Id] [uniqueidentifier] NOT NULL,
    [SinkCreatedOn] [datetime] NULL,
    [SinkModifiedOn] [datetime] NULL,
.
.
.
    [ak_detergentsmanualtoolspy] [decimal](38, 4) NULL,
    [ak_detergentsmanualtoolspy_base] [decimal](38, 4) NULL,
    [ak_detergentsmanualtoolsytd] [decimal](38, 4) NULL,
    [ak_detergentsmanualtoolsytd_base] [decimal](38, 4) NULL,
    [ak_aftermarketservicesytd] [decimal](38, 4) NULL,
    [ak_aftermarketservicesytd_base] [decimal](38, 4) NULL,
    [ak_aftermarketservicespy] [decimal](38, 4) NULL,
    [ak_aftermarketservicespy_base] [decimal](38, 4) NULL
)
GO

Then on the left navigation pane select the old type and select "Delete" from the context menu.
Once deleted you can execute the new UDT type script and recreate the UDT table type.

Now wait until the next sync run and the data for the new columns should get exported to your new columns.