The Lost Art of Enterprise Geodatabases Part 2: Geodatabase Administration

Have you ever wondered what the SDE user in your Enterprise Geodatabase is for? This user is the Geodatabase Administrator and they are required to monitor and run a number of key geodatabase operations. In this second installment of the Lost Art of Enterprise Geodatabases, we will uncover the roles and responsibilities of this powerful user.

In part one of this series, we looked at the important role the database administrator has to play in the administration of an enterprise geodatabase. In this blog, we are going to dive into the Geodatabase administrator and the key workflows they must undertake to ensure a functional and performant database.

With a slight caveat with SQL Server, typically the Geodatabase Administrator, often referred to as the SDE user, is a separate user from the database administrator. When creating an Enterprise Geodatabase, a user called ‘SDE’ is automatically created in the database and granted the required privileges. When the geodatabase is created, the SDE user schema will own all of the geodatabase system tables and procedures within the database.

Geodatabase system tables in SQL Server

This means that any tasks requiring administration specific to the geodatabase, (the collection of tables and stored procedures that enable functionality specific to ArcGIS clients) are performed by the SDE user. When considering deploying an Enterprise Geodatabase, incorporating the division of responsibilities between the geodatabase administrator and your database administrator into your GIS data governance plan is essential for efficient management of your data.

When considering the role of geodatabase administrator, it is important to understand what their role and responsibilities are.

Keeping the Geodatabase Version Up to date

Like with all software, the geodatabase itself has a version. Its version is tied to the client release that created the geodatabase. For example, if the Create Enterprise Geodatabase tool was run using ArcGIS Pro 2.9, the Geodatabase Version will be at 2.9. Keeping your geodatabase up to date is a critical maintenance task as the objects that make up the geodatabase are updated at new releases to ensure performance, add new functionality and fix existing bugs. It is best practice to ensure that your client software (Pro or Enterprise), your RDBMS version and your Enterprise Geodatabase version are all at supported releases and compatible with one another.

The Upgrade option in the Database Properties Dialogue

With some exceptions (always check the documentation for your RDBMS type), the geodatabase administrator is responsible for upgrading the geodatabase. The SDE user may also require additional privileges to upgrade the geodatabase. Again, it is important to check the documentation for your RDBMS for the required privileges:

Privileges required to upgrade a geodatabase in Postgres

Privileges required to upgrade a geodatabase in SQL Server

Privileges required to upgrade a geodatabase in Oracle

Before attempting an upgrade, it is best practice to work with the database administrator to set up and test the upgrade in a development environment before upgrading Production. 

Compressing the Geodatabase and Maintaining Performance

A fast geodatabase is a happy geodatabase. One of the primary causes of performance degradation we see here in Technical Support is when a traditionally versioned database has not been compressed and the indexes and statistics for the system tables that manage the versioning system have not been kept up to date. When using traditional versioning on feature classes, edits are stored in separate tables from the business tables (referred to as the delta tables). This means that every time a client accesses a feature class, all of the edits that have not been compressed have to be read and the current display of the data returned to the client. By compressing, these edits are pushed to the business table which reduces the query load required to display the data. The geodatabase administrator is required to run the compress tool at frequent intervals in order to ensure delta tables are continually cleared and query performance is maintained. Scripting this process is often a good strategy to ensure consistency.

If your organization is using traditional versioning workflows, keeping up with statistics and indexes on the tables that control the versioning system helps to maintain performance between compress operations. The geodatabase administrator can use the following workflows to maintain query performance: 

Update Database Statistics for System Tables (See the Include System Tables parameter)

Version Management 

Besides owning all of the system tables within an Enterprise Geodatabase, the SDE user also owns the default version (for both traditional and branch versions). This means that the SDE user can set the access level on this version. It can either be set to private, protected, or public. These access controls means that it is possible to restrict who can see and edit data within the default version. There are different implications for these access controls depending on whether or not you are accessing the default version for branch versioning or the default version for traditional versioning:

Manage Branch Versions

Manage Traditional Versions

In the traditional versioning context, the SDE user also has the ability to create and delete versions that are created by users.

Connection Management

The geodatabase administrator in many ways is the gatekeeper for the Enterprise Geodatabase as a whole. While permissions for specific datasets are applied by the data owners themselves (more on this in Part 3), the geodatabase administrator has the ability to close all connections to the geodatabase as well as to disconnect users that are connected to it. This may be required for troubleshooting, patching or upgrading the geodatabase. Please note that this may require additional privileges depending on your RDBMS. 

Of course, with great power comes great responsibility. It is crucial to ensure that any connections the geodatabase administrator wishes to disconnect are not running any active processes to prevent data corruption issues:

Removing Connections

Disconnecting a user in the Geodatabase Administration window

Disconnecting users may be required to clear up any sessions a user accidentally left open and to allow access to resources that may have been unexpectedly locked. 

Point of Contact

While the geodatabase administrator has many responsibilities for maintaining an enterprise geodatabase, it is key to remember that they are also a point of contact and a reference for all users that access the geodatabase. Keeping track of who your geodatabase administrator is (trust me, it’s not always clear!) will help anyone accessing the geodatabase address any problems quickly and efficiently. 

Conclusion 

The geodatabase administrator is the user who provides an interface between the database administrator and the users who access and interact with the geodatabase. They should have some knowledge of how both geodatabases and databases work. Crucially, they can be a key advocate for the geodatabase within an organization and ensure that the required maintenance tasks are being run and the geodatabase is up to date. In part 3, we will explore the role that data owners themselves play in the smooth operation of one of your most crucial pieces of GIS architecture.