Precise Instantiation for SQL Server

Starting with Oracle GoldenGate 23ai (23.8), precise instantiation method using initial load Extract for SQL Server is available. The precise instantiation method allows for the initial load of data from a source database to a target database while the source database remains online for application updates. This method ensures precise positioning of the change data capture and delivery processes without having duplicate data in the target database, and without the need to use HANDLECOLLISIONS in the Replicat.

Precise instantiation for Oracle GoldenGate for SQL Server requires setting the source SQL Server database to allow snapshot isolation and uses the INITIALLOADOPTIONS USESNAPSHOT parameter with the initial load Extract. This method can be used to instantiate any supported Oracle and Non-Oracle target databases from supported SQL Server source databases.

The following is a sample precise instantiation method using a schema dump from the source instance, then loaded to the target instance, followed by configuration of Oracle GoldenGate for change data replication:

ADD TRANDATA to the source database tables.

DBLOGIN USERIDALIAS src_alias

ADD TRANDATA dbo.*

After TRANDATA completes, verify if the database is enabled for snapshot isolation and if not, manually enable it.
```
ALTER DATABASE tpcc_source SET ALLOW_SNAPSHOT_ISOLATION ON;
```
For Microservices Architecture, add a Purge CDC Data task in the WebUI.

Create an Initial Load Extract, using the

INITIALLOADOPTIONS
                        USESNAPSHOT

parameter.

ADD EXTRACT einit, SOURCEISTABLE

--Sample Initial Load Extract Parameter File:
	EXTRACT einit
	USERIDALIAS tpcc_source2017 
	INITIALLOADOPTIONS USESNAPSHOT
	EXTFILE ei, MEGABYTES 500, PURGE
	TABLE dbo.*;

Start the Initial Load Extract.
```
START EXTRACT einit
```

When the Initial Load Extract completes and stops, view the report file, looking for a similar message as below, which will be used when creating the CDC Extract.

VIEW REPORT einit

INFO    OGG-05381  A consistent point is established in database 'tpcc_source' at LSN 0x00000488:00002a98:0002.  
	OGG-05379  Create or position a Change Data Capture Extract to LSN 0x00000488:00002a98:0002. Example: ADD EXTRACT <extract-name> TRANLOG LSN 
	0x00000488:00002a98:0002 or ALTER EXTRACT <extract-name> LSN 0x00000488:00002a98:0002

Set the source database snapshot isolation to OFF if it was not enabled prior to setting up for GoldenGate Precise Instantiation.
```
ALTER DATABASE tpcc_source SET ALLOW_SNAPSHOT_ISOLATION OFF;
```
Create a CDC Extract using the LSN output from the Initial Load Extract's report file.
```
ADD EXTRACT extcdc TRANLOG LSN 0x00000488:00002a98:0002
```
```
ADD EXTTRAIL ea, EXTRACT extcdc
```

Create and start an Initial Load Replicat.

ADD REPLICAT rinit, PARALLEL, EXTTRAIL ei, CHECKPOINTTABLE ggadmin.ggcheck

START rinit

--Sample Initial Load Replicat Parameter file
	REPLICAT rinit
	USERIDALIAS tgt_alias
	BATCHSQL
	SPLIT_TRANS_RECS 100000
	MAP dbo.*, TARGET dbo.*;

When the initial load Replicat ends processing, start the CDC Extract and create and start a CDC Replicat.
```
ADD REPLICAT repcd, COORDINATED, EXTTRAIL ea, CHECKPOINTTABLE ggadmin.ggcheck
```
```
START EXTRACT extcdc
```
```
START REPLICAT repcd
```