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

[LeonAus]

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?

[ChestonDobbins]

Hey LeonAus,

Sorry your encountering this behavior. Did you happen to have any support case opened for this issue? I think that might be the best way to proceed so that we can get the database backup in house to do some additional testing. 

Thanks, CD

[LeonAus]

I think we're going to work around it by creating a new geodatabase and importing the old database into it.

It will take longer than shifting the existing one (once working) but should be quicker than raising a support call to troubleshoot the shifting.

[ChestonDobbins]

That should definitely get you around the issue, but I would still like to investigate your database backup... Is there anyway you might be able to provide it to me directly so we can determine if there is a bug and prevent other users from hitting the same?

[NickKoopman]

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

[ChestonDobbins]

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

[MalcolmJ]

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!