Mapping Charting Solutions Enterprise Geodatabases Best Practices

MarceloMarques · ‎11-16-2020

MCS Enterprise Geodatabases Best Practices

Intended for database administrators as recommendations for establishing the product workspaces* in an Enterprise Geodatabase (Oracle®, SQL Server®, PostgreSQL®).

*Production Mapping, Data Reviewer, Workflow Manager

Anonymous User · ‎05-18-2021

Hello @MarceloMarques ,

Thank you for compiling such a comprehensive guide for MCS Enterprise Geodatabase best practices. It is very useful!

In the guide, it is mentioned that when deploying MCS in Oracle, each product workspace should have a standalone geodatabase (PDB's):

"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)"

Could you elaborate on this statement? What is the reasoning behind this recomendation? I'm particullary interested in Data Reviewer workspace. What are the negative consequences of having the reviewer workspace in a geodatabase together with other schemas?

Thanks,

Paulina
@Paulakark

MarceloMarques · ‎05-18-2021

Hello Paulina - @Anonymous User ,

I appreciate you reaching out.

Question: Could you elaborate on this statement? What is the reasoning behind this recommendation?

[Marcelo]: Performance and maintenance are the main words here! each product workspace must have its own separate enterprise geodatabase, this way performance improves, there are separate datafiles on disk for each geodatabase, I/O improves as consequence, also makes maintenance tasks easier and faster, gather new statistics, rebuild indexes, execute sde compress, each geodatabase has its own versioning workflow that does not interfere with other gis data and product workspace, also facilitates future geodatabase repository upgrades, product workspace upgrades (workflow manager, data reviewer, product library - workspace upgrades) and also facilitates future rdbms migrations and rdbms upgrades, more on maintenance, over time after intense OLTP workloads ( inserts, updates, deletes) the statistics get old and indexes fragmented, hence the rdbms administrator must gather new statistics, execute sde compress, gather statistics again, rebuild indexes, and gather statistics once more, and this needs to be done often to maintain geodatabase performance, hence having separate geodatabases for each product workspace and gis data by cartographic scale improves significantly these tasks, and has even a larger positive impact on performance on very large enterprise geodatabases.

Question: I'm particularly interested in Data Reviewer workspace. What are the negative consequences of having the reviewer workspace in a geodatabase together with other schemas?

[Marcelo]: poor performance, difficult to manage and perform rdbms / gdb maintenance tasks, more difficult to upgrade gdb repository and review workspace repository, more rdbms data file fragmentation because all product workspaces and gis data are in the same geodatabase, leads to poor performance, poor disk I/O, all data store in same datafiles, might need to perform maintenance tasks more often than normal for some schemas than others, difficult to implement different geodatabase versioning workflows for each schema. etc.

I hope this clarifies.

Thanks,

| Marcelo Marques | Esri PS Products | Principal Product Engineer |

| Cloud & Database Administrator | OCP – Oracle Certified Professional |

THE SCIENCE OF WHERE ®

Anonymous User · ‎05-19-2021

Hello @MarceloMarques ,

Thank you for your quick response. It makes sense that there are great advantages with regards to performance and maintenance. In the deployment that I'm working with (10.7.1, Oracle 19c), the PL, NIS and WMX workspaces are implemented as separate instances with separate geodatabases. However, the data reviewer workspace is configured in a geodatabase together with a couple of other user data schemas that are rarely used. Since performance wouldn't be an issue here, I wanted to make sure that there are no negative impact on the actual functionality of the reviewer workspace, when it's stored in a geodatabase together with other user data schemas and from what I'm reading in your response it seems like there shouldn't be?

Thanks,

Paulina
@Paulakark

MarceloMarques · ‎05-19-2021

Hello Paulina - @Paulakar ,

The best practice is for the Data Reviewer workspace to be store on its own separate enterprise geodatabase, without any other schema in that geodatabase. The recommendation is for each product workspace ( Data Reviewer, Workflow Manager, Product Library, NIS ) to have its own enterprise geodatabase, and if the NIS schema has different scales (e.g. 1:100K, 1:250K) then each scale shall have its own enterprise geodatabase as well. This will align the enterprise geodatabases deployment to be more scalable, flexible to manage, easier to upgrade and migrate, easier to configure, it enhances security and makes it easy to manage permissions, the list of benefits goes on, but this are the main ones. The physical design of the enterprise geodatabases also depend on the size of the data, number of users, type of infra structure, hardware, gis workflows, etc. The best practices above can be applied for small / medium / large / very large enterprise geodatabases deployments and will bring many benefits as I have described.

I hope this clarifies.

Thanks,