Database user used for replication has to be granted the following privileges:

• INSERT, UPDATE, DELETE, and TRUNCATE on the target tables.
• CREATE and DROP Synapse external file format.
• CREATE and DROP Synapse external data source.
• CREATE and DROP Synapse external table.

The merge SQL statement for Azure Synapse Analytics was made generally available during the later part of the year 2022 and therefore Oracle GoldenGate for Distributed Applications and Analytics (GG for DAA) uses merge statement by default. To disable merge SQL, ensure that a Java System property is set in the jvm.bootoptions parameter.

jvm.bootoptions=-Dsynapse.use.merge.sql=false

The following are the prerequisites:

• Uncompressed UPDATE records: If Oracle GoldenGate is configured to not use merge statement (see Merge SQL Statement), then it is mandatory that the trail files used to apply to Synapse contain uncompressed UPDATE operation records, which means that the UPDATE operations contain full image of the row being updated. If UPDATE records have missing columns, then replicat will ABEND on detecting a compressed UPDATE trail record.
• If Oracle GoldenGate is configured to use merge statement (see Merge SQL Statement), then the target table must be a hash distributed table.
• Target table existence: The target tables should exist on the Synapse database.
• Azure storage account: An Azure storage account and container should exist.

  Oracle recommends co-locating the Azure Synapse workspace, and the Azure storage account in the same azure region.

• If Oracle GoldenGate is configured to use merge statement, then the target table cannot define IDENTITY columns because Synapse merge statement does not support inserting data into IDENTITY columns. For more information about merging SQL statement, see Merge SQL Statement.

In some use cases, if the trail contains uncompressed update records, then the MERGE SQL statement can be optimized for better performance by setting gg.compressed.update=false.

If you want to use DELETE+INSERT SQL statements instead of a MERGE SQL statement, then set gg.eventhandler.synapse.deleteInsert=true.

Synapse replication involves configuration of multiple components, such as File Writer handler, Parquet Event handler, and Synapse Event handler.

The Automatic Configuration functionality helps to auto configure these components so that the user configuration is minimal.

The properties modified by auto configuration will also be logged in the handler log file.

To enable auto-configuration to replicate to Synapse target we need to set the parameter as follows: gg.target=synapse.

When replicating to Synapse target, customization of Parquet Event handler name and Synapse Event handler name is not allowed.

Synapse Event handler relies on the upstream File Writer handler and the Parquet Event handler.

Stage and merge targets supports INSERTALLRECORDS parameter.

See INSERTALLRECORDS in Reference for Oracle GoldenGate. Set the INSERTALLRECORDS parameter in the Replicat parameter file (.prm). Set the INSERTALLRECORDS parameter in the Replicat parameter file (.prm)

Setting this property directs the Replicat process to use bulk insert operations to load operation data into the target table. You can tune the batch size of bulk inserts using the File Writer property gg.handler.synapse.maxFileSize. The default value is set to 1GB. The frequency of bulk inserts can be tuned using the File Writer property gg.handler.synapse.fileRollInterval, the default value is set to 3m (three minutes).

If the compute machine has sufficient RAM, you can increase this parameter to speed up processing.

The following is an end-end configuration example which uses auto-configuration for FW handler, Parquet and Synapse Event handlers.

This sample properties file can also be found in the directory AdapterExamples/big-data/synapse/synapse.props:

# Azure Synapse Analytics Data Warehouse Template
# Configuration to load GoldenGate trail operation records into Azure Synapse Analytics by chaining
# File writer handler -> Parquet Event handler -> Synapse Event handler.
# Note: Recommended to only edit the configuration marked as  TODO

gg.target=synapse

#The Parquet Event Handler
No properties are required for the Parquet Event handler.
#gg.eventhandler.parquet.finalizeAction=delete

ADLS Gen 2 stage (Using Azure Blob SDK). 
#Azure Blob Event handler 
#TODO: Edit the Azure Blob Storage container name
gg.eventhandler.abs.bucketMappingTemplate=<abs-container-name> 
#TODO: Edit the Azure storage.account name. gg.eventhandler.abs.accountName=<storage-account-name> 
#TODO: Edit the Azure storage account key.
gg.eventhandler.abs.accountKey=<storage-account-key>

#The Synapse Event Handler
#TODO: Edit JDBC ConnectionUrl
gg.eventhandler.synapse.connectionURL=jdbc:sqlserver://<synapse-workspace>.sql.azuresynapse.net:1433;database=<db-name>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=300;
#TODO: Edit JDBC user name
gg.eventhandler.synapse.UserName=<db user name>@<synapse-workspace>
#TODO: Edit JDBC password
gg.eventhandler.synapse.Password=<db password>
#TODO: Edit Credential to access Azure storage.
gg.eventhandler.synapse.credential=OGGBD_ADLS_credential
#TODO: Edit the classpath to include dependencies for Parquet Event Handler, ABS Event handler and the Synapse JDBC driver.
gg.classpath=/path/to/parquet-deps/:/path/to/abs-deps/:path/to/synapse-deps/mssql-jdbc-8.4.1.jre8.jar:/path/to/hadoop-deps/*
#TODO: Provide sufficient memory (at least 8GB).
jvm.bootoptions=-Xmx8g -Xms8g -DSYNAPSE_STAGE=parquet,abs