There are still many users who use multiple spatial database model with ArcSDE at their organizations, even though ESRI have, for a long time, recommended using single database model. Multiple spatial database model basically implies that one will store all spatial data and ArcSDE system tables in one DBMS spatial database (let’s call it SDE) and all user data, such as tables of attributes, non-spatial tables, views, etc. will be stored in another database (let’s call it geodata). The models are described very well at the ESRI Web help.
Even though some users insist that this model is effective, I tend to think that it is better to use or migrate to the single database model, wherever possible. Based on my own experience, it is much easier to administer (that is, to manage user privileges, backup-restore procedures, etc.) a database that includes all data tables and system repository tables, rather than several databases with distributed data tables and system tables.
To migrate from the multiple to single spatial database model, I would recommend first visiting ESRI Knowledge Base article #29708. A more detailed description of the migration procedure steps can be found below. The prerequisites are: two databases – a sde (spatial database) and a geodata (non-spatial database); there is an ArcSDE service, which is called esri_sde which is being started at 5151 tcp/ip port. You can check the service’s name and port at <Windows installation folder>/system32/drivers/etc/services.
1. Run ArcSDE Post Installation. In the wizard, use SDE schema; create a new spatial database (its name should be different from sde/geodata, since databases with those names already exist. Use, for instance, GISDB). Bear in mind that you will not be able to change the name of ArcSDE spatial database after its creation, so use a meaningful name that could reflect its contents; the name of the project would be the most appropriate. If you have renamed an sde database, for example, in MS SQL Server by using Transact SQL sp_renamedb command, you will no longer be able to connect to this database. This is because of stored procedures that are hard-coded with the database name.
Keep in mind, though, that you will be able to create as many spatial databases as you wish later. However, now the task is to transfer the data from the two databases – non-spatial and spatial – to a single one, i.e., the spatial database GISDB. Then create and authorize an sde service, if need be. Remember to choose another port and another name for the service: it should be different from esri_sde and should work on the port 5152, for example. Otherwise, skip this step and use direct connection to databases.
2. In ArcCatalog, create a new spatial database connection to the GISDB database and the old Geodata database by using the database owner’s account (probably the sde user). Copy and paste all database objects from the Geodata database to the new GISDB database. Non-spatial tables that you cannot see from ArcCatalog can be transferred later in SQL Server Management Studio (you could use Import/Export data command: right-click a database > Tasks > Export data/Import data).
3. Now you have a single spatial database. Optionally, if there is a need to move this single database to another machine (for example, if you migrate from the development to the production), you could create a backup of the GISDB database in SQL Server Management Studio. Right-click the database > Tasks > Backup. Create a backup file on a local hard disk. Copy this file to a machine where you want to have this database. Restore the database in SQL Server Management studio. Right-click the Databases in the Object Explorer > Restore Database. Restore the database using the same name it had when you created it, that is, GISDB. If you have restored the database using another name, its spatial logic will be corrupted and you will not be able to use the spatial component of the database. The only way to change the name of a database in this case is to create a new geodatabase using the ArcSDE Post Installation wizard (complete option) and move data from one database to another in ArcCatalog. Then you could delete the first database and use the newly created one. Even though some users stated that they succeeded with the sde database renaming, proceed at your own risk.
You can easily check the name of a database while executing a simple SQL query to see that the spatial database name is hard coded in the ArcSDE repository table.
select * from SDE_table_registry
4. After you have restored the database, you will need to use the sp_change_users_login stored procedure in order to map the login to the sde user in the database. You can consult SQL Server Books Online for the info about its usage. In particular, you will have to execute the following queries:
exec sp_change_users_login ‘report’ (to see the users and SIDs not linked to any login yet)
exec sp_change_users_login ‘update_one’, ‘sde’, ‘sde’ (this should be done for each user; here it was done for the sde user)
Now you can connect to the database you have just restored on a production server.
A very good document on ArcSDE configuration and among others spatial databases backup and restore operations, check the ArcSDE configuration and tuning guide for SQL Server (pdf)