Enterprise Mapping and Charting Databases

From wiki.gis.com
Jump to: navigation, search

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 | System Design Strategies

App-esri-production-mapping.png

Marcelo Marques, GIS Technical Manager, OCP

An Esri ® Technical Reference Document • Spring 2019 | Specific license terms for this content

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 ( your gis data - create separate geodatabase for each cartographic scale e.g. pm50 (1:50k); pm250 (1:250k)
 pl - product library workspace
 rev - data reviewer workspace
 wmx - workflow manager workspace

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 Document • Spring 2019 | Specific license terms for this content

Guide Books ArcGIS Pro


The guide books are intended for database administrators to help establish the product workspaces in an Enterprise Geodatabase.

ArcGIS Pro 2.x February, 2019
    Production Mapping
       Production Database
         Best Practices Production Mapping 2.x Workspace in Oracle® ...
         Best Practices Production Mapping 2.x Workspace in SQL Server® ...
         Best Practices Production Mapping 2.x Workspace in PostgreSQL® ...
    Data Reviewer
      Best Practices Data Reviewer 2.x Workspace in Oracle® ...
      Best Practices Data Reviewer 2.x Workspace in SQL Server® ...
      Best Practices Data Reviewer 2.x Workspace in PostgreSQL® ...
    Workflow Manager
      Best Practices Workflow Manager 2.x Workspace in Oracle® ...
      Best Practices Workflow Manager 2.x Workspace in SQL Server® ...
      Best Practices Workflow Manager 2.x Workspace in PostgreSQL® ...

Guide Books ArcMap


The guide books are intended for database administrators to help establish the product workspaces in an Enterprise Geodatabase.

 ArcMap 10.7 Mar 19, 2019
    Production Mapping
       Production Database
         Best Practices Production Mapping 10.7 Workspace in Oracle®
         Best Practices Production Mapping 10.7 Workspace in SQL Server®
         Best Practices Production Mapping 10.7 Workspace in PostgreSQL®
       Product Library
         Best Practices Product Library 10.7 Workspace in Oracle®
         Best Practices Product Library 10.7 Workspace in SQL Server®
         Best Practices Product Library 10.7 Workspace in PostgreSQL®
    Data Reviewer
      Best Practices Data Reviewer 10.7 Workspace in Oracle®
      Best Practices Data Reviewer 10.7 Workspace in SQL Server®
      Best Practices Data Reviewer 10.7 Workspace in PostgreSQL®
    Workflow Manager
      Best Practices Workflow Manager 10.7 Workspace in Oracle®
      Best Practices Workflow Manager 10.7 Workspace in SQL Server®
      Best Practices Workflow Manager 10.7 Workspace in PostgreSQL®
    Bathymetric Solution (BIS)
      Best Practices Bathymetry Solution (BIS) 10.7 Workspace in Oracle®
      Best Practices Bathymetry Solution (BIS) 10.7 Workspace in SQL Server®

Guide Books Previous Releases


Previous Releases - ArcMap 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 Enterprise MCS Databases (VLEMCSDB), 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.

Marcelo Marques, GIS Technical Manager, OCP | An Esri ® Technical Reference Document • Spring 2019 | Specific license terms for this content

Geodatabases


Create a geodatabase for each software product workspace.

 pm - production database ( your gis data - create separate geodatabase for each cartographic scale e.g. pm50 (1:50k); pm250 (1:250k)
 pl - product library workspace
 rev - data reviewer workspace
 wmx - workflow manager workspace

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 and white papers best practices
 - the scripts also have very advanced rdbms and advanced geodatabase best practices for VLEMCSDB that go beyond the guide books and white papers recommendations
  
 Oracle 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"
      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 2.3 & ArcMap 10.7 January 10, 2019    *New
    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
  
 Oracle 11gR2:
     ArcMap 10.2 / 10.1 March 27, 2014
     - For consolidation, use Esri user-schema geodatabases (1 oracle database - multiple geodatabases)
  
 SQL Server 2014 / 2016 / 2017 (Windows/Linux):
     ArcGIS Pro 2.3 & ArcMap 10.7 January 10, 2019    *New
     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 May 11, 2018
  
 PostgreSQL:
   ArcGIS Pro 2.3 & ArcMap 10.7
     Postgres on Windows January 10, 2019    *New
     Postgres on Linux January 10, 2019    *New
   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.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
 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

Database Documentation Links

ArcGIS System Requirements Oracle SQL Server PostgreSQL
Help: 10.7 / 10.6 / 10.5 / 10.4 / 10.3 System Requirements Help: Oracle 18c Online Documentation Help: Windows Server 2019 Requirements pgfoundry.org
Help: 10.2 System Requirements Help: Oracle 12.2 Online Documentation Help: Windows Server 2016 Requirements postgis.org
Help: 10.1 System Requirements Help: Oracle 12.1 Online Documentation Help: Windows Server 2012 R2 Requirements postgresql.org
Support: ArcGIS Monitor Help: Oracle 11.2 Online Documentation Help: SQL Server Requirements and older enterprisedb.com
Support: Esri Support Help: Oracle 11.1 Online Documentation Help: SQL Server Books Online pgbarman.org
Support: mxdperfstat Help: Oracle 10g Online Documentation Support: SQL Server 2017 Patches odbc.postgresql.org
Support: PerfQAnalyzer Support: MOS - My Oracle Support Support: SQL Server 2016 Patches Replication, Clustering and Connection_Pooling
Training: Geodatabase Support: OTN - Oracle Technology Network Support: SQL Server 2014 Patches pg_rman
Help: 10.x Geodatabase in Amazon RDS SQL Server Support: Oracle Downloads Support: Microsoft Support pgadmin
Help: 10.x Geodatabase in Amazon RDS PortgreSQL Support: TOAD World Support: SQL Server Central
Help: 10.x Geodatabase in Azure SQL Database Training: Oracle Learning Library Support: MSDN - SQL Server Developer Center
Help: 10.x ArcGIS for Server on Cloud Platforms Training: Oracle-Base ( Tim Hall, OCM )
Training: samplecode.oracle.com
Training: onlinevideolecture ebooks
Storage: BAARF
Storage: Configuring Oracle Server for VLDB - Cary Millsap

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.



Marcelo Marques, GIS Technical Manager, OCP | An Esri ® Technical Reference Document • Spring 2019 | Specific license terms for this content


1. Enterprise MCS Databases Best Practices 2. ArcGIS Help Guide Books Previous Releases