Sources
SQL Server
Check which SQL Server versions SQL Flow supports and choose between DirectLog, Reconstruction, Polling and Native CDC.
SQL Flow captures changes from SQL Server 2016 SP2 and later in four capture modes. They differ in where they read changes from, what they need on the database host, and what the login must be allowed to do.
Supported versions
| Version | Supported |
|---|---|
| SQL Server 2016 SP2 (13.0.5026) and later | Yes, in every capture mode |
| SQL Server 2016 RTM and SP1, and every older release | No. The source test and every start refuse them by name. |
| Reconstruction on SQL Server 2016 SP2 and 2017 | Yes, with a signed helper procedure that a sysadmin installs once. See Reconstruction. |
| Azure SQL Managed Instance | Yes, added as a SQL Server source, with Native CDC or Polling. DirectLog and Reconstruction need a log broker on the database host, which Managed Instance doesn't allow. |
| Azure SQL Database and Hyperscale | Use the Azure SQL source. |
An unsupported version is refused with this message:
SQL Server 2016 SP2 (13.0.5026) or later (the source reports version <version>, which is SQL Server 2016 RTM or SP1; apply Service Pack 2). Azure SQL Database, Managed Instance and Hyperscale are supported.DirectLog and Reconstruction also need the SQL Server host to run Windows Server 2016 or later, or Linux, because the log broker runs there. DirectLog needs Standard, Enterprise or Developer edition, because Express and Web can't mark tables for capture.
Capture modes
| Mode | What it reads | What you install on the database host |
|---|---|---|
| DirectLog | The transaction log file (.ldf), through a log broker. The tables are marked for capture with SQL Server's CDC procedures. | A log broker |
| Reconstruction | The same .ldf through the same broker, with no CDC and no marking. Some updates carry the row's current value rather than every intermediate version. | A log broker |
| Polling | The log records, returned by sys.fn_dblog over the ordinary connection. | Nothing |
| Native CDC | SQL Server's own change tables under the cdc schema, filled by the CDC capture job. | Nothing |
The wizard selects DirectLog by default when you choose a SQL Server source. You change the mode with the Capture mode buttons on the Source step.
Choosing a capture mode
- Can you install a log broker on the SQL Server host? Managed services such as Amazon RDS don't allow it.
- No: go to question 4.
- Yes: go to question 2.
- Is the database encrypted with TDE? DirectLog and Reconstruction read the raw log, which TDE encrypts on disk.
- Yes: go to question 4.
- No: go to question 3.
- May the database have CDC enabled and the selected tables marked for capture, by a login that is
db_owner?- Yes: use DirectLog. It reads every change from the log, with lag in milliseconds and no query work on the server to find changes.
- No: use Reconstruction. It needs no CDC and no database role, and the target converges to the source rather than replaying every intermediate version of a row. On SQL Server 2016 SP2 and 2017, a
sysadmininstalls a signed helper procedure once.
- Is CDC already enabled, or may it be enabled, with the capture job running?
- Yes: use Native CDC.
- No: use Polling. Its login needs
sysadminordb_owner, and every read costs the server query work.
| DirectLog | Reconstruction | Polling | Native CDC | |
|---|---|---|---|---|
| Reads from | .ldf via log broker | .ldf via log broker | sys.fn_dblog | cdc change tables |
| Log broker on the host | Required | Required | Not used | Not used |
| CDC on the database | Enabled; tables marked | Not used | Not used | Enabled on database and tables |
| Login role | db_owner | None, specific grants | sysadmin or db_owner | None, SELECT on the tables and the cdc schema |
| Lag | Milliseconds | Reads the log as it is written, like DirectLog | Seconds | The capture job's scan interval |
| Source load | Log bytes, plus off-row values over SQL | Log bytes, plus keyed reads of current rows | sys.fn_dblog query work for every read | The capture job, plus reads of the change tables |
| Holds the source log | Yes, until the target commits; released on pause | No | No | No; the CDC capture job does |
| Every intermediate update version | Yes | No: converges to the current row | Yes | Yes |
| TDE database | Refused | Refused | Supported | Supported |
sql_variant into SQL Server and Azure SQL targets | Yes, outside the key | Yes, outside the key | Refused | Refused |
| Tables with a column set | Refused | Refused | Supported | Refused by SQL Server's CDC |
| CLR user-defined types | Captured as bytes | Refused | Captured as bytes | Refused |
| Recovery model | FULL with a log backup chain | FULL with a log backup chain | FULL with a log backup chain | Any |
| Minimum version | 2016 SP2 | 2016 SP2 (a signed helper on 2016 SP2 and 2017) | 2016 SP2 | 2016 SP2 |
See Choosing a capture mode for the comparison across every source engine.
Encrypted data
| DirectLog | Reconstruction | Polling | Native CDC | |
|---|---|---|---|---|
| Transparent Data Encryption (TDE) | Refused | Refused | Supported | Supported |
| Always Encrypted columns | Refused by default; ciphertext copy as an opt-in | Refused | Refused | Refused |
| Encrypted connection | Always | Always | Always | Always |
- TDE. DirectLog and Reconstruction read the raw
.ldf, which TDE encrypts on disk, so they refuse a TDE database. Polling and Native CDC read the log through the engine, which decrypts it. - Always Encrypted. Every mode refuses a selected table with an Always Encrypted column. DirectLog alone can copy the ciphertext unchanged to a SQL Server or Azure SQL target that uses the same column encryption keys. See DirectLog: Encrypted data.
- Connections. The agent always encrypts its connection to SQL Server. With Encryption at Require (default), it also validates the server certificate. For an on-premises server with a self-signed certificate, set Encryption to Prefer: the connection stays encrypted, but the certificate isn't checked. A host name under
.database.windows.net, such as a Managed Instance, is always validated.
See Encrypted data support for every engine.
