Topics:
Parent topic: Installing the Oracle GoldenGate Classic Architecture
Assign operating system privileges according to the following instructions:
Parent topic: Installing with SQL Server Databases
The Manager process can run as a Windows service, or it can run interactively as the current user. The Manager process requires:
Full control permissions over the files and folders within the Oracle GoldenGate directories.
Full control permissions over the trail files, if they are stored in a location other than the Oracle GoldenGate directory.
(Classic Extract) For a source capture installation of Oracle GoldenGate, Manager requires Read permissions on the SQL Server database transaction log files and transaction log backups.
(Classic Extract) If you are running a source capture in Archived Log Mode from a middle tier Windows server, Manager requires Read permissions to the network share where the transaction log backups are written, and Read permissions on the transaction log backups.
Membership in the server's local Administrators Group (on all nodes in a cluster).
If you are running Manager as a Windows service with an Extract or Replicat that is connected to a remote database using Windows Authentication, the process attempts to login to the database with the account that the Manager is running under. Ensure that the Manager's service account has the correct access to the remote SQL Server instance.
The programs that capture and replicate data, Extract and Replicat, run under the Manager account and inherit the Manager's operating system level privileges.
Parent topic: Operating System Privileges
In addition to Additional Considerations, you must observe the following program and settings information for Oracle GoldenGate for SQL Server:
To install capture on a remote Windows server, for a Classic Extract in archived log mode or for a remote CDC Extract, set the remote server's time and time zone to that of the database server.
(Classic Extract) To capture from a source SQL Server 2008/2008 R2/2012/2014 Standard Edition database, the SQL Server Replication features must be installed and a Distributor must be configured.
SQL Server Client Tools Connectivity features must be installed on the server where Oracle GoldenGate is to be installed. This feature is normally installed by default when installing an instance of SQL Server, but for a Windows server that is to be used for a remote Replicat, a Classic Extract running off the database server in an Archived Log Mode, or a remote CDC Extract, the required client connectivity drivers can be obtained through the SQL Server installation media or from the following links:
Microsoft SQL Server 2008 SP4 Feature Pack:
https://www.microsoft.com/en-us/download/details.aspx?id=44277
Microsoft SQL Server 2008 R2 SP3 Feature Pack:
https://www.microsoft.com/enus/download/details.aspx?id=44272
For SQL Server 2012, 2014, and 2016, install the Microsoft SQL Server 2012 Native Client, which is part of the Microsoft SQL Server 2012 Feature Pack:
https://www.microsoft.com/en-us/download/details.aspx?id=29065
Oracle GoldenGate fully supports all virtual machine environments. When you install Oracle GoldenGate into a virtual machine environment, select a build that matches the database and the operating system of the virtual machine, not the host system.
Parent topic: Installing with SQL Server Databases
To operate with SQL Server databases, Oracle GoldenGate requires the following setup in the database instance.
Parent topic: Installing with SQL Server Databases
Note the following requirements when you configure an Oracle GoldenGate for SQL Server instance:
Classic Extract supports SQL Server Enterprise and Standard editions of versions 2008, 2008R2, 2012, and 2014.
CDC Extract supports SQL Server Enterprise editions of versions 2008, 2008 R2, 2012, 2014, 2016, and Standard Edition of SQL Server 2016 with Service Pack 1 (or above).
(CDC Extract) – For SQL Server 2012, 2014, and 2016, Microsoft has identified and fixed an issue where some UPDATE operations may be written incorrectly to a CDC staging table as an INSERT followed by a DELETE, rather than a DELETE/INSERT pair. This may cause downstream replication issues, such as a primary key violation. We recommend that you apply the Microsoft fix for this issue: https://support.microsoft.com/en-us/help/3030352
Oracle GoldenGate Delivery supports SQL Server Enterprise and Standard editions of versions 2008, 2008 R2, 2012, 2014, and 2016.
The SQL Server server name (@@SERVERNAME) must not be NULL.
(CDC Extract) In order for Oracle GoldenGate to capture transactional data, the source SQL Server instance, the SQL Server Agent must be running on the source SQL Server instance in order for the CDC Capture job to load change data to the CDC tables.
(CDC Extract) If you are using SQL Server 2016, before you enable supplemental logging, ensure that you have applied the following bug fix from Microsoft: https://support.microsoft.com/en-us/help/3166120/fix-could-not-find-stored-procedure-sys.sp-cdc-parse-captured-column-list-error-in-sql-server-2016 If this fix is not applied to your SQL Server instance, issuing ADD TRANDATA against a table for the database may incorrectly report that supplemental logging succeeded, when in fact it may not have, and no records will be captured for the table.
(Classic Extract) To capture data from a source SQL Server Standard Edition database, you must install the SQL Server Replication features, configure a Distributor and create a distribution database.
If data capture a TEXT, NTEXT, IMAGE, or VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX), and columns exceed the SQL Server default size, set for the “max text repl size”, Oracle GoldenGate adjust the data as the target needed.
Parent topic: SQL Server Requirements
This section contains Oracle GoldenGate requirements that are specific to the Oracle Database. These apply to both capture modes unless explicitly noted.
Classic Extract captures all the columns by default. These behaviors do not affect like to like replications. However, with a replication to data warehouse, all the columns might have to be updated. If you are using the DBMS_LOB.LOADFROMFILE procedure to update a LOB column only and your supplemental log is on all the columns, Integrated Extract captures the key columns and LOB improving performance.
If you are converting from Classic Extract to Integrated Extract, you must use one of the following parameters to ensure that the Extract operates correctly:
Use KEYCOLS to add all columns (except LOB).
Use LOGALLSUPCOLS to control the writing of supplementally logged columns.
Ensure that your database has minimal supplemental logging enabled.
Database user privileges and configuration requirements are explained in Establishing Oracle GoldenGate Credentials in Using Oracle GoldenGate for Oracle Database.
If the database is configured to use a bequeath connection, the sqlnet.ora file must contain the bequeath_detach=true setting.
Oracle Databases must be in ARCHIVELOG mode so that Extract can process the log files.
Parent topic: SQL Server Requirements
Tables to be included for capture and delivery must include only the data types that are listed in Supported SQL Server Data Types:
Oracle GoldenGate supports capture of transactional DML from user tables, and delivery to user tables and writeable views.
DDL operations are not supported.
Source tables for an Enterprise Edition database do not require a primary key.
(Classic Extract) Source tables for a Standard Edition database do require a primary key.
Oracle GoldenGate supports the maximum permitted table names and column lengths for tables that are tracked by SQL Server Change Data Capture for Enterprise Edition, and for tables that are articles of a publication for SQL Server transactional replication.
The sum of all column lengths for a table to be captured from must not exceed the length that SQL Server allows for enabling Change Data Capture for the table. If the sum of all column lengths exceeds what is allowed by SQL Server procedure sys.sp.cdc_enable_table, then ADD TRANDATA cannot be enabled for that table. The maximum allowable record length decreases as more columns are present, so there is an inverse relationship between maximum record length and the number of columns in the table.
Parent topic: SQL Server Requirements
Oracle GoldenGate uses ODBC and OLE DB to connect to a database:
ODBC: The Extract process uses ODBC to connect to a source SQL Server database to obtain metadata and perform other process queries. The Replicat process uses ODBC to connect to a target SQL Server database to obtain metadata, but can optionally use it for its delivery of transactions as well. ODBC must be properly configured.
OLE DB: By default, the Replicat process attempts to use OLE DB to connect to a target SQL Server database to perform DML operations. Thus, there are always least two Replicat connections, ODBC for metadata and OLE DB for DML. If you are using the ODBC drivers from Microsoft that do not support OLEDB, then you must set the Replicat to run with the DBOPTIONS USEODBC parameter. DBOPTIONS USEODBC is required for Replicat when connecting to an Azure SQL Database target.
Limitations of Support
For SQL Server 2008/2008R2, use the SQL Server Native Client 10.0 driver. The older SQL Server driver (SQLSRV32.DLL) does not support newer SQL Server data types.
For SQL Server 2012/2014/2016, use the SQL Server Native Client 11.0 driver.
Using the SQL Server Native Client 11 OLE DB driver to connect to a SQL Server 2012 or a SQL Server 2014 instance in OLEDB mode may lead to a memory leak issue (Microsoft article 2881661). Microsoft has provided a fix in SQL Server 2012 SP1 CU7 (Microsoft article 2894115) and SQL Server 2014 CU1 (Microsoft article 2931693). To avoid a possible memory leak, you may choose one of the following options:
For SQL Server 2012, upgrade the SQL Native Client 11.0 driver to the SP1 CU7 level.
For SQL Server 2014, a possible memory leak still may exist after installing SQL Server 2014 CU1 on a new Windows system. This does not occur when you upgrade from SQL Server 2012 SP1 CU7 to SQL Server 2014 CU1.
For SQL Server 2014/2016, only the SQL Server Native Client 11.0 driver is supported. The ODBC Driver 11/13 for SQL Server is not supported.
An AlwaysOn Availability Group Listener connection is not supported.
Parent topic: SQL Server Requirements
The following database users and privileges are required for Oracle GoldenGate to capture from and apply to a SQL Server database.
Parent topic: SQL Server Requirements
A database user must issue the ADD TRANDATA command to enable supplemental logging on the source database in an Oracle GoldenGate configuration. A database login command (DBLOGIN) is issued from GGSCI before ADD TRANDATAis issued.
The database user that enables TRANDATA must have sysadmin rights.
(CDC Extract) Extract can run with dbowner permissions. However, you need sysadmin right to issueADD HEARTBEATTABLE, DELETE HEARTBEATTABLE , INFO HEARTBEATTABLE commands. sysadmin
Parent topic: Database User for Oracle GoldenGate Processes
The Oracle GoldenGate Extract processes capture data from a source SQL Server database for initial loads, from the transaction log and transaction log backups for a Classic Extract, and from the CDC tables for a CDC Extract. The Replicat process applies the data to a target SQL Server database. These processes can use either Windows Authentication or SQL Server Authentication to connect to a database.
To use Windows authentication, the Extract and Replicat processes inherit the login credentials of the Manager process, as identified by the Log On account specified in the Properties of the Manager service. This account must have the privileges listed here.
| Oracle GoldenGate Process | Manager privileges if using the Local System account | Manager privileges if using the local or domain account |
|---|---|---|
|
Classic Extract (source system) |
The |
The account must be a member of the SQL Server fixed server role |
|
CDC Extract (source system) |
The |
The account must be at least a member of the |
|
Replicat (target sdystem) |
The |
The account must be at least a member of the |
If you are using SQL Server authentication, you must specify the user and password with the USERID parameter (including the PASSWORD option) in the Extract or Replicat parameter file, or use the Oracle GoldenGate credential store and specify a user alias with the USERIDALIAS parameter.
To use SQL Server authentication, create a dedicated SQL Server login for Extract and Replicat and assign the privileges listed here.
| Classic Extract connecting using SQL Server Authentication | CDC Extract connecting using SQL Server Authentication | Replicat connecting using SQL Server Authentication |
|---|---|---|
|
The account must be a member of the SQL Server fixed server role |
The account must at least be a member of the |
The account must at least be a member of the |
Parent topic: Database User for Oracle GoldenGate Processes
If you are using SQL Server authentication instead of Windows authentication, as you set up and install Oracle GoldenGate, you occasionally must log into the database by using the DBLOGIN command in the GGSCI command interface. An example is when you add supplemental logging with the ADD TRANDATA command.
Encrypting the login password is a recommended security measure. However, using a secure password in the standard DBLOGIN command requires first encrypting it by using the ENCRYPT PASSWORD command. To avoid having to encrypt the password each time that you issue DBLOGIN, and to protect the user ID from exposure, you can create an Oracle GoldenGate credential store before you start setup and configuration.
When you use a credential store, you only have to supply an alias for the login credential whenever you log in with DBLOGIN. The credential store also makes the work of specifying login credentials for the Extract and Replicat processes easier and more secure when configuring the parameter files. You can create basic entries in the credential store at first and then use the management commands to expand it as needed.
Parent topic: SQL Server Requirements
Oracle GoldenGate capture for SQL Server supports real-time mode and archived log mode. For Classic Extract, real-time capture requires that Oracle GoldenGate is installed on the source database server. For Classic Extract in archived log mode, Oracle GoldenGate may be installed on the source database server or on a remote Windows server. CDC Extract supports real-time capture from the local database server or from a remote Windows server.
To apply to a SQL Server database, you can install Oracle GoldenGate on the database server or on a remote Windows server.
Parent topic: Installing with SQL Server Databases
Additional database preparation is required before running the Oracle GoldenGate processes, see Preparing the System for Oracle GoldenGate. Follow these steps to install Oracle GoldenGate on a Windows system or in the appropriate location in a cluster, see Installing on all Platforms. These instructions apply to all versions of SQL Server.
Parent topic: Installing with SQL Server Databases