Revise documentation for Upgrade Geodatabase GP tool when upgrading a SQL Server-based enterprise geodatabases to a version 11 geodatabase.

JamiePetersen

I recently upgraded a version 10.8.1 enterprise SQL Server geodatabase to version 11 using ArcGIS Pro 3.0 Upgrade Geodatabase GP tool. Doing so, come to find out by design, altered the way the geodatabase items are named. In v11 it no longer uses the [databasename].[schema].[tablename] format and is now only [schema].[tablename]. This change broke all of my enterprise paths in models and python scripts for things like feature class paths, field mapping paths, etc. I wasn't expecting that, and it was a big chore to repair. Please update the documentation to warn of that change when upgrading between geodatabase versions prior to v11.

https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/upgrade-geodatabas...

MarceloMarques · ‎11-02-2022

@RandyKreuziger1 - the best practice and recommendation if you are running ArcGIS Enterprise 10.9.x / 11.x is to upgrade the Geodatabase Repository using the companion ArcGIS Pro version going forward.
Client and geodatabase compatibility—ArcGIS Server | Documentation for ArcGIS Enterprise - Latest Release

AngelaVanderpas1 · ‎11-02-2022

So once I update sql then it will give me the option to upgrade in pro. It
is currently greyed out.

AngelaVanderpas1 · ‎11-02-2022

@MarceloMarques

I read the documentation. Thank you it is very helpful. I checked in pro for the option to upgrade. It is greyed out, so I am assuming that it will be available when we upgrade sql?

Everything else (arcserver, portal) is already sitting at 10.9.1 but the geodatabase is showing 10.6.1.

Are there any issues if you are federated?

MarceloMarques · ‎11-02-2022

@AngelaVanderpas1

Upgrade an enterprise geodatabase in SQL Server—ArcGIS Pro | Documentation

the Geodatabase Upgrade option is grayed out because you have SQL Server 2012, once you upgrade to SQL Server 2019 the option shall be enabled, but keep in mind that to upgrade the Geodatabase Repository you need 1. take a full backup of the database 2. stop all ArcGIS Server Services 3. disconnect all users connect via ArcMap or ArcGIS Pro 3. add the sde user as db_owner 4. connect as the sde user and perform the geodatabase repo upgrade 5. remove sde user as db_owner 6. take another full database backup.
The fact ArcGIS Enterprise (Portal + Server) is federated is not related with Geodatabase Repository Upgrades.

ArcGIS 11.0 and ArcGIS Pro 3.0 requirements for Microsoft SQL Server—ArcGIS Enterprise | Documentati...
it is important that you install the latest Microsoft ODBC Driver 17 for SQL Server in the machine running ArcGIS Enterprise, if SQL Server in running on the same box as ArcGIS Enterprise, then after the upgrade to SQL Server 2019 that will already be installed.

AngelaVanderpas1 · ‎11-03-2022

@MarceloMarques

The upgrade button in pro is currently greyed out. Will it become active after sql is upgraded?

TimMinter · ‎05-30-2023

@MarceloMarques, @ChestonDobbins - The documentation is a little light on details. After tossing out, "breaking changes" concepts like the changes to the fully qualified names for enterprise geodatabase objects, it would be helpful to specify what we can expect to break, suggest mitigations, etc.

So, when I upgrade my "10.9.1.2.9" enterprise geodatabases in SQL Server 2019 to "ArcGIS Pro 3.1.0 - 11.1.0.41833" enterprise geodatabases, should I expect some, all, or none of the following to break?

APRX/LYRX layer connections to EGDB feature classes, tables, etc.
ArcGIS Enterprise Server map services that use the EGDB feature classes, tables, etc.
Python 3 scripts
ModelBuilder models
other stuff?

thx,

tim

MarceloMarques · ‎05-30-2023

@TimMinter - the geodatabase repository upgrade will not break map document layer connections to eGDB featureclasses, tables, etc, as long as the connection properties, Server Name, SQL Server Instance Name and Port remain the same. The same applies to ArcGIS Server Services.

Now for Python 3 scripts and ModelBuilder models, there is always a small chance that the new version of ArcGIS Pro has enhcements and changes in the API thus is always necessary to test your scripts to make sure they will work with the new version of ArcGIS Pro and with the upgraded eGDB.

That is the reason the upgrade needs to be planned carefully and typically we restore a copy of the eGDB in another server to test the upgrade to determine if there will be any issues.

I hope this helps.

TimMinter · ‎05-31-2023

@MarceloMarques - Thank you. Revealing a bit more about my situation...

ArcGIS Pro clients are all at 3.1.2
ArcGIS Enterprise components are at 11.1.x
Python 3 scripts, ModelBuilder models, and similar incantations are all functioning more or less as expected.
All of the above are working happily enough with the 10.9.1.2.9 enterprise geodatabase in SQL Server 2019 via ODBC Driver 18.
I manage enterprise geodatabases in sandbox, development, test, and production environments. For an upgrade like this, I'll restore a backup of the production databases to the development instance, and then operate on those, iterating until things go green.
I'll deal with my custom SQL code, SQL Server views, stored procedures, triggers, etc. as I need to. I own the costs and risks of that stuff getting busted by Esri's enterprise geodatabase changes.
We dropped all support for ArcGIS 32-bit apps in February 2022, so if any linger and someone yells, we'll use it as an opportunity to get them current.

So, given those conditions, it sounds like you're suggesting minimal or no trouble if I perform the recommended upgrade process from 10.9.1.2.9 to "ArcGIS Pro 3.1.x - 11.1.y.z" in my development SQL Server 2019 environment. Does that sound about right?

Your answer helps me design my upgrade process. I'm looking to short-circuit as much of the process as possible, just expecting that there is a happy path I need to find. Ideally, that happy path is laid out for me in the documentation. I'm asking about all this stuff because my experience with the documentation is that there are frequently gaps between what it states and how the software works.

thx,

tim

MarceloMarques · ‎05-31-2023

@TimMinter
"So, given those conditions, it sounds like you're suggesting minimal or no trouble if I perform the recommended upgrade process from 10.9.1.2.9 to "ArcGIS Pro 3.1.x - 11.1.y.z" in my development SQL Server 2019 environment. Does that sound about right?"

Yes, it is a minor geodatabase repository upgrade, in your case from 10.9.1.2.9 to 11.1.0.3.1, and you shall have no trouble or just some minimal changes that need to be made.

But keep in mind the following changes to the SQL Server Geodatabase introduced at ArcGIS Pro 3.0, you might already be aware of these changes, but I wanted to list them here in case someone else reads this thread.

ArcGIS Pro Help

Methods to move a geodatabase in SQL Server—ArcGIS Pro | Documentation

When you move a SQL Server database that contains a 10.9.1.x or earlier version geodatabase,
you cannot rename the database when you move it. When you restore the database, for example,
you are given the opportunity to restore it with a different name. Don't do this with a geodatabase;
you won't be able to connect to it.
When you move a SQL Server that contains an 11.x version geodatabase, you can rename the
database when you move it.

What's new in ArcGIS Pro 3.1—ArcGIS Pro | Documentation

Enterprise geodatabases and databases

Enterprise geodatabases in Microsoft SQL Server that you create or upgrade in ArcGIS Pro 3.0
allow you to restore the SQL Server database with a different name from the original name.

Changes associated with this functionality mean that the following is true of 11.x geodatabases in

SQL Server:

ArcGIS Pro 2.2 and earlier versions cannot connect to them.

ArcGIS 10.6.1 and earlier versions cannot connect to them.

Table and feature class names no longer include the database name.

For example, a table named productdata.dataowner.inventory in a 10.9.x geodatabase is named
dataowner.inventory starting with 11.x geodatabases.

Also, read this article: Restoring SQL Server Databases with a New Name (esri.com)

In case you need more info on how to move a SQL Server Geodatabase read my white paper.

How to Move the SQL Server Enterprise Geodatabase with a Database Backup

For more advanced best practices visit my community.esri.com blog post at Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

I hope this helps with your upgrade.

TimMinter · ‎05-31-2023

@MarceloMarques - Got it. Thanks again. If there were a "Kudos" button for your replies, I'd be clicking them wildly 🙂.