PostgreSQL as alternative to SQL Server Express

3214
8
03-09-2017 12:27 PM
danbecker
Occasional Contributor III

We're upgrading our current GIS box and would like to improve upon our current SQL Server Express instance.

I want the RDBMS to utilize the expanded system resources of the new box and Express just can't do that.

I just got the quote to upgrade to SQL Server Standard 2014 (minimum 4-core license) and about lost my lunch, so I'm looking for alternatives and PostgreSQL looks promising.

New box will run Hyper-V Core, then virtual OSEs

  • VM-1 -- MS Server 2012 Std; Domain Controller
  • VM-2 -- MS Server 2012 Std; ArcGIS Server
  • VM-3 -- Ubuntu Server 16.04 LTS; PostgreSQL

I assume that adding database server connection to PostgreSQL in ArcGIS Desktop will be a similar experience?

After the connection is made, can I simply right click and "create new geodatabase"? 

I guess I just am looking for the differences in user experience when interacting with a PostgreSQL backend vs. SQL Server.

thanks!

0 Kudos
8 Replies
George_Thompson
Esri Frequent Contributor

What level of ArcGIS Enterprise are you using?

You cannot use PostgreSQL with Workgroup level license: Types of geodatabases—ArcGIS Help | ArcGIS Desktop 

ArcGIS Enterprise Workgroup also includes ArcSDE support for SQL Server Express. With this level of ArcSDE, you can use SQL Server Express for up to 10 simultaneous Windows desktop users and editors (users of ArcGIS Desktop Basic, Desktop Standard, Desktop Advanced, a custom ArcGIS Engine application, AutoCAD, MicroStation, and so on) plus any number of additional server connections from Web applications. (Consult your license agreement for specific information on the number of connections for your implementation.)

If you do not have ArcGIS Enterprise (Standard or Advanced) licenses the switch may not work.

Also confirm that you are running PostgreSQL on a supported O/S, which it looks like you are: PostgreSQL database requirements for ArcGIS 10.5—Help | ArcGIS Desktop 

Hope this points you in the correct direction.

https://community.esri.com/community/gis/enterprise-gis?sr=search&searchId=4256ba13-6288-4d4c-aa5e-4...

--- George T.
danbecker
Occasional Contributor III

ArcGIS Enterprise Workgroup Standard Up to Two Cores License

0 Kudos
George_Thompson
Esri Frequent Contributor

I am not sure that you can use PostgreSQL with Workgroup. The limitations on SQL Server Express are all on the MS side. Esri only limits the # of concurrent connections

Microsoft limits the use of SQL Server Express to one CPU (or core within a socket) and 1GB of RAM. The maximum database size for SQL Server 2005 or 2008 Express is limited to 4GB. The maximum database size for SQL Server 2008 Express R2 is 10GB. (Consult your Esri license agreement for specific information on the instance size limitations for your implementation.)

You may need to contact your account manager and upgrade to ArcGIS Enterprise licensing.

--- George T.
0 Kudos
JoshuaBixby
MVP Esteemed Contributor

Technical limitations/situations aside, Esri has baked some pretty restrictive conditions into the Product and Services Terms of Use.  As it stands now, any edition (Basic, Standard, or Advanced) of a Workgroup server "requires a supported version of SQL Server Express. Supported versions are listed with the system requirements for the product on the Esri website."  Additionally, "use is restricted to a maximum of 10 gigabytes of Customer's data."

In short, regardless of what is or isn't technically possible, there are licensing restrictions as well.

danbecker
Occasional Contributor III

I just spoke with an ESRI licensing rep and both Joshua and George are correct.

Here is a good doc explaining the functionality and differences:

http://www.esri.com/library/brochures/pdfs/arcgis-enterprise-functionality-matrix.pdf 

Here are some facts for everyone, talking non-ELA, commercial version:

  1. We were "grandfathered" into ArcGIS Enterprise Workgroup Standard because of our current license maintenance for ArcGIS Server Workgroup Standard.
  2. All versions of ArcGIS Enterprise Workgroup (Basic, Standard, Advanced)
    • cap at a max. of 10 level 2 Portal for ArcGIS users
    • can only use SQL Server Express as RDBMS backend (which means max: db size of 10GB, 1GB memory utilization, 1 CPU Core utilization)
  3. Our current ArcGIS Enterprise Workgroup Standard license includes 5 level 2 Portal for ArcGIS users
  4. You have to upgrade to at least ArcGIS Enterprise Standard (note the absence of "Workgroup") in order to:
    • get more then 10 level 2 Portal for ArcGIS users
    • use RDBMSs other then SQL Server Express. For example PostgreSQL, SQL Server Std, ect...

The cost to upgrade  ArcGIS Enterprise Workgroup Standard -- to -- ArcGIS Enterprise Standard is significant. It's difficult to justify the cost considering we will still receive the same exact number (5) level 2 Portal for ArcGIS users. The ONLY advantage that I can pinpoint is being able to deploy on larger hardware, not have to deploy on a single machine and being able to use RDBMSs other then SQL Server Express. 

Going from 5 level 2 Portal for ArcGIS users at ArcGIS Enterprise Standard to 50 at ArcGIS Enterprise Advanced doesn't seem to make sense. Too bad Standard didn't include 15 or 20, that would def. "sweeten" the deal for us.

0 Kudos
danbecker
Occasional Contributor III

We are moving forward with the upgrade from workgroup to ArcGIS Enterprise Standard. Also upgrading from SQL Server Express to PostgreSQL.

Purchased the following server:

Supermicro Mobo

12 core Xeon processor

64GB RAM

1x250GB 7.2K RPM SATA drive

8x600GB 10K RPM SAS drives

Supermicro (LSI) SAS RAID controller

Total of 6 intel NICs (each VM will have a dedicated NIC port)

Created a single RAID10 Volume (OBR10); total size 2.4TB.

Installed Hyper-V Core (no GUI) on the SATA drive.

Here's how we plan to breakdown the VM's:

  • VM1 -- MS Server 2012 R2 Std; 1 CPU Core; 8GB RAM
    • 1 VHD (dynamic) for everything; 128GB
    • Domain Controller
    • IIS ArcGIS Web Adapter
  • VM2 -- MS Server 2012 R2 Std; 4 CPU Cores; 24GB RAM
    • 1 VHD (dynamic) for everything; 256GB
    • ArcGIS Server
  • VM3 -- Ubuntu Server 16.04 LTS; 4 CPU Cores; 16GB RAM
    • VHD (dynamic) for OS; 64GB
    • VHD (dynamic) for data; 500GB
    • PostgreSQL
  • VM4 -- Ubuntu Server 16.04 LTS; 3 CPU Cores; 16GB RAM
    • VHD (dynamic) for OS; 64GB
    • VHD (dynamic) for data; 500GB
    • Portal for ArcGIS
    • ArcGIS Data Store

Questions:

  1. The guest OSs are all supported, I checked that. But what about the distribution of system resources?
  2. We only have 2 licenses for Win Server 2012. Would it be better to have fewer Linux VMs, or more?
  3. More or less VHDs? For Win server, we usually have os.vhdx and data.vhdx. The data drive stores network share data. Should we continue that for ArcGIS Enterprise?

thanks in advance

0 Kudos
MalcolmMeyer2
Occasional Contributor II

I know this is two years old, but if you're still there, Dan, what finally convinced you or your superiors to make the financial investment for the upgrade to Enterprise Standard? 

0 Kudos
danbecker
Occasional Contributor III

To start, in hindsight it was the correct decision, we're currently up to >20 users now and have not had ANY issues with our on-premise deployment. Each component of the base deployment is running on it's own VM, some are Ubuntu Server 16.0 LTS, others are Win Server 2012 R2. Guest VMs are running on 2 different physical boxes; both are running FREE MS Hyper-V Server Core 2016. We've recently added a 5th component, ArcGIS Datastore - Spatiotemporal role. We started using Tracker for ArcGIS and it requires the spatiotemporal data store. It took about 10 minutes to spin up another Ubuntu Server VM and install/configure the datastore. Again, everything has been working great.

The final decision to upgrade came down to these limitations of ArcGIS Server Workgroup:

  • only a max of 10 users
  • only SQL Server Express was supported

Since switching to PostgreSQL, the db daemon can utilize as much RAM as you can throw at it. Ver. 10 can also use multiple processors, among several other advantages. Backups are configured as simple cron jobs and the best part yet is that it's running on FREE ubuntu server and did I mention that Postgres is FREE also? It's a win-win for us, and the your end users won't have any clue that db backend your using.