Community
Select to view content in your preferred language

Support editing in sql databases without SDE/Geodatabase

1179
0
12-26-2019 11:24 AM
Status: Open
Labels (1)
Labels
roemhildtg
by
Occasional Contributor III

Disclaimer: I may have mixed up my sde/geodatabase terminology but bear with me. When referring to SDE I mean all the internal geodatabase methods and functions that happen.

 

Idea:

Remove the ArcGIS dependency on SDE to edit databases and support native database functionality. Setting Geodatabases on top of a normal database introduces some issues, and it would sometimes be quite beneficial to not have this layer.

 

Issues with SDE:

  • Performance: I've seen tons of examples of SQL databases that run and perform slowly in the ArcGIS platform. SQL Server studio/PGAdmin run the queries on the data very quickly but in arcgis stuff is slow...and there's often no obvious answer for it. Using SDE as the communication between databases introduces overhead.
  • Backups are more complex - you can't simply back up a database and restore it like any other database. You must restore it to the same database name, and apply all the same permissions, and users to that database for any hope to have it work correctly again. Native backup tools (depending on the database) require more complex steps than a simple restore. Postgres, for example must be restored in a very particular schema based order. While I've been able to get this working, errors get thrown regardless and its not always clear whether the restore actually went as expected.
  • System incompatibility - Integrating SDE with third party systems is difficult. Example: rather than having an autogenerating ID column, you must perform certain selects to get the next ID. Issues also occurs with all sorts of native sql database functionality, like triggers. Create a trigger on a database table? Nope, ArcGIS no longer works. GIS is supposed to be the tool that integrates third party systems. Well this is really only true if your database is a "geo-enabled" database. Otherwise you're looking at Read-only at best.
  • System upgrades - Its difficult to decide whether to upgrade a backend database system. On one hand, new features in the software may be very beneficial, but on the other hand, what could break is scary at best. If anything goes wrong you may be resorting to backups (which as mentioned can be quite flakey) Also be sure to cross your fingers and pray that everything goes alright, because if it doesn't, your basically stuck on the existing database release, or re-creating your databases from scratch because SDE won't properly upgrade. Sure, you can spend a week on the phone with support but in they end they may recommend rebuilding from scratch anyways.
  • Database Migrations - Almost every organization would love to have a "Development" and "Production" database. However, I've worked with a lot of organizations and I have yet to see a sustainable way of doing this in ArcGIS. Maintaining two different databases is very difficult with ArcGIS because of the abstraction layer. You can't use any modern migration tools out there, nodejs, php, etc everything has to run through arcgis. There are certain tools that just can't run through code and keeping two databases in sync is really not possible. You can manually copy stuff back and forth and try to keep them similar but they'll never truly be exact replica's of each other and therefore stuff will break when migrating applications from Dev to Prod.
  • Annoying sde tables: Enabling gdb creates tons of extra cruft. It becomes difficult to browse sql tables in any third party tool because the list of tables is quadrupled.

 

Solution:

All of these issues would essentially be resolved if ArcGIS didn't rely on its SDE Geodatabase backend. Allow the ArcGIS Platform to edit data in sql without the need for "Geodatabases". There would be limitations, of course, like a loss of the geodatabase support for Topologies, Versioning, Archiving, etc. But in some cases, the native database backend can handle these limitations. In others, the pros may outweigh the limitations.