Database Developer - SQL Server/T-SQL/Database Migration

Database developers frequently handle tasks involving relational database servers and database migrations. A key skill is utilizing tools to move data between different database management systems. This course focuses on migrating a third-party database from Microsoft SQL Server to Oracle using Oracle SQL Developer, a powerful and free tool.

Here's a breakdown of the database migration process we'll cover:

• Setting up a Migration Repository: A Migration Repository is a crucial database schema. It acts as a central location to store metadata collected and transformed during the migration. Understanding how to set this up is the first step towards a successful database migration.

• Configuring the Third-Party Database Connection: Establishing a connection to your source database is essential. This involves two key steps:

  • Downloading the JDBC Driver: The JDBC driver enables communication between Oracle SQL Developer and your third-party database.
  • Connecting to the Third-Party Database: Using the downloaded driver, you'll establish a connection to the database you intend to migrate.

• Capturing the Source Database: This step creates a snapshot of your existing database's current state. This "point-in-time" view is vital, allowing SQL Developer to work with metadata rather than directly querying your live database.

• Converting the Captured Model to Oracle: The captured database model needs to be transformed into a format compatible with Oracle. This conversion prepares the data for successful migration.

• Translating Third-Party SQL Objects to Oracle: This involves translating T-SQL objects (like constraints, functions, procedures, triggers, and views) into their equivalent Oracle SQL objects. Understanding these translations is critical for maintaining database functionality.

• Generating Scripts to Create the Target Database: Once the conversion is complete, SQL Developer generates SQL scripts. These scripts define the schema (structure) of the new Oracle database and can be executed to create the database.

• Moving Data from Third-Party Database to Oracle: The final step involves copying the data from the source database to the newly created Oracle tables. The Migration Wizard utilizes the same database connection used for script execution to efficiently move the data.

This course provides a practical, step-by-step guide to database migration, empowering you with the skills to seamlessly move your data between different database platforms.