Enterprise geodatabase replication to AGOL doesn't work when data is updated with SQL

Question

This is a repost from an ongoing issues I've had - would like to bubble it up to see if anyone has found a solution.

I am creating a table in my enterprise geodatabase, enabling global ids and archiving, and sharing as a referenced feature service with sync enabled to my Portal. I do this all with ArcGIS Pro / Python.

When I share this feature layer with my distributed collaboration configured to send as copies, a hosted feature layer is created in my ArcGIS Online instance reflecting the data in my enterprise geodatabase.  That works as expected.

However, this table is periodically updated outside ArcGIS - that is, with native SQL.  This process runs a truncate on the table, and repopulates with new data.  This process also generates new global ids to populate on load as well.

This is where the distributed collaboration breaks.  
My logs say "failure in processing exports for Replica", "Failed to export data changes message for replica with Guid," "Failed to export data changes to replica". "Invalid column value [globalid]."

So I'm assuming something is happening with the globalid.  They look to be the standard format {8}-{4}-{4}-{4}-{12}, where the number is the number of characters (e.g. 52B2EBC3-DBA2-46C1-93F1-0D6DD52A2F13)

So two questions:

1. It is unsupported to maintain a distributed collaboration when the source table is maintained outside of ArcGIS

2. If not, is there a different process our DBA should follow so that the synchronization successfully processes?

For example - is there a different enterprise database system table (e.g. archive table) that is updated as well that helps facilitate the collaboration.  So when updating an database with ArcGIS tools, the tool internals automatically update the system table, but when updating with SQL, that system table doesn't get updated.  Searching for any help here - I feel like this should be supported somehow - the replication system is great, but to assume all the data ETL is done with ArcGIS is a little short-sighted.

Jay_Gregory · Answer

@RyanUthoff Thank you - this is the farthest I've gotten in trying to investigate this issue - I did not know about archive views.  It's strange that the documentation only exists for the old products - I found nothing on archive views in the newer, Pro based documentation.  I'll try to do some internal testing and see what I find.

@JakeSkinner Thanks for your response - I would prefer not to have to maintain and potentially troubleshoot Python scripts for achieving the synchronizing.  Particularly with large datasets, the truncate and append via API to AGOL can have issues.  From a scalability perspective, ArcGIS Enterprise distributed collaboration to replicate datasets to AGOL is a pretty great feature, since the DBAs can do the updating themselves.  It's just figuring out how to do it in an "esri approved" way outside of Esri tools, if its possible.  We can of course always fall back to Python workflows, but the distributed collaboration was sold to us as a no-code solution for synchronizing.

ryanuthoff · Answer

Are the tables in the EGDB registered as versioned? Or is it non-versioned, but with archiving enabled?

If the answer is yes, then you must edit the data the "Esri approved" way when editing directly in SQL. I'll include a couple links below that tell you how you can edit either versioned or non-versioned with archiving enabled data the "Esri approved" way when editing directly in SQL. The only time where you don't really need to do anything special is when the table is non-versioned with no archiving.

While I haven't directly experienced your issue in a distributed collaboration, I have experienced this issue where we update the data in SQL, but then Esri doesn't recognize it. The solution is to follow the correct editing procedures in the documentation below.

https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/editing-nonversioned-geodatabase-data-in-sql-server-using-sql.htm

https://desktop.arcgis.com/en/arcmap/latest/manage-data/using-sql-with-gdbs/edit-versioned-data-using-sql-sqlserver.htm

jskinner · Answer

Hi @Jay_Gregory,

I don't know the specifics of how the distributed collaboration works, but I know GlobalIDs are required to create copies of the data and keep them in-sync, so I agree with you that the recreation of this field is the culprit.

Are there several feature classes/tables that are updated via SQL that participate in your collaboration?  It may be worth exploring other options outside of a distributed collaboration to keep these datasets in-sync.  Here is one solution I've implemented with several customers:

https://community.esri.com/t5/arcgis-online-documents/overwrite-arcgis-online-feature-service-using/ta-p/904457