topic Re: Migration to Azure SQL Database, upgrade geodatabase error - duplicate key in object 'sde.SDE_table_registry', Enterprise geodatabase in Geodatabase Questions

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

LeonAus — Wed, 06 Oct 2021 05:38:02 GMT

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?

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

ChestonDobbins — Thu, 21 Oct 2021 17:17:53 GMT

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

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

LeonAus — Thu, 21 Oct 2021 21:37:13 GMT

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.

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

ChestonDobbins — Thu, 21 Oct 2021 21:43:01 GMT

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?

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

NickKoopman — Thu, 28 Oct 2021 19:39:01 GMT

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

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

ChestonDobbins — Fri, 29 Oct 2021 14:27:47 GMT

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

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

MalcolmJ — Wed, 26 Oct 2022 22:52:11 GMT

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!