SQL Server

Replicate into SQL Server, set up the target login, see what SQL Flow creates there, and write into Always Encrypted columns.

SQL Flow applies changes to a SQL Server database with transactional, exactly-once writes. The same target type covers SQL Server on-premises and on virtual machines; use the Azure SQL target for Azure SQL Database and Hyperscale.

Requirements

Permissions

The target login needs these permissions in the target database. Membership in db_owner covers all of them.

Replace <database_name> and <user_name>, and repeat the last statement for each schema that holds target tables:

USE [<database_name>];
GRANT CREATE SCHEMA, CREATE TABLE, CREATE TYPE TO [<user_name>];
GRANT VIEW DEFINITION TO [<user_name>];
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON SCHEMA::[dbo] TO [<user_name>];

When the login lacks a bookkeeping permission, the start is refused with every missing permission and the GRANT that fixes it, for example:

target_bookkeeping_schema_not_ready: the target user [<user_name>] cannot create the bookkeeping schema [sqlflow], which does not exist yet: it lacks CREATE SCHEMA in the database. SqlFlow keeps its apply ledger and its scope registry, table types, staging tables and LOB control table there. Grant: GRANT CREATE SCHEMA TO [<user_name>]; Alternatively a database administrator creates it for that user: CREATE SCHEMA [sqlflow] AUTHORIZATION [<user_name>];

What SQL Flow creates on the target

Your tables

SQL Flow never creates or changes your tables during a load. It creates them only when you ask:

• On the wizard's Prepare step, select Prepare target schema. Select Show SQL first to read exactly what will run.
• When you add a target to an existing pipeline, choose the offered create action, such as Create tables & keys.

Each missing table is created with the source columns and a primary key in source key order, in a schema that is created if needed. An existing table without a matching key gets the primary key, or a unique index named ux_<table>_<columns> when it already has a different primary key. Preparing never drops, truncates or otherwise alters an existing table, and never adds missing columns: fix those by hand.

When the initial load finds rows in a target table, the wizard asks Replace existing target rows?. Replace rows and start empties those tables, then loads them.

After the start, source column changes are applied to the target tables. See Schema changes.

The bookkeeping schema

Everything SQL Flow needs for itself lives in one schema, sqlflow by default; nothing is created in your schemas. It holds the apply ledger (which source transactions are applied, which makes apply exactly-once), the committed position of each pipeline, initial-load progress, staging tables and table types for set-based apply, schema changes waiting for review, and the large-object control table. Don't change or drop these objects.

To use another schema, enter it in Bookkeeping schema on the Target step. It can't change after the pipeline is created. The name must be one unquoted identifier: an ASCII letter or underscore, then letters, digits or underscores, at most 128 characters. System schemas such as sys, INFORMATION_SCHEMA and guest, and the schemas of the fixed database roles such as db_owner, are refused.

A ledger left by an older SQL Flow version is refused with claim_ledger_shape_unsupported. Stop every pipeline that applies into the database with the older version, then drop the two tables the message names; the next start creates the current ledger.

Options

• Collapse row versions (faster). Untick it if a target trigger or audit must see every change. Even when it is ticked, the Target apply plan applies every change in order for tables with enabled triggers, temporal, ledger and memory-optimized tables, and when SQL Flow can't see the whole catalog.
• Atomic transaction limits. Apply stops if one source transaction exceeds either limit, keeping the transaction intact for recovery.
• Allow streamed large-object loads despite triggers. Large values load as an INSERT plus one update each. Tick it only if the target's triggers accept that.

Set connection tuning with the CLI's --opt. The wizard has no fields for these:

Always Encrypted targets

SQL Flow can write into Always Encrypted columns of a target table. The apply agent encrypts each plaintext value with the column's keys before it reaches SQL Server. A source can't be decrypted on the way: an Always Encrypted source column is refused, except for the DirectLog ciphertext copy described below.

Encrypt plaintext into encrypted columns

1. Create the column master key and column encryption keys, and the encrypted target columns, in the target database. SQL Flow doesn't create keys.
2. Make the column master key available to the apply agent:
   • Certificate: install the certificate with its private key in the Windows certificate store location that the column master key's path names, on the apply agent's host, readable by the agent's service account. The certificate store works only on Windows agents.
   • Azure Key Vault: give the agent's identity the key permissions get, unwrapKey and verify on the column master key.
3. Set the target options with the CLI, for example sqlflow connection edit <name> --opt alwaysEncrypted=encrypt --opt alwaysEncryptedKeyStore=azurekeyvault --opt alwaysEncryptedKeyVaultCredential=managedidentity.
4. Untick Collapse row versions (faster), or set applyMode=ordered. Encrypted columns need ordered, row-by-row apply.

Supported encrypted columns. Deterministic or randomized encryption with AEAD_AES_256_CBC_HMAC_SHA_256, on columns of type bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, date, datetime, smalldatetime, datetime2, datetimeoffset, time, char, varchar, nchar, nvarchar, binary, varbinary and uniqueidentifier. A value longer than the column, or one that would lose precision in it, stops the apply rather than being truncated.

Ordered apply only. Both the initial load and streaming write encrypted columns row by row. A setting that conflicts with that is refused:

always_encrypted_target_route: encrypt requires ordered scalar apply without bulk load, grouped apply or LOB projection; TVPs cannot target encrypted columns.

Copy ciphertext from a DirectLog source

With alwaysEncrypted=ciphertext on both the DirectLog source and the target, the ciphertext is copied without being decrypted. The source and target columns must use the same column encryption key and have the same plaintext type, size and collation; otherwise the copy is refused. Replication keys must be unencrypted, every target must be a SQL Server or Azure SQL target on a local apply agent, and a relay is refused.

Limitations

• Keys. A target table without a primary key or unique key that matches the source key is refused at the start.
• Key columns. rowversion, timestamp, hierarchyid, sql_variant, xml, json, geography, geometry, vector, and unbounded columns (MAX, text, ntext, image) can't be part of the key that Prepare target schema creates. The Prepare step refuses such a key column by name; deselect its table.
• Types. CLR user-defined types are refused. sql_variant is supported outside the key from a DirectLog or Reconstruction source, and needs ordered apply (untick Collapse row versions (faster)); see Data type mapping. A source collation that the target server doesn't have is refused; UTF-8 collations need SQL Server 2019 or later.
• Existing tables. Missing columns aren't added to an existing table. A table that another pipeline writes to is refused.

See also

• Azure SQL target
• SQL database in Fabric target
• Initial load
• Data type mapping
• Multiple targets