18 Understanding What's Supported for SQL Server

This chapter contains information on database and table features supported by Oracle GoldenGate for SQL Server.

Topics:

18.1 Supported Objects and Operations for SQL Server

The following objects and operations are supported:

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

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

18.2 Non-Supported Objects and Operations for SQL Server

The following objects and operations are not supported:

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

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

18.2.1 Requirements for Table Level DDL Changes

Oracle GoldenGate for SQL Server, including table index rebuild operations, does not support the capture or delivery of DDL changes for SQL Server. Extra steps are required for Oracle GoldenGate processes on the source and target to handle any table-level DDL changes.

Operations considered to be table level DDL changes include, but are not limited to ALTER TABLE, TRUNCATE, 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.

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

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

18.4 System Schemas for SQL Server

The following schemas or objects are not be automatically replicated by Oracle GoldenGate unless they are explicitly specified without a wildcard.

  • "sys"

  • "cdc"

  • "INFORMATION_SCHEMA"

  • "guest"

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