Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices
Intended for database administrators as recommendations for establishing the product workspaces in an Enterprise Geodatabase (Oracle®, SQL Server®, PostgreSQL®).
_______________________________________________________________
Table of Contents
________________________________________________________________________________________________
MCS Enterprise Databases Best Practices - Previous Releases
________________________________________________________________________________________________
Esri MCS
Esri's Mapping and Charting Solutions (MCS) is used by all types of organizations to improve workflows and meet the most challenging mapping and charting production needs.
Key Reference Sites | Wiki - System Design Strategies | community.esri.com - System Design Strategies
An Esri ® Technical Reference January • 2024
________________________________________________________________________________________________
Esri MCS ArcGIS Extensions
- Nautical: ArcGIS Maritime - Charting ArcGIS Bathymetric
- Linear Referencing: Roads & Highways Pipeline Referencing
________________________________________________________________________________________________
Enterprise MCS Databases Best Practices
The guide books are intended for database administrators as recommendations for establishing the product workspaces in an Enterprise Geodatabase (Oracle®, SQL Server®, PostgreSQL®).
* a successful enterprise ArcGIS application deployment starts with a strong physical database design and best database administration practices
Create a geodatabase for each software product workspace.
pm - production database
Production Mapping & Defense Mapping - Topographic Mapping - MGCP, TDS, etc.
your gis data - create separate geodatabase for each
cartographic scale e.g. pm50 (1:50k); pm250 (1:250k)
pl - product library workspace - Product Library* (* ArcMap only)
rev - data reviewer workspace - Data Reviewer
wmx - workflow manager workspace - Workflow Manager
mar1 - maritime charting database - Nautical
your gis data - create separate geodatabase for each
cartographic scale e.g. mar500k (1:500,000); mar1m (1:1,000,000)
mar2 - bathymetric database - Nautical
aero1 - aeronautical charting database - Aviation
your gis data - create separate geodatabase for each
cartographic scale e.g. aero500k (1:500,000); aero1m (1:1,000,000)
aero2 - airports database - Aviation
lr - linear referencing database - Roads & Highways / Pipeline Referencing
ind - indoors database - Indoors
For a very large featureclass create a separate dbtune keyword that points to separate tablespaces and datafiles in the database storage, this reduces disk contention and improves I/O.
An Esri ® Technical Reference January• 2024
________________________________________________________________________________________________
FAQ - Frequent Asked Questions
How Load Large Featureclass Oracle Geodatabase
How Load Large Featureclass SQL Server Geodatabase
How to Move and Upgrade a WMX Repository Enterprise GDB
How to Move the WMX table JTX_JOB_ATTACHMENTS
Pro Branch Version & Oracle Table Compression
Oracle eGDB SDE Repo Upgrade Using Oracle Restore Point | Download PDF Version
How to Move the Oracle Enterprise Geodatabase with the Oracle Data Pump Utility
How to Move the PostgreSQL Enterprise Geodatabase with pg_dump and pg_restore
How to Move the SQL Server Enterprise Geodatabase with a database backup
How to Install Database Clients for ArcGIS
How to Install the Oracle Database Client for ArcGIS?
How to Install the SQL Server Client for ArcGIS?
How to Upgrade the PostgreSQL and PostGIS version for the Enterprise Geodatabase on Windows
How to Upgrade the PostgreSQL and PostGIS version for the Enterprise Geodatabase on Linux
How to Configure Windows Authentication for the PostgreSQL Enterprise Geodatabase
________________________________________________________________________________________________
Guide Books ArcGIS Pro
The guide books are intended for database administrators to help establish the product workspaces in an Enterprise Geodatabase.
ArcGIS Pro 3.x
Production Mapping & Defense Mapping - Topographic Mapping
Production Database
Best Practices Production Mapping 3.x Workspace in Oracle® March/13/2024
Best Practices Production Mapping 3.x Workspace in SQL Server® March/13/2024
Best Practices Production Mapping 3.x Workspace in PostgreSQL® March/13/2024
Data Reviewer
Best Practices Data Reviewer 3.x Workspace in Oracle® March/13/2024
Best Practices Data Reviewer 3.x Workspace in SQL Server® March/13/2024
Best Practices Data Reviewer 3.x Workspace in PostgreSQL® March/13/2024
Workflow Manager
Best Practices Workflow Manager 3.x Workspace in Oracle® March/13/2024
Best Practices Workflow Manager 3.x Workspace in SQL Server® March/13/2024
Best Practices Workflow Manager 3.x Workspace in PostgreSQL® March/13/2024
Aviation - Charting / Airports
Best Practices Aviation 3.x Workspace in Oracle® March/13/2024
Best Practices Aviation 3.x Workspace in SQL Server® March/13/2024
Best Practices Aviation 3.x Workspace in PostgreSQL® March/13/2024
Maritime - Charting
Best Practices Maritime Charting 3.x Workspace in Oracle® March/13/2024
Best Practices Maritime Charting 3.x Workspace in SQL Server® March/13/2024
Best Practices Maritime Charting 3.x Workspace in PostgreSQL® March/13/2024
Maritime - Bathymetry
Best Practices Bathymetry 3.x Workspace in Oracle® March/13/2024
Best Practices Bathymetry 3.x Workspace in SQL Server® March/13/2024
Best Practices Bathymetry 3.x Workspace in PostgreSQL® March/13/2024
Linear Referencing - Roads & Highways / Pipeline Referencing
Best Practices LR 3.x Workspace in Oracle® March/13/2024
Best Practices LR 3.x Workspace in SQL Server® March/13/2024
Best Practices LR 3.x Workspace in PostgreSQL® March/13/2024
Indoors
Best Practices Indoors 3.x Workspace in Oracle® March/13/2024
Best Practices Indoors 3.x Workspace in SQL Server® March/13/2024
Best Practices Indoors 3.x Workspace in PostgreSQL® March/13/2024
________________________________________________________________________________________________
Guide Books ArcMap
The guide books are intended for database administrators to help establish the product workspaces in an Enterprise Geodatabase.
Note: Roads and Highways, Pipeline Referencing, GeoEvent, Indoors, Aviation customers can follow the same best practices in the Production Mapping Guide Books
ArcMap 10.8 Feb 20, 2020
Production Mapping
Production Database
Best Practices Production Mapping 10.8 Workspace in Oracle®
Best Practices Production Mapping 10.8 Workspace in SQL Server®
Best Practices Production Mapping 10.8 Workspace in PostgreSQL®
Product Library
Best Practices Product Library 10.8 Workspace in Oracle®
Best Practices Product Library 10.8 Workspace in SQL Server®
Best Practices Product Library 10.8 Workspace in PostgreSQL®
Data Reviewer
Best Practices Data Reviewer 10.8 Workspace in Oracle®
Best Practices Data Reviewer 10.8 Workspace in SQL Server®
Best Practices Data Reviewer 10.8 Workspace in PostgreSQL®
Workflow Manager
Best Practices Workflow Manager 10.8 Workspace in Oracle®
Best Practices Workflow Manager 10.8 Workspace in SQL Server®
Best Practices Workflow Manager 10.8 Workspace in PostgreSQL®
Bathymetric Solution (BIS)
Best Practices Bathymetry Solution (BIS) 10.8 Workspace in Oracle®
Best Practices Bathymetry Solution (BIS) 10.8 Workspace in SQL Server®
Nautical Solution (NIS)
Best Practices Nautical Solution (NIS) 10.8 in Oracle®
Best Practices Nautical Product Library 10.8 in Oracle®
Best Practices Nautical Solution (NIS) 10.8 in SQL Server®
Best Practices Nautical Product Library 10.8 in SQL Server®
Previous Releases ArcMap 10.7 / 10.6 / 10.5 / 10.4 / 10.3 / 10.2 / 10.1 / 10.0 .
________________________________________________________________________________________________
Guide Books Previous Releases
Previous Releases - ArcGIS Pro 2.x - ArcMap 10.7 / 10.6 / 10.5 / 10.4 / 10.3 / 10.2 / 10.1 / 10.0 .
________________________________________________________________________________________________
Enterprise MCS Databases Template Scripts
How to install, configure, backup and maintain Very Large MCS Enterprise Databases (VLMCSEDB), detailed instructions for data loading, backup and recovery and to manage multiple user logins.
*For Professional Database Administrators, requires advanced RDBMS and advanced Geodatabase experience.
An Esri ® Technical Reference January• 2024
Geodatabases
Create a geodatabase for each software product workspace.
pm - production database
Production Mapping & Defense Mapping - Topographic Mapping - MGCP, TDS, etc.
your gis data - create separate geodatabase for each
cartographic scale e.g. pm50 (1:50k); pm250 (1:250k)
pl - product library workspace - Product Library* (* ArcMap only)
rev - data reviewer workspace - Data Reviewer
wmx - workflow manager workspace - Workflow Manager
mar1 - maritime charting database - Nautical
mar2 - bathymetric database - Nautical
aero1 - aeronautical charting database - Aviation
aero2 - airports database - Aviation
lr - linear referencing database - Roads & Highways / Pipeline Referencing
ind - indoors database - Indoors
For a very large featureclass create a separate dbtune keyword that points to separate tablespaces and datafiles in the database storage, this reduces disk contention and improves I/O.
Database Template Scripts
- the scripts cover the guide books best practices
- the scripts also have very advanced rdbms and advanced geodatabase best practices for VLMCSEDB that go beyond the guide books recommendations
Oracle
Oracle 21c / 19c / 18c / 12c (12.2, 12.1)
- For consolidation going forward adopt the Oracle 12c multi-tenant architecture:
1 CDB (container database):
"mcs" hosts PDB's (pluggable databases):
"pm", "pl", "rev", "wmx", "mar", "aero", "lr", "ind"
each pluggable database is a standalone geodatabase with only
the sde master repository installed
- Discontinue the use of Esri user-schema geodatabases
(1 oracle database - multiple geodatabases)
ArcGIS Pro 3.x & ArcMap 10.9.x June 21, 2022 *New
ArcGIS Pro 2.3 / 2.4 & ArcMap 10.7 / 10.8 Dec 18, 2020
ArcGIS Pro 2.2 & ArcMap 10.6 / 10.5 / 10.4 / 10.3 / 10.2 Aug 02, 2018
MCS Oracle 12c: Step-by-step Installation of Oracle RAC 12c on VMWare Nov 12, 2017
How to Load a Large Featureclass in an Oracle Geodatabase Oct 23, 2020
Oracle Geodatabase SDE Repository Upgrade using Oracle Restore Point Sep 06, 2022
How to Move eGDB using Oracle Data Pump Sep 13, 2022
Oracle 11gR2
ArcMap 10.2 / 10.1 March 27, 2014
- For consolidation, use Esri user-schema geodatabases
(1 oracle database - multiple geodatabases)
SQL Server
SQL Server 2019 / 2017 / 2016 / 2014 (Windows/Linux)
ArcGIS Pro 3.x & ArcMap 10.8.x June 21, 2022 *New
ArcGIS Pro 2.3 / 2.4 & ArcMap 10.7 / 10.8 March 27, 2020
ArcGIS Pro 2.2 & ArcMap 10.6 / 10.5 / 10.4 / 10.3 / 10.2 / 10.1 November 13, 2017
MCS SQLServer: Step-by-step Installation of SQL Server 2016/2017
Cluster on a Windows Server 2016 Failover Cluster
SQLServer Cluster on Windows Failover Cluster May 11, 2018
How to Load a Large Featureclass in a SQL Server Geodatabase Dec 03, 2020
PostgreSQL
ArcGIS Pro 3.x & ArcMap 10.8.x
Postgres on Windows July 05, 2023 *New
Postgres on Linux July 05, 2023 *New
ArcGIS Pro 2.3 / 2.4 & ArcMap 10.7 / 10.8
Postgres on Windows March 20, 2020
Postgres on Linux March 20, 2020
ArcGIS Pro 2.2 & ArcMap 10.6 / 10.5 / 10.4 / 10.3 / 10.2 / 10.1
Postgres on Windows April 14, 2017
Postgres on Linux August 22, 2017
________________________________________________________________________________________________
Database Connections Best Practices
1. ArcSDE Application Server ("SDE Service") is deprecated, use "direct connect".
- What's news in ArcMap 10.8 / 10.7 / 10.6 / 10.5 / 10.4 / 10.3
see Databases & Geodatabases
- What's news in ArcGIS Pro
- Deprecation Plan for ArcGIS Products
2. Never use the "sde" user connection in: Maps (e.g. mxd's), GP tools, Map Services,
Feature Services, etc.
3. "sde" user is only for geodatabase administration (e.g. sde compress),
sde user is the geodatabase repository owner
4. Never use "data owner" user connections in: Maps (e.g. mxd's), GP tools, Map Services,
Feature Services, etc.
- Unless really necessary to create new featureclasses or tables in the geodatabase
- But need to understand the implications
- For Geoprocessing tools always prefer file geodatabases as scratch workspaces,
its faster!!!!
- The problem with multiple "data owner" user connections is that this causes
table locks in the database
- That can lead to deadlocks and make ArcGIS Desktop (ArcMap/Pro) and
ArcGIS Server connections to halt / freeze
- Exception is Branch Versioning, only the data owner can publish the Feature Service
5. How to avoid the "deadlock" issue then?
- If not editing data in ArcGIS then use a "viewer" user connection to create the map
- If editing data in ArcGIS then use an "editor" user connection to create the map
- The "viewer" user connection has read-only (select) permission on the Featureclasses
and Tables of the "data owner" user
- The "editor" user connection has read-write (select, insert, update and delete)
permissions on the Featureclasses and Tables of the "data owner" user
- If creating ArcGIS Server Map Services use a "viewer" user connection to create
the map before it’s published
- If creating ArcGIS Server Feature Services use an "editor" user connection to create
the map before it’s published
- If creating ArcGIS Server Geoprocessing Services: if editing data use "editor"
otherwise use the "viewer" user connection
6. "deadlock" explanation more details
- Oracle-Base deadlocks | Oracle Documentation deadlocks
- SQLServer Documentation deadlocks
- PostgreSQL Documentation deadlocks
________________________________________________________________________________________________
Database Documentation Links
________________________________________________________________________________________________
Database Storage Best Practices
Why to use Multiple Tablespaces and RAID 10
• Large production enterprise geodatabase systems should employ a hardware striping solution.
Your best disk and data organization strategies involve spreading your data across
multiple disks.
• With data spread across multiple disks, more spindles actively search for it.
This can increase disk read time and decrease disk contention.
• There are two main ways of achieving striping: tablespaces and redundant array of
independent disks (RAID).
• You can also combine the two by creating tablespaces within disk arrays.
• You can employ data segregation strategies; keeping tables from indexes or certain types
of tables from other tables will improve performance and alleviate administrative burdens.
• Standard GIS storage recommendations favor keeping index and log files separate from
vector and tabular business tables.
• For performance reasons, it is better to position the business, feature, and spatial index
tables separately and position tablespace data files based on their usage pattern.
• For a multiversioned, highly active editing geodatabase, database files of the VERSIONS
tablespace may be separated and dispersed across available disks to avoid I/O contention.
• As a rule, you should create database files as large as possible based on the maximum
amount of data you estimate the database will contain to accommodate future growth.
• By creating large files, you can avoid file fragmentation and gain better
database performance.
• In many cases, you can allow data files to grow automatically; just be sure to
limit autogrowth by specifying a maximum growth size that leaves some available
hard disk space.
• By putting different tablespaces on different disks, you can also help eliminate physical
fragmentation of your files as they grow.
BAARF - Battle Against Any RAID 5
Configuring Oracle Server for VLDB - Cary Millsap, Oracle
Famous white paper!!!!!
• Fine-grained striping can be a bad choice for the tablespaces to which Oracle Server
writes sort segments if a lot of applications processes will be sorting simultaneously.
Fine-grained striping can a so yield surprisingly bad results for applications that
use the Oracle's parallel query optimization (PQO) feature, because PQO's use of multiple
query slaves manufactures its own high concurrency level even for a single user session.
• If your database contains one or more small tablespaces for which large raw slices
would be wasteful, you may wish to select one or two more standard raw slice sizes for
those tablespaces. If you use more than one raw slice size, then make the sizes integral
multiples and divisors of each other.
• I/O performance—Each tablespace should contain segments whose I/O concurrency and I/O size
characteristics are similar, to facilitate disk array size and stripe size selection.
Grouping read-only segments into read-only tablespaces reduces backup and recovery data
transfer volumes and reduces PCM lock maintenance.
• Outage resilience—Small tablespaces allow offline tablespace maintenance with minimal
application outage. The system tablespace cannot be taken off-line, and it cannot be
dropped and recreated, so keeping as few segments there as possible minimizes the need
for database downtime. Isolating rollback segments into as few tablespaces as possible
reduces outage frequency, because any tablespace containing an on-line rollback segment
cannot be taken off-line. Storing referentially related segments in small groups of
tablespaces maximizes your ability to exploit the tablespace point-in-time
recovery feature.
• Space management—Isolating segments with short lifespans minimizes the impact of tablespace
free space fragmentation that can block Oracle Server extent allocation.
VLDB administrators profit from using tablespace default storage parameters instead of
maintaining segment sizing parameters at the segment level.
• Quota management—Oracle Server space quotas are administered to users by tablespace.
Hence, you should assign groups of segments in one schema to a small group of tablespaces.
• If you are using fine-grained striping for data files, then it is not necessary to
separate indexes from data to distribute I/O load across disks.
However, if your operational procedures include periodic index rebuilds,
you may want to consider isolating indexes into their own tablespaces to minimize the
possible impact of tablespace free space fragmentation caused by the drop index.
Optimal Storage Configuration Made Easy - Juan Loaiza, Oracle
• This methodology assigns a specific load to various subsets of the database such as
log files, rollback segments, index tablespaces, data tablespaces, etc. and
assigns disks to these subsets based on their load profile.________________________________________________________________________________________________
An Esri ® Technical Reference January• 2024
________________________________________________________________________________________________
