SQL Server: Supported Data Types, Objects, and Operations

Learn about support information for Oracle GoldenGate on SQL Server Database.

With Oracle GoldenGate for SQL Server supports capture and delivery of initial load and transactional data for supported SQL Server database versions.

Oracle GoldenGate for SQL Server supports the mapping, filtering, and transformation of source data, unless noted otherwise in this document, as well as replicating data derived from other source databases supported by Oracle GoldenGate, into SQL Server databases.

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.

• It is recommended to install the most recent Service Pack or Cummulative Update for your SQL Server instance to ensure proper functionality. For SQL Server 2012, 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 KB 4073684.

Database Requirements

Observe the following requirements and limitations for supporting Oracle GoldenGate:

• Only user databases are supported for capture and delivery.

• Ensure that Auto Create Statistics and Auto Update Statistics are enabled for the database.

• The database must be set to the compatibility level of the SQL Server instance version.

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

• Capture from SQL Server databases (SQL Server 2017 CU15 and higher releases) enabled with In-Memory OLTP (in-memory optimization) is supported, with Oracle GoldenGate releases 21.15 and 23.5 onwards. However, only capture from on-disk tables is supported, and not from the memory optimized tables.

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

• Replicat performance consideration: Beginning with SQL Server 2016, Microsoft changed the default setting for the database option TARGET_RECOVERY_TIME from 0 to 60 seconds. It has been demonstrated in internal testing that this can reduce the Replicat’s throughput. If you experience Replicat throughput degradation, consider adjusting the TARGET_RECOVERY_TIME setting to 0.

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.

• (AlwaysOn) Capture from databases configured in 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.

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. {: .infoboxnote}

• 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 database and the time that Extract fetches the data for the transaction record that is being processed.

• When a column with unsupported data type sql_variant is used in the FETCHCOLS or FETCHMODCOLS options, Extract abends with following error:

  ERROR   OGG-25230 Unsupported datatype '*column-name* (sql_variant)' used with 'FETCHCOLS/FETCHCOLSEXCEPT' in table '*table-name>*.

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

Non-Supported SQL Server Data Types and Features

• SQL_Variant data type is not supported for capture.

• Tables that contain unsupported data types may cause Extract to Abend. As a workaround, you must remove TRANDATA from those tables and remove them from the Extract’s TABLE statement, or use the Extract’s TABLEEXCLUDE parameter for the table.

Supported Objects and Operations for SQL Server

The following objects and operations are supported:

• Parallel Replicat is supported with Oracle GoldenGate for SQL Server.

• Oracle GoldenGate supports capture of transactional DML from user tables and delivery to user tables and writeable views.

• TEXT, NTEXT, IMAGE, VARBINARY, VARBINARY (MAX) VARCHAR (MAX), and NVARCHAR (MAX) columns are supported in their full size for operations that are logged by SQL Server Chang Data Capture. For example, columns of IMAGE, NTEXT, and TEXT data types are logged as a NULL value for delete operations. For more information, review the Large Object Data Types content at 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 the maximum row sizes that are permitted for tables that are enabled for SQL Server Change Data Capture.

• Oracle GoldenGate supports capture from tables enabled with PAGE and ROW compression. For partitioned tables that use compression, all partitions must be enabled with the same compression type.

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

• 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 the SQL Server procedure sys.sp.cdc_enable_table, then ADD TRANDATA cannot be added 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.

Non-Supported Objects and Operations for SQL Server

The following objects and operations are not supported:

• Consider a parent-child relationship between two tables, where a primary key in the parent table is referenced by a foreign key in the child table with the ON UPDATE SET DEFAULT clause. The deferred PK update on the parent table results in DELETE and INSERT pairs. It is not possible to reliably identify the corresponding DELETE and INSERT operations on the parent table. When such a deferred update is performed on the parent table, Oracle GoldenGate Extract may not reliably capture all events.

• For source databases, operations that are not supported by SQL Server Change Data Capture, such as TRUNCATE statements. Refer to Microsoft SQL Server Documentation for a complete list of the operations that are limited by enabling SQL Server Change Data Capture.

• Oracle GoldenGate for SQL Server does not support the capture or delivery of DDL changes for SQL Server and extra steps are required for Oracle GoldenGate processes on the source and target to handle any table level DDL changes, including table index rebuild operations. See Requirements for Table Level DDL Changes.

• Views are not supported.

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

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

• Partition switches against a source table. SQL Server Change Data Capture treats partition switches as DDL operations, and the data moved from one partition to another is not logged in the CDC tables, so you must follow the procedures in Requirements for Table Level DDL Changes to manually implement a partition switch when the table is enabled for supplemental logging.

• Due to a limitation with SQL Server’s Change Data Capture, column level collations that are different from the database collation, may cause incorrect data to be written to the CDC tables for character data and Extract will capture them as they are written to the CDC tables. It is recommended that you use NVARCHAR, NCHAR or NTEXT data type for columns containing non-ASCII data or use the same collation for table columns as the database. For more information see, About Change Data Capture (SQL Server).

• Due to a limitation with SQL Server’s Change Data Capture, NOOPUPDATES are not captured by the SQL Server Change Data Capture agent so there are no records for Extract to capture for no-op update operations.

• Temporal tables are not supported for enabling Change Data Capture, therefore cannot be configured for Extract for source implementations.

Requirements for Table Level DDL Changes

Oracle GoldenGate for SQL Server does not support the capture or delivery of DDL changes. However, beginning with Oracle GoldenGate 21c, changes made to tables enabled with TRANDATA will not cause Extract to abend. Extract will continue to process change data for the table as it existed when TRANDATA was enabled.

Operations considered to be table-level DDL changes include, but are not limited to: ALTER TABLE, TRUNCATE TABLE, index rebuilds, and partition switches.

To avoid data inconsistencies due to table level DDL changes, the following steps are required.

1. Source: Pause or Stop application data to the table or tables to be modified.

2. Source: Ensure that there are no open transactions against the table to be modified.

3. Source: Ensure that the SQL Server CDC Capture job processes all remaining transactions for the table that is to be modified.

4. Source: Ensure that the Extract processes all the transactions for the table that is to be modified, prior to making any DDL changes.

5. Target: Ensure that the Replicat processes all the transactions for the table that is to be modified, prior to making any DDL changes.

6. Optionally, implementing an Event Marker table can be used to determine when all of the remaining transactions have been processed for the table that is to be modified, and handle the coordination of when to correctly stop the Extract and Replicat.

7. Source: Stop the Extract process.

8. Target: Stop the Replicat process.

9. Source: Disable supplemental logging for the table to be modified by running DELETE TRANDATA.

10. Source: Make table DDL changes to the source table.

11. Target: Make table DDL changes to the target table.

12. Source: Re-enable supplemental logging by running ADD TRANDATA to the table(s) after the modifications have been performed.

13. Source: Start the Extract.

14. Target: Start the Replicat.

15. Source: Resume application data to the table or tables that were modified.

System Schemas for SQL Server

The following system schemas are not supported for Replication:

• sys

• cdc

• INFORMATION_SCHEMA

The guest schema is not supported using wildcard mappings, but is supported when explicitly listed, such as TABLE guest.employees.