Canonical: https://molo17.com/solutions/replicate-to-sql-server/ Markdown mirror: https://molo17.com/solutions/replicate-to-sql-server/index.md Title: Replicate to SQL Server in real time with Gluesync | MOLO17 Description: Real-time replication to SQL Server from Oracle, IBM i, PostgreSQL, MySQL, and Couchbase, with JDBC batches and staged SqlBulkCopy bulk cycles. Start a trial. [Solutions](/solutions/) Replicate to SQL Server SOLUTION · REPLICATE TO SQL SERVER # Real-time data replication to SQL Server from your operational databases **Committed changes from your systems of record land in SQL Server continuously, through JDBC batches and SqlBulkCopy staging, instead of waiting for the next batch window.** Gluesync by MOLO17 captures changes from Oracle, IBM i, MySQL, PostgreSQL, MongoDB, and other [heterogeneous sources](/integrations/?target=MS+SQL+Server#integration-finder) with a dedicated agent per database, then writes them to Microsoft SQL Server through a target agent built on the official SQL Server JDBC driver. Snapshots seed each table first, CDC follows, and bulk cycles stage rows with SqlBulkCopy. Core Hub, the Gluesync control plane, runs every pipeline from one web UI and REST API. [Start a Gluesync trial](/get-gluesync/) [Talk to us](/contacts/) VENDOR COMPATIBILITY ## Battle-tested on every Microsoft SQL Server you deliver to The same Gluesync Microsoft SQL Server agent is tested against each vendor offering below, self-managed or fully managed, so delivery behaves the same wherever Microsoft SQL Server runs. - **MS SQL Server** Source Target Tested - **Amazon RDS for SQL Server** Source Target Tested - **Azure SQL** Source Target Tested - **Microsoft Azure SQL Database** Source Target Tested - **Microsoft Azure SQL Server** Source Target Tested - **Microsoft Azure Synapse Analytics** Source Target Tested WHO THIS IS FOR ## Teams that need SQL Server to reflect the system of record now, not after the nightly load - Analytics and application engineers who need SQL Server tables that match the source, with primary keys carried over, identity values kept equal to the source, and a reviewed `CREATE TABLE` generated from the source definition - Data platform leads feeding SQL Server from Oracle, IBM i, PostgreSQL, MySQL, or MongoDB who want one Core Hub for every source instead of one connector per source, backed by [best-in-class enterprise support, rated 4.9/5 by customers](/support/#customer-ratings) - Architects who decide which tables use native SqlBulkCopy bulk load and which use optimized batches, which sources feed which SQL Server databases, and where each agent runs - Engineers replacing Debezium and Kafka Connect sinks, or scheduled SSIS and ETL jobs, with [one product for every source](/integrations/?target=MS+SQL+Server#integration-finder), with snapshots and monitoring built in THE PROBLEM ## SQL Server data that arrives in batches is already behind Most SQL Server targets are fed by scheduled jobs: an SSIS package, an ETL tool, or a script extracts rows from the source, loads them, and merges them into tables on a timer. Reports and applications then read the state at the last window, every extract adds load to a production database, and each source ends up with its own schedule and failure mode. Teams weighing a fix usually compare native SQL Server replication, Azure Data Factory, a Debezium and Kafka stack with a JDBC sink, Qlik Replicate or Fivetran HVR, and scripts they maintain themselves. Gluesync addresses that with **per-agent CDC into SQL Server**. A source agent reads each database's native change mechanism, Core Hub routes the changes, and the SQL Server target agent applies them with JDBC batches or staged SqlBulkCopy cycles. The pipeline model, the snapshot, and the operations stay the same whether the data comes from Oracle, IBM i, or MongoDB. HOW IT WORKS ## How Gluesync writes to SQL Server ### The write path: optimized batches and SqlBulkCopy bulk load The SQL Server agent connects over TCP/IP with the official SQL Server JDBC driver, with TLS. It is a target agent: it applies the change stream from any Gluesync source agent to your tables in real time, after a snapshot seeds each table. Gluesync never writes one row at a time; each entity uses one of two paths. - **Optimized batches, never row by row:** by default, changes are grouped into highly optimized SQL batches, which cuts round-trips and keeps load on the instance predictable. Chunk sizes are configurable separately for writes and deletes. - **Native bulk load:** switch it on per entity for the initial snapshot, for ongoing CDC, or both, and rows stream into SQL Server with `SqlBulkCopy`, as described below. ### Native bulk load for snapshots and CDC Bulk load is switched on per entity with two independent settings, one for the initial load and one for the ongoing change stream. Turn on one, the other, or both, on an existing entity without recreating it. In each mirroring cycle Core Hub collects the change events and collapses those on the same primary key: an insert followed by a delete is skipped, and consecutive updates become one update. The agent streams the batch with `SqlBulkCopy` into a staging table in the Gluesync schema, optionally backfills columns the source log omitted, then applies deletes and inserts to the target table in one pass and clears the staging table. Each changed key lands as one current row. Fire triggers on bulk copy is on by default, so triggers on the target table run during bulk copy. You can turn it off per agent. ### Snapshot first, then continuous CDC - **Seed, then stream:** each entity loads its full table into SQL Server, then follows the source agent's change stream, applied in commit order per entity. - **INSERT or UPSERT:** INSERT with `TRUNCATE before snapshot` is the fast path for empty or reset tables. UPSERT merges the snapshot with rows that already exist in SQL Server. - **On duplicate key:** Upsert, Skip, or Fail, set per entity. Upsert overwrites the row, Skip keeps the row already in SQL Server and raises a notification, and Fail stops the entity. - **Identity columns:** the agent writes identity values equal to the source for snapshot and CDC writes, then resets the Force identity option after each commit, so keys in SQL Server match the system of record. - **Two-way sync:** enable recursion protection on the agent so a change applied to a table is not sent back when two-way sync covers that table. ### Schema, types, and keys in SQL Server - **Table creation:** when a target table does not exist, Core Hub generates a `CREATE TABLE` from the source columns, data types, and primary key, converted to SQL Server syntax. You review and edit the statement before it runs. - **Type mapping:** each source value is normalized into a Gluesync data family and written in the closest representation the SQL Server connector supports. Fixed-point columns keep their precision, and the Fields Editor shows the inferred target type for each column before you deploy. - **Pre and post SQL:** pre and post commands run around the snapshot and CDC, for example to set session parameters or to clean up staging objects. ### What your SQL Server administrator sets up The agent needs a login that can read and write the target database and tables, and TLS 1.2 or higher on the instance. Full connection fields are in the [SQL Server target setup guide ↗](https://docs.molo17.com/gluesync/latest/agents/ms-sql-server-agent-change-data-capture-target.html). 1. In SQL Server Configuration Manager, enable TCP/IP under the protocols for your instance, then restart the SQL Server service. The agent connects over TCP/IP only. 2. Enable TLS 1.2 or higher on the instance for secure connections. 3. Create a login with read and write access to the target database and tables. Table creation and bulk staging also need DDL rights on the schemas they use. 4. In Core Hub, enter the host, the port (1433 by default), the database name, and the credentials, with TLS and the certificate path set. ### Architecture around Core Hub Lightweight agents sit close to each source. Core Hub orchestrates them through its web UI and REST APIs and routes changes to the SQL Server agent. A pipeline groups the source agents, the SQL Server agent, and the entities they replicate. Core Hub and the agents deploy with Docker and Podman, Docker Compose, or Kubernetes. See [CDC streaming](/solutions/cdc-streaming/). [Explore the general CDC streaming architecture →](/solutions/cdc-streaming/) WRITE OPTIONS ## The SQL Server target agents: batches by default, bulk cycles per entity Both SQL Server agents write to SQL Server through the same JDBC path: optimized SQL batches by default, with native SqlBulkCopy bulk load for snapshots and CDC switched on per entity. | Agent | Write technique | Versions | Best for | | --- | --- | --- | --- | | [MS SQL Server Agent for Gluesync ↗](https://docs.molo17.com/gluesync/latest/agents/ms-sql-server-agent-change-data-capture-intro.html) | Official SQL Server JDBC driver with TLS; optimized SQL batches by default; native SqlBulkCopy bulk load for snapshots and CDC | SQL Server on-premises and Azure SQL (SQL Azure), with TLS 1.2 or higher on the instance | Continuous replication into SQL Server from any Gluesync source. Optimized batches by default, and native `SqlBulkCopy` bulk load for snapshots and CDC, switched on per entity. | | [MS SQL Server Change Tracking Agent for Gluesync ↗](https://docs.molo17.com/gluesync/latest/agents/ms-sql-server-agent-change-tracking-intro.html) | Official SQL Server JDBC driver with TLS; optimized SQL batches by default; native SqlBulkCopy bulk load for snapshots and CDC | SQL Server on-premises and Azure SQL (SQL Azure), with TLS 1.2 or higher on the instance | The same SQL Server target, with the same bulk load and batch settings, paired with the Change Tracking source agent for SQL Server tables that have primary keys. | SOURCES AND TOPOLOGIES ## Feed SQL Server from the systems of record you already run Any Gluesync source agent can feed SQL Server, each with its own native capture technique. Open the [integrations finder with SQL Server pre-selected](/integrations/?target=MS+SQL+Server#integration-finder) to see every source you can pair with it. One Core Hub runs Oracle to SQL Server, IBM i to SQL Server, and Couchbase to SQL Server side by side, with the same snapshot, monitoring, and write settings for each. Gluesync keeps pace with your change volume at any scale, and [MOLO17 Professional Services](/solutions/professional-services/) can help design the pipeline with your team. - Oracle to SQL Server from the redo logs through LogMiner or XStream, or through triggers: see [Oracle CDC](/solutions/oracle-cdc/) - IBM i (AS/400) to SQL Server through the native journal APIs: see [IBM i CDC](/solutions/ibm-i-cdc/) - PostgreSQL to SQL Server from the write-ahead log, for cross-engine moves: see [PostgreSQL CDC](/solutions/postgresql-cdc/) - Couchbase to SQL Server, for NoSQL data that feeds relational reporting: see [Couchbase CDC](/solutions/couchbase-cdc/) FAIR, HIGH-LEVEL COMPARISON ## Where Gluesync fits among SQL Server ingestion approaches | Approach | What buyers usually get | Where Gluesync fits | | --- | --- | --- | | Native SQL Server transactional replication | Built-in replication between SQL Server databases, with SQL Server subscribers as the usual destination; it stays inside the SQL Server family | The same SQL Server destination fed from any source, not only SQL Server, with every pipeline managed in one Core Hub | | Azure Data Factory and SSIS | Orchestrated copy and transform jobs on a schedule; incremental loads rely on the watermark or change-tracking logic you build around them | Changes applied to SQL Server as they arrive, with snapshot and CDC on one entity, so teams stop scheduling copy windows; see [warehouse sync](/solutions/warehouse-sync/) | | Debezium, Kafka, and a JDBC sink connector | Open-source capture into Kafka topics, then a sink connector that writes to SQL Server; you run Kafka, Connect, and the schemas, and usually add a step that turns change events into current-state tables | Changes applied to current-state SQL Server tables with no Kafka cluster in the path, and Kafka still available as another target. Compare the [Debezium alternative](/solutions/debezium-alternative/) | | Qlik Replicate, Fivetran HVR, and Striim | Commercial log-based replication with broad source and target catalogs; licensing and deployment differ by product | Native capture per engine, optimized batches and SqlBulkCopy bulk load, and enterprise support from MOLO17 behind every pipeline | | Managed ELT services | Managed connector catalogs with scheduled syncs; incremental and CDC modes vary by connector, and the sync schedule sets how fresh the data is | Changes applied as they arrive instead of on a sync schedule, with MOLO17 enterprise support behind every pipeline | | DIY scheduled jobs and scripts | Full control; your team owns the extract queries, the merge logic, retries, and the load each run puts on production | Log-based capture, staged bulk apply, and Core Hub monitoring without pipeline code to maintain; read [batch ETL vs real-time data replication](/blog/batch-etl-vs-real-time-data-replication/) | RELATED CONTENT ## SQL Server replication background and implementation detail - [MS SQL Server CDC: transaction log versus change tracking](/blog/ms-sql-server-cdc-transaction-log-vs-change-tracking/) - [Batch ETL vs real-time data replication: how to choose](/blog/batch-etl-vs-real-time-data-replication/) - [Database offload: move read traffic off the system of record](/solutions/database-offload/) - [Warehouse sync: keep cloud warehouses synchronized with operations](/solutions/warehouse-sync/) - [SQL Server CDC: change data capture and real-time replication](/solutions/sql-server-cdc/) - [SQL Server agent overview ↗](https://docs.molo17.com/gluesync/latest/agents/ms-sql-server-agent-change-data-capture-intro.html) - [SQL Server target setup guide ↗](https://docs.molo17.com/gluesync/latest/agents/ms-sql-server-agent-change-data-capture-target.html) - [Bulk load: staging and apply phases ↗](https://docs.molo17.com/gluesync/latest/core-hub/bulk-load.html) FAQ ## SQL Server replication questions What does replicating to SQL Server with Gluesync involve? A source agent captures committed changes from your database through its native change mechanism, Core Hub routes them, and the SQL Server agent applies them to your tables continuously after a snapshot seeds each table. Does Gluesync bulk load into SQL Server? Yes, for the initial snapshot and for ongoing CDC, with a separate switch for each on every entity. Changes are collapsed per primary key, streamed with SqlBulkCopy into a staging table, and applied to the target table in one pass. Without bulk load, the agent writes through the official SQL Server JDBC driver in optimized SQL batches. Which sources can replicate to SQL Server? Any Gluesync source agent, including Oracle, IBM Db2 for i, PostgreSQL, MySQL, MariaDB, and MongoDB. The integrations finder on our website shows every source that pairs with SQL Server. How does an Oracle to SQL Server pipeline work? A dedicated Oracle agent reads the redo logs through LogMiner or XStream, or uses triggers. A snapshot seeds SQL Server first, and the SQL Server agent then applies the change stream in commit order per entity. How are duplicate keys handled when replicating to SQL Server? The On duplicate key setting is applied per entity. Upsert overwrites the existing row and is the default, Skip keeps the row already in SQL Server and raises a notification, and Fail stops the entity. What does the SQL Server administrator need to set up? TCP/IP enabled on the instance, TLS 1.2 or higher, and a login with read and write access to the target tables, with DDL rights where Gluesync creates tables or staging objects. Can Gluesync create the tables in SQL Server? Yes. When a target table is missing, Core Hub generates a CREATE TABLE from the source definition, converted to SQL Server syntax, and you review it before it runs. Which SQL Server deployments can Gluesync write to? SQL Server on-premises and Azure SQL, reached over TCP/IP with TLS through the JDBC driver. REPLICATE TO A TARGET ## Other targets Gluesync delivers to - [Replicate to Aerospike](/solutions/replicate-to-aerospike/) - [Replicate to DynamoDB](/solutions/replicate-to-dynamodb/) - [Replicate to Redshift](/solutions/replicate-to-redshift/) - [Replicate to Amazon S3 & S3-compatible](/solutions/replicate-to-amazon-s3/) - [Replicate to Cassandra](/solutions/replicate-to-cassandra/) - [Replicate to Kafka](/solutions/replicate-to-kafka/) - [Replicate to Cosmos DB](/solutions/replicate-to-cosmos-db/) - [Replicate to Azure Data Lake](/solutions/replicate-to-azure-data-lake/) - [Replicate to ClickHouse](/solutions/replicate-to-clickhouse/) - [Replicate to CockroachDB](/solutions/replicate-to-cockroachdb/) - [Replicate to Couchbase](/solutions/replicate-to-couchbase/) - [Replicate to file stores](/solutions/replicate-to-file-stores/) - [Replicate to BigQuery](/solutions/replicate-to-bigquery/) - [Replicate to Google Cloud Storage](/solutions/replicate-to-google-cloud-storage/) - [Replicate to Google Pub/Sub](/solutions/replicate-to-google-pubsub/) - [Replicate to GridGain](/solutions/replicate-to-gridgain/) - [Replicate to Db2](/solutions/replicate-to-db2/) - [Replicate to Informix](/solutions/replicate-to-informix/) - [Replicate to MariaDB](/solutions/replicate-to-mariadb/) - [Replicate to MongoDB](/solutions/replicate-to-mongodb/) - [Replicate to MySQL](/solutions/replicate-to-mysql/) - [Replicate to Oracle](/solutions/replicate-to-oracle/) - [Replicate to PostgreSQL](/solutions/replicate-to-postgresql/) - [Replicate to RavenDB](/solutions/replicate-to-ravendb/) - [Replicate to Redis](/solutions/replicate-to-redis/) - [Replicate to SAP ASE](/solutions/replicate-to-sap-ase/) - [Replicate to SAP HANA](/solutions/replicate-to-sap-hana/) - [Replicate to ScyllaDB](/solutions/replicate-to-scylladb/) - [Replicate to SingleStore](/solutions/replicate-to-singlestore/) - [Replicate to Snowflake](/solutions/replicate-to-snowflake/) - [Replicate to Solace PubSub+](/solutions/replicate-to-solace/) - [Replicate to Vertica](/solutions/replicate-to-vertica/) - [Replicate to YugabyteDB](/solutions/replicate-to-yugabytedb/) ## Evaluate Gluesync with your SQL Server databases Start a trial on your own infrastructure, or talk to MOLO17 about your sources, the write path for each table, and how SQL Server fits your replication plan. [Start a Gluesync trial](/get-gluesync/) [Talk to MOLO17](/contacts/)