1. Oracle GoldenGate Classic Documentation
  2. Prepare
  3. Prepare Your Database for Oracle GoldenGate Classic Architecture
  4. SQL Server
  5. Understanding What's Supported for SQL Server
  6. Supported SQL Server Data Types

Supported SQL Server Data Types

The following data types are supported for capture and delivery, unless specifically noted in the limitations that follow:

  • Binary Data Types

    • (binary, varbinary, varbinary (max))

    • (varbinary (max)with FILESTREAM)

  • Character Data Types

    • (char, nchar, nvarchar, nvarchar (max), varchar, varchar (max))

  • Date and Time Data Types

    • (date, datetime2, datetime, datetimeoffset, smalldatetime, time)

  • Numeric Data Types

    • (bigint, bit, decimal, float, int, money, numeric, real, smallint, smallmoney, tinyint)

  • LOBs

    • (image, ntext, text)

  • Other Data Types

    • (timestamp, uniqueidentifier, hierarchyid, geography, geometry, sql_variant (Delivery only), XML)

  • Oracle GoldenGate for SQL Server can replicate column data that contains SPARSE settings..

Limitations:

  • Oracle GoldenGate does not support filtering, column mapping, or manipulating large objects larger than 4KB. Full Oracle GoldenGate functionality can be used for objects of up to 4KB.

  • 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 or unique constraint, or a substitute key specified with a KEYCOLS clause in the TABLE or MAP statements. For more information see Assigning Row Identifiers.

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

  • If capture of data for TEXT, NTEXT, IMAGE, 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 and adjust the option as needed.

    Note:

    Amazon RDS for SQL Server does not allow max text repl size to be greater than 64MB.

  • Columns of IMAGE, NTEXT, and TEXT data types are logged as a NULL value for delete and before image update operations. Columns of VARBINARY(MAX), VARCHAR(MAX), and NVARCHAR(MAX) are logged as a NULL value for before image update operations unless the column was updated.

    For more information, review the Large Object Data Types content in the following Microsoft document:

    https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-capture-instance-ct-transact-sql?view=sql-server-ver15

  • Oracle GoldenGate supports UDT and UDA data of up to 2 GB in size. All UDTs except SQL_Variant are supported.

  • Common Language Runtime (CLR), including SQL Server built-in CLR data types (such as, geometry, geography, and hierarchy ID), are supported. CLR data types are supported only in a like-to-like SQL Server configuration. Transformation, filtering, and other types of manipulation are not supported for CLR data.

  • VARBINARY (MAX) columns with the FILESTREAM attribute are supported up to a size of 4 GB. Extract uses standard Win32 file functions to read the FILESTREAM file.

  • The range and precision of floating-point numbers depends on the host machine. In general, 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 time stamp data from 0001/01/03:00:00:00 to 9999/12/31:23:59:59. If a time stamp is converted from GMT to local time, these limits also apply to the resulting time stamp. Depending on the time zone, conversion may add or subtract hours, which can cause the time stamp to exceed the lower or upper supported limit.

Limitations on Computed Columns:

  • Computed columns, either persisted or non-persisted, are not supported by Microsoft’s Change Data Capture. Therefore, no data is written to the trail for columns that contain computed columns. To replicate data for non-persisted computed columns, 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 the time that the column was changed in the data base and the time that 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, is written to the trail or sent to the target, depending on the method that is 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 the key, the non-persisted computed column is ignored. This may affect data integrity if the remaining columns do not enforce uniqueness.

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

  • If a unique key or index contains a non-persisted computed column and is the only unique identifier in 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, Replicat may apply operations containing this identifier to the wrong target rows.