topic Re: SDE Database connection curruption in SQL server. in Geodatabase Questions

SDE Database connection curruption in SQL server.

PaulWirth — Wed, 18 Jan 2023 15:37:29 GMT

I have an SDE SQL database that has been somehow corrupted. I cannot connect to it from ArcGIS desktop anymore. It was 10.x something. I need help fixing this connection and if I can't somehow copy the data and design out of it and save it somewhere else.

Re: SDE Database connection curruption in SQL server.

VinceAngelo — Wed, 18 Jan 2023 15:49:16 GMT

More information is needed:

What exact RDBMS are you using? (If it's changed recently, we need both old and new versions)
What is the exact release of the geodatabase? ("10.x something" encompasses most databases produced in the past decade)
What is the exact release of the GIS client used to connect to the database?
Has anything changed on the system?

- V

Re: SDE Database connection curruption in SQL server.

PaulWirth — Wed, 18 Jan 2023 16:17:11 GMT

SQL server 2012 R2

I do not know the exact release sorry, it is an earlier one maybe 10.1

Re: SDE Database connection curruption in SQL server.

MarlonAmaya — Wed, 18 Jan 2023 16:35:00 GMT

@PaulWirth

If we do not know the specific version of the enterprise geodatabase. Can you provide the ArcGIS Client and version you are currently using?

If you can't cannot to it anymore, when was the last time you were able to connect to the database and was it using the same client you have now?

Can you provide an error message you receive from ArcMap/Pro?

Marlon

Re: SDE Database connection curruption in SQL server.

PaulWirth — Wed, 18 Jan 2023 16:46:33 GMT

I know I upgraded to 10.x from 9.1. Somehow something is wrong or got changed. Nothing happened to my other sde.

Re: SDE Database connection curruption in SQL server.

MarceloMarques — Wed, 18 Jan 2023 17:29:42 GMT

@PaulWirth

1. You need to upgrade the Geodatabase Repository

Client and geodatabase compatibility—ArcGIS Server | Documentation for ArcGIS Enterprise

2. Old versions of SQL Server are not supported with new version of ArcGIS Desktop.

You need to upgrade your SQL Server version to a supported version.

Microsoft SQL Server database requirements for ArcGIS 10.7.x and ArcGIS Pro 2.3 and 2.4—System Requirements | Documentation

You also need to upgrade the Microsoft ODBC Driver Version to a supported version when SQL Server is in one machine and ArcGIS Desktop is in another machine.

Re: SDE Database connection curruption in SQL server.

PaulWirth — Wed, 18 Jan 2023 20:09:21 GMT

Marcelo,

I understand with what you want me to do and I don't disagree. The problem I am having is that I have another SDE database in the same area and on the same SQL server 2012 R2 that I can connect to using my ArcGIS desktop 10.7.1 or ArcGIS Pro 3.0.3, with no problem. Is there some way to fix it without upgrading at this time because everything else is working, upgrading is a hassle with IT, and there is no guarantee that upgrading will work and now everything that works now doesn't.

Really all I want to do is copy all that info and design in that SDE database and paste it into another database if that one is somehow corrupt.

Re: SDE Database connection curruption in SQL server.

ShannonShields — Wed, 18 Jan 2023 21:53:35 GMT

It is likely that the geodatabase you can connect to has been upgraded to a 10.x release. Geodatabase upgrades are separate from upgrading ArcMap and ArcGIS Pro, as well as SQL Server.

Are you able to connect directly to the SQL Server instance using SQL Server Management Studio? If you can, look for a table called sde_version and compare the contents between the database you can connect to, and the one you to which you cannot.

Newer ArcGIS clients cannot upgrade geodatabases that are at versions earlier than 10.1. Major changes were made to the structure of geodatabases at 10.0, and upgrades to 10.0 or later releases could only be done from a geodatabase that was upgraded to 9.3.

Do you have access to older versions of ArcSDE software? This is going to be the only way to get the 9.1 database upgraded to a point where it can either be seen from 10.x and Pro software, or upgraded to something that can be connected to.
This doc from 10.1 https://resources.arcgis.com/en/help/main/10.1/index.html#/A_quick_tour_of_enterprise_geodatabase_upgrades/002q0000005p000000/
outlines
You can upgrade ArcSDE 9.3, 9.3.1, or 10 geodatabases to geodatabase release 10.1. If your geodatabase is at a lower release, you must first upgrade to a supported release, then upgrade to 10.1.
Getting to 9.3 from 9.1 is going to require ArcSDE software - upgrades prior to 10.x were done using ArcSDE software, not Desktop applications like ArcMap.

-Shannon

Re: SDE Database connection curruption in SQL server.

MarceloMarques — Wed, 18 Jan 2023 21:57:38 GMT

@PaulWirth - Please see @ShannonShields reply above. She is right on the problem there.

Re: SDE Database connection curruption in SQL server.

MarceloMarques — Wed, 18 Jan 2023 21:58:03 GMT

@PaulWirth - Please see @ShannonShields reply below. She is right on the problem there.

Re: SDE Database connection curruption in SQL server.

PaulWirth — Wed, 18 Jan 2023 22:41:08 GMT

Shannon

I don't understand it, but it happened. In the version under the database that doesn't work it says 9.1. I know I upgraded about 10 years ago to 9.3 and then to 10.1, but somehow it got changed (IT). The other one that works says 10.7.1. I don't use this database obviously on a regular bases or else I would have known when the problem happened. I didn't create it, but I do want the data, table, design from it, so I can clean up the sde databases.. Is there a way to do this in sql server using some database diagrammer or design programm that captures the tables and how they are connected in sql server. I remember hearing something about that more than 10 years ago from an ESRI support specialist that worked with SDE databases.

Re: SDE Database connection curruption in SQL server.

ShannonShields — Thu, 19 Jan 2023 00:22:42 GMT

Could it be that the database was overwritten with an older backup? Could you check with whomever manages the SQL Server instance to see if you can get the upgraded database back?
There isn't any straightforward, or supported way to get anything out of a really old ArcSDE database without ArcGIS software. The data pre-dates SQL Server spatial types, which wasn't introduced until 2008, so all the spatial data would still be in sdebinary format. The 'geodatabase' component was in the old model where any behavior (subtypes, domains, relationships etc) was stored in blob formats. Before trying to hack anything at the database level I'd exhaust the possibilities of getting a newer backup restored, or getting access to old ArcSDE software to perform the upgrade.

Re: SDE Database connection curruption in SQL server.

PaulWirth — Thu, 19 Jan 2023 14:33:18 GMT

Well, it must have happened a while ago and IT will be unable to restore it. The other option is also not available to me either because I do not have the 9.1 - 9.3 sde disc with me anymore. I remember throwing it away because I upgraded everything to 10.x and IT reverted it back to 9.1. Any other ideas would be appreciated.

Re: SDE Database connection curruption in SQL server.

MarceloMarques — Thu, 19 Jan 2023 22:58:17 GMT

@PaulWirth - instead of going through the process to upgrade the geodatabase, which will require more labor and you might face other issues and it will require legacy ArcSDE Software Binaries that you don't have anymore or are difficult to find, the only other option I can think is for you to find the ArcGIS Desktop ArcCatalog/ArcMap 10.1-10.3 Software Installer then you "might" be able to connect to the Geodatabase at version 9.1, then you can copy the data into a File Geodatabase.

ArcGIS Help 10.1 - Client and geodatabase compatibility

ArcGIS Help 10.2 - Client and geodatabase compatibility

ArcGIS Help 10.3 - Client and geodatabase compatibility

I am not sure how you can download legacy software. I don't think this will be available for you in "my.esri.com". You might have to contact Esri Technical Support or your Esri Account Manager to find more information.

Re: SDE Database connection curruption in SQL server.

PaulWirth — Thu, 19 Jan 2023 20:46:01 GMT

Marcel,

I do have ArcGIS 10.1 disks. Should I call Tech support to get some help with licensing? Will 10.1 Desktop work?

Re: SDE Database connection curruption in SQL server.

MarceloMarques — Thu, 19 Jan 2023 23:03:29 GMT

@PaulWirth

ArcGIS 10.1-10.3 will not be able to connect unfortunately.

I read through the archived arcgis help of the old releases.

See below for more information.

Problem 1:

ArcGIS Desktop 9.2/9.3/10.0 cannot connect to a Geodatabase 9.1 using "direct connect"
ArcGIS Desktop 9.1 can connect to a Geodatabase 9.1 using "direct connect"
this means you do not need the ArcSDE 9.1 Installer and you do not need to create the ArcSDE Service, you can simple use "direct connect" if you use ArcGIS Desktop 9.1
otherwise, if you use ArcGIS Desktop 9.2/9.3/10.0 you will need the ArcSDE Service 9.1 to be able to connect.

Desktop Help 10.0 - Client and geodatabase compatibility (arcgis.com)

ArcGIS Desktop Help 9.3 - Client and geodatabase compatibility (esri.com)

Old Help Documentation: Archive—Links to archived documentation for ArcGIS 10.2 and earlier

Problem 2:

ArcGIS Desktop 9.1 and ArcSDE Service 9.1 does not support SQL Server 2012
I could not find any system requirements page to confirm this, seems the web help was archived and is not available

Workaround:

the only viable solution then is to upgrade the geodatabase repository first using ArcSDE 9.3 hence you will need to obtain the ArcSDE 9.3 Installer and use the ArcSDE command line to upgrade the Geodatabase
then ArcGIS Desktop 9.3.1/10.0/10.1/10.2/10.3 will be able to connect to a Geodatabase 9.3 using "direct connect"
Problem: ArcSDE 9.3 does not support SQL Server 2012
arcgis_server 9.3 system requirements.pdf (esri.com)
arcsde 9.3_microsoft_sql_server_database_requirements.pdf (esri.com)
you might still give it a shot and see if ArcSDE 9.3 is able to connect to the SQL Server 2012 Geodatabase and perform the Geodatabase Repository Upgrade from 9.1 to 9.3.

Caveat - I would take a database backup of the SQL Server Geodatabase 9.1 and restore it on another machine then I would do the Geodatabase Repository Upgrade tests there first.

Please, open an Esri Technical Support Ticket and Support will assist you further to upgrade the geodatabase. This can be a difficult task and there are other factors here to consider e.g. Windows Operating System Version, SQL Server Version, Microsoft ODBC Driver Version, etc.

Re: SDE Database connection curruption in SQL server.

PaulWirth — Fri, 20 Jan 2023 16:18:05 GMT

Marcelo,

Thank you for the info. I was going to ask some questions after I read it but you summed it up perfectly.

To set up a ticket, which area do I need to contact for support? Desktop, enterprise, Database? It is kind of complex.

Re: SDE Database connection curruption in SQL server.

MarceloMarques — Fri, 20 Jan 2023 16:21:55 GMT

@PaulWirth - Enterprise Geodatabases

Re: SDE Database connection curruption in SQL server.

PaulWirth — Fri, 20 Jan 2023 16:24:06 GMT

Thanks