Microsoft SQL Server (Microsoft CDC based)

This section explains how to set up a Microsoft SQL Server (Microsoft CDC) source in a data task. Before you start the data task, make sure that you have fulfilled the Prerequisites, set up the Required permissions, and familiarized yourself with the Limitations and considerations.

When moving data from a Microsoft SQL Server (Microsoft CDC based) source, changes are captured from Microsoft Change Tables as opposed to the SQL Server transaction log . Eliminating the need to rely on the transaction log minimizes the risk of data loss resulting from online transaction log retention time and backup transaction log inaccessibility.

Setting connection properties

This section describes the available connection properties. All properties are required unless otherwise indicated.

To open the connector, do the following:

1. In Connections, click Create connection.

2. Select the Microsoft SQL Server (Microsoft CDC based) source connector and then provide the following settings:

Data source

• Data gateway

  Select a Data Movement gateway to use for moving data.

• Cloud provider: Select one of the following:

  • None if your SQL Server is on-premises or Amazon RDS for SQL Server
  • Microsoft Azure if it is hosted on Azure (Azure SQL Managed Instance or Azure SQL Database)

• Server: The host name or IP address of the computer on which the Microsoft Azure SQL Managed Instance database is installed.

  Information note

  To override the default port, add the port to the server name, separated by a comma. For example, if the server name is myserver.company.local and the port is 3333, then the server name should be:

  myserver.company.local,3333

Account properties

User Name and Password: The user name and password of a user authorized to access the Microsoft Azure SQL Managed Instance.

Database properties

• Database name: There are two methods you can use to specify a database:

  • Method 1 - Select from a list: This method requires the user to be created on the master database. Click Load databases and then select a database.
  • Method 2 - Manually: Select Enter database name manually and then enter the database name.

CDC properties

Enable MS-CDC on all captured tables - Select this option to automatically enable MS-CDC for all selected source tables (on all columns). When this option is selected, the MS-CDC tables will be created in the database's default filegroup.

DDL properties

• Capture DDL events: When this option is selected, the following operations occur:
  • To facilitate capture of DDL events, Data Movement gateway creates triggers and other operational artifacts in the Microsoft SQL Server database when the task starts. You can later remove these artifacts as described in Removing artifacts from the source database below.
  • Streamed DDL events are captured.
• Create DDL artifacts in schema: The schema in which the DDL database artifacts will be created. The default value is dbo.

Removing artifacts from the source database

In order to capture DDLs, Data Movement gateway creates various artifacts in the Microsoft SQL Server database when the task starts. You can remove these artifacts if you decide to delete the task at some point in the future.

To remove the artifacts, execute the following statements (in the order they appear below), where dbo is the default schema in which the artifacts were created:

Internal properties

Internal properties are for special use cases and are therefore not exposed in the dialog. You should only use them if instructed by Qlik Support.

Use the and buttons to the right of the fields to add or remove properties as needed.

Name

The display name for the source connection.