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:

Create an Azure SQL Managed Instance resource.
Configure Azure SQL Managed Instance to allow public connections.
Connect to Azure SQL Managed Instance using a SQL client, and then complete the following steps:
1. 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. Create a database:
```
Create database SRC_OCIGGLL
```
3. Disconnect and reconnect as the newly created user (GGADMIN) and database (SRC_OCIGGLL).
4. Enable Change Data Capture (CDC) at the database level:
```
EXECUTE sys.sp_cdc_enable_db
```
5. Create the schema:
```
Create schema GGADMIN
```
6. Run the SQL script.
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. Unlock 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.

Create deployments for the source SQL Server and target Oracle database.
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.
Assign the Azure Manage Instance connection to the SQL Server deployment.
Create a GoldenGate connection and then assign it to the SQL Server deployment.
Create an Autonomous Transaction Processing (ATP) connection.
Assign the ATP connection to the Oracle deployment.

Task 2: Create the Extracts

Create the Initial Load and Change Data Capture Extracts.

On the Deployments page, select the SQL Server deployment created in Task 1.
On the deployment Details page, click Launch Console.
Sign in to the source SQL Server deployment console using the Administrator credentials specified when you created the deployment in Task 1.
Add transaction information
1. In the navigation menu, click DB Connections, then the source database, and then TRANDATA.
2. On the Trandata page, click Add TRANDATA (plus icon).
3. In the Add Trandata panel, for Schema Name, enter SRC_OCIGGLL, and then click Submit.
  
  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.
Next, in the navigation menu, select Tasks, then Purge Change Data and complete the following:
1. Click on the Add Purge Change Data (add icon).
2. Enter a name.
3. Click Submit.
Add the Initial Load Extract.
1. On the Extracts page, click Add Extract (plus icon), and then complete the form as follows:
  - On the Extract Information page:
    1. For Extract type, select Initial Load.
    2. For Process Name, enter EIL.
    3. Click Next.
  - On the Paramter File page, replace MAP *.*, TARGET *.*; with the following:
```
TABLE SRC_OCIGGLL.*;
```
2. Click Create and Run.
You return to the Extracts page, where it may take a moment for the Extract to start. You can then select the Extract to review its details and review the Report file. It lists all the tables and the number of exported records for each.
Add the Integrated Extract.
1. In the navigation menu, click Extracts.
2. On the Extracts page, click Add Extract (plus icon), and then complete the form as follows:
  - On the Extract Information page:
    1. For Extract type, select Change Data Capture.
    2. For Process Name, enter ECDC.
    3. Click Next.
  - On the Extract Options page:
    1. For Source credentials, select the Doman and Alias of your source database.
    2. For Extract Trail Name, enter a two-character name, such as C1.
    3. Click Next.
  - On the Paramter File page, replace MAP *.*, TARGET *.*; with the following:
```
TABLE SRC_OCIGGLL.*;
```
3. Click Create and Run.

Task 3: Create the Distribution Paths

If using GoldenGate credential store, create a user for the Distribution Path in the target Oracle deployment, and a Path Connection in the source SQL Server deployment. If using IAM credential store, skip this step.
In the source Azure SQL Managed Instance GoldenGate deployment console, Create Distribution Path for Initial Load Extract. In our example, we name our Initial Load Extract, EIL.
Create Distribution Path for Change Data Capture Extract. In our example, we name our Change Data Capture Extract, ECDC.

Task 4: Create the Replicats

Launch and log in to the Oracle deployment console created in task 1.
Open the navigation menu and then click DB Connections.
Click Connect to the database.
Add a Checkpoint table:
1. In the navigation menu, click Checkpoint.
2. In the Checkpoint page, click Add Checkpoint (plus icon).
3. For Checkpoint Table, enter SRCMIRROR_OCIGGLL.CHECKTABLE.
4. Click Submit.
  The new Checkpoint table appears in the list.
Add a Replicat for Initial Load Extract:
1. Click Administrator Service, and then Replicats.
2. On the Replicats page, click Add Replicat (plus icon), and then complete the form as follows:
  1. On the Replicat Information, complete the following form fields , and then click Next:
    1. For Replicat type, select Nonintegrated Replicat.
    2. For Process Name, enter a name, such as RIL.
  2. On the Replicat Options page, complete the following form fields, and then click Next:
    1. For Name, enter the name of the Trail from Task 2 (I1).
    2. For Domain, select the domain for the Autonomous Database connection.
    3. For Alias, select the alias of the Autonomous Database connection.
    4. For Checkpoint table, select the Checkpoint table you created in Step 4.
  3. On the Parameters page, add the following mapping, and then click Create and run:
```
MAP SRC_OCIGGLL.*, TARGET SRCMIRROR_OCIGGLL.*;
```
  You return to the Overview page, where you can review the Replicat details.
3. Select the Replicat (RIL) and view its Details.
4. 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.
Add a Replicat for Change Data Capture :
1. On the Replicats page, click Add Replicat (plus icon), and then complete the form as follows:
  1. On the Replicat Information, complete the following form fields , and then click Next:
    1. For Replicat type, select Nonintegrated Replicat.
    2. For Process Name, enter a name, such as RCDC.
  2. On the Replicat Options page, complete the following form fields, and then click Next:
    1. For Trail Name, enter the name of the Trail from Task 2 (C1).
    2. For Domain, select the domain for the Autonomous Database connection.
    3. For Alias, select the alias of the Autonomous Database connection.
    4. For Checkpoint table, select the Checkpoint table you created in Step 4.
  3. On the Parameters page, add the following mapping:
```
MAP SRC_OCIGGLL.*, TARGET SRCMIRROR_OCIGGLL.*;
```
2. Click Create. Do not run the Replicat.
3. On Replicats page, select the Replicat for Initial Load (RIL) and view its Details.
4. 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.
5. Return to the Administration Service Replicats page and then start the RCDC Replicat.
6. After starting RCDC, review its Details and Statistics to view the number of Inserts.

Title and Copyright Information

Oracle and/or its affiliates.