topic Re: Migrating from On-premises MS SQL Server to AWS Aurora PostgreSQL in ArcGIS Enterprise Questions

Migrating from On-premises MS SQL Server to AWS Aurora PostgreSQL

SrinivasJasti — Fri, 10 Jan 2020 14:45:23 GMT

We are considering moving to Aurora PostgreSQL for our Enterprise Geodatabases. Posting to see if any one with experience in this type of migration will share any tips/recommendations.

I think the only spatial storage that works is PostGIS as ST_Geometry extension from ESRI cannot be used? Would this be a problem? Any other useful info is much appreciated.

Thanks in advance..

-J

Re: Migrating from On-premises MS SQL Server to AWS Aurora PostgreSQL

George_Thompson — Fri, 10 Jan 2020 15:23:24 GMT

You would want to follow the doc for creating the Enterprise GDB here: Create a geodatabase on Amazon RDS for PostgreSQL or Aurora PostgreSQL—ArcGIS Enterprise on AWS | ArcGIS Enterprise

The ArcGIS technology supports PostGIS (PG_Geometry); PostgreSQL data types supported in ArcGIS—Geodatabases in PostgreSQL | ArcGIS Desktop

Re: Migrating from On-premises MS SQL Server to AWS Aurora PostgreSQL

SrinivasJasti — Fri, 10 Jan 2020 15:30:28 GMT

Thank you for the reply.

Do you have any experience related to performance of spatial data between the two DB engines assuming everything else (specs, network, etc) being the same? For some complex spatial data we found MS SQL Spatial performance is really bad and we have to create SDE BINARY copies (read only) to workaround the performance issues.

Re: Migrating from On-premises MS SQL Server to AWS Aurora PostgreSQL

George_Thompson — Fri, 10 Jan 2020 15:48:34 GMT

I do not have any specific data but have heard that some people have said that the Postgres Aurora promising performance. When it comes to performance, there are MANY different factors involved.

Please note that Aurora is supported starting at ArcGIS 10.7.x and not tested on versions before that. I would recommend using it with ArcGIS 10.7.1 or ArcGIS Pro 2.4.3 for the best compatibility and performance.

I would also not recommend using SDEBINARY if at all possible as newer tools and functionality may not work correctly.

Are you accessing the data from a local (in office machine) to the DB in AWS?

Re: Migrating from On-premises MS SQL Server to AWS Aurora PostgreSQL

SrinivasJasti — Fri, 10 Jan 2020 16:00:25 GMT

Thanks again for the insights.

Are you accessing the data from a local (in office machine) to the DB in AWS?

>> Everything is local (data center) with users (ArcGIS Dektop, Pro) close to the DB & App servers right now but once DB and App servers are moved to AWS. We will have to evaluate the performance of desktop clients connecting to DB directly.

Re: Migrating from On-premises MS SQL Server to AWS Aurora PostgreSQL

George_Thompson — Fri, 10 Jan 2020 16:09:32 GMT

When you move to AWS, make sure that the clients are in the same region/area to get the best performance.

You can connect to an enterprise geodatabase in an Amazon RDS instance from an on-premises installation of ArcGIS Pro; however, performance will be slower than if you connect from an ArcGIS Pro installation on an Amazon Elastic Compute Cloud (EC2) instance in the same AWS region as the RDS instance. Also, you must open ports in your security group to connect to the RDS instance from an on-premises machine.

Connect from an ArcGIS Server instance to an enterprise geodatabase—ArcGIS Enterprise on AWS | ArcGIS Enterprise

Re: Migrating from On-premises MS SQL Server to AWS Aurora PostgreSQL

SrinivasJasti — Fri, 10 Jan 2020 16:50:29 GMT

Thanks. I am aware of the ports and performance implications for desktop clients. What I am most interested in is the performance of serving spatial data using Aurora PostgreSQL vs MS SQL Spatial when everything else is almost the same. We ran into performance issues with MS SQL Spatial types and ArcGIS.

Thank you for taking time to provide useful info. I guess I have some testing to do. I will update the post if I find any interesting info to share.