Migration to Azure SQL Database, upgrade geodatabase error - duplicate key in object 'sde.SDE_table_registry', Enterprise geodatabase

Question

Hi,

I am trying to migrate an on-prem 10.1 enterprise geodatabase from SQL Server to a 10.8.1 geodatabase in Azure SQL Database.

Using this technical article as a guide, we have upgraded the on-prem 10.1 geodatabase to 10.8.1 using the upgrade geodatabase tool  (worked ok after giving the sde user the right permissions).

Then migrated the geodatabase into an Azure SQL Database using the Azure Data Migration Assistant , then ran the 10.8.1 upgrade geodatabase tool a second time (to update the stored procedures) but in doing so got the following error:

Could not update server tables and stored procedures. (sde_branches : Error (-37)
  DBMS error code: 2627
[Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Violation of UNIQUE KEY constraint 'registry_uk2'. Cannot insert duplicate key in object 'sde.SDE_table_registry'. The duplicate key value is (SDE_BRANCHES, SDE, [database name]).

There is no further information in the log files referenced in the error. The geodatabase isn't versioned, though I did try a compress but that had no effect.

Is this a bug with the upgrade geodatabase tool using an Azure SQL Database or is there a permission or step that I am missing?

MalcolmJ · Answer

I just had this exact same problem and was baffled to what was causing it. Found your post and your solution fixed it for me as well. For some reason on one of the five SDE databases I was upgrading, the 'sde' user was set to 'dbo' for default schema but not the others. Very strange!

ChetDobbins · Answer

Thanks NickKoopman, We will try the SDE user with DBO default schema scenario to see if we can reproduce the issue.

NickKoopman · Answer

Hi there. I just had the exact same problem with the exact same error code. I managed to fix it by checking the login properties of the "sde" user in SQL Server Management Studio. The default schema was listed as "dbo". When I updated it to "sde", then the problem was fixed and I could upgrade the database. Hope that points you in the right direction