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; } } }

Exporting data from ArcGIS Enterprise geodatabase on MS Azure SQL Server

simoxu

We recently moved our geodatabase into MS SQL Server in Azure, and we are experiencing strange issues.

I am trying to export a large featureclass into an FGDB in ArcGIS Pro, which is installed on the desktop machine, it will take many hours and only export a small amount of records. (2900 of 180,000 records of the featureclass)

If the featureclass is not that big, let’s say only hundreds of records, it can be exported very quickly.

After monitoring the SQL Server in Azure, the DB guy told me there were some small activities, but nothing major to stress the server. On my client machine, I can see lots of network activities, but the progress bar barely move.

I tried to use ArcGIS Python API to load data from featureclass, the script will hang.

Never experience this issue before on the on-prem SQL Server.

If anyone can share some experience here, that would be much appreciated.

Encontre mais publicações marcadas com

Enterprise geodatabase

Python

Accepted answers

simoxu

update:

It turns out deploying the SDE database in the cloud is only one of the causes.

The biggest cause is:

The Text columns is extremely long (>1 billion), this is a result of creating these layers at the backend using SQL directly, and ArcGIS translates the nVarchar(MAX) to Text (length: 1073741822 ). I created a new featureclass based on one of the original layers but with limited length for the Text columns, then loaded original layer data into it. The performance of this new featureclass is far better, I can copy the whole layer in less than 1 minute instead of 4 hours.

A useful link:

https://community.esri.com/t5/data-management-questions/text-data-type-and-length-size-problem/td-p/257295

Hope this is useful for others.

All comments

David_Brooks

... following

George_Thompson

Is the ArcGIS Pro client also in the same region as the Azure SQL DB?

simoxu

Hi George,

The ArcGIS Pro is on my desktop.

Today I also tested on a VM which I am not sure if it is in the same region as the DB.

The strange thing is, it was using lots of network bandwidth (>5MPS ) , and for 3 hours it only copied 13K records out of 180K records of a POINT featureclass.

This only happens when I copy and paste featureclass in ArcGIS.

If I directly copy the featureclass table (using SQL or pyodbc+Python), it will only take minutes.

I just don't understand what it was doing with 5MPS bandwidth for 3 hours...

Thanks, Simo

simoxu

Just called the ICT department, and was told the VM is actaully on-prem! that partially explains it.

George_Thompson

Yep, if the client is not in the same cloud / region the performance will be bad.

Connections from ArcGIS software to databases in the cloud must originate from machines in the same cloud. For best performance, connections should originate from machines in the same cloud region. In the majority of cases, connection and query performance is poor when you connect from ArcGIS clients installed on-premises to databases in the cloud or make connections between clients and databases in different cloud regions, and connections may time out.

https://enterprise.arcgis.com/en/system-requirements/latest/windows/databases-in-the-cloud.htm

I would guess all the information that you saw being "transferred" was the chattiness of the Pro client.

Recommendation - Create a Pro client in the same cloud region and retest.

simoxu

update:

It turns out deploying the SDE database in the cloud is only one of the causes.

The biggest cause is:

A useful link:

https://community.esri.com/t5/data-management-questions/text-data-type-and-length-size-problem/td-p/257295

Hope this is useful for others.

Participe da Conversa

Membros conectados podem postar, seguir atualizações e mais. Novo aqui? Registre uma conta gratuita.

Links Rápidos

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