This blog covers the basic steps in migrating a SQL Server 2016 database to Autonomous Database 19c via SQL Developer migration tool offline.
What I used:
- SQL Server 2016 running on a Windows server in OCI as the source in online mode
- Autonomous Database on Shared infrastructure as the target in offline mode
- SQL Developer 21.4.3 running on the same server as SQL Server
- SQL Developer documentation
Basic steps all within SQL Developer:
- create connection to the source (SQL Server 2016)
- Setup migration repository, I used a different schema within the target Autonomous Database
- capture source information online
- review convert/translate options for meta info, procedures, triggers, views, ...
- select offline mode for DDL and data
- run the job
- after job is completed run the DDL, extract data from source and load the data to the target
Almost always you will use offline in which SQL Developer creates the scripts which you will use to move a production database. Online is nice for testing.
After creating connections within SQL Developer to the source and target as well as setting up the migration repository select Tools->Migration->Migrate.
|Step 1 -- Overview of migration steps
|Step 2 -- Select migration repository
|Step 3 -- Create project, note that this is where the scripts and
data will be stored so make sure you have enough storage
as well as where to place the files perhaps on a NFS mount
that both could access.
|Step 4: Select Source in online mode
|Step 5: Select Database(s)
|Step 6: Review data type convert options
|Step 7: Select Objects to migrate, default is all
|Step 8: Select offline to move DDL
|Step 9: Select data to be moved offline
|Step 10: Summary and Start job to migrate
After the job is completed successfully you will want to run the scripts to create the DDL and copy the data to the target database.
|In the project directory you will find the DDL and data scripts
|Run the master.sql script on the target which creates the DDL
|These are the scripts to extract and load the data
|command parameters are IP address of source database,
userid and password to connect to source. This command
exports the data from source database to files
command parameters are connect string , user and password
to the target database. This commands loads the data from
the files to the target database.