21 Preparing the System for Oracle GoldenGate

This chapter contains steps to take so that the database with which Oracle GoldenGate interacts is correctly configured to support Oracle GoldenGate capture and delivery. Some steps apply only to a source system, some only to a target, and some to both.

Topics:

• Prepare Database Users and Privileges
  
• Configuring a Database Connection
  
• Preparing Tables for Processing
  
• Globalization Support
  

Prepare Database Users and Privileges

Learn about required database users, privileges, and permissions for Oracle GoldenGate for SQL Server, including supported SQL Server cloud databases.

• Oracle GoldenGate for SQL Server
  
• Amazon RDS User Permissions and Requirements
  
• Azure SQL Database
  

Oracle GoldenGate for SQL Server

Oracle GoldenGate processes require a database user in order to capture from and apply data to a SQL Server database and it is recommended to create a dedicated database user to be used exclusively by Oracle GoldenGate processes.

Oracle GoldenGate for SQL Server supports SQL Server authentication for all of its certified platforms and Windows authentication for Classic Architecture only when Oracle GoldenGate is installed on a Windows server.

• To use Windows authentication for Oracle GoldenGate Classic Architecture, the Extract and Replicat processes inherit the login credentials of the Manager process. By default, the Manager process runs interactively as the user logged on to the Windows server or optionally can be added as a Windows Service with a default service name of GGSMGR. Whichever method that the Manager process is using, the user that it is running as needs to have the required SQL Server privileges listed above.

• To use SQL Server authentication, create a dedicated SQL Server login for Extract and Replicat and assign the privileges listed below.

SQL Server and Azure SQL Managed Instance

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to capture from and apply to a SQL Server or Azure SQL Managed Instance database.

1. Create a dedicated login for Oracle GoldenGate for SQL Server or Azure SQL Managed Instance.

2. Add the login as a user to the msdb database and to the source or target database.

3. Create a schema in the source or target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.

4. Enable the following privileges and permissions for the Oracle GoldenGate user based on whether the user is for an Extract, or for a Replicat.

Table 21-1 Privileges and Permissions for Oracle GoldenGate User

Privilege	Extract	Replicat	Syntax
msdb Database Roles and Privileges
SQLAgentReaderRole	Yes	No	ALTER ROLE SQLAgentReaderRole ADD MEMBER gguser;
SQLAgentUserRole	Inherited	Yes	ALTER ROLE SQLAgentUserRole ADD MEMBER gguser;
SELECT ON sysjobactivity	Yes	No	GRANT SELECT ON msdb.dbo.sysjobactivity TO gguser;
SELECT ON sysjobs	Yes	No	GRANT SELECT ON msdb.dbo.sysjobs TO gguser;
User Database Roles and Privileges
SYSADMIN	Yes	No	Required for a one time change to enable database level Change Data Capture (CDC) if not already enabled and can be revoked once TRANDATA has been enabled. ALTER SERVER ROLE sysadmin ADD MEMBER gguser; Database Administrators with sysadmin credentials can manually enable the database for CDC using the following, which would negate the need for the Extract user to have this privilege: EXEC msdb.sys.sp_cdc_enable_db ‘source_database’
DBOWNER	Yes	Yes	ALTER ROLE db_owner ADD MEMBER gguser;

Amazon RDS User Permissions and Requirements

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to capture from and apply to an Amazon RDS for SQL Server database:

1. Create a dedicated login for Oracle GoldenGate for Amazon RDS for SQL Server.

2. Add the login as a user to the msdb database and to the source or target database.

3. Create a schema in the source or target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.

4. Enable the following privileges and permissions for the Oracle GoldenGate user based on whether the user is for an Extract, or for a Replicat.

Table 21-2 Privileges and Permissions for Oracle GoldenGate User

Privilege	Extract	Replicat	Syntax
msdb Database Roles and Privileges
EXECUTE ON rds_cdc_enable_db	Yes	No	GRANT EXECUTE ON msdb.dbo.rds_cdc_enable_db TO gguser; Database administrators with master credentials can manually enable the database for Change Data Capture using the following command, which would negate the need for the Extract user to have this permission: EXEC msdb.dbo.rds_cdc_enable_db ‘source_database’
SQLAgentOperatorRole	Yes	No	ALTER ROLE SQLAgentOperatorRole ADD MEMBER gguser;
SQLAgentUserRole	Inherited	Yes	ALTER ROLE SQLAgentUserRole ADD MEMBER gguser;
SELECT ON sysjobactivity	Yes	No	GRANT SELECT ON msdb.dbo.sysjobactivity TO gguser;
SELECT ON sysjobs	Yes	No	GRANT SELECT ON msdb.dbo.sysjobs TO gguser;
User Database Roles and Privileges
DBOWNER	Yes	Yes	ALTER ROLE db_owner ADD MEMBER gguser;

Azure SQL Database

The following user requirements and minimum database privileges and permissions are required for Oracle GoldenGate to apply to an Azure SQL Database:

1. Create a dedicated login for Oracle GoldenGate for Azure SQL Database.

2. Add the login as a user to the target database.

3. Create a schema in the target database, to be used for objects required for Oracle GoldenGate. This schema should map to the GGSCHEMA value used in the GLOBALS parameter file.

4. Enable the following privileges and permissions for the Oracle GoldenGate user.

Table 21-3 Privileges and Permissions for Oracle GoldenGate User

Privilege	Extract	Replicat	Syntax
User Database Roles and Privileges
DBOWNER	NA	Yes	ALTER ROLE db_owner ADD MEMBER gguser;

Configuring a Database Connection

This section contains instructions for setting up the Extract and Replicat connections to a SQL Server database.

• Configuring an Extract Database Connection
  
• Configuring a Replicat Database Connection
  
• Configuring a Database Connection on Linux
  
• Configuring a Database Connection on Windows
  

Configuring an Extract Database Connection

Extract connects to a source SQL Server database through an ODBC (Open Database Connectivity) connection. To create this connection, set up a data source name (DSN) through the Data Sources (ODBC) control panel. See Configuring a Database Connection on Windows for instructions.

Connecting to the Listener in an Always On Environment

Extract can connect to the Listener of an Always On environment and can be configured to route its read-only queries to an available readable, synchronous mode secondary replica. By default, if Extract connects to a Listener, all processing will be done against the primary replica, but if the Extract is configured with the TRANLOGOPTIONS ALWAYSONREADONLYROUTING parameter, its read-only queries are routed by the Listener to an available readable secondary replica. See TRANLOGOPTIONS and Requirements Summary for Capture and Delivery of Databases in an AlwaysOn Availability Group for more information.

Configuring a Replicat Database Connection

Replicat can connect to the target database to perform DML operations in the following ways:

• Through ODBC.

• Through OLE DB if the SQL Server driver supports it.

• Through OLE DB as the SQL Server replication user. NOT FOR REPLICATION must be set on IDENTITY columns, foreign key constraints, and triggers.

Before you select a method to use, review the following guidelines and procedures to evaluate the advantages and disadvantages of each.

• Using ODBC or Default OLE DB
  
• Using OLE DB with USEREPLICATIONUSER
  

Using ODBC or Default OLE DB

If Replicat connects through ODBC or through the default OLE DB connection, the following limitations apply:

• To keep IDENTITY columns identical on source and target when using ODBC or default OLE DB, Replicat creates special operations in its transaction to ensure that the seeds are incremented on the target. These steps may reduce delivery performance.

• You must adjust or disable triggers and constraints on the target tables to eliminate the potential for redundant operations.

To use Replicat with either ODBC or OLE DB, follow these steps:

1. To use ODBC exclusively, include the DBOPTIONS parameter with the USEODBC option in the Replicat parameter file. (To use the default OLE DB connection, no parameter is required.). For SQL Server CDC for Linux for Oracle GoldenGate, the USEODBC option is not allowed.
2. Disable triggers and constraints on the target tables. See Disabling Triggers and Cascade Constraints on the Target.
3. To use IDENTITY columns in a bidirectional SQL Server configuration, define the IDENTITY columns to have an increment value equal to the number of servers in the configuration, with a different seed value for each one. For example, a two-server installation would be as follows:

   • Sys1 sets the seed value at 1 with an increment of 2.

   • Sys2 sets the seed value at 2 with an increment of 2.

   A three-server installation would be as follows:

   • Sys1 sets the seed value at 1 with an increment of 3.

   • Sys2 sets the seed value at 2 with an increment of 3.

   • Sys3 sets the seed value at 3 with an increment of 3.

4. Configure an ODBC data source. See Configuring a Database Connection on Windows.

Using OLE DB with USEREPLICATIONUSER

If Replicat connects as the SQL Server replication user through OLE DB with the USEREPLICATIONUSER option, and NOT FOR REPLICATION is enabled for IDENTITY, triggers with foreign key constraints, the following benefits and limitations apply.

• IDENTITY seeds are not incremented when Replicat performs an insert. For SQL Server bidirectional configurations, stagger the seed and increment values like the example in Step 3 of the previous section.

• Triggers are disabled for the Replicat user automatically on the target to prevent redundant operations. However triggers fire on the target for other users.

• Foreign key constraints are not enforced on the target for Replicat transactions. CASCADE updates and deletes are not performed. These, too, prevent redundant operations.

• CHECK constraints are not enforced on the target for Replicat transactions. Even though these constraints are enforced on the source before data is captured, consider whether their absence on the target could cause data integrity issues.

  Note:

  Normal IDENTITY, trigger, and constraint functionality remains in effect for any users other than the Replicat replication user.

To use Replicat with USEREPLICATIONUSER, follow these steps:

Note:

For SQL Server CDC for Linux for Oracle GoldenGate, the USEREPLICATIONUSER option is not allowed.

Note:

For Replicat, connections using a Microsoft ODBC driver, install the Microsoft OLE DB Driver 18 for SQL Server to support the USEREPLICATIONUSER option:

https://www.microsoft.com/en-us/download/details.aspx?id=56730

1. In SQL Server Management Studio (or other interface) set the NOT FOR REPLICATION flag on the following objects. For active-passive configurations, set it only on the passive database. For active-active configurations, set it on both databases.

   • Foreign key constraints

   • Check constraints

   • IDENTITY columns

   • Triggers (requires textual changes to the definition; see the SQL Server documentation for more information.)

2. Partition IDENTITY values for bidirectional configurations.
3. In the Replicat MAP statements, map the source tables to appropriate targets, and map the child tables that the source tables reference with triggers or foreign-key cascade constraints. Triggered and cascaded child operations are replicated by Oracle GoldenGate, so the referenced tables must be mapped to appropriate targets to preserve data integrity. Include the same parent and child source tables in the Extract TABLE parameters.

   Note:

   If referenced tables are omitted from the MAP statements, no errors alert you to integrity violations, such as if a row gets inserted into a table that contains a foreign key to a non-replicated table.

4. In the Replicat parameter file, include the DBOPTIONS parameter with the USEREPLICATIONUSER option.
5. Configure an ODBC data source. See Configuring a Database Connection on Windows.

Configuring a Database Connection on Linux

Oracle GoldenGate for SQL Server database configuration provides the same support for Linux and Windows. However, you need the msodbcsql13* or msodbcsql17* drivers to set up the connections in a Linux environment.

See:

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-2017

The following example demonstrates how to create an ODBC data source in a Linux environment:

1. Create a template file for your data source:

   vi odbc_template_file.ini

2. Describe the data source in the template file. In the following example, myserver_ss2017_source is used as the name with DBLOGIN and SOURCEDB to connect to the database.:

   [myserver_SS2017_source]            
   Driver = ODBC Driver 17 for SQL Server            
   Server = myserver,1433            
   Database = source_database            
   User = ssuser            
   Password = ssuserpassword

3. Install the data source using the command:

   odbcinst -i -s -f odbc_template_file.ini

   For more information, see:

   https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/connection-string-keywords-and-data-source-names-dsns?view=sql-server-2017

Configuring a Database Connection on Windows

Follow these instructions to create a SQL Server system data source name (DSN) for a source or a target SQL Server database. A DSN stores information about how to connect to a SQL Server database through ODBC (Open Database Connectivity).

To create a SQL Server DSN

1. To run the ODBC client, select Control Panel, select Administrative Tools, and then select Data Sources (ODBC).
2. In the ODBC Data Source Administrator dialog box of the ODBC client, select the System DSN tab, and then click Add.
3. Under Create New Data Source, select the correct SQL Server driver supported for your version of SQL Server, and then click Finish. The Create a New Data Source to SQL Server wizard appears. To determine the correct SQL Server driver, see Database Connection.
4. Supply the following, and then click Next:

   • Name: Can be of your choosing. In a Windows cluster, use one name across all nodes in the cluster.

   • Description: (Optional) Type a description of this data source.

   • Server: Select the SQL Server server or instance name. Optionally, the listener\instance name of an Always On Availability Group can be listed.

5. For login authentication, do one of the following, and then click Next:
   1. Select With Integrated Windows Authentication for Oracle GoldenGate to use Windows authentication.
   2. To use database credentials, With SQL Server authentication using a login ID and password entered by the user, and supply login information.
6. If the default database is not set to the one that Oracle GoldenGate will connect to, select Change the default database to, and then select the database. Set the other settings to use ANSI. Click Next.
7. Leave the next page set to the defaults. Click Finish.
8. Click Test Data Source to test the connection.
9. If the test is successful, close the confirmation box and the Create a New Data Source box.
10. Repeat this procedure for each SQL Server source and target system.

Preparing Tables for Processing

The table attributes in the following sections must be addressed in your Oracle GoldenGate environment.

• Disabling Triggers and Cascade Constraints on the Target
  
• Assigning Row Identifiers
  
• Improving IDENTITY Replication with Array Processing
  

Disabling Triggers and Cascade Constraints on the Target

In an environment where SQL Server is the target, consider triggers and cascade constraints that may repeat an operation that occurred on the source. For example, if the source has an insert trigger on TableA that inserts a record into TableB, and Oracle GoldenGate is configured to capture and deliver both TableA and TableB, the insert trigger on the target table, TableA, must be disabled. Otherwise, Replicat inserts into TableA, and the trigger fires and insert into TableB. Replicat will then try to insert into TableB, and then terminate abnormally.

When a trigger or cascade constraint repeats an operation that occurred on the source, you do not have to disable the trigger or constraint when the following conditions are both true:

• You use the DBOPTIONS USEREPLICATIONUSER parameter in Replicat.

• You use OLE DB connection for Replicat. The use of the OLE DB connection is the default configuration. Note that the trigger, constraint, or IDENTITY property must have NOT FOR REPLICATION enabled.

In the following scenario, disable the triggers and constraints on the target:

• Uni-directional replication where all tables on the source are replicated.

In the following scenarios, enable the triggers and constraints on the target:

• Uni-directional replication where tables affected by a trigger or cascade operation are not replicated, and the only application that loads these tables is using a trigger or cascade operation.

• Uni-directional or -bi-directional replication where all tables on the source are replicated. In this scenario, set the target table cascade constraints and triggers to enable NOT FOR REPLICATION, and use the DBOPTIONS USEREPLICATIONUSER parameter in Replicat.

Assigning Row Identifiers

Oracle GoldenGate requires unique row identifiers on the source and target tables to locate the correct target rows for replicated updates and deletes. Source tables can have any kind of key listed in How Oracle GoldenGate Determines the Kind of Row Identifier to Use. If there is no primary key identified on a table that has fixed-length columns, the length of one of the fixed-length columns must be below 3800 bytes.

• How Oracle GoldenGate Determines the Kind of Row Identifier to Use
  
• Using KEYCOLS to Specify a Custom Key
  

How Oracle GoldenGate Determines the Kind of Row Identifier to Use

Unless a KEYCOLS clause is used in the TABLE or MAP statement, Oracle GoldenGate selects a row identifier to use in the following order of priority:

1. First unique key alphanumerically that does not contain a timestamp or non-materialized computed column.
2. If neither of these key types exist , Oracle GoldenGate constructs a pseudokey of all columns that the database allows to be used in a unique key, excluding those that are not supported by Oracle GoldenGate in a key or those that are excluded from the Oracle GoldenGate configuration. For SQL Server, Oracle GoldenGate requires the row data in target tables that do not have a primary key to be less than 8000 bytes.

   Note:

   If there are types of keys on a table or if there are no keys at all on a table, Oracle GoldenGate logs a message to the report file. Constructing a key from all of the columns impedes the performance of Oracle GoldenGate on the source system. On the target, this key causes Replicat to use a larger, less efficient WHERE clause.

Using KEYCOLS to Specify a Custom Key

If a table does not have an applicable row identifier, or if you prefer that identifiers are not used, you can define a substitute key, providing that the table has columns that always contain unique values. You define this substitute key by including a KEYCOLS clause within the Extract TABLE parameter and the Replicat MAP parameter. The specified key overrides any existing primary or unique key that Oracle GoldenGate finds.

Improving IDENTITY Replication with Array Processing

Because only one table per session can have IDENTITY_INSERT set to ON, Replicat must continuously toggle IDENTITY_INSERT when it applies IDENTITY data to multiple tables in a session. To improve the performance of Replicat in this situation, use the BATCHSQL parameter. BATCHSQL causes Replicat to use array processing instead of applying SQL statements one at a time.

Globalization Support

Oracle GoldenGate provides globalization support that lets it process data in its native language encoding. The Oracle GoldenGate apply process (Replicat) can convert data from one character set to another when the data is contained in character column types.