topic Re: Upgrade Enterprise Geodatabase from 10.8.1 in ArcGIS Enterprise Questions

Upgrade Enterprise Geodatabase from 10.8.1

ZachBodenner — Fri, 16 Feb 2024 22:20:41 GMT

Hello, I have some questions about upgrading our egdb. Background:

Currently, egdb is on 10.8.1 on a SQL Server 2019, Enterprise deployment (server/portal clients) are on 11.1. All desktop users are using Pro 3.2, with a few exceptions that have been tough to completely pull away from ArcMap. From what I understand, it isn't inherently bad to have different egdb and client versions, though it is recommended to keep them in step. I was reading about upgrading egdb and came across this article: Upgrade Enterprise Geodatabase in SQL Server In particular, this passage caught my eye:

"When you upgrade a geodatabase in SQL Server to 11.0 or a later release, the fully qualified names of tables and feature classes will no longer contain the database name. For example, a feature class named productdata.dataowner.inventory in a 10.9.x geodatabase will be named dataowner.inventory after you upgrade the geodatabase."

This seems very important, but I'm wondering what the knock-on consequences of this change are. For example, if I have web services that contain a feature class joined to a table, will the removal of the database name affect the join? Any other concerns that I should be aware of but can't see? Finally, any other issues you may have run into upgrading your egdb? This will be my first time going through the process.

Re: Upgrade Enterprise Geodatabase from 10.8.1

Scott_Tansley — Sat, 17 Feb 2024 00:10:47 GMT

Some really good questions. I'm not an EGDB expert, but consult around the edges of it, so I'll start the ball rolling where I feel I can and others can step in.

First, yes, it was nice in the past to keep things all aligned. As a purist, I'd love to see clients on Pro 3.2, AGE 11.1/2, and SQL server 2019+ with an EGDB version of 11.1/3.2. Real life gets in the way, so I've got clients down on SQL2016, and EGBDs of 10.7.1 and such like.

There used to be prescriptive documentation for version support, so 10.8.1 clients could work with 10.2.1 EGDBs for example, and then you got the list of supported databases and drivers. Yes, it's a complex mix.

The most recent documented 'story' seems to be to keep everything as close together as possible, which is what people have been doing for ages.

Always we have to look at the lowest common denominator. I have a client that uses a third-party tool that is an addin to ArcMap, it only works on 10.6.1, so the EGDBs and a single ArcMap version are 'held back' to that version. Everything else comes up above it. The same client has more modern workflows, which need Pro and more recent versions. So, they hold the databases that they must back to 10.6.1 and then use other geodatabases that support the modern workflows.

You suggested that your situation is inherently bad, I'd say it's almost typical. I've seen far worse. In terms of the more modern functions like Parcel Fabrics, UNM, Branch Versioning, I'd be recommending to stay as close to latest as possible. But if your using simple types, or things that have been with us for ages, then it's not 'inherently bad'. It could be better, but your constrained by ArcMap by the sounds of it. Be aware that ArcMap can connect to higher versions, but there could be things in there that it doesn't understand because the ArcMap code base doesn't support those concepts/functions, and so you may get some weirdness.

So, look at your lowest common denominator and make sure you can work around that. That sounds like ArcMap. I won't lecture. Then try and come up as close to current as you can for anything and everything else. Instead of good bad, you could end up with most of your geodatabases in a modern version, and those that you must hold back as older. So instead of black/white, you end up (hopefully) light grey.

I'm comfortable in saying all the above, the next bits I'm offering up but expect to be challenged by others, and I'm open to learning like yourself.

SQL Server has been an issue for years in that the geodatabase version had the database name held in the SDE tables. This meant that if you had one SQL Server, you couldn't duplicate the geodatabase and use it for testing and a myriad of other functions. It also made it impossible to rename the database and things like that. The new approach is awesome and one of the best things Esri have done in this space. I haven't used it in anger, but I would assume it would break your connections from older versions, I'd 'guess' that ArcMap won't support it. You will almost certainly need to replace SDE files and data stores in Enterprise, so yes a degree of reworking, but the new flexibilities it offers means it's worth the effort.

Re: Upgrade Enterprise Geodatabase from 10.8.1

MarceloMarques — Sat, 17 Feb 2024 00:38:10 GMT

Question: if I have web services that contain a feature class joined to a table, will the removal of the database name affect the join? Any other concerns that I should be aware of but can't see? Finally, any other issues you may have run into upgrading your egdb?

Answer: each deployment is unique, hence it is difficult to determine what can break in your environment, therefore the best approach here is to test before you do the upgrade in production, you will need to setup new servers for the upgrade test, restore a copy of the SQL Server Geodatabase and restore the ArcGIS Enterprise Services, then perform the upgrade as necessary and document the upgrade steps and what problems it might cause, then work through the issues to mitigate each problem.

------------------------------------------------
Client and geodatabase compatibility—ArcGIS Pro | Documentation

You do not have to keep your geodatabase and ArcGIS clients at the same release, but it is recommended that you do so. Geodatabases and client software are designed to work together, and if you let one get too many releases away from the other, you risk encountering problems or unexpected behavior.

This is especially true when you use a mix of client versions at your site. A newer client can create newer dataset types in the geodatabase that older clients cannot access. For enterprise geodatabases, waiting too long between geodatabase upgrades may mean you have to upgrade the underlying database more than once before you can upgrade the geodatabase.

------------------------------------------------

FAQ: What SQL Server Database Versions Are Supported by ArcGIS Enterprise and ArcGIS Pro? (esri.com)

ArcGIS 11.1 and ArcGIS Pro 3.1 requirements for Microsoft SQL Server—ArcGIS Enterprise | Documentation for ArcGIS Enterprise

------------------------------------------------

Upgrade Enterprise Geodatabase in SQL Server

When you upgrade a geodatabase in SQL Server to 11.0 or a later release, the fully qualified names of tables and feature classes will no longer contain the database name. For example, a feature class named productdata.dataowner.inventory in a 10.9.x geodatabase will be named dataowner.inventory after you upgrade the geodatabase.

------------------------------------------------

Note, this change was introduced in Pro 3.0 to make it possible to rename the SQL Server Database without breaking the ArcSDE Geodatabase Repository.

------------------------------------------------

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

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.
------------------------------------------------
• Restoring SQL Server databases with a new name (esri.com)
Read this article.

------------------------------------------------

For more best practices visit my community.esri.com blog.

Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices
"Database Guide Books and Database Template Scripts for SQL Server Geodatabases"

How to Install Database Clients for ArcGIS
How to Install the SQL Server Client for ArcGIS?
How to Move the SQL Server Enterprise Geodatabase with a Database Backup
How Load Large Featureclass SQL Server Geodatabase

------------------------------------------------
I hope this helps.

Re: Upgrade Enterprise Geodatabase from 10.8.1

ZachBodenner — Tue, 20 Feb 2024 22:13:09 GMT

Thanks to both of you for the very thorough answers!

Some follow-ups:
So ArcMap 10.8.x could connect to an 11.1 database, but might experience unexpected problems? If so, I might be willing to deal with it as we have only a few users left on ArcMap.

What are my best steps in order to test? From what @MarceloMarques says, I would duplicate my egdb (entire server?), and then run the upgrade? But it sounds like that might cause issues? I'm a little unclear by what you mean @Scott_Tansley when you say "you couldn't duplicate the geodatabase and use it for testing and a myriad of other functions. It also made it impossible to rename the database and things like that. " You are explaining duplicating the database on the same SQL Server? Would there be consequences to duplicating a database, or you just actually couldn't do it?

Re: Upgrade Enterprise Geodatabase from 10.8.1

Scott_Tansley — Tue, 20 Feb 2024 22:17:08 GMT

For clarity. If you have a database called 'assets' then assets is part of the fully qualified name in the SDE management tables. If you copied the assets database and tried to restore it on the same server as assetstest, for example then it wouldn't work because the fully qualified name is incorrect.

You could install a new/test SQL Server or reuse an existing, and (assuming there isn't a database called assets) then you could restore the database there.

From 11.0 onwards, the database name is not part of the naming convention in the management tables, so you have more flexibility.

In summary, you can duplicate to another - assuming the database name isn't already used.

Re: Upgrade Enterprise Geodatabase from 10.8.1

ZachBodenner — Tue, 20 Feb 2024 22:27:09 GMT

Okay, so in my case, in order to test the effects of upgrading the egdb, it would probably be best to restore it on a separate server, make sure I have some kind of identical web service on a test server to one that's already in production, and then upgrade to see if it breaks?

Re: Upgrade Enterprise Geodatabase from 10.8.1

Scott_Tansley — Tue, 20 Feb 2024 22:28:17 GMT

Sounds like a plan. 🙂

Re: Upgrade Enterprise Geodatabase from 10.8.1

MarceloMarques — Wed, 21 Feb 2024 17:14:15 GMT

@ZachBodenner

Backup Basics for ArcGIS Enterprise (esri.com)
Back up and restore best practices—Portal for ArcGIS | Documentation for ArcGIS Enterprise

You can create new test servers, one for the SQL Server Instance, where you will restore a copy of the production SQL Server Geodatabase, How to Move the SQL Server Enterprise Geodatabase with a Database Backup, and another server for the ArcGIS Enterprise, assuming you have a silo deployment, otherwise you will need more servers, then you can restore the Site using ArcGIS Enterprise backups, more information in the links above.

Notes:

- you will need to create a new URL and SSL Certificate for the test server running ArcGIS Enterprise, if you run into issues to import the backup then open a ticket with Esri Technical Support.

- The ArcGIS Server Services will still point to the production database, but you can stop ArcGIS Server and change the local hosts file and add an entry to point to the test SQL Server database, for this to work fine your test SQL Server machine needs to have a static ip address.

prodsqldb - 10.0.0.17 | testsqldb - 10.0.0.77

then in the local host file add "prodsqldb 10.0.0.77" to redirect to the test SQL Server database.

- This way you will not have to change the ArcGIS Server Services.

- Otherwise, the ArcGIS Pro 3.2 Update Data Source Tool now supports to update the Data Source of ArcGIS Server Services.

- Or if all this is too much, then you can just republish the services in your Test ArcGIS Enterprise Site.

Re: Upgrade Enterprise Geodatabase from 10.8.1

ZachBodenner — Tue, 27 Feb 2024 14:10:03 GMT

So I have a bit of a follow-up question after starting this process out. My Network Admin and I backed up our main egdb, restored it on the same server under a different name, and my thought was to publish out some services, upgrade the test gdb and see what happens. I'm running into some behavior I didn't expect, and though I suspect it's just a problem with initial database creation, I'm not exaclty sure what I missed.

Here's a sample of datasets in our existing geodatabase as seen in pro:

and here's what I see after making the sde file to the new database:

The icons seen in the second snip appear the same as when I, for example, make a sql view but it's not yet registered with the geodatabase. I don't need to do that for every single feature class do I? Attempting to drag any of the feature classes in the test gdb to the map gives me the crossed-out-circle icon indicating it can't be added.

Re: Upgrade Enterprise Geodatabase from 10.8.1

MarceloMarques — Tue, 27 Feb 2024 16:35:32 GMT

@ZachBodenner

You cannot restore the SQL Server Database with a different name if the ArcSDE Geodatabase Repository Version is 10.8.1.

You must create another SQL Server Instance on the same SQL Server Machine or in a different Machine and then restore the SQL Server Database with the same name.

Otherwise, if you rename the SQL Server Database you break the ArcSDE Geodatabase Repository, for more detail read the articles below.

How to Move the SQL Server Enterprise Geodatabase with a Database Backup
Restoring SQL Server databases with a new name (esri.com)

FYI: I have covered this topic in previous replies and I hope this clarifies.

Re: Upgrade Enterprise Geodatabase from 10.8.1

ZachBodenner — Tue, 27 Feb 2024 16:57:58 GMT

It does, thank you.

Re: Upgrade Enterprise Geodatabase from 10.8.1

ChristopherBowering — Fri, 12 Apr 2024 16:16:19 GMT

We recently upgraded several EGDBs from 10.8.1 directly to 11.1 and are now experiencing significant issues with some of our web services. Our deployment is comprised of 3 servers with the GIS Enterprise version at 11.1. The Enterprise upgrade to 11.1 occurred last fall. We are using SQL 2017 and Windows Server 2019 Standard (10.0).

We have multiple database connections/users, each uniquely set up, which are used to access the aforementioned databases. Some services published thru certain database connections remain perfectly functional...services published thru one specific database connection only partially appear when viewing the REST URL (some layers are there, some are missing)...services published thru a couple different database connection are not even viewable in REST anymore (URL times out after a couple minutes) and corresponding layers already in WebMaps fail to load. Everything was working fine prior to upgrading.

Are there any known issues - or steps to be taken we may have missed - related to database connections and web publishing when upgrading EGDBs? For reference, I am able to load data from all database connections into Pro without issue. The web services are what is problematic.

Re: Upgrade Enterprise Geodatabase from 10.8.1

MarceloMarques — Fri, 12 Apr 2024 16:40:50 GMT

@ChristopherBowering - You might have to republish your services using ArcGIS Pro, see the documentation below.

What's new in ArcGIS Enterprise 11.0—ArcGIS Enterprise | Documentation for ArcGIS Enterprise

Migrating services to the ArcGIS Pro service runtime—ArcGIS Server | Documentation for ArcGIS Enterprise

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

You do not have to keep your geodatabase and ArcGIS clients at the same release, but it is recommended that you do so. Geodatabases and client software are designed to work together, and if you let one get too many releases away from the other, you risk encountering problems or unexpected behavior.

This is especially true when you use a mix of client versions at your site. A newer client can create newer dataset types in the geodatabase that older clients cannot access. For enterprise geodatabases, waiting too long between geodatabase upgrades may mean you have to upgrade the underlying database more than once before you can upgrade the geodatabase.

I hope this helps.

Re: Upgrade Enterprise Geodatabase from 10.8.1

ChristopherBowering — Fri, 12 Apr 2024 16:51:05 GMT

Hi there. All of our services have already been published in Pro. We have no remaining ArcMap published services. We are using Pro 3.2.x as well.

Re: Upgrade Enterprise Geodatabase from 10.8.1

MarceloMarques — Fri, 12 Apr 2024 17:21:21 GMT

@ChristopherBowering - if all services were published with Pro, then check the SQL Server machine cpu and memory utilization, in SQL Server Activity Monitor you can see things like waits and active expensive queries, something might show up there, you can also enable SQL Server Query Store on the database to help diagnose database performance further, see SQL Server documentation, next you can bring each featureclass into ArcGIS Pro and make sure the performance to draw is acceptable, make sure the geodatabase maintenance is executed often, e.g., sde compress (if working with traditional version), gather new statistics, rebuild indexes (attribute columns and spatial). If this test passes, then the issue is not with the SQL Server Instance nor with the SQL Server Database nor with the Geodatabase. It must be something in ArcGIS Server Services, next I would try to reboot the ArcGIS Server machine to see if things clear up, and monitor, for the services affected, I would check the cpu/memory utilization in the ArcGIS Server machine, and then tune the min/max number of SOC processes of each ArcGIS Server service according to the workload, I would also look into the ArcGIS Server Logs in debug mode to see if any useful info show up.
I hope this helps.

Re: Upgrade Enterprise Geodatabase from 10.8.1

ChristopherBowering — Fri, 12 Apr 2024 17:48:55 GMT

Thank you for your in-depth responses and suggestions. We did reboot the ArcGIS Server machine last night with no change. Our services were running smoothly prior to the EGDB upgrade so there were no changes in pooling, memory usage or general database configurations. The 'trigger' for these issues was the upgrade from 10.8.1 to 11.1.

I do not believe the issues lies with the data. The inconsistency in the behavior of the services depending on the user from which data is published is telling, I just can't figure it out. There are no issues when actually publishing the services from Pro - no warnings or errors. Only when we attempt to view in REST and/or load/view services in WebMaps do the problems become apparent. Have you heard of any user/connection specific problems after performing a EGDB upgrade in regards to publishing? We have not altered our database connections and they function fine in Pro as I had mentioned. I feel as though the root of the problem lies within these database connections by the varying degrees that the services work - or don't work - depending on which connection a service is published thru.

Re: Upgrade Enterprise Geodatabase from 10.8.1

MarceloMarques — Fri, 12 Apr 2024 18:00:29 GMT

@ChristopherBowering

Question: Have you heard of any user/connection specific problems after performing a EGDB upgrade in regards to publishing?

Answer: I did not hear of any issues.

You can trace the service request using Fiddler, that might help diagnose further.

Monitoring web service requests using Fiddler - Esri Community

How To: Use Fiddler to Capture Https Connections and Decrypt Https Traffic (esri.com)

You shall open a ticket with Esri Technical Support to investigate the issue as well.

Re: Upgrade Enterprise Geodatabase from 10.8.1

ChristopherBowering — Fri, 12 Apr 2024 18:52:08 GMT

Thank you for those resources. I will check that out!

I opened a ticket with ESRI yesterday. Not much progress is being made which is why I posted here in an attempt to figure things out.

Re: Upgrade Enterprise Geodatabase from 10.8.1

MarceloMarques — Fri, 12 Apr 2024 19:46:58 GMT

@ChristopherBowering - please share the esri support case number, if I learn anything new about your problem, I let you know.

Re: Upgrade Enterprise Geodatabase from 10.8.1

ChristopherBowering — Fri, 12 Apr 2024 19:53:39 GMT

The case # is 03601725. Thank you for keeping an eye on this!