Documentation
Advanced Search


Installing and Configuring Oracle GoldenGate for SQL Server

1 System Requirements and Preinstallation Instructions

This chapter contains the requirements for the system and database resources that support Oracle GoldenGate.

This chapter includes the following sections:

1.1 Supported Platforms

To find out which Oracle GoldenGate builds are available for a specific combination of database version and operating system, log onto My Oracle Support at http://support.oracle.com and select the Certifications tab. For assistance, click Tips for Finding Certifications.

You will need to provide an e-mail address and password to enter this site.

1.2 Operating System Requirements

This section describes the operating system requirements of Oracle GoldenGate. These requirements fall into one of the following categories:

1.2.1 Memory Requirements

The amount of memory that is required for Oracle GoldenGate depends on the amount of data being processed, the number of Oracle GoldenGate processes running, the amount of RAM available to Oracle GoldenGate, and the amount of disk space that is available to Oracle GoldenGate for storing pages of RAM temporarily on disk when the operating system needs to free up RAM (typically when a low watermark is reached). This temporary storage of RAM to disk is commonly known as swapping or paging (herein referred to as swapping). Depending on the platform, the term swap space can be a swap partition, a swap file, a page file (Windows) or a shared memory segment (IBM i platforms).

Modern servers have sufficient RAM combined with sufficient swap space and memory management systems to run Oracle GoldenGate. However, increasing the amount of RAM available to Oracle GoldenGate may significantly improve its performance, as well as that of the system in general.

Typical Oracle GoldenGate installations provide RAM in multiples of gigabytes to prevent excessive swapping of RAM pages to disk. The more contention there is for RAM the more swap space that is used.

Excessive swapping to disk causes performance issues for the Extract process in particular, because it must store data from each open transaction until a commit record is received. If Oracle GoldenGate runs on the same system as the database, the amount of RAM that is available becomes critical to the performance of both.

RAM and swap usage are controlled by the operating system, not the Oracle GoldenGate processes. The Oracle GoldenGate cache manager takes advantage of the memory management functions of the operating system to ensure that the Oracle GoldenGate processes work in a sustained and efficient manner. In most cases, users need not change the default Oracle GoldenGate memory management configuration.

For more information about evaluating Oracle GoldenGate memory requirements, see the CACHEMGR parameter in Reference for Oracle GoldenGate for Windows and UNIX.

1.2.2 Disk Requirements

Assign free disk space according to the following instructions:

  • To determine the size of the Oracle GoldenGate download file, view the Size column before downloading your selected build from Oracle Software Delivery Cloud. The value shown is the size of the files in compressed form. The size of the expanded Oracle GoldenGate installation directory will be significantly larger on disk. For more information, see Section 2.2, "Downloading Oracle GoldenGate."

  • To install Oracle GoldenGate into a cluster environment, install the Oracle GoldenGate binaries and files as the Oracle user on a shared file system that is available to all cluster nodes. See Section 2.3, "Preparing to Install Oracle GoldenGate Within a Cluster" for more information.

  • An additional 1 GB of disk space on any system that hosts Oracle GoldenGate trails, which are files that contain the working data. You may need more or less than this amount, because the space that is consumed by the trails depends on the volume of data that will be processed. See the guidelines for sizing trails in Administering Oracle GoldenGate for Windows and UNIX.

1.2.3 Temporary Disk Requirements

By default, Oracle GoldenGate maintains data that it swaps to disk in the dirtmp sub-directory of the Oracle GoldenGate installation directory. The cache manager assumes that all of the free space on the file system is available. This directory can fill up quickly if there is a large transaction volume with large transaction sizes. To prevent I/O contention and possible disk-related Extract failures, dedicate a disk to this directory. You can assign a name to this directory with the CACHEDIRECTORY option of the CACHEMGR parameter.

1.2.4 Network

Configure networking according to the following instructions:

  • Configure the system to use TCP/IP services, including DNS. Oracle GoldenGate supports IPv4 and IPv6 and can operate in a system that supports one or both of these protocols.

  • Configure the network with the host names or IP addresses of all systems that will be hosting Oracle GoldenGate processes and to which Oracle GoldenGate will be connecting. Host names are easier to use.

  • Oracle GoldenGate requires some unreserved and unrestricted TCP/IP ports, the number of which depends on the number and types of processes in your configuration. See the Administering Oracle GoldenGate for Windows and UNIX for details on how to configure the Manager process to handle the required ports.

  • Keep a record of the ports that you assigned to Oracle GoldenGate. You will specify them with parameters when configuring the Manager process.

  • Configure your firewalls to accept connections through the Oracle GoldenGate ports.

1.2.5 Operating System Privileges

Assign operating privileges according to the following instructions.

1.2.5.1 Manager

The Manager process can run as a Windows service, or it can run interactively as the current user. The Manager process requires:

  • Full control over the files and folders within the Oracle GoldenGate directories.

  • Full control over the trail files, if stored in a location other than the Oracle GoldenGate directory.

  • Membership in the local Administrators Group (on all nodes in a cluster).

The programs that capture and replicate data (Extract and Replicat) run under the Manager account and inherit those Administrator rights.

1.2.6 Other Programs

Before installing Oracle GoldenGate on a Windows system, install and configure the Microsoft Visual C ++ 2010 SP1 Redistributable Package. Make certain it is the SP1 version of this package, and make certain to get the correct bit version for your server. This package installs runtime components of Visual C++ Libraries. For more information, and to download this package, go to http://www.microsoft.com.

Oracle GoldenGate fully supports virtual machine environments created with any virtualization software on any platform. When installing 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.

1.3 SQL Server Requirements

To operate with Microsoft SQL Server databases, Oracle GoldenGate requires the following setup in the database instance.

1.3.1 Instance Configuration

To configure an instance, the following must be true:

  • To capture from a SQL Server database, the instance must be the Enterprise Edition of SQL Server.

  • Change Data Capture (CDC) must be enabled for Oracle GoldenGate and will be enabled by Oracle GoldenGate by means of the ADD TRANDATA command. See Section 4.4, "Enabling Supplemental Logging" for more information.

1.3.2 Database Configuration

The database should be configured according to the following recommendations:

  • A SQL Server source database must be set to use the full recovery model.

  • Oracle GoldenGate does not support system databases.

  • After the source database is set to full recovery, a full database backup must be taken. This backup could be one that was already done, prior to the installation of Oracle GoldenGate, for a database that was previously using the full or bulk-logged recovery model. If you need to make a backup, see Section 4.6, "Making a Full Database Backup Before You Start Oracle GoldenGate."

  • The log chain on the source database must not be broken between the time of the last full database backup and the time when Oracle GoldenGate is installed. (The log chain is broken if the log was backed up with the no_log or truncate_only options, or if the recovery model was set to simple or bulk logged at any time after the initial full database backup was completed.) In addition, the log chain must not be broken after Oracle GoldenGate is installed. For more information, see the Microsoft SQL Server documentation on Log Chains.

1.3.3 Database Connection

Oracle GoldenGate uses ODBC and/or OLE DB to connect to a database:

  • ODBC: The Extract process uses ODBC (Open Database Connectivity) to connect to a source SQL Server database. The Replicat process uses ODBC to connect to a target SQL Server database to obtain metadata, but can optionally use it for its delivery transactions as well. ODBC must be properly configured. For more information, see Section 3.1.3, "Configuring an ODBC connection."

  • OLE DB: By default, the Replicat process uses 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 data). For more information about Replicat connection options, see Section 3.1, "Configuring a Database Connection."

1.3.4 Database Connectivity Drivers

Using the SQL Server Native Client 11 OLE DB driver to connect to a SQL Server 2012 instance may lead to a memory leak issue (Microsoft article 2881661). Microsoft has provided a fix in SQL Server 2012 SP1 CU7 (Microsoft article 2894115). To avoid a possible memory leak, you may choose one of the following options:

  • Use ODBC mode

  • Upgrade the SQL Native Client 11 driver to the SP1 CU7 level.

Use at least the SQL Server Native Client 10.0 driver. The SQL Server 2000 driver does not support newer data types.

1.3.5 Database User for Oracle GoldenGate Processes

The following database users and privileges are required for Oracle GoldenGate to capture from, and apply to, a Microsoft SQL Server database.

1.3.5.1 User that Enables Supplemental Logging

A database user is required to issue the ADD TRANDATA command to enable supplemental logging on the source database objects in the Oracle GoldenGate configuration. A database login command (DBLOGIN) is issued from GGSCI before issuing ADD TRANDATA. This user must be a member of the SQL Server System Administrators (sysadmin) role.

ADD TRANDATA is performed for tables that are being added to the Oracle GoldenGate capture configuration, either during the initial deployment or when tables are added to the configuration later. For more information, see Section 4.4, "Enabling Supplemental Logging."

1.3.5.2 Extract (Capture) and Replicat (Apply) Users

The Oracle GoldenGate Extract process captures data from a source SQL Server database, and the Replicat process applies it 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 in Table 1-1 on the source and target systems.

  • To use SQL Server authentication, create a dedicated SQL Server login for Extract and Replicat and assign the privileges listed in Table 1-2. If using SQL Server authentication, you will need to specify the user and password with the USERID parameter (including the PASSWORD option) in the Extract or Replicat parameter file or, alternatively, use the Oracle GoldenGate credential store and specify a user alias with the USERIDALIAS parameter. For more information about these parameters, see Administering Oracle GoldenGate for Windows and UNIX.

Table 1-1 Required SQL Server Privileges for Manager When Using Windows Authentication

Oracle GoldenGate Process Manager privileges if using Local System account Manager privileges if using local or domain account

Extract

(source system)

BUILTIN\Administrators account must be a member of the SQL Server fixed server role System Administrators.

Account must be a member of the SQL Server fixed server role System Administrators.

Replicat

(target sdystem)

BUILTIN\Administrators account must be at least a member of the db_owner fixed database role of the target database.

Account must be at least a member of the db_owner fixed database role of the target database.


Table 1-2 Required SQL Server Privileges for Extract and Replicat When Using SQL Server Authentication

Extract login Replicat login

Member of the SQL Server fixed server role System Administrators.

At least a member of the db_owner fixed database role of the target database.


1.3.6 Encrypting and Storing User Credentials

If using SQL Server authentication rather than Windows authentication, you will at times during installation and setup of Oracle GoldenGate need to 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 encrypting it through the previous use of the ENCRYPT PASSWORD command. To avoid having to encrypt the password each time that you issue DBLOGIN, and also to protect the user ID from exposure, you can create an Oracle GoldenGate credential store before performing any setup and configuration.

The credential store enables you to simply supply an alias for the login credential whenever you log in with DBLOGIN. It 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 now and then use the management commands to expand it later as needed. For more information, see Administering Oracle GoldenGate for Windows and UNIX.

1.4 Supported SQL Server Data Types

Oracle GoldenGate supports most SQL Server data types except those listed under Section 1.5, "Non-Supported SQL Server Data Types."

Limitations of Support

  • Oracle GoldenGate does not support the filtering, column mapping, or manipulation of large objects larger than 4K. Full Oracle GoldenGate functionality can be used for objects that are 4K or smaller.

  • Oracle GoldenGate treats XML data as a large object (LOB), as does SQL Server when the XML does not fit into a row. SQL Server extended XML enhancements (such as lax validation, DATETIME, union functionality) are not supported.

  • A system-assigned TIMESTAMP column or a non-materialized computed column cannot be part of a key. A table containing a TIMESTAMP column must have a key, which can be a primary key, a unique constraint, or a substitute key specified with a KEYCOLS clause in the TABLE or MAP statement. See Section 3.2.2, "Assigning row identifiers."

  • Oracle GoldenGate supports multi-byte character data types and multi-byte data stored in character columns. Multi-byte data is only supported in a like-to-like configuration. Transformation, filtering, and other types of manipulation are not supported for multi-byte character data.

  • If 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, extend the size. Use sp_configure to view the current value of max text repl size.

  • IDENTITY columns are supported as follows:

    • Fully in a uni-directional configuration when the increment values are configured properly.

    • Fully, or with limitations, in a bi-directional configuration, depending on how the Replicat connection is defined. Full support, including support for IDENTITY ranges, requires OLE DB to be used with NOT FOR REPLICATION enabled for the IDENTITY columns and with Replicat operating as the replication user.

    • For more information, see Chapter 3, "Preparing the System for Oracle GoldenGate."

  • Oracle GoldenGate supports UDT and UDA data of up to 2 GB in size. All UDTs except SQL_Variant are supported. CLR (common language runtime), including SQL Server built-in CLR data types (such as geometry, geography and hierarchyid), are supported.

  • The support of range and precision for floating-point numbers depends on the host machine. In general, the precision is accurate to 16 significant digits, but you should review the database documentation to determine the expected approximations. Oracle GoldenGate rounds or truncates values that exceed the supported precision.

  • Oracle GoldenGate supports timestamp data from 0001/01/03:00:00:00 to 9999/12/31:23:59:59. If a timestamp is converted from GMT to local time, these limits also apply to the resulting timestamp. Depending on the timezone, conversion may add or subtract hours, which can cause the timestamp to exceed the lower or upper supported limit.

  • VARBINARY (MAX) column with the FILESTREAM attribute (introduced in SQL Server 2008) is supported up to a size of 4 GB. Extract uses standard Win32 file functions to read the FILESTREAM file.

1.5 Non-Supported SQL Server Data Types

SQL_Variant is the only non-supported data type.

1.6 Supported Objects and Operations for SQL Server

The following objects and operations are supported:

  • Oracle GoldenGate supports the extraction and replication of DML operations on tables that contain rows of up to 512 KB in length. TEXT, NTEXT, IMAGE, VARBINARY, VARCHAR (MAX), and NVARCHAR(MAX) columns are supported in their full size.

  • Oracle GoldenGate supports the maximum sizes that are permitted for tables that are tracked by CDC.

  • Oracle GoldenGate supports SQL Server tables that use data compression. This includes row compressed format in both ROW and PAGE mode.

  • Oracle GoldenGate supports delivery to writeable views. You can map data to a view in a Replicat MAP statement.

  • Oracle GoldenGate supports partitioned tables if the table has the same physical layout across all partitions.

Limitations on Computed Columns

  • Oracle GoldenGate supports tables with non-persisted computed columns, but does not capture change data for these columns, because the database does not write it to the transaction log. To replicate data for non-persisted computed columns, you can use the FETCHCOLS or FETCHMODCOLS option of the TABLE parameter to fetch the column data from the table. Keep in mind that there can be discrepancies caused by differences in data values between when the column was changed in the database and when Extract fetches the data for the transaction record that is being processed.

  • Replicat does not apply DML to any computed column, even if the data for that column is in the trail, because the database does not permit DML on that type of column. Data from a source persisted computed column, or from a fetched non-persisted column, can be applied to a target column that is not a computed column.

  • In an initial load, all of the data is selected directly from the source tables, not the transaction log. Therefore, in an initial load, data values for all columns, including non-persisted computed columns, gets written to the trail or sent to the target, depending on the method that is being used. As when applying change data, however, Replicat does not apply initial load data to computed columns, because the database does not permit DML on that type of column.

  • Oracle GoldenGate does not permit a non-persisted computed column to be used in a KEYCOLS clause in a TABLE or MAP statement.

  • If a unique key includes a non-persisted computed column and Oracle GoldenGate must use that key, the non-persisted computed column will be ignored. This might affect data integrity if the remaining columns do not enforce uniqueness.

  • If a unique index is defined on any non-persisted computed columns, it will not be used.

  • If a unique key or index contains a non-persisted computed column and is the only unique identifier on a table, Oracle GoldenGate must use all of the columns as an identifier to find target rows. Because a non-persisted computed column cannot be used in this identifier, it is possible that Replicat could apply operations containing this identifier to the wrong target rows.

1.7 Non-Supported Objects and Operations for SQL Server

The following objects and operations are not supported by SQL Server:

  • Operations that are not supported by SQL Server Change Data Capture. SQL Server tables that are in the Extract configuration are marked for SQL Server Change Data Capture when you enable supplemental logging (see "Enabling Supplemental Logging"). Refer to SQL Server Books Online for a complete list of the operations that are limited by enabling SQL Server Change Data Capture.

  • Extraction or replication of DDL (data definition language) operations.

  • Capture and delivery of views. The underlying tables can be extracted and replicated.

  • Operations by the TextCopy utility and WRITETEXT and UPDATETEXT statements. These features perform operations that either are not logged by the database or are only partially logged, so they cannot be supported by the Extract process.

  • TDE (Transparent Data Encryption).

  • Partitioned tables that have more than one physical layout across partitions.

  • Partition switching.

  • Oracle GoldenGate does not support non-native SQL Server transaction log backups, such as those offered by third-party vendors. However, if using the TRANLOGOPTIONS parameter with the ACTIVESECONDARYTRUNCATIONPOINT option, Extract does not need to read from any transaction log backups, so any log backup utility may be used. For more information, see Chapter 4, "Preparing the Transaction Logs for Oracle GoldenGate."

Close Window

Table of Contents

Installing and Configuring Oracle GoldenGate for SQL Server

Expand | Collapse