Precise Instantiation for SQL Server

Starting with Oracle GoldenGate 23ai (23.8), precise instantiation can be orchestrated using the initial load Extract for SQL Server. 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 within the initial load Extract. This method can be used to instantiate any supported target database from any supported SQL Server source database.

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 that 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. See Purge CDC Staging Data and Purge Process to know more.

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

The source database option to allow snapshot isolation can now be set back to OFF, if it was manually enabled per step 2.
```
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 that reads from the trail created by the initial load Extract.

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, create and start a CDC Replicat that reads from the trail created by the CDC Extract.
```
ADD REPLICAT repcd, PARALLEL, EXTTRAIL ea, CHECKPOINTTABLE ggadmin.ggcheck
```
```
START EXTRACT extcdc
```
```
START REPLICAT repcd
```