Microsoft SQL Server and Azure SQL Database targets

• •Database Ingestion and Replication does not support Microsoft SQL Server as a target in jobs that replicate data from Teradata sources.
• •The SQL Server JDBC driver is delivered with Database Ingestion and Replication. You do not need to install it separately.
• •The Database Ingestion and Replication user requires following database roles, at minimum, to create target tables and write data to the tables:
• - db_datareader
• - db_datawriter
• - db_ddladmin
• •In Administrator, when you define a SQL Server connection for connecting to a SQL Server target, set the following required connection properties:
• - SQL Server Version. Select either SQL Server 2017 or SQL Server 2019.
• - Authentication Mode. Select SQL Server Authentication or Windows Authentication v2.
• - User Name
• - Password
• - Host
• - Port
• - Database Name
• - Schema
• - Code Page
Optionally, to use SSL encryption for the target connection, you can set the Encryption Method property to one of the following options: SSL, Request SSL, or Login SSL. If you enable encryption, also set the following properties:
• - Crypto Protocol Version
• - Validate Server Certificate. Select True.
• - Trust Store
• - Trust Store Password
• - Host Name in Certificate
Other connection properties are not supported.
• •Database ingestion and replication incremental load and initial and incremental load jobs with a SQL Server target generate a LOG table based on the target table schema, with some additional metadata columns. The LOG table is created right before change data is flushed to the target. The incoming DML data is inserted to the LOG table by supplying a local CSV file to the Bulk Copy API of the SQL Server driver. A merge apply statement is generated based on the information in the LOG table, and then the DML operations are applied to the actual target table. After the DML changes are applied, the LOG table is dropped.
The LOG table might cause a temporary spike in additional space or size requirements in the customer database instance if you run multiple jobs or a job with multiple tables. The space and size required by the LOG table depends on the number of rows received as part of a flush cycle.
• •The number of columns in a source table that a database ingestion and replication incremental load or an initial and incremental load job can propagate to a SQL Server target must not exceed 508 columns. If a source table contains more than 508 columns, the job fails while creating the LOG table.
• •Database ingestion and replication incremental load jobs and combined initial and incremental load jobs generate a recovery table named INFORMATICA_CDC_RECOVERY on the target to store internal service information that prevents jobs restarted after a failure from propagating previously processed data again. This recovery table is generated in the same schema as the target tables.