Join two SQL Databases and Share the Resulting Layer in Enterprise Portal

[FrancisHourigan1]

I have two databases one is an Enterprise Geodatabase, and the other is a SQL Server database. How do I connect two tables (one from each DB) with a join in ArcGIS Pro and then share the resulting layer as a referenced feature service in ArcGIS Enterprise Portal? I want the changes in each database to be "live" and reflected in my web layer. Is there documentation on this?

[VinceAngelo · Accepted solution]

You'll probably want to reconsider this. Cross-database queries within a single database instance are an antipattern. Cross-server queries are pretty much worst case, in terms of performance and reliability. 

The best practice solution would be to stage a copy of the table(s) in a single database and transmit INSERT/UPDATE/DELETE messages from the primary database to the publishing database.  In this way, you can take full advantage of the SQL engine to index and optimize queries, and you don't put the burden on the GIS engine to do the database's job.

- V

[KimGarbade]

@VinceAngelo would you do this via the Service Broker in SQL Server for example, or would you have to use something like FME Workbench, since you need SDE in the middle when talking to Feature classes in an enterprise GDB?

[VinceAngelo]

I'm more likely to use ArcPy to move geometries, but I've done this a half-dozen different ways in the past 27 years. Since any solution is likely to be specific to a given database/dataset, using the tools you feel most comfortable with is likely to be easiest.

- V

[FrancisHourigan1]

@VinceAngelo would Esri's Data Interoperability Extension work for this since it is essentially built on FME? I have not used either before but I have played with demo versions of both. 

[VinceAngelo]

I have no experience with either FME or Data Interoperability, so I'm the wrong person to ask.

- V

[BrianFausel]

I use FME to load tables from external SQL databases to our Enterprise geodatabase (SDE) and also ArcGIS Online. It can definitely do what you are looking for either through truncate-append or change detection updates. There is a bit of a learning curve to FME but they do offer quite a bit of free training and on-demand webinars.

My update processes are created in FME Desktop and then scheduled to run nightly/weekly/on-demand via Windows Task Scheduler. Your original post does mention "live," which suggests you might need to see changes instantly. If that is the case, you'd likely need to use FME Server or Cloud along with FME Desktop, which will add more to your purchase cost if you decide to use FME.