Replicate data from Azure SQL Managed Instance to Autonomous AI Transaction Processing

Learn to replicate data from Azure SQL Managed Instance to Autonomous AI Transaction Processing using OCI GoldenGate.

Environment set up: Azure SQL Managed Instance

To successfully complete this quickstart, 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 = '<password>'
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.

Environment set up: Autonomous AI Transaction Processing

Download and unzip the sample database schema.
Unlock the GGADMIN user.
1. In the Oracle Cloud console, select your ATP instance from the Autonomous AI Databases page to view its details and access Database Actions.
2. Select Database Actions, and then select Database Users.
3. Locate GGADMIN, and then select its ellipsis menu (three dots) and select Edit.
4. In the Edit User panel, enter the GGADMIN password, confirm the password, and then deselect Account is Locked.
5. Select Apply Changes.
Load the target sample schema and data.
1. From the Database Actions menu, select SQL.
2. Copy and paste the script from OCIGGLL_OCIGGS_SETUP_USERS_ADW.sql into the SQL worksheet.
3. Select 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 Autonomous AI Transaction Processing.
Create an Azure SQL 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 SQL 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 SQL Managed Instance connection to the SQL Server deployment.
Create a GoldenGate connection and then assign it to the SQL Server deployment.
Create an Autonomous AI 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, select 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, select DB Connections, then the source database, and then TRANDATA.
2. On the Trandata page, select Add TRANDATA (plus icon).
3. In the Add Trandata panel, for Schema Name, enter SRC_OCIGGLL, and then select 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 select 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. Select on the Add Purge Change Data (add icon).
2. Enter a name.
3. Select Submit.
Add the Initial Load Extract.
1. On the Extracts page, select 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. Select Next.
  - On the Paramter File page, replace MAP *.*, TARGET *.*; with the following:
```
TABLE SRC_OCIGGLL.*;
```
2. Select 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 Change Data Capture Extract.
1. In the navigation menu, select Extracts.
2. On the Extracts page, select 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. Select Next.
  - On the Extract Options page:
    1. For Source credentials, select the Domain and Alias of your source database.
    2. For Extract Trail Name, enter a two-character name, such as C1.
    3. Select Next.
  - On the Parameter File page, replace MAP *.*, TARGET *.*; with the following:
```
TABLE SRC_OCIGGLL.*;
```
3. Select 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 select DB Connections.
Select Connect to the database.
Add a Checkpoint table:
1. In the navigation menu, select Checkpoint.
2. In the Checkpoint page, select Add Checkpoint (plus icon).
3. For Checkpoint Table, enter SRCMIRROR_OCIGGLL.CHECKTABLE.
4. Select Submit.
  
  The new Checkpoint table appears in the list.
Add a Replicat for Initial Load Extract:
1. Select Administrator Service, and then Replicats.
2. On the Replicats page, select Add Replicat (plus icon), and then complete the form as follows:
  1. On the Replicat Information, complete the following form fields , and then select 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 select Next:
    1. For Name, enter the name of the Trail from Task 2 (I1).
    2. For Domain, select the domain for the Autonomous AI Database connection.
    3. For Alias, select the alias of the Autonomous AI 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 select 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. Select 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, select Add Replicat (plus icon), and then complete the form as follows:
  1. On the Replicat Information, complete the following form fields , and then select 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 select Next:
    1. For Trail Name, enter the name of the Trail from Task 2 (C1).
    2. For Domain, select the domain for the Autonomous AI Database connection.
    3. For Alias, select the alias of the Autonomous AI 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. Select Create. Do not run the Replicat.
3. On Replicats page, select the Replicat for Initial Load (RIL) and view its Details.
4. Select 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, select Performance Metrics Service, select the Extract, and then select 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.