<commit_before>= {prodname} Connector for SQL Server

ifndef::cdc-product[]
include::../_attributes.adoc[]
:toc:
:toc-placement: macro
:linkattrs:
:icons: font
:source-highlighter: highlight.js

[NOTE]
====
Want to help us further hone and improve it? link:/docs/contribute/[Learn how].
====

toc::[]
endif::cdc-product[]

ifdef::cdc-product[]
[IMPORTANT]
====
Technology Preview features are not supported with Red Hat production service-level agreements (SLAs) and might not be functionally complete; therefore, Red Hat does not recommend implementing any Technology Preview features in production environments. This Technology Preview feature provides early access to upcoming product innovations, enabling you to test functionality and provide feedback during the development process.
For more information about support scope, see link:https://access.redhat.com/support/offerings/techpreview/[Technology Preview Features Support Scope^].
====
endif::cdc-product[]

{prodname}'s SQL Server Connector can monitor and record the row-level changes in the schemas of a SQL Server database.

The first time it connects to a SQL Server database/cluster, it reads a consistent snapshot of all of the schemas.
When that snapshot is complete, the connector continuously streams the changes that were committed to SQL Server and generates corresponding insert, update and delete events.
All of the events for each table are recorded in a separate Kafka topic, where they can be easily consumed by applications and services.

[[sqlserver-overview]]
== Overview

The functionality of the connector is based upon https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017[change data capture] feature provided by SQL Server Standard (https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/[since SQL Server 2016 SP1]) or Enterprise edition.
Using this mechanism a SQL Server capture process monitors all databases and tables the user is interested in and stores the changes into specifically created _CDC_ tables that have stored procedure facade.
The connector has been tested with SQL Server 2017, but community members have reportedly used it successfully with earlier versions up to 2014, too (as long as the CDC feature is provided).

The database operator must https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server?view=sql-server-2017[enable] _CDC_ for the table(s) that should be captured by the connector.
The connector then produces a _change event_ for every row-level insert, update, and delete operation that was published via the _CDC API_, recording all the change events for each table in a separate Kafka topic.
The client applications read the Kafka topics that correspond to the database tables they're interested in following, and react to every row-level event it sees in those topics.

The database operator normally enables _CDC_ in the mid-life of a database an/or table.
This means that the connector won't have the complete history of all changes that have been made to the database.
Therefore, when the SQL Server connector first connects to a particular SQL Server database, it starts by performing a _consistent snapshot_ of each of the database schemas.
After the connector completes the snapshot, it continues streaming changes from the exact point at which the snapshot was made.
This way, we start with a consistent view of all of the data, yet continue reading without having lost any of the changes made while the snapshot was taking place.

The connector is also tolerant of failures.
As the connector reads changes and produces events, it records the position in the database log (_LSN / Log Sequence Number_), that is associated with _CDC_ record, with each event.
If the connector stops for any reason (including communication failures, network problems, or crashes), upon restart it simply continues reading the _CDC_ tables where it last left off.
This includes snapshots: if the snapshot was not completed when the connector is stopped, upon restart it begins a new snapshot.

[[setting-up-sqlserver]]
== Setting up SQL Server

Before using the SQL Server connector to monitor the changes committed on SQL Server, first enable _CDC_ on a monitored database.
Please bear in mind that _CDC_ cannot be enabled for `master` database.
[source,sql]
----
-- ====
-- Enable Database for CDC template
-- ====
USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GO
----

Then enable _CDC_ for each table that you plan to monitor
[source,sql]
----
-- =========
-- Enable a Table Specifying Filegroup Option Template
-- =========
USE MyDB
GO

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@role_name     = N'MyRole',
@filegroup_name = N'MyDB_CT',
@supports_net_changes = 1
GO
----

Verify that the user have access to the _CDC_ table.
[source, sql]
----
-- =========
-- Verify the user of the connector have access, this query should not have empty result
-- =========

EXEC sys.sp_cdc_help_change_data_capture
GO
----
If the result is empty then please make sure that the user has privileges to access both the capture instance and _CDC_ tables.

[[azure]]
=== SQL Server on Azure

The SQL Server plug-in has not been tested with SQL Server on Azure.
We welcome any feedback from a user to try the plug-in with database in managed environment.

ifndef::cdc-product[]
[[always-on-replica]]
=== SQL Server Always On

The SQL Server plug-in can capture changes from an Always On read-only replica.
Few pre-requisities are necessary to be fulfilled

* Change data capture is configured and enabled on the master node.
SQL Server does not support CDC directly on replicas.
* The configuration option `database.applicationIntent` must be set to `ReadOnly`.
This is required by SQL Server.
When Debezium detects this configuration option then it will:

** set `snapshot.isolation.mode` to `snapshot` as this is the only one transaction isolation mode supported by raed-only replicas
** commit the (read-only) transaction in every execution of the streaming query loop, as this is necessary to get the latest view on CDC data
endif::cdc-product[]

[[how-the-connector-works]]
== How the SQL Server connector works

=== Snapshots

SQL Server CDC is not designed to store the complete history of database changes.
It is thus necessary that {prodname} establishes the baseline of current database content and streams it to the Kafka.
This is achieved via a process called snapshotting.

By default (snapshotting mode *initial*) the connector will upon the first startup perform an initial _consistent snapshot_ of the database
(meaning the structure and data within any tables to be captured as per the connector's filter configuration).

Each snapshot consists of the following steps:

1. Determine the tables to be captured
2. Obtain a lock on each of the monitored tables to ensure that no structural changes can occur to any of the tables.
The level of the lock is determined by `snapshot.isolation.mode` configuration option.
3. Read the maximum LSN ("log sequence number") position in the server's transaction log.
4. Capture the structure of all relevant tables.
5. Optionally release the locks obtained in step 2, i.e. the locks are held usually only for a short period of time.
6. Scan all of the relevant database tables and schemas as valid at the LSN position read in step 3, and generate a `READ` event for each row and write that event to the appropriate table-specific Kafka topic.
7. Record the successful completion of the snapshot in the connector offsets.

=== Reading the change data tables

Upon first start-up, the connector takes a structural snapshot of the structure of the captured tables
and persists this information in its internal database history topic.
Then the connector identifies a change table for each of the source tables and executes the main loop

1. For each change table read all changes that were created between last stored maximum LSN and current maximum LSN
2. Order the read changes incrementally according to commit LSN and change LSN.
This ensures that the changes are replayed by {prodname} in the same order as were made to the database.
3. Pass commit and change LSNs as offsets to Kafka Connect.
4. Store the maximum LSN and repeat the loop.

After a restart, the connector will resume from the offset (commit and change LSNs) where it left off before.

The connector is able to detect whether the CDC is enabled or disabled for whitelisted source table during the runtime and modify its behaviour.

=== Topic names

The SQL Server connector writes events for all insert, update, and delete operations on a single table to a single Kafka topic. The name of the Kafka topics always takes the form _serverName_._schemaName_._tableName_, where _serverName_ is the logical name of the connector as specified with the `database.server.name` configuration property, _schemaName_ is the name of the schema where the operation occurred, and _tableName_ is the name of the database table on which the operation occurred.

For example, consider a SQL Server installation with an `inventory` database that contains four tables: `products`, `products_on_hand`, `customers`, and `orders` in schema `dbo`. If the connector monitoring this database were given a logical server name of `fulfillment`, then the connector would produce events on these four Kafka topics:

* `fulfillment.dbo.products`
* `fulfillment.dbo.products_on_hand`
* `fulfillment.dbo.customers`
* `fulfillment.dbo.orders`

=== Schema change topic

The user-facing schema change topic is not implemented yet (see {jira-url}/browse/DBZ-753[DBZ-753]).

=== Events

All data change events produced by the SQL Server connector have a key and a value, although the structure of the key and value depend on the table from which the change events originated (see link:#topic-names[Topic names]).

[WARNING]
====
The SQL Server connector ensures that all Kafka Connect _schema names_ are http://avro.apache.org/docs/current/spec.html#names[valid Avro schema names].
This means that the logical server name must start with Latin letters or an underscore (e.g., [a-z,A-Z,\_]),
and the remaining characters in the logical server name and all characters in the schema and table names must be Latin letters, digits, or an underscore (e.g., [a-z,A-Z,0-9,\_]).
If not, then all invalid characters will automatically be replaced with an underscore character.

This can lead to unexpected conflicts when the logical server name, schema names, and table names contain other characters, and the only distinguishing characters between table full names are invalid and thus replaced with underscores.
====

{prodname} and Kafka Connect are designed around _continuous streams of event messages_, and the structure of these events may change over time.
This could be difficult for consumers to deal with, so to make it easy Kafka Connect makes each event self-contained.
Every message key and value has two parts: a _schema_ and _payload_.
The schema describes the structure of the payload, while the payload contains the actual data.

[[change-event-keys]]<commit_msg>DBZ-1903 Linking to correct issue in SQL Server docs<commit_after>= {prodname} Connector for SQL Server

ifndef::cdc-product[]
include::../_attributes.adoc[]
:toc:
:toc-placement: macro
:linkattrs:
:icons: font
:source-highlighter: highlight.js

[NOTE]
====
Want to help us further hone and improve it? link:/docs/contribute/[Learn how].
====

toc::[]
endif::cdc-product[]

ifdef::cdc-product[]
[IMPORTANT]
====
Technology Preview features are not supported with Red Hat production service-level agreements (SLAs) and might not be functionally complete; therefore, Red Hat does not recommend implementing any Technology Preview features in production environments. This Technology Preview feature provides early access to upcoming product innovations, enabling you to test functionality and provide feedback during the development process.
For more information about support scope, see link:https://access.redhat.com/support/offerings/techpreview/[Technology Preview Features Support Scope^].
====
endif::cdc-product[]

{prodname}'s SQL Server Connector can monitor and record the row-level changes in the schemas of a SQL Server database.

The first time it connects to a SQL Server database/cluster, it reads a consistent snapshot of all of the schemas.
When that snapshot is complete, the connector continuously streams the changes that were committed to SQL Server and generates corresponding insert, update and delete events.
All of the events for each table are recorded in a separate Kafka topic, where they can be easily consumed by applications and services.

[[sqlserver-overview]]
== Overview

The functionality of the connector is based upon https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017[change data capture] feature provided by SQL Server Standard (https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/[since SQL Server 2016 SP1]) or Enterprise edition.
Using this mechanism a SQL Server capture process monitors all databases and tables the user is interested in and stores the changes into specifically created _CDC_ tables that have stored procedure facade.
The connector has been tested with SQL Server 2017, but community members have reportedly used it successfully with earlier versions up to 2014, too (as long as the CDC feature is provided).

The database operator must https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server?view=sql-server-2017[enable] _CDC_ for the table(s) that should be captured by the connector.
The connector then produces a _change event_ for every row-level insert, update, and delete operation that was published via the _CDC API_, recording all the change events for each table in a separate Kafka topic.
The client applications read the Kafka topics that correspond to the database tables they're interested in following, and react to every row-level event it sees in those topics.

The database operator normally enables _CDC_ in the mid-life of a database an/or table.
This means that the connector won't have the complete history of all changes that have been made to the database.
Therefore, when the SQL Server connector first connects to a particular SQL Server database, it starts by performing a _consistent snapshot_ of each of the database schemas.
After the connector completes the snapshot, it continues streaming changes from the exact point at which the snapshot was made.
This way, we start with a consistent view of all of the data, yet continue reading without having lost any of the changes made while the snapshot was taking place.

The connector is also tolerant of failures.
As the connector reads changes and produces events, it records the position in the database log (_LSN / Log Sequence Number_), that is associated with _CDC_ record, with each event.
If the connector stops for any reason (including communication failures, network problems, or crashes), upon restart it simply continues reading the _CDC_ tables where it last left off.
This includes snapshots: if the snapshot was not completed when the connector is stopped, upon restart it begins a new snapshot.

[[setting-up-sqlserver]]
== Setting up SQL Server

Before using the SQL Server connector to monitor the changes committed on SQL Server, first enable _CDC_ on a monitored database.
Please bear in mind that _CDC_ cannot be enabled for `master` database.
[source,sql]
----
-- ====
-- Enable Database for CDC template
-- ====
USE MyDB
GO
EXEC sys.sp_cdc_enable_db
GO
----

Then enable _CDC_ for each table that you plan to monitor
[source,sql]
----
-- =========
-- Enable a Table Specifying Filegroup Option Template
-- =========
USE MyDB
GO

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'MyTable',
@role_name     = N'MyRole',
@filegroup_name = N'MyDB_CT',
@supports_net_changes = 1
GO
----

Verify that the user have access to the _CDC_ table.
[source, sql]
----
-- =========
-- Verify the user of the connector have access, this query should not have empty result
-- =========

EXEC sys.sp_cdc_help_change_data_capture
GO
----
If the result is empty then please make sure that the user has privileges to access both the capture instance and _CDC_ tables.

[[azure]]
=== SQL Server on Azure

The SQL Server plug-in has not been tested with SQL Server on Azure.
We welcome any feedback from a user to try the plug-in with database in managed environment.

ifndef::cdc-product[]
[[always-on-replica]]
=== SQL Server Always On

The SQL Server plug-in can capture changes from an Always On read-only replica.
Few pre-requisities are necessary to be fulfilled

* Change data capture is configured and enabled on the master node.
SQL Server does not support CDC directly on replicas.
* The configuration option `database.applicationIntent` must be set to `ReadOnly`.
This is required by SQL Server.
When Debezium detects this configuration option then it will:

** set `snapshot.isolation.mode` to `snapshot` as this is the only one transaction isolation mode supported by raed-only replicas
** commit the (read-only) transaction in every execution of the streaming query loop, as this is necessary to get the latest view on CDC data
endif::cdc-product[]

[[how-the-connector-works]]
== How the SQL Server connector works

=== Snapshots

SQL Server CDC is not designed to store the complete history of database changes.
It is thus necessary that {prodname} establishes the baseline of current database content and streams it to the Kafka.
This is achieved via a process called snapshotting.

By default (snapshotting mode *initial*) the connector will upon the first startup perform an initial _consistent snapshot_ of the database
(meaning the structure and data within any tables to be captured as per the connector's filter configuration).

Each snapshot consists of the following steps:

1. Determine the tables to be captured
2. Obtain a lock on each of the monitored tables to ensure that no structural changes can occur to any of the tables.
The level of the lock is determined by `snapshot.isolation.mode` configuration option.
3. Read the maximum LSN ("log sequence number") position in the server's transaction log.
4. Capture the structure of all relevant tables.
5. Optionally release the locks obtained in step 2, i.e. the locks are held usually only for a short period of time.
6. Scan all of the relevant database tables and schemas as valid at the LSN position read in step 3, and generate a `READ` event for each row and write that event to the appropriate table-specific Kafka topic.
7. Record the successful completion of the snapshot in the connector offsets.

=== Reading the change data tables

Upon first start-up, the connector takes a structural snapshot of the structure of the captured tables
and persists this information in its internal database history topic.
Then the connector identifies a change table for each of the source tables and executes the main loop

1. For each change table read all changes that were created between last stored maximum LSN and current maximum LSN
2. Order the read changes incrementally according to commit LSN and change LSN.
This ensures that the changes are replayed by {prodname} in the same order as were made to the database.
3. Pass commit and change LSNs as offsets to Kafka Connect.
4. Store the maximum LSN and repeat the loop.

After a restart, the connector will resume from the offset (commit and change LSNs) where it left off before.

The connector is able to detect whether the CDC is enabled or disabled for whitelisted source table during the runtime and modify its behaviour.

=== Topic names

The SQL Server connector writes events for all insert, update, and delete operations on a single table to a single Kafka topic. The name of the Kafka topics always takes the form _serverName_._schemaName_._tableName_, where _serverName_ is the logical name of the connector as specified with the `database.server.name` configuration property, _schemaName_ is the name of the schema where the operation occurred, and _tableName_ is the name of the database table on which the operation occurred.

For example, consider a SQL Server installation with an `inventory` database that contains four tables: `products`, `products_on_hand`, `customers`, and `orders` in schema `dbo`. If the connector monitoring this database were given a logical server name of `fulfillment`, then the connector would produce events on these four Kafka topics:

* `fulfillment.dbo.products`
* `fulfillment.dbo.products_on_hand`
* `fulfillment.dbo.customers`
* `fulfillment.dbo.orders`

=== Schema change topic

The user-facing schema change topic is not implemented yet (see {jira-url}/browse/DBZ-1904[DBZ-1904]).

=== Events

All data change events produced by the SQL Server connector have a key and a value, although the structure of the key and value depend on the table from which the change events originated (see link:#topic-names[Topic names]).

[WARNING]
====
The SQL Server connector ensures that all Kafka Connect _schema names_ are http://avro.apache.org/docs/current/spec.html#names[valid Avro schema names].
This means that the logical server name must start with Latin letters or an underscore (e.g., [a-z,A-Z,\_]),
and the remaining characters in the logical server name and all characters in the schema and table names must be Latin letters, digits, or an underscore (e.g., [a-z,A-Z,0-9,\_]).
If not, then all invalid characters will automatically be replaced with an underscore character.

This can lead to unexpected conflicts when the logical server name, schema names, and table names contain other characters, and the only distinguishing characters between table full names are invalid and thus replaced with underscores.
====

{prodname} and Kafka Connect are designed around _continuous streams of event messages_, and the structure of these events may change over time.
This could be difficult for consumers to deal with, so to make it easy Kafka Connect makes each event self-contained.
Every message key and value has two parts: a _schema_ and _payload_.
The schema describes the structure of the payload, while the payload contains the actual data.

[[change-event-keys]]
{'fraction_non_alphanumeric': 0.060022148394241415, 'fraction_numerical': 0.00460686600221484, 'mean_word_length': 4.724137931034483, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 8, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '675160', 'n_tokens_mistral': 5705, 'n_tokens_neox': 5425, 'n_words': 3193}