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

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

JamiePetersen — Tue, 06 Sep 2022 18:15:57 GMT

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-geodatabase-sqlserver.htm

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

MarceloMarques — Wed, 07 Sep 2022 18:52:40 GMT

This change in the SQL Server Geodatabase at 11.0.0.3.0 ( ArcGIS Pro 3.0 ) was announced.
See the links below for more details.

Restoring SQL Server databases with a new name (esri.com)

What's new in ArcGIS Pro 3.0—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:
o ArcGIS Pro 2.2 and earlier versions cannot connect to them.
o ArcGIS 10.6.1 and earlier versions cannot connect to them.

o Table and feature class names no longer include the database name.
o For example, a table named productdata.dataowner.inventory in a 10.9.x geodatabase
is named dataowner.inventory starting with 11.x geodatabases.

The Pro 3.0 Online Help Documentation should have included this very important information as well.

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

I hope this clarifies.

Thanks.

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

JamiePetersen — Tue, 06 Sep 2022 18:39:27 GMT

Thank you for the reply. I am aware that this is new, expected functionality. However, it furthers my suggested idea. The GP tool that performs this upgrade, in my opinion, should warn SQL Server users of this change in the documentation, as it affects every single enterprise SQL database prior to v11. I do not think the release notes are in peoples minds as they are referencing this tool's help; they weren't in mind. It is a major change, one worthy of a note in the documentation. I think it will save other customers from facing major problems like I had.

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

ChestonDobbins — Thu, 08 Sep 2022 17:29:56 GMT

Hey Jaime, What is the chance that you could share one of the python scripts that was broken as a result of this issue? We tried to ensure that scripting wasnt affected, but there is certainly a chance we missed something.

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

JamiePetersen — Fri, 09 Sep 2022 13:57:18 GMT

@ChestonDobbins the issue is about paths.

Declared variables, field maps for appends, etc are all broken by this change.

Parcels = "\\\\files\\GIS\\Scripts\\Database Connections\\ParcelFabric.sde\\ParcelFabric.DBO.ParcelEditing\\ParcelFabric.DBO.ParcelFabric_Parcels" arcpy.Append_management("Subdivisions", "Sub_FabricExport", "NO_TEST", "Sub_Name \"Sub_Name\" true true false 255 Text 0 0 ,First,#,\\\\files\\GIS\\Scripts\\Database Connections\\ParcelFabric.sde\\ParcelFabric.DBO.ParcelEditing\\ParcelFabric.DBO.ParcelFabric_Parcels"

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

ChestonDobbins — Fri, 09 Sep 2022 14:43:01 GMT

Thanks @JamiePetersen I will investigate this further and we will also update the documentation.

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

ChestonDobbins — Fri, 09 Sep 2022 15:32:15 GMT

I quickly tried this arcpy script using ArcGIS Pro 3.0 and a 3.0 geodatabase and it works for me:

states_fc = "C:\\temp\\cdobbins1.sde\\testDB.MAP.USA\\testDB.MAP.states" states_1_fc = "C:\\temp\\cdobbins1.sde\\testDB.MAP.USA\\testDB.MAP.states_1" arcpy.management.Append(states_fc, states_1_fc, "NO_TEST", r'STATE_NAME "STATE_NAME" true true false 25 Text 0 0,First,#,states_fc,STATE_NAME,0,25;STATE_FIPS "STATE_FIPS" true true false 2 Text 0 0,First,#,states_fc,STATE_FIPS,0,2;SUB_REGION "SUB_REGION" true true false 7 Text 0 0,First,#,states_fc,SUB_REGION,0,7;STATE_ABBR "STATE_ABBR" true true false 2 Text 0 0,First,#,states_fc,STATE_ABBR,0,2;POP1990 "POP1990" true true false 8 Double 8 38,First,#,states_fc,POP1990,-1,-1;POP2000 "POP2000" true true false 8 Double 8 38,First,#,states_fc,POP2000,-1,-1;POP90_SQMI "POP90_SQMI" true true false 4 Long 0 10,First,#,states_fc,POP90_SQMI,-1,-1', '', '')

Based on your example I suspect you might be using ArcMap to run these scripts, can you confirm?

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

JamiePetersen — Mon, 12 Sep 2022 13:13:29 GMT

*Edited for correctness:

That's true. Python 2.7 did eventually work, but only after repairing the paths.

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

AngelaVanderpas1 — Wed, 02 Nov 2022 22:14:09 GMT

Has anyone upgraded sql separate from a complete upgrade of enterprise. I am trying to determine if there is anything that needs to occur on the ESRI side. We are at 10.9 and I would like to stay there but out SQL server is at 2012, so I can not create any new enterprise geodatabases.

My plan was to do a sql backup and a vm snapshot, then run the sql upgrades and restore. It looks like I will need then "upgrade" the geodatabases, but I am unsure that I am interpreting the documentation correctly.

ESRI tech said they do not provide information / support for sql upgrades. Which I understand, but I would like to know if there are any gotchas on the ESRI side to be aware of.

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

MarceloMarques — Wed, 02 Nov 2022 22:29:35 GMT

@AngelaVanderpas1 - see the documentation links below.

ArcGIS 11.0 and ArcGIS Pro 3.0 requirements for Microsoft SQL Server—ArcGIS Enterprise | Documentation for ArcGIS Enterprise

- yes, you can upgrade SQL Server from 2012 to 2019, then after this is done you can upgrade the Geodatabase Repository.

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

- the best practice is to upgrade the Geodatabase Repository to the same version of the ArcGIS Enterprise deployment (Portal / ArcGIS Server) to keep things in sync.

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

RandyKreuziger1 — Wed, 02 Nov 2022 22:32:58 GMT

@MarceloMarques

When will SQL geodatabase upgrades stop being compatible with ArcGIS Desktop (ArcMap)?

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

MarceloMarques — Wed, 02 Nov 2022 22:45:25 GMT

@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

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

AngelaVanderpas1 — Wed, 02 Nov 2022 23:02:30 GMT

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

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

AngelaVanderpas1 — Wed, 02 Nov 2022 23:23:22 GMT

@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?

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

MarceloMarques — Thu, 03 Nov 2022 00:18:29 GMT

@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 | Documentation for ArcGIS Enterprise
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.

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

AngelaVanderpas1 — Thu, 03 Nov 2022 23:39:36 GMT

@MarceloMarques

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

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

TimMinter — Tue, 30 May 2023 22:19:04 GMT

@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

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

MarceloMarques — Tue, 30 May 2023 22:48:40 GMT

@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.

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

TimMinter — Wed, 31 May 2023 15:15:27 GMT

@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

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

MarceloMarques — Wed, 31 May 2023 17:07:38 GMT

@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.