8 Installing Oracle GoldenGate for SQL Server Databases

Learn about the requirements and how to install Oracle GoldenGate for SQL Server databases.

Topics:

• Operating System Privileges for Oracle GoldenGate Processes for SQL Server
  
• Other Programs and Settings
  
• SQL Server Requirements
  
• Where to Install Oracle GoldenGate
  
• Installing for SQL Server
  

Operating System Privileges for Oracle GoldenGate Processes for SQL Server

Assign operating system privileges according to the following instructions:

• Manager Running on Windows
  
• Extract and Replicat
  

Manager Running on Windows

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.

• 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.

Extract and Replicat

See Configuring a Database Connection in Using Oracle GoldenGate for Heterogeneous Databases to know the Extract and Replicat configuration for a database connection and users.

Other Programs and Settings

Observe the following program and settings information for Oracle GoldenGate for SQL Server:

• Install either the Microsoft ODBC Driver 17 or Microsoft ODBC Driver 18 for the operating system where Oracle GoldenGate is to be installed:

  For Oracle GoldenGate on Windows, install the driver available at the following link:

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

  For Oracle GoldenGate on Linux, install the driver available at this link, and follow the instructions for RHEL and Oracle Linux packages:

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

  Note:

  Support for Microsoft ODBC Driver 18 was added with Oracle GoldenGate release 19.1.0.0.221021. Versions prior to release 19.1.0.0.221021 do not support the Microsoft ODBC Driver 18 for SQL Server.

• Installation of the Oracle GoldenGate CDC cleanup tasks requires the Microsoft sqlcmd Utility. Download instructions for Windows and Linux systems can be found at:

  https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15

• To install capture on a remote Linux or Windows server, set the remote server's time and time zone to that of the database server, or use LSN based positioning for the Extract.

SQL Server Requirements

To operate with SQL Server databases, Oracle GoldenGate supports the following instance, database, and other configurations and settings.

• SQL Server Supported Versions
  
• Instance Requirements
  
• Database Requirements
  
• Table Requirements
  
• Database Connectivity
  
• Encrypting and Storing User Credentials
  

SQL Server Supported Versions

The following are the requirements to configure an Oracle GoldenGate for SQL Server instance:

• Oracle GoldenGate Extract supports SQL Server installed on Windows. SQL Server Enterprise Edition of versions 2012, 2014, 2016, 2017, and 2019, and SQL Server Standard Editions of versions 2016 with Service Pack1 (or higher), 2017, and 2019 are supported.

• (Extract) For SQL Server 2014, 2016, and 2017, Microsoft has identified and fixed several important issues that directly affect the SQL Server Change Data Capture feature. This situation impacts the ability for Oracle GoldenGate to correctly capture data. The current known issues that require Microsoft patches include KB3030352, KB3166120, and KB4073684. If you are using SQL Server 2014, 2016, or 2017 as a source database, Oracle highly recommends that you apply the latest Service Pack or Cumulative Update for your version of SQL Server.

• Oracle GoldenGate Delivery supports SQL Server installed on Windows. SQL Server Enterprise and Standard editions of versions 2012, 2014, 2016, 2017, and 2019 are supported.

• Oracle GoldenGate supports remote capture and delivery for Azure SQL database managed instance and remote delivery for Azure SQL Database.

• Oracle GoldenGate supports remote capture and delivery for Amazon RDS for SQL Server.

Instance Requirements

• The SQL Server server name (@@SERVERNAME) must not be NULL.

• (Extract) For Oracle GoldenGate to capture transactional data, the SQL Server Agent must be running on the source SQL Server instance and the SQL Server Change Data Capture job must be running against the database. If SQL Server Transactional Replication is also enabled for the database, then the SQL Server Log Reader Agent must be running.

• If your data for TEXT, NTEXT, IMAGE, or VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) columns will exceed the SQL Server default size set for the max text repl size option, then extend the size. Use sp_configure to view or adjust the current value of max text repl size.

  Note:

  For Amazon RDS for SQL Server, to adjust instance settings, you need to use Parameter Groups instead of sp_configure.

Database Requirements

Observe the following requirements and limitations for supporting Oracle GoldenGate:

• Only user databases are supported for capture and delivery.

• Ensure that Auto Update Statistics is enabled for the database.

• The database must be set to the compatibility level of the SQL Server instance version. Database compatibility levels below SQL Server 2012 (110) are not supported

• Oracle GoldenGate supports SQL Server databases configured with Transparent Data Encryption (TDE).

• (Extract) The source database can be set to any recovery model that supports the change data capture feature in Microsoft SQL Server.

• If the source database was created by restoring a backup from a different instance you must synchronize the database owner SID with the SID on the new instance. Alternatively, you can use sp_changedbowner to set the restored database to a current login.

• (AlwaysOn) Extract supports capturing from the primary database, or a read-only, synchronous-commit mode. Asynchronous-commit mode are not supported for capture.

Limitations:

• Oracle GoldenGate does not support capture or delivery of system databases.

• Oracle GoldenGate does not support capture from contained databases.

• Source database names cannot exceed 121 characters. This limitation is due to the SQL Server stored procedures that are used to enable supplemental logging.

• If you are configuring the Oracle GoldenGate heartbeat functionality, the SQL Server database name must not exceed 107 characters.

• Capture from SQL Server databases enabled with In-Memory OLTP (in-memory optimization) is not supported. When you add a Memory Optimized Data file group to your database, Oracle GoldenGate is not allowed to enable supplemental logging for any table in the database. Conversely, if supplemental logging has been enabled for any table in the database prior to the creation of a Memory Optimized Data file group, SQL Server does not allow a Memory Optimized Data file group to be created.

• SQL Server 2014, 2016, 2017, and 2019 provide the option of delayed transaction durability for transactions. However, transactions against a table configured with supplemental logging (TRANDATA) by Oracle GoldenGate will always be fully durable.

• (AlwaysOn) Capture from asynchronous-commit mode of an AlwaysOn Availability group are not supported.

Table 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.

• Oracle GoldenGate supports the maximum permitted table names and column lengths for tables that are tracked by SQL Server Change Data Capture.

• 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.

Database Connectivity

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: OLE DB: By default, the Replicat process attempts to use OLE DB to connect to a target SQL Server database to perform DML operations. If the driver used only supports ODBC, then the Replicat will apply DML via ODBC. To use OLE DB in an ODBC-only driver, install the Microsoft OLE DB Driver 18 for SQL Server. Using OLE DB allows the use of the DBOPTIONS USEREPLICATIONUSER parameter, which supports the Not for Replication flag of certain table properties.

• Using the Microsoft 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 Server instance to at least Cumulative Update 7 of Service Pack 1.

  • For SQL Server 2014, upgrade the SQL Server instance to at least Cumulative Update 1.

  • Use a Microsoft supported ODBC driver.

• For Azure SQL Database, use a Microsoft supported ODBC driver.

• Always On availability group listeners are supported and are required to support read-only routing for capture against a synchronous mode secondary replica.

Encrypting and Storing User Credentials

As you set up and install Oracle GoldenGate, you occasionally must log into the database by using the DBLOGIN command in the Admin Client or GGSCI command interface. For example, 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. You can create an encryption profile using the Admin Client to set up your credential store.

To know more about setting up security, see How to Configure an Encryption Profile?

Where to Install Oracle GoldenGate

Oracle GoldenGate Capture for SQL Server supports change data capture installed on a local or remote, Windows or Linux server. Oracle GoldenGate for SQL Server installed on Windows supports local and remote Capture and Delivery for SQL Server running on Windows. For Oracle GoldenGate installed on Linux, only remote Capture and Delivery are supported for SQL Server running on Windows. Oracle GoldenGate does not support SQL Server running on Linux.

To apply to a SQL Server database, you can install Oracle GoldenGate on the database server or on a remote Linux or Windows server.

Installing for SQL Server

To install Oracle GoldenGate for SQL Server, follow the instructions in Installing for all Platforms.