topic Re: PostgreSQL or MSSQL with ArcGIS for Server in Implementing ArcGIS Questions

PostgreSQL or MSSQL with ArcGIS for Server

MartinSirkovsky1 — Sat, 03 Nov 2018 13:44:59 GMT

Hey.

This might be a quite general question, but I am wondering if there is any recommendation on which DBMS to use with ArcGIS for Server.

I am deciding on using PostgreSQL or MSSQL. If we do not think about the cost benefits of PostgreSQL, are there any performance or functionality advantages of using one over another?

Thank you

Martin

Re: PostgreSQL or MSSQL with ArcGIS for Server

JacobBoyle412 — Fri, 09 Nov 2018 00:06:58 GMT

Microsoft SQL Server and PostgreSQL can both be used with great success, but certain tasks(backup, user management, integration with AD) to name a few are "easier" to do in SQL Server, if not can only be done in SQL Server.

It really comes down to you and your organization's comfort and familiarity with each.

Re: PostgreSQL or MSSQL with ArcGIS for Server

MartinSirkovsky1 — Fri, 09 Nov 2018 12:07:35 GMT

Thank you for you answer, Jacob.

Is integration with AD not possible with PostgreSQL? I believe it should be possible, but the setup may be more complicated with PostgreSql than with MSSQL.

Re: PostgreSQL or MSSQL with ArcGIS for Server

George_Thompson — Fri, 09 Nov 2018 13:17:43 GMT

According to the help we support Database Authentication for ArcGIS Pro and ArcMap:

Connect to PostgreSQL from ArcGIS—Help | ArcGIS Desktop - ArcMap

Connect to PostgreSQL from ArcGIS—Help | ArcGIS Desktop - ArcGIS Pro

I am not sure I have seen anyone use AD or similar with Postgres.

Geodatabase‌ ArcGIS Enterprise‌ Enterprise GIS‌

Re: PostgreSQL or MSSQL with ArcGIS for Server

JacobBoyle412 — Fri, 09 Nov 2018 19:28:39 GMT

Yes, only PostgreSQL Database tier authentication is supported.

Further, using AD with PostgreSQL can be facilitated through direct LDAP connections to AD. I do not think it's something we support natively.

Re: PostgreSQL or MSSQL with ArcGIS for Server

MartinSirkovsky1 — Sat, 10 Nov 2018 17:30:48 GMT

Hi.

Thanks for the input. So to conclude, both of these DBMS are fully capable to be used with ArcGIS for Server, but if the integration with AD is an important requirement, than MSSQL would be recommended DMBS to use.

Please correct me if I am wrong.

Martin.

Re: PostgreSQL or MSSQL with ArcGIS for Server

George_Thompson — Mon, 12 Nov 2018 12:42:39 GMT

I would agree with that comment on needing SQL Server due to integration with AD.

Re: PostgreSQL or MSSQL with ArcGIS for Server

JoshuaBixby — Mon, 12 Nov 2018 14:55:43 GMT

This is a MAJOR documentation fail on Esri's part.

Looking at Oracle User accounts and groups—Help | ArcGIS Desktop :

There are two types of authentication used with Oracle databases: operating system authentication and database authentication.

Looking at SQL Server User accounts and groups—Help | ArcGIS Desktop :

There are two types of authentication used with SQL Server databases: operating system authentication and database authentication.

Looking at DB2 User accounts and groups—Geodatabases in Db2 | ArcGIS Desktop :

There are two types of authentication used with Db2 databases: operating system authentication and database authentication.

Looking at Informix User accounts and groups—Help | ArcGIS Desktop :

There are two types of authentication used with Informix databases: operating system authentication and database authentication.

The corresponding statement for PostgreSQL simply doesn't exist. It seems Esri wants to take credit when they support both, but they don't want to directly acknowledge when they don't support both. Just like the four DBMS platforms above, there should be a clear statement for which types of authentication are supported so users are not left to guess and infer!

Re: PostgreSQL or MSSQL with ArcGIS for Server

JacobBoyle412 — Mon, 19 Nov 2018 18:49:05 GMT

Joshua, this is a great point, I'll reach out to the product documentation team for PostgreSQL.

Re: PostgreSQL or MSSQL with ArcGIS for Server

JoshuaBixby — Mon, 19 Nov 2018 19:52:29 GMT

Thanks, it would be great to get the issue clarified in Esri's documentation.

Re: PostgreSQL or MSSQL with ArcGIS for Server

KevinDunlop — Mon, 19 Nov 2018 20:27:38 GMT

There is also a gap in help on examples to for doing back end development. There is SQL Server and Oracle but nothing else.

Example: Resolving domain codes to description values using SQL—Help | ArcGIS Desktop

As for which is better. We use both SQL Server and Postgres in our office. I find almost everything database management is easier with SQL Server. In addition, the Always On options that SQL Server provides is much better and simpler than the Postgres high availability options.

Re: PostgreSQL or MSSQL with ArcGIS for Server

KyleGonterwitz — Thu, 14 Feb 2019 18:24:07 GMT

I love this question! So what is best for GIS, SQL Server or Postgresql (or Oracle)?

Depends. It's almost a question akin to what is your favorite flavor? I think the answer depends a lot on the nature of your agency, your IT support, your security needs, and to some degree database administrator bias.

My DBA's have traditionally supported Oracle and SQL server, they have invested significant training and developed expertise in those platforms. Recently a project required them to deal with Postgresql - this does not make them happy, but it might open a door for a more serious discussion about this in the future.

I use PostgreSQL on my desktop for development and local processing and I love it for that, I use PGIII for admin - but I wouldn't run my enterprise on it at this point because I would rather rely on my DBA team than try to go my own way.

So here are some more of my opinions - Postgresql with PostGIS has more native spatial functionality than MS SQL server, and rivals Oracle functionality. If you want to do a lot of tabular and spatial queries and use advanced native SQL functions like linear referencing, then PostgreSQL and PostGIS is great. VACUUM is a nice tool with PostgreSQL. I think more SQL spatial expertise exists in the Oracle community, and the transition for the analyst having Oracle Spatial SQL experience to PostgresSQL more empowers that rare developer than going to Microsoft SQL server. The geodatabse containers in PosgreSQL are a little more like SQL server in a database as opposed to Oracle, which is at the schema. Updating a geodatabse in Oracle is a chore compared to SQL server or Postgresql geodatabase updates, but you didn't ask about Oracle so I digress...anyway that is about the same between MSSQL and Posgresql so for me each of those options score a point against oracle there.

The advantage to me to using MSSQL in my agency is the support and expertise I get from IT involved in hosting, backing up, configuring, and maintaining databases in a supported environment - not to mention from Microsoft and Esri. From what I've seen Esri has a great relationship with the PostGRESQL developer community, some of the Esri GDB team are contributors the the FOSS project, postgresql is enterprise ready, even Gartner says so, and postgresql implementations are an increasing trend. However unless you pay someone for support (like we do microsoft and oracle) who do you have to email or call and call you back right away when you have a support issue? There are companies that I'm sure will provide that service for a cost, just something to think about that can be important to an enterprise.

Re: PostgreSQL or MSSQL with ArcGIS for Server

JacobBoyle412 — Thu, 14 Feb 2019 18:49:05 GMT

All great points Kyle Gonterwitz!

The long and short of it is there is no "right" answer in this realm, many factors should and will contribute to a decision of this size, like Kyle laid out above.

The long and short of it, in my opinion, is use whatever database Esri supports that you and your support staff are most comfortable with.