nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Add Data Store database hosted on amazon web services

DavidEvans5

Hi,

I'm trying to do something that may not be possible in our Enterprise Portal (version 11.0). We have a feature service that I would like to publish with a table join to a table containing live customer billing data hosted in an Amazon Web Services (AWS) database. This AWS database is not managed by me or my organization, I just have a URL and credentials that I've used to connect to using the Microsoft OLE DB connection for SQL Server.

I'm trying Add the database as a data store to our Portal in ArcGIS Pro to a machines we have set up for hosting that is federated with our Portal, but when I do this I get an error informing me there was an Error validating with the server (see attached image).

I've read through the documentation for Adding a database data store item on ESRI's website which says that I need to have a .SDE database connection file. As mentioned above I don't have direct access to the AWS site and before I go down a rabbit hole with the AWS/billing software's IT department I wanted to see if anyone here could advise me on whether adding an AWS database as a datastore is even possible in Enterprise, and if not if there's a different method we could employ to allow us to publish a feature service linked to that billing data.

Thank you all for any assistance/expertise you can provide

datastore validation error.png

AndrewSakowicz_0-1730485389403.png

Find more posts tagged with

Accepted answers

All comments

Scott_Tansley

It sounds like your other databases and ArcGIS Enterprise are not in AWS? If that's the case then technically it would be possible if you create an SDE file. However, it's not good architectural practice. Being very blunt, it's one of those things where it may look fun to jump into the river from a bridge, but it's most likely very dangerous.

What would happen is that you're ArcGIS Server would receive a request, get the data from your local feature class and then have to navigate the internet to AWS to get the other data. While ArcGIS Server is waiting for that response, it's waiting, and potentially causing a bottleneck for other requests.

From a performance and reliability perspective you'd be much better having all the data as close to the ArcGIS Server as possible to maximise throughput. Can you build a process that refreshes the data from AWS into your local SQL Server on a regular basis? You'd get better performance and an easier Esri config if you can.

DavidEvans5

Thank you Scott for your candid and very illuminating response. I suspected that the solution might be to develop some kind of script to take an image of the AWS table every night and write that to the SQL geodatabase within our enterprise. I have not had any experience with creating this type of process myself. You wouldn't happen to know of any resources (tutorials, sample code, etc.) that might help me get started on this track? I'm hoping I might find something within the esri community forums, though it can sometimes can be a task trying to narrow down search terms to find what you're looking for!

Scott_Tansley

Hi, Sorry, I don't develop very much these days as I'm focussed on the infrastructure side of things. My go to at this point would actually be something like Data Interop or FME rather than a script. The visual design tools are a lot easier to comprehend for non-developers and are effectively self-documenting, so that's the route I recommend to most clients. 🙂

DavidEvans5

Thanks again Scott. I have had very limited exposure to both the Data Interoperability extension and FME but would love to learn more about their capabilities. I have been trying out the approach you recommended and have created a Model Builder function that can copy the table from the AWS server to our local geodatabase. My next challenge/question to the community is how do automate that Model Builder function to make it run every night? Is that even possible with Model Builder in ArcGIS Enterprise?

I'm also abandoning my original plan to use publish a point feature service with a table join (to the table that's now been copied into our SQL database), in favor of a Database View, something I also have very little experience with, leading to a second community posting here. I think this makes more sense since the table will always have the lat/long coordinates for each account location while the point feature would require constant updating.

Again anyone who created a process like this that can share their thoughts/recommendations will have my sincere appreciation and kudos.

Scott_Tansley

What you have described to me is an aspatial table in a database, not a geodatabase, that has attributes and spatial properties in the form of two double fields, one for Lat and one for Long. This needs to be consumed in the GIS. In a pure desktop world, we would have introduced this to the Table of Contents as a X/Y Event layer, and then to get some form of performance, you would right click on the layer and 'Export As'.

In logical steps this is what you need to do here. Two doubles cannot be indexed spatially, literally they're just numbers and not shapes or geometries. There are many ways of doing all of this.

If you have someone skilled in SQL then you could create a table that has the same schema as AWS, but has a geography/geometry column. Inserts/Updates would then update the 'shape' with the attributes of your Lat/Long. It's just SQL. You can then use a Query Layer.

If you've got a developer, you could look to use Python to do the same thing. Theoreticlly, you can do it with model builder as well, but I haven't touched it in years... Sorry.

Otherwise, and the way I always recommend is to use Data Interop or FME.

My skillset these days is architecture and IaaS/Cloud and deploying Esri into that, rather than writing scripts etc. 😞 As much as I miss that. I'm probably not the best person to help you here, but X/Y Event layers do not perform well in ArcGIS Server and I cannot recommend them. They create bottlenecks and performance issues.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.