Upgrade Enterprise Geodatabase from 10.8.1

[ZachBodenner]

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.

[Scott_Tansley · Accepted solution]

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.

[MarceloMarques · Accepted solution]

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

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

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.

[ZachBodenner]

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? 

[Scott_Tansley]

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.

[ZachBodenner]

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?

[Scott_Tansley]

Sounds like a plan.  🙂

[MarceloMarques]

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

[ZachBodenner]

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.

[MarceloMarques]

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