How to see real time updates to SQL Server in portal

EricAnderson14 · ‎06-19-2017

We have ArcGIS Portal (10.5) set up in a 'Federated' environment. We get nightly updates to a SQL server database with new individuals with latitude and Longitude information. I have been able to create a map using ArcGIS desktop that shows the 'Points' on the map. I have been able to publish the layer as a service to our Portal environment successfully. However, after publishing the map to portal, when the nightly updates to the SQL Server database take place, the values that are visible from the Portal do not appear to be updated.

Can someone share with me the steps of how to create a map that will live in the portal, that will be able to reflect the nightly updates from the SQL server?

Thank you so much for your time!

Respectfully,

Eric Anderson

JakeSkinner · ‎06-21-2017

Hi Eric,

A Map Image Layer will work in this case. The Map Image Layer will still read the updates successfully. However, you can still add a feature layer from the Map Image Layer. For example the Map Image Layer would have a URL similar to:

https://server.domain.com/arcgis/rest/services/XY/MapServer

To add a feature layer, you simply would add the index of the layer at the end of the URL. For example:

https://server.domain.com/arcgis/rest/services/XY/MapServer/0

You will still be able to view the attribute table of a Map Image Layer. You just need to click on the layer name in the web map's table of contents to drill down further:

View solution in original post

JakeSkinner · ‎06-19-2017

Hi Eric,

You will want to make sure you are publishing the service to ArcGIS Server rather than Portal. For example, here are the steps:

1. Execute the 'Make XY Event Layer' tool to create the point data

2. File > Share As > Service > Publish a Service > choose your ArcGIS Server connection

When you choose the option to publish a service to My Hosted Services (Portal for ArcGIS), the data is copied to the ArcGIS Data Store and therefore is disconnected from your SQL Server instance.

EricAnderson14 · ‎06-19-2017

Ok, it appears that I may have been posting it to 'My hosted Services'. Let me see if I can get the publishing to the 'ArcGIS Server' connection to work. I will reply again shortly.

Thank you! - Eric-

JakeSkinner · ‎06-19-2017

You will also want to make sure, as randall_williams-esristaff‌ stated, that the geodatabase is registered with ArcGIS Server. If not you will receive a High level warning that the data is going to be copied to the server.

EricAnderson14 · ‎06-19-2017

When I try to connect to either our Staging or Production 'ArcGIS on ArcGIS.xxx.xxx (Publisher)', I am getting an 'Unable to open connection. Please verify that the server is running and that you have appropriate access privileges'. I'm a system administrator and so I can't imaging that I am missing any privileges. Any suggestions would be greatly appreciated.

Thanks!

-Eric -

JakeSkinner · ‎06-19-2017

Since ArcGIS Server is federated with Portal you will want to connect with your Portal credentials. Are you using Integrated Windows Authentication with Portal? If so, try specifying your username as 'domain\username'.

RandallWilliams · ‎06-19-2017

I have a few questions I'd like to ask:

a. Is your SQL Server set as your Hosting server's managed database, or are you using the Data Store product?

b. Along those same lines, when you publish your service, is data being copied up to the server?

Essentially, I'm asking if the service is actually referencing the SQL Server database, or is data being copied up into ArcGIS Data Store at publish time?

EricAnderson14 · ‎06-20-2017

Hi there everyone,

Ok, so I am able to do the publishing, but the result is a 'Map Image Layer' instead of what I would have expected, which is a feature layer. I'm thinking if I want to see nightly updates to the SQL server data reflected on the map, a 'Map image layer' is not what I want. With it being a map image layer, I am not able to see a list of the rows or attributes in the base table.

I did 'Share as' > 'Service...' > 'Publish as a service' > I sent it to a '... (Publisher) ' connection with it going to a > [root] folder.

I'd welcome any additional insight anyone may have.

Thank you for your help!

JakeSkinner · ‎06-21-2017

Hi Eric,

A Map Image Layer will work in this case. The Map Image Layer will still read the updates successfully. However, you can still add a feature layer from the Map Image Layer. For example the Map Image Layer would have a URL similar to:

https://server.domain.com/arcgis/rest/services/XY/MapServer

To add a feature layer, you simply would add the index of the layer at the end of the URL. For example:

https://server.domain.com/arcgis/rest/services/XY/MapServer/0

You will still be able to view the attribute table of a Map Image Layer. You just need to click on the layer name in the web map's table of contents to drill down further:

EricAnderson14 · ‎06-21-2017

Thank you everyone for your assistance in this regards. All of the information was very helpful and I am now able to do everything that I need to with the live connection to the SQL database.