Migrate from the multiple to single database model for SQL Server Enterprise geodatabases

Summary

Instructions provided describe how to migrate from the multiple to single database model for Microsoft SQL Server Enterprise geodatabases. Geodatabases created at version 8.3 are under what is known as the multiple database model. This includes a SDE database where repository information is stored and one or more user databases. These user databases are used to store geographic data by storing the business, F, S, and i tables, while the SDE database stores the repository information (metadata) for the spatial data. The combination of the user database along with the SDE repository database collectively make up an ArcSDE geodatabase.

While it is possible to still have the multiple database model at version 9.0 and greater, this is not the default. ArcSDE 9.0 geodatabases created by the post installation wizard are created to be self contained under the single database model. This means that each database contains the business, F, S, i, and all of the repository tables. Each database also has a unique service pointing to it that runs on a unique port number.

To read more about the multiple and single database models, see the Related Information section at the bottom of this page.

Migration from Multiple to Single database model:

An issue exists with a change in architecture that manifests when users switch over and migrate to the new single database model. Since the geodatabase is split among multiple SQL Server databases, it requires that certain steps be taken to re-organize tables within the database.

Procedure

Note:
WARNING:
It is always suggested that a full database backup of all ArcSDE databases occurs before performing any type of change within a database schema.

• Use the ArcCatalog interface to transfer data to a new single database model geodatabase. Stay within the same version of the ArcSDE software and re-run the complete version of the ArcSDE Post Installation Wizard. Do all of this within the same instance of SQL Server. This works at version 9.0 and greater. If you are currently at 8.3, that best choice would be to upgrade first and then attempt migrating after you get everything setup at the 9.0 (or greater) version.
  
  The Post installer creates a new database in SQL Server, so populate the repository tables into it, and create a service to access the geodatabase with. Then make a connection in ArcCatalog to both the old multiple database model in one connection and the new single database model in another. Next, transfer data between the connections by way of paste, or using various other methods of migrating data.
• Use an unsupported ArcScript migrator tool. Proceed with caution when attempting to use this tool, as it is unsupported by ESRI's support staff.
  
  Multi Database to Single Spatial Database Migrator Tool