The SQL Server multiple spatial database model

Legacy:

The multiple spatial database model of a geodatabase in SQL Server was how ArcSDE geodatabases in SQL Server were initially implemented. Though this model is still supported, ESRI recommends you use the single-spatial database model, in which all geodatabase tables are stored in one SQL Server database.

To store spatial data in an ArcSDE geodatabase, a collection of tables, stored procedures, views, and functions (collectively known as the ArcSDE repository) must be present. The collection of user-defined data and related ArcSDE repository forms a geodatabase.

Prior to ArcSDE 9, geodatabases in SQL Server were always stored using the multiple spatial database model. For ArcSDE 9 and later releases, you should use the single spatial database model. However, since the multiple spatial database model is still supported (though not recommended), the following is a description of this model.

In the multiple spatial database model, the ArcSDE repository is stored in a database called sde. User-defined data, such as feature classes, raster, and nonspatial tables, reside in other databases. The ArcSDE repository in the sde database stores references to the location of all the user-defined data for a particular instance of ArcSDE. The sde database and any other related databases form a single geodatabase. This data storage model is known as the multiple spatial database model because it takes multiple SQL Server databases to make one ArcSDE geodatabase.

The single spatial database model explained in the previous section is the recommended way to store your geodatabases. However, if you would like to use the multiple spatial database model, the following are rules and guidelines for its use and creation.

Rules for using a multiple spatial database instance

• The database that stores the ArcSDE metadata must be named sde. Additional databases that store user-defined data can have any name that follows SQL Server rules for identifiers and do not start with a number.
• Each instance of SQL Server can only host one multiple spatial database instance. SQL Server database names must be unique and can, therefore, only support one database named sde. If you want to store multiple geodatabases in the same SQL Server instance, you can have one multiple spatial database instance and several single spatial database instances.
• In multiple spatial database instances, the ArcSDE repository must reside on the sde schema. These instances do not support ArcSDE repositories on a dbo schema. See A comparison of geodatabase owners in SQL Server for a discussion of the two types of schemas that can be used for the ArcSDE geodatabase repository.
• The ArcSDE geodatabase system tables must be owned by a user named sde. (In SQL Server 2005 or later releases, make sure the sde user's default schema is also named sde.)
• The sde user must be granted access to every database that stores user-defined ArcSDE data.
• All users who connect to this geodatabase instance must be added to the sde database, even if user-defined data is not stored in the sde database. If the users will store data in the sde database, a new schema must be created for that user in the sde database. Again, the user name and schema name must be the same.
• When you connect to one of the databases, you have access to all feature classes in any of the databases to which you have been granted access, provided the CROSS_DB_QUERY_FILTER parameter in the SDE_dbtune table is set to 0. However, you can only access nonspatial tables that are in the database you specify in your spatial database connection, no matter to what the CROSS_DB_QUERY_FILTER parameter is set.
• Cross-database queries are permitted and ArcSDE views can reference data from multiple databases provided the CROSS_DB_QUERY_FILTER parameter in the SDE_dbtune table is set to 0.
• SELECT, INSERT, UPDATE, and DELETE (also known as data manipulation language (DML)) statements can be used across database boundaries. For example, if you connect to the public_works database, you can display and edit data from the planning database. However, you cannot execute CREATE, ALTER, and DROP (DDL) statements across database boundaries.
• When you connect to a multiple spatial database geodatabase using a direct connection, you must specify a databases to which you will connect. If you use an ArcSDE service to connect, the sde database is set as the default database to connect to in the server registry; therefore you do not have to specify a database name provided the CROSS_DB_QUERY_FILTER parameter in the SDE_dbtune table is set to 0.
• The collection of the sde database and other related databases forms a single geodatabase. One set of ArcSDE geodatabase system tables supports all the user-defined data that it references. Therefore, backup and restore operations must apply to the entire geodatabase. If all databases are not backed up and restored simultaneously, you risk geodatabase corruption.
• The sde and other related databases must use the same collation.
• All spatial databases that contain an ArcSDE XML column or an indexed native SQL Server XML column must have their own full-text catalog.
• To see a list of the permissions necessary for the sde and other users, see User permissions for geodatabases in SQL Server.

Guidelines for creating a multiple spatial database instance

Here are some guidelines for creating a database to store spatial data.

• Use the ArcSDE Post Installation wizard to create the database that will store the ArcSDE repository. The wizard will create the sde database, grant the sde login access to it, and install the ArcSDE system tables. Make sure you call this database sde.
• Give the data files a large initial size, then use SQL Server Management Studio to increase the autogrowth increment of both the database and transaction log file. At this time, make an initial backup.
• To create additional databases that store user-defined data, use SQL Server tools such as Management Studio. The sde user must be added to each additional database you create. This user requires no special permissions in these databases. Permissions are automatically granted to the sde user when datasets are created or registered as versioned.

Migrating from the multiple to the single spatial database model

As mentioned above, the single database model is the recommended model to use for your geodatabases. If you have an existing geodatabase stored using the multiple database model and want to migrate to the single database model, follow the steps below:

1. Create a full database backup of all ArcSDE databases.
2. Create a new single database model geodatabase using the Post Installation wizard. Be sure to run the Post Installation wizard for the same release you are using. For example, if you are using the multiple database model with ArcSDE 9.1 and want to move to the single database model, run the 9.1 Post Installation wizard to create the single geodatabase. Also, be sure to perform these steps within the same instance of SQL Server.
   Note:

   If you are currently using ArcSDE 8.3, first upgrade to at least ArcSDE 9, then migrate to the single database model.

3. Use the Catalog interface to transfer data to your new single database model geodatabase.

   The Post Installation wizard creates a new database in SQL Server, so create a service or set up a direct connection with which to access the new geodatabase. Then, start ArcMap, open the Catalog window, and make a connection to both the old multiple database model and the new single database model. Next, transfer data between the geodatabases using copy/paste, import, export, or various other methods of migrating data.

   Note:

   The user who is logged into the new geodatabase when data is transferred to it will be the owner of the data. Therefore, if you want to preserve the same data ownership, individual users must transfer their own data.