Prepare a SQL Server target so snapshots and CDC run without truncation or type errors

On the source PostgreSQL database, run:

CREATE SCHEMA mt_demo;

CREATE TABLE mt_demo.customers_demo (
  id integer PRIMARY KEY, name varchar(100), notes text, ref uuid,
  payload jsonb, photo bytea, credit numeric(12,2),
  created_at timestamp, active boolean);
INSERT INTO mt_demo.customers_demo VALUES
  (1,'Acme Spa','first notes','2f8c7d2e-6b1a-4c5e-9d0f-1a2b3c4d5e6f',
   '{"tier":"gold"}','\xdeadbeef',1500.50,'2026-10-28 10:00:00',true);

CREATE TABLE mt_demo.trunc_demo (
  id integer PRIMARY KEY, name varchar(200), note text, city varchar(30),
  price numeric(12,2), big bigint, born timestamp, active boolean);
INSERT INTO mt_demo.trunc_demo VALUES
  (1,'Short name','short note','Rome',10.50,100,'2026-01-01 10:00:00',true);

CREATE TABLE mt_demo.reserved_demo (
  id integer PRIMARY KEY, "order" integer, "user" varchar(50),
  "key" varchar(50), "group" varchar(50), "begin" varchar(50), "my col" varchar(50));
INSERT INTO mt_demo.reserved_demo VALUES (1,10,'alice','k1','g1','b1','m1');

CREATE TABLE mt_demo.case_demo (code varchar(30) PRIMARY KEY, label varchar(60));
INSERT INTO mt_demo.case_demo VALUES ('abc','lower'),('ABC','upper'),('ábc','accent');

On the target SQL Server, create the database and the schema, and a table that is narrower than the source on purpose:

CREATE DATABASE gsdemo COLLATE SQL_Latin1_General_CP1_CI_AS;
GO
USE gsdemo;
GO
CREATE SCHEMA demo;
GO
CREATE TABLE demo.trunc_demo (
  id int NOT NULL PRIMARY KEY,
  name varchar(30) NULL, note varchar(100) NULL, city nvarchar(30) NULL,
  price numeric(6,2) NULL, big int NULL, born datetime NULL, active bit NULL);
CREATE TABLE demo.reserved_demo (
  id int NOT NULL PRIMARY KEY,
  [order] int NULL, [user] varchar(50) NULL, [key] varchar(50) NULL,
  [group] varchar(50) NULL, [begin] varchar(50) NULL, [my col] varchar(50) NULL);
CREATE TABLE demo.case_demo (code varchar(30) NOT NULL PRIMARY KEY, label varchar(60) NULL);

Connect to the target database as an administrator and create the user:

USE gsdemo;
CREATE LOGIN gluesync_writer WITH PASSWORD = '<a strong password>';
CREATE USER gluesync_writer FOR LOGIN gluesync_writer;
ALTER ROLE db_datareader ADD MEMBER gluesync_writer;
ALTER ROLE db_datawriter ADD MEMBER gluesync_writer;

If Gluesync creates tables for you (step 3), grant the right to create a table and to use the schema:

GRANT CREATE TABLE TO gluesync_writer;
GRANT ALTER ON SCHEMA::demo TO gluesync_writer;

If you turn on bulk operations (Use bulk operations during CDC or Use bulk operations during Snapshot), Gluesync keeps its staging tables in a schema named gluesync. Create it once, owned by the same user, so that Gluesync does not need the right to create schemas:

CREATE SCHEMA gluesync AUTHORIZATION gluesync_writer;

In the Control Plane, set this user as Username of the MS SQL Server target connection.

In the Control Plane, open the pipeline and select Entities (the blue button with the plus sign above the entity list). The Add entities page opens with the Object browser;

Expand the source schema, for example mt_demo, and select the check box of the table customers_demo. The Field mapping dialog opens;

In Target Schema select demo;

In Target Table type customers and select Create table "customers". The banner New table customers will be created on confirm. appears and every source column gets a proposed target type;

Look at the Length column. The cells of notes, ref, payload and photo show 0 with a red mark, the banner says Missing required field and Confirm is disabled.

For each of the four columns, open the Target type list and choose the SQL Server type from the type mapping table in this guide: nvarchar with Length -1 for notes and payload, uniqueidentifier for ref, and varbinary with Length -1 for photo. A length of -1 means MAX;

Check that the Error tab reads Error (0), then select Confirm.

A second dialog, Create table customers, shows the exact statement. Read it, then select Create table customers. Select Copy snippet instead if your DBA must run the statement.

Select Confirm and continue to save the entity.

In the Field mapping dialog choose the existing table in Target Table instead of Create table;

Open the Error and Warning tabs. Each entry is a source and target type that do not fit, and the dialog cannot be confirmed while the Error tab is not empty (step 7 explains how to clear it);

For lengths, which the dialog does not judge, list the real target widths in T-SQL:

SELECT c.name AS col, t.name AS type,
       CASE WHEN c.max_length = -1 THEN 'MAX'
            WHEN t.name IN ('nvarchar','nchar') THEN CAST(c.max_length/2 AS varchar(10))
            ELSE CAST(c.max_length AS varchar(10)) END AS length_in_chars,
       c.precision, c.scale
FROM sys.columns c JOIN sys.types t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('demo.trunc_demo') ORDER BY c.column_id;

On the source, list the longest value of each text column and the largest number of each numeric column. For PostgreSQL:

SELECT max(length(name)) AS name_chars, max(length(note)) AS note_chars,
       max(octet_length(note)) AS note_bytes, max(price) AS max_price
FROM mt_demo.trunc_demo;

Compare the two results column by column. Every target column must be at least as wide as the longest source value, with room to grow, and every numeric target must have at least the source precision and scale.

Create an entity from mt_demo.trunc_demo to the existing demo.trunc_demo and start it with change data capture (CDC). Then, on the source, insert a long value:

INSERT INTO mt_demo.trunc_demo (id, name, note)
VALUES (40, 'Order 40', repeat('long note ', 15));

Open the entity. A red banner Target write failed names the table and the column, and shows the value that did not fit. Select Copy error to copy it, or View notifications for the notification String or binary data found truncated.

Select Compare DDL. The source CREATE TABLE is on the left and the target on the right. The row of the column in the error is the one where the target is shorter, here note text against note varchar(100).

Widen the column in SQL Server, to the longest source value with room to grow, or to MAX:

ALTER TABLE demo.trunc_demo ALTER COLUMN note varchar(300) NULL;

Do not restart anything. Wait a few seconds and check the row:

SELECT id, len(note) AS l FROM demo.trunc_demo WHERE id = 40;

For a number that does not fit, for example inserting 99999.99 into price numeric(6,2), the notification reads Arithmetic overflow error converting numeric to data type numeric. Widen the column:

ALTER TABLE demo.trunc_demo ALTER COLUMN price numeric(12,2) NULL;

For a bigint source and an int target, inserting 9000000000 gives Arithmetic overflow error converting expression to data type int. Use bigint:

ALTER TABLE demo.trunc_demo ALTER COLUMN big bigint NULL;

For a timestamp source and a datetime target, a date before 1753 gives Batch Update with The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value. (SQL Server error 242). Use datetime2, which also keeps microseconds:

ALTER TABLE demo.trunc_demo ALTER COLUMN born datetime2(6) NULL;

Select the fx button at the end of the target row;

Open Functions collection and choose the function that produces the target type: Convert Int to String for integer to varchar, Convert Boolean to String (true/false) for boolean to varchar, Convert Date and Time to String and Convert Date to String (both with a pattern) for timestamp and date, Convert Decimal to String for numeric to varchar, Convert String to Boolean for text to bit, Convert String to Date (with a pattern) for text to date, String to Byte Array (charset UTF-8) for text to varbinary, and Convert String to Decimal for text to a decimal column;

Repeat for every red row, then select Confirm.

Create an entity from mt_demo.reserved_demo to the existing demo.reserved_demo, and start it with a snapshot;

On the source, run an insert, an update and a delete:

INSERT INTO mt_demo.reserved_demo VALUES (2,20,'bob','k2','g2','b2','m2');
UPDATE mt_demo.reserved_demo SET "order" = 11, "begin" = 'b1-upd' WHERE id = 1;
DELETE FROM mt_demo.reserved_demo WHERE id = 2;

On the target, read the table:

SELECT * FROM demo.reserved_demo ORDER BY id;

Create an entity from mt_demo.case_demo to demo.case_demo and run a snapshot. On the target, read it:

SELECT code, label FROM demo.case_demo;

To keep all three keys, make the key column case sensitive. Drop the primary key, change the collation and add the key again, then delete the entity and add it again so that Gluesync reads the new collation:

DECLARE @pk sysname = (SELECT name FROM sys.key_constraints
                       WHERE parent_object_id = OBJECT_ID('demo.case_demo') AND type = 'PK');
EXEC('ALTER TABLE demo.case_demo DROP CONSTRAINT ' + @pk);
ALTER TABLE demo.case_demo ALTER COLUMN code varchar(30) COLLATE Latin1_General_100_CS_AS NOT NULL;
ALTER TABLE demo.case_demo ADD CONSTRAINT PK_case_demo PRIMARY KEY (code);
DELETE FROM demo.case_demo;

Run the snapshot of the new entity and read the table again.