Tuesday, May 31, 2022

Migrating SQL Server to Oracle Cloud database offline

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.

No comments:

Post a Comment