Data Migration and upgrading geodatabase with SQL Server

[VickyWang]

The workflow for upgrading geodatabase and migrating data using SQL Server backup file.  

The requirement is for Geodatabase 10.3 upgrades to Geodatabase 10.9.x

1. Source database cut off (no more editing on the production geodatabase)
2. All GIS users checked in their version data to the default base version.
3. Do a Fully compress a geodatabase which has enable the versioning.
   The compress command to reconcile and post all outstanding changes in the child version to the DEFAULT version (eg. I*, a*, d* dataset)
4. DBA to create the Geodatabase full Backup file (*.bak)
5. Create the relevant SQL user accounts in the new SQL Server 2019 if not exists
6. Restore the backup file to New SQL Server 2019 with its identical name
7. Pre-process for Geodatabase Upgrade  
• Run the premigration SQL script to enable Allow_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT options to be ON (the options are required after ArcGIS geodatabase 10.6+. )

Spoiler

ALTER DATABASE Geodatabase_name
SET READ_COMMITTED_SNAPSHOT ON
GO

ALTER DATABASE Geodatabase_name
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

• Run SQL script to link the user account with current SQL server 2019 (ensure the source geodatabase’s user account is created in step 5. This step to link the restored database with current SQL server instance. )

Spoiler

EXEC sp_change_users_login 'Update_One', 'sde', 'sde'

go

EXEC sp_change_users_login 'Update_One', 'GeoDatabaseCreator', 'GeoDatabaseCreator'

go

Execute geodatabase upgrade using ArcGIS Pro.

• Create database connection file to destination geodatabase with the sysadmin right (not geodatabase admin user)
• Right-click on the database connection file
• Expand the Connections section
• Expand the Upgrade Status
• Click Run Upgrade
• Checked options for Perform Pre-Requisite Check and Upgrade Geodatabase

Review the dataset in the new geodatabase.

The above workflow for geospatial data types includes the feature class, tables, dataset, and relationship class/tables. If there is the requirement to upgrade raster_storage or geometry_storage for SDEBINARY will need to add extra steps in the pre-process for Geodatabase upgrade.