Unsupported Data Type when using SQL Data Sync to Azure

Question

I'd like to sync an on-premise SQL Server 2012 SP2 database to Azure using SQL Data Sync. When I try to do the sync I get "Unsupported Data Type" error on one of the tables for the ID_Index column:

The Azure Management Portal gives no further explanantion for the error. The table design in SQL Server Management Studio:

The table creation script:

CREATE TABLE [dbo].[FlightPlanData](
  [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_FlightPlanData_ID]  DEFAULT (newid()),
  [Airline_ID] [int] NOT NULL,
  [FlightID_FK] [uniqueidentifier] NOT NULL,
  [FlightPlanID] [int] NOT NULL,
  [DateInserted] [datetime] NOT NULL CONSTRAINT [DF_FlightPlanData_DateInserted]  DEFAULT (getdate()),
  [Type] [varchar](20) NOT NULL CONSTRAINT [DF_FlightPlanData_Type]  DEFAULT (''),
  [FileName] [varchar](100) NOT NULL CONSTRAINT [DF_FlightPlanData_FileName]  DEFAULT (''),
  [ClientID_FK] [uniqueidentifier] NULL,
  [ID_Index] [int] IDENTITY(1,1) NOT NULL,
    CONSTRAINT [PK_FlightPlanData] PRIMARY KEY NONCLUSTERED ([ID] ASC))

CREATE CLUSTERED INDEX [IX_FlightPlanData] ON [dbo].[FlightPlanData]([ID_Index] ASC)

The table has a GUID primary key, but it's not clustered, instead we use a clustered in index (ID_Index). I can't remove the ID_Index column, and I'd prefer not to make it the primary key. Is there any way to solve this? I heard Azure requires a clustered index for each table, but it doesn't have to be the primary key. So what's the problem here?

Just a guess, but it's possible that SQL Data Sync doesn't support non-primary-key identity columns. What if you were to make ID_Index a uniqueidentifier instead of an int? — Marty

Alberto Morillo Alberto Morillo · Accepted Answer · 2017-11-16T04:46:45

A table cannot have an identity column that is not the primary key. This is one of the general requirements of SQL Data Sync. For more information, please visit this GitHub documentation.

Unsupported Data Type when using SQL Data Sync to Azure

1 Answers