FAQ: How does ArcSDE for MS SQL Server work with multiple and single databases?
How does ArcSDE for MS SQL Server work with multiple and single databases?
ArcSDE 8.x requires the use of a database called 'sde' in which all SDE and GDB system tables (sde.SDE_layers, sde.GDB_objectclasses, etc.) are stored.
Feature classes and raster layers can be stored either in the sde database or in any other database. The sde system tables hold information about spatial data. An ArcSDE service that is comprised of an sde database and other spatial databases is known as a 'multi-database' ArcSDE service.
When a connection is made to an ArcSDE 8.x service and a database is not specified, by default, the connection is made to the sde database. This is not a problem, because any feature class or raster layer can be queried in any other database while connected to any database that participates in the ArcSDE 8.x service. This includes retrieving lists of feature classes and raster layers through sdelayer -o describe or ArcCatalog. Regular DBMS tables, even if they are registered with ArcSDE, are filtered across database lines. This means when connecting, using ArcCatalog, to the database 'planning', the initial object list will not include tables from the 'parcels' database. Any spatial data in 'parcels' that are available based on permissions will be listed.
In this multi-database model, execute select, insert, update, and delete (DML) statements can be used across database boundaries. For example, if connecting to the parcels database, the data can be displayed and edited from the planning database. However, create, alter, and drop (DDL) statements cannot be executed across database boundaries. For example, while connected to the parcels database, a shapefile cannot be loaded into the planning database. This rule also applies to the C-API, Java API, and ArcObjects: Any function that will execute a DDL statement, like SE_layer_alter, SeTable.addColumn(), or IDataset::Delete, will fail if executed across database boundaries.
ArcSDE 9.x introduces two important changes with regard to storing data in multiple databases:
The first change is that it is no longer required to have an 'sde' database that contains all the system tables. The system tables can be stored in any database. In this configuration, all cross database querying is prohibited. An ArcSDE service configured in this manner is known as a 'single spatial database'. This change has several important side effects: a single SQL Server instance can now support numerous ArcSDE services; single spatial databases can be detached from their SQL Server and be moved to another without dependencies on other databases; backup and restore does not rely upon multiple databases; permissions management becomes much simpler, as users are not required to be added to both the spatial database and the sde database. The ArcSDE post-installer at 9.x creates single spatial databases, by default. To do this manually, run the sdesetupmssql -o install tool with the -D (database) switch to install the ArcSDE system tables. To create an ArcSDE service that points to this single spatial database, either use the post-installer or sdeservice -o create followed by sdeservice -o register -r ADMIN_DATABASE -v spatial_database_name command tools. ArcSDE 8.x multi-databases will not be upgraded automatically to ArcSDE 9.0 single spatial databases. The multi-database configuration is still supported at ArcSDE 9.x. There will be a sample multi-database to single spatial database migration tool.
The second important change introduced at ArcSDE 9.x has to do with a login's default database. As, at 9.x, there is a possibility that an ArcSDE service has no sde database, ArcSDE must make more use of the connecting login's default database. If a connection is made to a single spatial database by way of the application server (3-tier connection) and no database name is supplied, by default, the connection is made to that service's ADMIN_DATABASE. See the command usage and the ArcSDE Administration Command Reference for more information on sdeservice and ADMIN-DATABASE. If a connection is made by way of direct connect and no database name is supplied, a connection is made to that user's default database. At ArcSDE 9.x, if connecting to either single spatial databases or multi-databases, always supply a database name for the connections.