A client is requesting we come up with a Python script to have an AGOL feature layer and a SQL Server database containing similar information be able to read/write to each other without having to use ArcGIS for Server. Their concern is the amount of traffic on the in-house server and would like to bypass this. The client is also looking into ArcGIS Portal but currently doesn't have it. Also, while AGOL is clearly public, their SQL database is behind a firewall.

Doing some research, it doesn't look likely that this is possible. However, I figured I'd ask here in case anyone has tried to get around this before.

Is there any way around ArcGIS for Server I'm not finding through my own research?

[Update]: With much more clarification, I believe we have come up with a solution that would work. We know Python can interact with AGOL, and we know Python can interact with SQL Server DBs. From here, we just want the two tables to verify each other. Seems much simpler than was previously discussed.

You basically want a python script to keep data sync'd between the 2 sources? That shouldn't be "hard". The trigger or action to make the script check is a major question. In terms of what technology does this today? If you have Enterprise setup (Portal + Server), then you could use Collaborations to auto-magically keep the data sync'd.
– KHibmaJan 8 '19 at 18:30

We would likely just run it as a Windows service that checks for inconsistencies every few hours or so, but this is in the very early planning stages. I just don't want to go too far down the path without making sure I understand the role ArcGIS for Server does/doesn't have to play in this workflow. And Portal can't currently be a part of the discussion since the client doesn't want that yet.
– Jeremy MullinsJan 8 '19 at 18:33

The other question/concern is if your SQL Server database will be a geo database. ie. True featureclasses inside. You could be getting into uncharted territory having basic python trying to keep geometry in sync. If it's just the attributes from online to your sql server table, that's probably simple. If its geometry changes, you may want to use GIS Server and make a geodatabase with featureclasses and use arcpy tools to help rectify deltas.
– KHibmaJan 8 '19 at 18:53

1

SQL is a language. Most databases support it, including Microsoft SQL Server. It seems as if you want to use "SQL" as equivalent to "SQL Server". If you mean SQL Server, please Edit the question. It is certainly possible to write SQL code to keep any arbitrary source in sync with an RDBMS. However, given the data locations, it will be immensely difficult to avoid copying all the data out of AGOL on a regular basis, and any row comparison which is not Boolean in nature will likely be exceptionally difficult.
– VinceJan 8 '19 at 18:59

@KHibma - Thanks for your help! The AGOL table will stay (of course) spatially aware while the SQL table will remain data only. We might suggest them using the spatial components of SQL Server 2014 later, but for now, it's a proof of concept.
– Jeremy MullinsJan 8 '19 at 20:19