A quick tour of administering geodatabases

There is at least some amount of administration needed for every type of geodatabase. The tasks you perform to administer a geodatabase vary depending on the type of geodatabase you are using.

Administering enterprise geodatabases

Enterprise geodatabases are stored in one of the following relational database management systems (RDBMS):

• IBM DB2
• Informix
• Microsoft SQL Server
• Oracle
• PostgreSQL

Enterprise geodatabases are typically accessed by a large number of users. As a result, there is some administration required to make sure the geodatabase is set up properly, users can access the data they need, and the database runs smoothly.

Some administration tasks can be performed using ArcGIS. In many cases, administration tasks are slightly different from one RDBMS to another. Therefore, there is a section in the help specific to each type of RDBMS. Be sure to read the topics pertaining to the RDBMS you are using.

Key tasks for the administration of an enterprise geodatabase include the following:

Installing software and creating a geodatabase

You must install both RDBMS and ArcGIS software, create a database, then enable geodatabase functionality in the database.

For most database management systems, obtain the RDBMS installation from a third-party vendor. An installation of PostgreSQL can be obtained from the Esri Customer Care portal, or you can get your own. Check the system requirements on the ArcGIS Resource Center to be sure you are installing a version of the RDBMS that is supported with the version of ArcGIS you want to use.

Once your ArcGIS and RDBMS software is installed and configured, you can use either the Create Enterprise Geodatabase or Enable Enterprise Geodatabase geoprocessing tool or a Python script to create a geodatabase.

Configuring the RDBMS and the geodatabase

The RDBMS is the container for your data. Advanced users can configure it to meet specific needs.

Configuration of the geodatabase is minimal; in many cases, default configuration settings are sufficient.

The most common form of configuration has to do with storing data in separate locations in the RDBMS. You can create different tablespaces or file groups, then specify these locations in the geodatabase's DBTUNE table. See the topics in the DBTUNE section specific to your RDBMS for more information on storage settings.

Creating user accounts and administering user access

To control access to the geodatabase, you must create or add user accounts to the database. These users may be database users, or they may be operating system accounts that are added to the database as authorized users. (See A quick tour of user accounts for more information.) You can add users to Oracle, PostgreSQL, or SQL Server databases using the Create Database User geoprocessing tool or a Python script. To simplify user administration, you will likely create groups or roles and add users to them. You can use the Create Role geoprocessing tool to create database roles in Oracle, PostgreSQL, or SQL Server databases.

Once users have been added to the database, you can grant permissions to them, individually or in groups, to allow them to perform their work in the geodatabase. These include Data Definition Language (DDL) privileges to create, alter, or drop objects in the database. Individual data owners can grant Data Manipulation Language (DML) privileges to other users or groups to allow them to select, insert, update, or delete records in their data. DML privileges can be granted through ArcGIS for Desktop. See Granting and revoking privileges on datasets for information on how to manage DML privileges through ArcGIS for Desktop. For information on privileges needed for different types of users, see the topic specific to your RDBMS:

User privileges for geodatabases in DB2User privileges for geodatabases in InformixUser privileges for geodatabases in OracleUser privileges for geodatabases in PostgreSQL

Creating connections to the geodatabase

To work with the data, users connect to the geodatabase from client applications. Connections can be made directly between clients and the database or between clients, an ArcSDE service, and the database. You can use one or both of these connection methods at your site.

For direct connections to the geodatabase, you must install an RDBMS client on the client computer. You can obtain an RDBMS client from your RDBMS vendor. Oracle, PostgreSQL, and SQL Server users can also download an RDBMS client from the Esri Customer Care portal.

If you want some or all of your users to use an ArcSDE service to connect, you need to set up and maintain the service. The ArcSDE service can run on the same server as the RDBMS, or it can run on a separate server. To use a service, you must download the ArcSDE application server for your RDBMS from the Esri Customer Care portal and install it. Instructions for setting up the service are included with the ArcSDE application server installation.

Maintaining the geodatabase

As data is added to the geodatabase and is altered, the geodatabase must be maintained so that the data is available to all authorized users when needed and without any depreciation in performance.

To ensure the data is not lost, the administrator must perform regular backups and have a recovery plan in place and tested. See the topics specific to your RDBMS for more information:

DB2 backups Recovery models for DB2Informix backupsRecovery models for InformixOracle backupsRecovery models for OraclePostgreSQL backupsRecovery models for PostgreSQL

When new data is added or existing data updated, database statistics should be updated and indexes rebuilt to be sure the RDBMS optimizer can efficiently query the data. The geodatabase administrator can update statistics and indexes on system tables using the Analyze Datasets and Rebuild Indexes geoprocessing tools, respectively. Data owners can use these tools to update statistics and indexes on their own data tables.

For versioned geodatabases, the administrator must regularly compress the geodatabase to maintain performance levels. Geodatabase compression is performed from ArcGIS using either the Compress command in the Catalog tree or the Compress geoprocessing tool or Python script.

For more information on the compress operation, see The geodatabase compress operation.

Upgrading the geodatabase

When a new release of ArcGIS is available, your organization may want to move to that release to take advantage of new functionality and fixes. As an administrator, you should set up a test server on which you install the new ArcGIS software and upgrade a copy of your production geodatabase. Tests should then be run to be sure the new version works as you expected.

Once testing is complete, you can upgrade the production software and geodatabases. See topics appropriate to your RDBMS for instructions:

IBM DB2

Preparing to upgrade a geodatabase in DB2Upgrading an ArcSDE geodatabase in DB2 from ArcGIS for DesktopUpgrading an ArcSDE geodatabase for DB2 on zOSScripting an ArcSDE geodatabase upgrade in DB2

Informix

Preparing to upgrade a geodatabase in InformixUpgrading an ArcSDE 9.3 or later release geodatabase in Informix from ArcGIS for DesktopScripting an ArcSDE geodatabase upgrade in Informix

Oracle

Preparing to upgrade a geodatabase in OracleUpgrading a geodatabase in Oracle from ArcGIS for DesktopUpgrading geodatabases in user schemas in OracleScripting a geodatabase upgrade in Oracle

PostgreSQL

Preparing to upgrade a geodatabase in PostgreSQLUpgrading a geodatabase in PostgreSQL from ArcGIS for DesktopScripting a geodatabase upgrade in PostgreSQL

Understanding geodatabase storage

To work with an enterprise geodatabase, database and geodatabase administrators should understand how the geodatabase tables are stored and the data types that are used. The "Inside a geodatabase" section of the help for each DBMS provides information on data type and dataset storage. This section also includes information on using views on geodatabase data.