Azure SQL Database and ArcGIS experience

[PeterKjeld]

I am testing how we can use Windows Azure SQL Databases along with ArcGIS Server 10.1 and would like to hear your experience. Currently I'm having problems publishing a map service.

Scenario:
1) Created an empty database via the Azure SQL Database interface - SUCCESS
2) Connected to my Azure SQL database from ArcCatalog - SUCCESS
3) Created a new feature class (multipoint) from ArcCatalog - SUCCESS
4) Loaded some points into the feature class from ArcCatalog - SUCCESS
5) Created a new map document showing the points in ArcMap - SUCCESS
6) Tried to publish the map from ArcMap in our ArcGIS Server environment - FAILED

Problem: The data source is not registered and the data will be moved to AGS: '24011 - Layer's data source is not registered with the server and data will be copied to the server'.

When I then try to register the data source (Azure SQL) I get the message: '...The connection property set was missing a required property or the property value was unregnized... Could not open a connection to SQL Server...'

Since it is not a problem for me to connect from ArcCatalog and ArcMap I do not really see the problem.

Any ideas or experience are welcome.

[JonPeck]

I was not using Azure but I had a similar issue with the data always wanting to be copied to the ArcServer, even after registering the source with ArcServer.  I also received the same errors as you.  What ended up working for me was manually disabling the 'automatic data copying when publishing to server'. 

See this link for directions.  http://resources.arcgis.com/en/help/main/10.1/index.html#/Disabling_automatic_data_copying_when_publ... 

Once I disabled this, I stopped receiving the error messages and was able to publish my service.  Give it a try and see.  It's a pretty quick and simple process.

Sorry forgot to add I am using ArcSDE 10.1 SP1 on SQL2008R2 and ArcServer 10.1 SP1 also ArcGIS desktop 10.1 sp1

[PeterKjeld]

Thanks for the tip, I have had a similar problem earlier but it was always solved by registering the source. Will ask our administrator to change that setting.

By reading the documentation I found that Sql Azure apparently cannot be used as a geodatabase:
ArcGIS geodatabases are not supported with Windows Azure SQL Database. (http://resources.arcgis.com/en/help/system-requirements/10.1/index.html#//01510000008m000000). This is a strange to me since I can create feature classes and populate it with spatial data and visualize it in ArcMap.

[MarcoBoeringa · Accepted solution]

Thanks for the tip, I have had a similar problem earlier but it was always solved by registering the source. Will ask our administrator to change that setting.

By reading the documentation I found that Sql Azure apparently cannot be used as a geodatabase:
ArcGIS geodatabases are not supported with Windows Azure SQL Database. (http://resources.arcgis.com/en/help/system-requirements/10.1/index.html#//01510000008m000000). This is a strange to me since I can create feature classes and populate it with spatial data and visualize it in ArcMap.

Hi,

This is not as strange as you think, it is a new feature of 10.0 onwards informely called "direct SQL access". It allows you to query and view data layers from any database containing spatial data - but without an ArcSDE Repository and Geodatabase system tables - using "Query Layers", and also to export data to a read-only(!) Feature Class in that same database. You will not be able to edit these layers directly though from within ArcMap, as that requires the data to be in a true geodatabase, nor have it participate in any geodatabase related advanced data types, like Geometric Networks etc.

As the Help page you yourself referenced says:
- ArcGIS geodatabases are not supported with Windows Azure SQL Database.
- Connections to Windows Azure SQL databases, including access to spatial data type fields, are supported.

By the way, you can edit these layers in a GIS web-application or ArcMap, if you use ESRI's new 10.1 ArcGIS Spatial Data Server and serve out the data as an ESRI Feature Service, and load that Feature Service in your ArcMap session.

This is also a part solution to your problem: you should use ArcGIS Spatial Data Server instead of ArcGIS for Server to make your web map services.

See:
What is ArcGIS Spatial Data Server?
in the Help

[PeterKjeld]

Thanks, now it seems to make sense to me. If I understand it correct it means that when I create a feature class from ArcCatalog in my Azure SQL database it simply uses the spatial daa types of SQL Server.

I was hoping that I would be able to prepare a mxd document in ArcMap and publish that as a ArcGIS mapservice. Using the ArcGIS Spatial Data Server will not allow that as it streams the data to the client which is then responsible for the drawing.

[MarcoBoeringa]

I was hoping that I would be able to prepare a mxd document in ArcMap and publish that as a ArcGIS mapservice. Using the ArcGIS Spatial Data Server will not allow that as it streams the data to the client which is then responsible for the drawing.

Actually, if your only goal is a read-only map service, than theoretically, ArcGIS for Server should be able to do this. The process would require adding Query Layers to ArcMap (as you seem to have successfully done reading your first post in this thread), and than use this Map Document as the source for publishing to ArcGIS for Server as a Map Service. This should work for any database on your local LAN, and in this way uses the direct SQL access properties of ArcMap and ArcGIS for Server.

However, reading your error / remark in the first post:

When I then try to register the data source (Azure SQL) I get the message: '...The connection property set was missing a required property or the property value was unregnized... Could not open a connection to SQL Server...'

I am starting to think there may be an undocumented limitation that this specific option of direct SQL access / Query Layer does not work for a Cloud based database like Azure in ArcGIS for Server. Maybe not an entire surprise, but not documented if so.

[PeterKjeld]

I think you are right about the 'undocumented limitation'. In my setup I only needed the read-only map service. In theory it should be possible but for now it does not seems to be working.

If anyone have had better luck in using Azure SQL Database along with ArcMap and AGS 10.1 please let me know.