Interacting with data from multiple SQL DB servers

[ademshark]

Afternoon,

I have an Enterprise environment within my organization with multiple SQL server instances established for our GIS purposes. I am attempting to setup a process where we pull data from multiple other DBs on other servers (from other depts.). Seems fairly straightforward, I can run simple GP tools, run a script, etc. However, we are attempting to setup an automated approach that is mostly headless for maintenance. 

Originally, I was working with our DBAs to get a linked server setup to just run DB query layers, DB views, etc. within Pro, but it appears cross-DB views aren't possible. This is what led me to experimenting with task scheduler/scripting, FME, etc. to automate a process via multiple arcpy/other tools. I suppose I could also just join the data via scripting and go from there. But before I proceed too much further I figured I'd get some solid veteran community input and not end up running down more unnecessary rabbit holes and doing more lifting than might be necessary.

My question is this: what would you recommend for a straightforward process? I'm trying not to use all these expensive licenses and multiple logins in the meantime. I realize this is heavily dependent on the workflow and data types, but just assume were dealing with straightforward tables, nothing beyond that.

Thank you for your time!

[CodyPatterson]

Hey @ademshark 

I believe that DB views are actually possible, you'd just have to use the FQDN of the server to actually pull the DB view out, but if you'd rather use arcpy then it's possible.

Arcpy would just let you immediately link the database connection files into the Python script, and you'd be able to manipulate using a search cursor from there, if you had a specific query or similar. I've had a few instances where this happens within a larger project. I pull data from, say, two databases and then deposit it into a third, which is then used as a KPI where we view different information. This is entirely possible, and shouldn't be too difficult to get started with, let me know if you need any examples, I'll have to work off of documentation though since I no longer have access to pro unfortunately!

Cody