The Lost Art of Enterprise Geodatabases Part 1: Database Administration
Enterprise geodatabases often fly under the radar in the GIS world. However, they are foundational pieces of many GIS departments and power the workflows of all different kinds of spatial operations. Managing them is not always straightforward, but with the right people and tools, you can ensure your GIS data is fast and functional.
An enterprise geodatabase is not a flashy piece of software. In one form or another, it has been around since ArcGIS 8.0. New functionality, like branch versioning, has been added over time, but at its core, it has remained largely unchanged. And to this day, it is the foundation of many enterprise level GIS systems. It provides an authoritative system of record for managing geospatial data, in addition to providing a platform for business integration across an organization. If you are new to using or managing an enterprise geodatabase, this blog is for you. In this series, we will aim to uncover some of the lost art of enterprise geodatabase management, and how you can maximize the access, performance and availability of your valuable GIS data.
Like any piece of GIS software, an enterprise geodatabase exists between both the GIS and IT worlds. However, in some ways the enterprise geodatabase is unique as it is deployed within an existing Relational Database Management System (RDBMS), like PostgreSQL, SQL Server, or Oracle. This sets it apart from a File Geodatabase. This means that administration needs to be coordinated and shared between both IT and GIS professionals. Coordinating a GIS Governance policy that considers this fact is essential for getting the most out of your geodatabase. Geodatabase administration is typically shared between the system administrator and the geodatabase administrator (typically referred to as the SDE user):
System Administrator/Database Administrator (DBA): Required for creating the enterprise geodatabase, creating and managing users and creating backups and test/development environments.
Geodatabase Administrator: Also required for creating the enterprise geodatabase, compressing the geodatabase, updating statistics on system tables, among other administrative tasks.
Note: In the case of SQL Server, the system administrator may also be the geodatabase administrator. See the following link for more information.
Create a geodatabase in SQL Server
In this blog, we are going to look specifically at the role the system administrator/DBA has to play in the management of an enterprise geodatabase. In Part 2, we will look at the role the Geodatabase Administrator has to play as well as the importance of compressing the geodatabase when working with traditionally versioned data.
Backups
Arguably, one of the most important administrative tasks for the system administrator is maintaining backups of the database. Sometimes overlooked, incorporating a backup schedule for your database is critical for disaster recovery, data persistence and even troubleshooting. When troubleshooting issues with enterprise geodatabases in Technical Support, one of our first questions is often, "do you have a full recent backup of your database?". System administrators should always incorporate backups into their maintenance schedule and have a plan for restoring these backups when required.
Creating the Geodatabase and Setting up Environments
It is highly recommended that organizations maintain Test or Development database environments. While licensing of the underlying RDBMS may limit some organizations, test environments are critical for QA/QC of new workflows before they are implemented in production. Creating and maintaining these databases can save a lot of time and headaches down the road. In addition, upgrading your geodatabase is an essential administrative task and working through the process in Test or Dev can help iron out the process before upgrading production.
Geoprocessing tool dialog for creating an enterprise geodatabase
User Management
Users and roles are maintained at the database level. This means that the geodatabase administrator does not have the ability to create and manage these users. The system administrator can create users and roles directly through ArcGIS Pro or through direct access to the database (via SQL or a database client like SQL Server Management Studio).
Tool dialog for creating a new database user. The Input Connection file is using the system administrator user credentials.
When it comes to privilege management however, it is important to remember that privileges on individual datasets within an enterprise geodatabase are controlled by the data owner and must be managed through ArcGIS. You may also have the DBA/system administrator provide additional privileges to the geodatabase administrator user in order to upgrade your geodatabase. Please see the links below for more information:
Privileges for geodatabases in SQL Server
Privileges for geodatabases in Oracle
Privileges for geodatabases in PostgreSQL
Troubleshooting
Your DBA/system administrator is also an important ally when troubleshooting issues with the database. Whether assessing performance, identifying locks or troubleshooting connection issues, these users will have the permissions and privileges to dig into the RDBMS system and provide valuable technical insights.
Conclusion
The active participation of your DBA/system administrator is critical for the smooth operation of your enterprise geodatabase. They can help to ensure that your valuable GIS data is always backed up, that the databases you need for your testing workflows are operational, and that database users are managed effectively. They can even help troubleshoot when things go wrong. Stay tuned for part 2 where we will investigate the role the geodatabase administrator user has to play in managing your enterprise geodatabase.