SQL Azure Data Sync Error/Warnings

Question

Hi people, we are trying to use data sync and we got these warnings and this error messages:

<u5:p> </u5:p>

==================================================

##### WARNING: #####

<u5:p> </u5:p>

Sync completed with warnings in 9705.61 seconds.

                Upload:   0 changes applied/0 failed

                Download: 62458 changes applied/997 failed

<u5:p> </u5:p>

Data Sync will stop synchronizing changes for this sync group member in 44 days if the failures are not resolved.

               

<u5:p> </u5:p>

Download -

errors for first 5 rows that failed to apply:

Error #1: SqlException Error Code: -2146232060 - SqlError Number:2627, Message: Violation of UNIQUE KEY constraint 'IX_Organization_UrlCode'. Cannot insert duplicate key in object 'Organization.Organization'. The duplicate key value is (<NULL>). SqlError Number:3621, Message: The statement has been terminated.

<u5:p> </u5:p>

Error #2: SqlException Error Code: -2146232060 - SqlError Number:2627, Message: Violation of UNIQUE KEY constraint 'IX_Organization_UrlCode'. Cannot insert duplicate key in object 'Organization.Organization'. The duplicate key value is (<NULL>). SqlError Number:3621, Message: The statement has been terminated.

<u5:p> </u5:p>

Error #3: SqlException Error Code: -2146232060 - SqlError Number:2627, Message: Violation of UNIQUE KEY constraint 'IX_Organization_UrlCode'. Cannot insert duplicate key in object 'Organization.Organization'. The duplicate key value is (<NULL>). SqlError Number:3621, Message: The statement has been terminated.

<u5:p> </u5:p>

Error #4: SqlException Error Code: -2146232060 - SqlError Number:2627, Message: Violation of UNIQUE KEY constraint 'IX_Organization_UrlCode'. Cannot insert duplicate key in object 'Organization.Organization'. The duplicate key value is (<NULL>). SqlError Number:3621, Message: The statement has been terminated.

<u5:p> </u5:p>

Error #5: SqlException Error Code: -2146232060 - SqlError Number:2627, Message: Violation of UNIQUE KEY constraint 'IX_Organization_UrlCode'. Cannot insert duplicate key in object 'Organization.Organization'. The duplicate key value is (<NULL>). SqlError Number:3621, Message: The statement has been terminated.

<u5:p> </u5:p>

For more information, provide tracing id ‘678d8db0-b1ac-407e-8852-06f2bfe8e20a’ to customer support.

==================================================

<u5:p> </u5:p>

==================================================

##### ERROR: #####

<u5:p> </u5:p>

Database re-provisioning failed with the exception "Invalid column name 'PersonId'.

Inner exception: SqlException Error Code: -2146232060 - SqlError Number:207, Message: Invalid column name 'PersonId'.

"

<u5:p> </u5:p>

For more information, provide tracing id ‘e82c3a47-bb6f-49d3-8b57-dfefa61be0fe’ to customer support.

==================================================

<u5:p> </u5:p>

<u5:p> </u5:p>

<u5:p> </u5:p>

About the warnings we realized that the data sync didn't create the "IX_Organization_UrlCode" index correctly. See:

<u5:p> </u5:p>

==================================================

##### ORIGINAL "IX_Organization_UrlCode": #####

<u5:p> </u5:p>

USE [Agentto]

GO

<u5:p> </u5:p>

/****** Object:  Index [IX_Organization_UrlCode]    Script Date: 08/06/2012 08:51:32 ******/

CREATE UNIQUE NONCLUSTERED INDEX [IX_Organization_UrlCode] ON [Organization].[Organization]

(

                [UrlCode] ASC

)

WHERE ([UrlCode] IS NOT NULL)

WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

GO

<u5:p> </u5:p>

<u5:p> </u5:p>

##### SYNCED "IX_Organization_UrlCode": #####

<u5:p> </u5:p>

USE [Agentto]

GO

<u5:p> </u5:p>

/****** Object:  Index [IX_Organization_UrlCode]    Script Date: 08/06/2012 08:52:07 ******/

ALTER TABLE [Organization].[Organization] ADD  CONSTRAINT [IX_Organization_UrlCode] UNIQUE NONCLUSTERED

(

                [UrlCode] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)

GO

==================================================

What can I do in those situations?

Thanks all!

Answer 1

Hi,

SQL Server permits UNIQUE constraints to be created on nullable columns and treats nulls as unique values distinct from other values for the purposes of evaluating the unique constraint.

There is the possibily to circle this issue by creating unique filtered indexes. Or, you can also do (nasty thing to do, btw!!), is creating a constraint on a computed column or inserting a random GUID (uniqueidentifier) string or something meaningful, instead of NULL. So, your UrlCode column will never have NULLs.

Hope this helps!

---

Cheers, Carlos Sardo

Answer 2

did you modify the sync group's dataset to add a new column?

if you think the index was wrongly created, then simply drop and recreate it.

@Carlos, where does it say in the docs that filtered indexes are not supported in Azure SQL Database? the syntax in the link you reference includes the WHERE clause.

looking at the posted scripts, these seems to be from an on-prem database instead of Azure as well. is that right?

Answer 3

For below error:

Database re-provisioning failed with the exception "Invalid column name 'PersonId'.

You need to make sure column PersonId is created at both the member databases of the sync group, did you create the column on both side already?

Answer 4

Guys,

Thanks for the answers, but I didn't create any table/columm/index by hand. The proper Data Sync created them.

Should I create the whole Squema before sync?

Answer 5

if you want an exact copy of the database schema from source, its strongly adviced that you create them on the target before configuring sync.

the error you posted above (Invalid column name 'PersonId') indicates you have altered the Sync Group's Dataset. as suggested by Zhan Li, did you create the columns on both sides first?

see detailed steps for altering sync groups here: Edit a Sync Group

Answer 6

June,

I let the Data Sync create everything to me, but, as you advise, I will try to create the schema before sync.

I will let you know the results.

Thanks!

Answer 7

@Carlos, where does it say in the docs that filtered indexes are not supported in Azure SQL Database? the syntax in the link you reference includes the WHERE clause.

Edited my reply. ;) Thanks!

---

Cheers, Carlos Sardo