Replicate data from Azure SQL Managed Instance to Autonomous Transaction Processing

Learn to replicate data from Azure SQL Managed Instance to an Autonomous Database using OCI GoldenGate.

Before you begin

To successfully complete this quicktart, you must have the following:
  1. Create an Azure SQL Managed Instance resource.
  2. Configure Azure SQL Managed Instance to allow public connections.
  3. Connect to Azure SQL Managed Instance using a SQL client, and then complete the following steps:
    1. Enter the following commands to create a GGADMIN user:
      CREATE login GGADMIN with password = 'W3lcome@1234'
Create user GGADMIN for login GGADMIN
ALTER SERVER ROLE sysadmin ADD MEMBER GGADMIN

      Note:

      The sysadmin role is only required to add trandata and heartbeat tables. You can then remove the sysadmin privileges and instead use db_owner: ALTER ROLE db_owner ADD MEMBER GGADMIN;
    2. Enter the following command to create a database:
      Create database SRC_OCIGGLL
    3. Disconnect and reconnect as the newly created user (GGADMIN) and database (SRC_OCIGGLL).
    4. To enable Change Data Capture (CDC) at the database level: 
      EXECUTE sys.sp_cdc_enable_db
    5. Enter the following command to create the schema:
      Create schema GGADMIN
    6. Run the SQL script.
  4. Set up Autonomous Transaction Processing:
    1. Download and unzip the sample database schema.
    2. In the Oracle Cloud console, select your ATP instance from the Autonomous Databases page to view its details.
    3. Click Database Actions.
    4. Enable the GGADMIN user:
      1. Under Administration, click Database Users.
      2. Locate GGADMIN, and then click its ellipsis menu (three dots) and select Edit.
      3. In the Edit User panel, enter the GGADMIN password, confirm the password, and then deselect Account is Locked.
      4. Click Apply Changes.
    5. Load the target sample schema and data:
      1. From the Database Actions menu, under Development, select SQL.
      2. Copy and paste the script from OCIGGLL_OCIGGS_SETUP_USERS_ADW.sql into the SQL worksheet.
      3. Click Run Script. The Script Output tab displays confirmation messages.
      4. Clear the SQL worksheet and then copy and paste the SQL from OCIGGLL_OCIGGS_SRC_MIRROR_USER_SEED_DATA.sql.
      5. To verify that the tables were created successfully, close the SQL window and reopen it again. In the Navigator tab, look for the SRC_OCIGGLL schema and then select tables from their respective dropdowns.

Task 1: Create the OCI GoldenGate Resources

This quickstart example requires deployments and connections for both the source and target.
  1. Create deployments for the source SQL Server and target Oracle database.
  2. Create an Azure Managed Instance connection for Azure SQL database, using following values:
    1. For Database, enter SRC_OCIGGLL.
    2. For Port, enter 3342 for public endpoints.
    3. For Host, use the server name from Azure Managed Instance details page in Azure console (for example,xyz.database.windows.net).
    4. For User, enter GGADMIN.
    5. For Password, enter the GGADMIN user password.
    6. For Security protocol, select Plain from the dropdown.
  3. Assign the Azure Manage Instance connection to the SQL Server deployment.
  4. Create a GoldenGate connection and then assign it to the SQL Server deployment.
  5. Create an Autonomous Transaction Processing (ATP) connection.
  6. Assign the ATP connection to the Oracle deployment.

Task 2: Create the Extracts

  1. On the Deployments page, select the SQL Server deployment created in Task 1.
  2. On the deployment details page, click Launch Console.
  3. Sign in to the source SQL Server deployment console using the Administrator credentials specified when you created the deployment in Task 1.
  4. Open the navigation menu, and then select Configuration.
  5. On the Database page, in the Credentials table, under Action, click Connect to database Azure SQL. Checkpoint, TRANDATA Information, and Heartbeat options appear.
  6. Next to TRANDATA Information click Add TRANDATA, and complete the following:
    1. For Schema Name, enter SRC_OCIGGLL.
    2. Click Submit. The deployment console processes your request, but does not refresh the page.

      Tip:

      The screen will not refresh on submit. To confirm the TRANDATA was added, enter the SRC_OCIGGLL into the search field and then click Search. SRC_OCIGGLL is returned and 6 tables are prepared for instantiation.
  7. Next, select Tasks, select Purge Change Data and complete the following:
    1. Click on the Add Purge Change Data (add icon).
    2. Enter a name.
    3. Click Submit.
  8. Next, click Overview in the left navigation.
  9. On the Overview page, click Add Extract (plus icon).
  10. On the Add Extract page, complete the following:
    1. Select Integrated Load, and then click Next.
    2. For Process Name, enter EINISQL.
    3. For Credential Alias, select a credential from the dropdown menu.
    4. For Trail Name, enter I1.
    5. Click Next.
    6. On the Parameter File page, in the text area, add a new line to the existing text and add the following: TABLE SRC_OCIGGGLL.*
    7. Click Create and Run. You return to the Overview page.
    It may takes a few minutes for the extract to be created. The yellow exclamation point icon changes to a green checkmark.
  11. Select the Extract to view its details and review the Report file. It lists all the tables and the number of exported records for each one of them.
  12. Click Overview.
  13. On the Overview page, click Add Extract (plus icon).
  14. On the Add Extract page, select Change Data Capture, and then click Next.
  15. On the Extract Options page, complete the following:
    1. For Process Name, enter ECDCSQL.
    2. For Credential Alias, select a credential from the dropdown menu.
    3. For Trail Name, enter M1.
    4. Click Next.
  16. On the Parameter File page, in the text area, add a new line to the existing text and add the following: TABLE SRC_OCIGGGLL.*
  17. Click Create and Run. You return to the Overview page.
    It may takes a few minutes for the extract to be created. The yellow exclamation point icon changes to a green checkmark.

Task 3: Create the Distribution Paths

  1. Create distribution path for Initial Load Extract. In our example, we name our Initial Load Extract, DPINISQL.
  2. Create distribution path for CDC Extract. In our example, we name our CDC Extract, DPCDCSQL.

Task 4: Create the Replicats

  1. Launch and log in to the Oracle deployment console created in task 1.
  2. Open the navigation menu and then click Configuration.
  3. On the Database page, in the Credentials table, under Action, click Connect to Autonomous Transaction Processing. Checkpoint, TRANDATA Information, and Heartbeat options appear.
  4. Next to TRANDATA Information click Add TRANDATA, and complete the following:
    1. For Schema Name, enter SRCMIRROR_OCIGGLL.CHECKTABLE.
    2. Click Submit. The deployment console processes your request, but does not refresh the page.

      Note:

      To verify, click Search TRANDATA, and then enter SRCMIRROR_OCIGGLL.CHECKTABLE into the Search field and click Search.

      SRCMIRROR_OCIGGLL.CHECKTABLE is returned and 6 tables are prepared for instantiation.

  5. Add a checkpoint table for SRCMIRROR_OCIGGLL.CHECKTABLE.
  6. To add a Replicat for Initial Load, complete the following:
    1. Click Administrator Service, and then click Add Replicat (plus icon).
    2. On the Add Replicat page, under Replicat type, select Nonintegrated Replicat, and then click Next.
    3. On the Replicat Options page, complete the following form fields, and then click Next:
      • For Process Name, enter a name, such as RIL.
      • For Credential Domain, select the domain for the Autonomous Database connection.
      • For Credential Alias, select the alias of the Autonomous Database connection.
      • For Trail Name, enter the name of the Trail from Task 2 (I1).
      • For Checkpoint Table, select the Checkpoint table you created in Step 1.
    4. On the Replicat Parameters page, add the following mapping, and then click Next:
      MAP SRC_OCIGGLL.*, TARGET SRCMIRROR_OCIGGLL.*;
    5. Click Create and Run. You're returned to the Overview page, where you can review the Replicat details.
    6. Select the Initial Load Replicat (RIL) and view its Details.
    7. Click Statistics and review the number of Inserts. Refresh the page.
      • If the number of Inserts doesn't change, then all the records from the Initial Load have been loaded and you can stop the Replicat (RIL).
      • If the number of Inserts continues to increase, then keep refreshing the page until the Initial Load records are all loaded before continuing.
  7. To add a Replicat for Change Data Capture, complete the following:
    1. Click Administrator Service, and then click Add Replicat (plus icon).
    2. On the Add Replicat page, under Replicat type, select Nonintegrated Replicat, and then click Next.
    3. On the Replicat Options page, complete the following form fields, and then click Next:
      • For Process Name, enter a name, such as RCDC.
      • For Credential Domain, select the domain for the Autonomous Database connection.
      • For Credential Alias, select the alias of the Autonomous Database connection.
      • For Trail Name, enter the name of the Trail from Task 2 (M1).
      • For Checkpoint Table, select the Checkpoint table you created in Step 1.
    4. On the Replicat Parameters page, add the following mapping, and then click Next:
      MAP SRC_OCIGGLL.*, TARGET SRCMIRROR_OCIGGLL.*;
    5. Click Create. Do not run the Replicat.
    6. On the Administration Service Overview page, select the Replicat for Initial Load (RIL) and view its Details.
    7. Click Statistics and review the number of Inserts. Refresh the page.
      • If the number of Inserts doesn't change, then all the records from the Initial Load have been loaded and you can stop the Replicat (RIL).
      • If the number of Inserts continues to increase, then keep refreshing the page until the Initial Load records are all loaded before continuing.

        Note:

        If you don't see any Inserts, click Performance Metrics Service, select the Extract, and then click Database Statistics.
    8. Return to the Administration Service Overview page and then start the Replicat for Change Data Capture (RCDC).
    9. After starting the Replicat for Change Data Capture, review its Details and Statistics to view the number of Inserts.