Mapping and Charting Solutions (MCS) Enterprise Databases Best Practices

331086
0
12-18-2014 02:21 PM
MarceloMarques
Esri Regular Contributor
11 0 331K

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

________________________________________________________________________________________________

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
ArcSDE User-Schema Geodatabases in Oracle are deprecated

________________________________________________________________________________________________

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

Previous Releases ArcGIS Pro

________________________________________________________________________________________________

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

ArcGIS System RequirementsOracleSQL Server

PostgreSQL

Help: ArcGIS Pro Database System RequirementsHelp: Oracle 19c Online Documentation 

 

Help: 10.8 / 10.7 / 10.6 / 10.5 / 10.4 / 10.3 Database System RequirementsHelp: Oracle 18c Online DocumentationHelp: Windows Server 2019 Requirements

 

Help: 10.2 Database System RequirementsHelp: Oracle 12.2 Online DocumentationHelp: Windows Server 2016 Requirementspostgis.org
Help: 10.1 Database System RequirementsHelp: Oracle 12.1 Online DocumentationHelp: Windows Server 2012 R2 Requirementspostgresql.org
Support: ArcGIS MonitorHelp: Oracle 11.2 Online DocumentationHelp: SQL Server Requirements and olderenterprisedb.com
Support: Esri SupportHelp: Oracle 11.1 Online DocumentationHelp: SQL Server Books Onlinepgbarman.org
Support: mxdperfstatHelp: Oracle 10g Online Documentation odbc.postgresql.org
Support: PerfQAnalyzerSupport: MOS - My Oracle Support Replication, Clustering and Connection_Pooling
Training: GeodatabaseSupport: OTN - Oracle Technology NetworkSupport - SQL Server Update Centerpg_rman
Help: 10.x Geodatabase in Amazon RDS SQL ServerSupport: Oracle DownloadsSupport: Microsoft Supportpgadmin
Help: 10.x Geodatabase in Amazon RDS PortgreSQLSupport: TOAD WorldSupport: SQL Server Central 
Help: 10.x Geodatabase in Azure SQL DatabaseTraining: Oracle Learning LibrarySupport: MSDN - SQL Server Developer Center 
Help: 10.x ArcGIS for Server on Cloud PlatformsTraining: Oracle-Base ( Tim Hall, OCM )  
 Training: samplecode.oracle.com  
What is Egdbhealth?Training: onlinevideolecture ebooks  
Using Egdbhealth to Evaluate a GeodatabaseStorage: BAARF 

 

 Storage: Configuring Oracle Server for VLDB - Cary Millsap  
 Oracle 19c Licensing  
 Oracle 18c Licensing  
 Oracle 12c Licensing  
 Oracle 11gR2 Licensing  
 Pricing  
 How To: Perform schema restores of an Oracle enterprise geodatabase  
 FAQ: Working with ST_Geometry and Oracle export/import  

________________________________________________________________________________________________

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.


Configuring oracle raid 10 .jpg

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

________________________________________________________________________________________________

About the Author
| Marcelo Marques | Esri Principal Product Engineer | Cloud & Database Administrator | OCP - Oracle Certified Professional | 30 years' experience | www.linkedin.com/in/mmarquesbr | "I embarked on my journey with Esri Technology in 1992, and since 1997, I have been working with ArcSDE Geodatabases, right from its initial release." | " a successful Enterprise ArcGIS application deployment starts with a strong physical database design and best database administration practices" | " I do not fear computers. I fear the lack of them." - Isaac Asimov |