You have three options when migrating from the multiple database model in SQL Server to the single database model.
- Move all user data into the master sde database.
- Create one single-model geodatabase and move all your data into it.
- Create several single-model geodatabases and move data from each of your user databases to each of the new stand-alone geodatabases.
Move all user data into the existing sde master database
If you don't need to keep data in separate databases but you do need to preserve existing connections, move the existing data into the master sde database.
You might do this if the following is true:
- You are using the multiple-spatial database model because it had been the only option available when you first created your geodatabase, prior to ArcSDE 9.0.
- You need all your data in the same geodatabase so you can join and relate tables.
- You want your users to keep using the same connection information they've always used.
- You have published services from the existing geodatabase.
Follow the steps in the next four sections to move data from the user databases into the master sde database:
Get all edits into base tables and delete versions
If your existing geodatabase is versioned, you must move edits from the delta tables to the base tables and delete all named versions.
- Reconcile and post all versioned edits to the Default version.
- Compress the geodatabase while all other geodatabase users are off the system.
- Delete named versions.
Configure the sde database to allow data owners to create data
-
Create schemas in the sde database for each user who will own data in the user databases.
Schemas must have the same name as the user.
- Grant the users data creator permissions in the sde database.
Move data and grant privileges
Whichever user is logged in to the sde geodatabase when the data is moved will own the data in the sde geodatabase. Therefore, if you want the same users to own the data as before, each user must connect and move his or her own data.
- The data owner must connect to the sde database and the user database from ArcMap.
- Take note of who has access to the data and what privileges they have, as you must regrant privileges after you move the data to the sde database. To see privileges, right-click each feature class or table in the Catalog tree, point to Manage, and click Privileges.
- The data owner can use one of the following options to move the data:
- Once the data has been moved, the data owner must grant privileges on the data to other users.
Delete the user databases
Once all data is moved to the master sde database, you can delete the user databases.
Move all your data to a new single-model geodatabase
If you want to start with a new geodatabase and you don't need to keep data in separate databases, create a single-model geodatabase and move all your data into it.
You might do this if the following is true:
- You are using the multiple-spatial database model because it was the only option available when you first created your geodatabase, prior to ArcSDE 9.0.
- You need all your data in the same geodatabase so you can join and relate tables.
- Users can create new connection files to the new geodatabase.
- You didn't publish services from the old geodatabase.
Follow the steps in the next four sections to create a new single-model geodatabase and move the data into it.
Get all edits into base tables and delete versions
If your existing geodatabase is versioned, you must move edits from the delta tables to the base tables and delete all named versions.
- Reconcile and post all versioned edits to the Default version.
- Compress the geodatabase while all other geodatabase users are off the system.
- Delete named versions.
Create and configure a geodatabase
Create a geodatabase to move the data to, create users, set up user schemas, and grant database permissions.
- Create a geodatabase in SQL Server.
- Add other users and roles to the new database.
- If you used SQL to create the users who will own data, create schemas in the database for those users.
Schemas must have the same name as the user.
- For those users who will own data in the new geodatabase, grant them data creator permissions.
Move data to the new geodatabase and grant privileges
Whichever user is logged in to the new geodatabase when the data is moved will own the data.
- Each data owner must connect to the old geodatabase and the new geodatabase from ArcMap.
- Take note of who has access to the data and what privileges they have, as you must regrant privileges after you move the data to the new geodatabase. To see privileges, right-click each feature class or table in the old geodatabase in the Catalog tree, point to Manage, and click Privileges.
- The data owner has three options for moving data:
- Once the data has been moved, the data owner must grant privileges on the data to other users.
- All other users must create new connections to the new geodatabase, and data in existing ArcMap documents (MXDS and MSDS) must be mapped to the new data source. If these maps were published as services, you must republish the services.
Delete the old geodatabase
Once data has been moved and tested in the new geodatabase, you can delete the old multidatabase model geodatabase.
Move each user database into its own single-model geodatabase
If you had previously used the user databases to group data for different departments or projects, move data from each user database into individual single-model geodatabases.
You might do this if the following is true:
- Your user databases were used to group different types of data and you want to keep this behavior.
- You only need to join and relate tables that can be placed in the same geodatabase.
- Users can create new connection files to the new geodatabases.
- You didn't publish services from the old geodatabase.
Follow the steps in the next four sections to create multiple single-model geodatabases and move data from each user database into a new geodatabase:
Get all edits into base tables and delete versions
If your existing geodatabase is versioned, you must move edits from the delta tables to the base tables and delete all named versions.
- Reconcile and post all versioned edits to the Default version.
- Compress the geodatabase while all other geodatabase users are off the system.
- Delete named versions.
Create geodatabases
Create one geodatabase for each of the user databases you had before, create users, set up user schemas, and grant permissions.
- Create one new geodatabase in SQL Server for each user database you had in the multiple-model geodatabase.
- Add other users and roles to each new geodatabase.
- If you used SQL to create the users who will own data, create schemas in each database for those users.
Schemas must have the same name as the user.
- For those users who will own data in each new geodatabase, grant them data creator permissions.
Move data to each new geodatabase and grant privileges
Whichever user is logged in to each geodatabase when the data is moved will own the data in that geodatabase.
- Each data owner must connect to the old geodatabase and the new geodatabase from ArcMap.
- Take note of who has access to the data and what privileges they have, as you must regrant privileges after you move the data to the new geodatabase. To see privileges, right-click each feature class or table in the old geodatabase in the Catalog tree, point to Manage, and click Privileges.
- The data owner has three options for moving data:
- Once the data has been moved, the data owner must grant privileges on the data to other users.
- All other users must create new connections to the new geodatabase, and data in existing ArcMap documents (MXDS and MSDS) must be mapped to the new data source. If these maps were published as services, you must republish the services.
Delete the old geodatabase
Once data has been moved and tested in the new geodatabases, you can delete the old multidatabase model geodatabase.