Migrating from a multiple database to a single database geodatabase in SQL Server

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 new 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.

Moving all user data into the existing sde master database

If you don't need to keep data in separate databases, you can move the existing data into the master sde database or to a new single-model geodatabase.

You might do this if:

• You are using the multiple-spatial database model simply 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 do joins and relates.
• You want your users to keep using the same connection information they've always used.

Follow these sets of steps to move the data into the sde database:

Move edits from the delta tables to the base tables and delete versions.

Steps:

1. Reconcile and post all versioned edits to the DEFAULT version.
2. Compress the geodatabase while all other geodatabase users are off the system.
3. Delete named versions.

Set up the sde database so data owners can create data in it.

Steps:

1. Create schemas in the sde database for each user who owns data in the user databases.

   Schemas must have the same name as the user.

2. Grant the users permissions to create data in the sde database.

Move data.

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, each user must connect and move his or her own data.

Steps:

1. The data owner must log in to the sde database and the user database from ArcGIS for Desktop.
2. The data owner can use one of the following options to move the data:
   • While connected to the sde geodatabase, import the data from the user database.
   • While connected to the user database, export the data to the sde database.
   • Copy the data from the user database and paste it into the sde database.
3. Once the data has been moved, the data owner must regrant privileges on the data to other users.

Moving all your data to one new single-model geodatabase

If you want to start with a new database and you don't need to keep data in separate databases, you can create a single-model geodatabase and move all your data into it.

You might do this if:

• You are using the multiple-spatial database model simply 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 do joins and relates.
• Users can create new connection files to the new geodatabase.

Follow these sets of steps to create a new single-model geodatabase and move the data into it:

Move edits from the delta tables to the base tables and delete versions.

Steps:

1. Reconcile and post all versioned edits to the DEFAULT version.
2. Compress the geodatabase while all other geodatabase users are off the system.
3. Delete named versions.

Create a geodatabase to move the data to, create users, set up user schemas, and grant database permissions.

Steps:

1. Create a new database in SQL Server. Size it appropriately to hold all your data.
2. If you want to use an sde-schema geodatabase, create an sde user and corresponding schema in the new database.
   Tip:

   If you created the database on a different SQL Server instance, you will first need to add an sde log in to the SQL Server instance.

3. If using an sde-schema geodatabase, grant the sde user CREATE FUNCTION, CREATE PROCEDURE, CREATE TABLE, and CREATE VIEW permissions in the new database.
4. Add other, nonadministrative users and roles to the new database.
5. Create schemas in the database for those users who will own data.

   Schemas must have the same name as the user.

6. Grant the users permissions to create data in the new database.
7. Make a connection from ArcGIS for Desktop to your new database.

   Connect as the sde user if you want the geodatabase stored in the sde user's schema or connect as a user in the sysadmin fixed server role to store the geodatabase in the dbo schema.

8. Use the Enable Enterprise Geodatabase geoprocessing tool or a Python script to create a geodatabase in the new database.

Move data.

Whichever user is logged in to the new geodatabase when the data is moved will own the data.

Steps:

1. Each data owner must have a connection to the old geodatabase and the new geodatabase from ArcGIS for Desktop.
2. The data owner has three options for moving data:
   • Connect to the new geodatabase and import the data from the old one.
   • Connect to the old geodatabase and export the data to the new geodatabase.
   • Connect to the old geodatabase, copy the data, connect to the new geodatabase, and paste the data into it.
3. Once the data has been moved, the data owner must regrant privileges on the data to other users.
4. 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.

Moving 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, you can move data from each user database into individual single-model geodatabases.

You might do this if:

• Your user databases were used to group different types of data and you want to keep this behavior.
• You only need to create joins and relates between datasets that can be placed in the same geodatabase.
• Users can create new connection files to the new geodatabases.

Follow these sets of steps to create multiple single-model geodatabases and move the data from each user database into a new geodatabase:

Move edits from the delta tables to the base tables and delete versions.

Steps:

1. Reconcile and post all versioned edits to the DEFAULT version.
2. Compress the geodatabase while all other geodatabase users are off the system.
3. Delete named versions.

Create one geodatabase for each of the user databases you had before, create users, set up user schemas, and grant permissions.

Steps:

1. Create one new database in SQL Server for each user database you had in the multiple-model geodatabase. Size each database appropriately to hold the data you will move into it.
2. For those geodatabases that you want to store in the sde user's schema, create an sde user and corresponding schema in the database.
   Tip:

   If you created the database on a different SQL Server instance, you will first need to add an sde log in to the SQL Server instance.

3. Grant the sde user CREATE FUNCTION, CREATE PROCEDURE, CREATE TABLE, and CREATE VIEW permissions in each database that will use an sde-schema.
4. Add other, nonadministrative users and roles to each new database.
5. Create schemas in each database for those users who will own data.

   Schemas must have the same name as the user.

6. Grant the users permissions to create data in the appropriate database.
7. Make a connection from ArcGIS for Desktop to each new database.

   Connect as the sde user if you want the geodatabase stored in the sde user's schema, or connect as a user in the sysadmin fixed server role to store the geodatabase in the dbo schema.

8. Use the Enable Enterprise Geodatabase geoprocessing tool or a Python script to create a geodatabase in the new database. Repeat this step for each new database.

Move data.

Whichever user is logged in to a geodatabase when the data is moved will own the data.

Steps:

1. Each data owner must have a connection to the old geodatabase and the new geodatabase from ArcGIS for Desktop.
2. The data owner has three options for moving data:
   • Connect to the new geodatabase and import the data from the old one.
   • Connect to the old geodatabase and export the data to the new geodatabase.
   • Connect to the old geodatabase, copy the data, connect to the new geodatabase, and paste the data into it.
3. Once the data has been moved, the data owner must regrant privileges on the data to other users.
4. All other users must create new connections to the new geodatabases, and data in existing ArcMap documents (MXDS and MSDS) must be mapped to the new data sources.