The SQL Server multiple spatial database model

LegacyLegacy:

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.

SQL Server multiple spatial database model; multiple databases make up a single 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

Guidelines for creating a multiple spatial database instance

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

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.
    NoteNote:

    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.

    NoteNote:

    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.


8/19/2013