Streaming Data from SQL Server to ClickHouse for Fast Analytics: Step-by-Step Guide

## Streaming in Action: What to Expect[​](#streaming-in-action-what-to-expect "Direct link to Streaming in Action: What to Expect")

Now your pipeline is set up and active!

### Step 4: Watch the Data Stream[​](#step-4-watch-the-data-stream "Direct link to Step 4: Watch the Data Stream")

Here’s what happens:

- As new data hits the source table on SQL Server, the Streamkap pipeline captures the change and sends it to ClickHouse.
- ClickHouse (thanks to ReplacingMergeTree and part merging) ingests these rows and merges updates.
- Schema keeps up—add columns in SQL Server and they’ll show in ClickHouse too.

Live dashboard or logs showing row counts growing in ClickHouse and SQL Server in real-time.

You can literally see rows in ClickHouse ramping up as SQL Server gets data.

```
-- Example: Checking rows in ClickHouse 
SELECT COUNT(*) FROM analytics.events; |
```

Expect some lag in heavy-load scenarios, but most use cases see near-real-time streaming.

## Under the Hood: What’s Streamkap Actually Doing?[​](#under-the-hood-whats-streamkap-actually-doing "Direct link to Under the Hood: What’s Streamkap Actually Doing?")

To give you a little insight:

- Streamkap watches SQL Server’s binary log (the same log used for replication).
- As soon as a row is inserted, updated, or deleted in your table, Streamkap catches the event.
- It turns the event into something ClickHouse understands and ships it over—applying changes instantly in your analytics DB.

This isn’t just ETL—it’s full change data capture (CDC), streamed in real time.

## Advanced Options[​](#advanced-options "Direct link to Advanced Options")

### Upsert vs. Insert Modes[​](#upsert-vs-insert-modes "Direct link to Upsert vs. Insert Modes")

What is the difference between just inserting every row (Insert Mode) and making sure updates and deletes are mirrored too (Upsert Mode)?

- Insert Mode: Every new row is added—even if it’s an update, you get duplicates.
- Upsert Mode: Updates to existing rows overwrite what’s there—way better for keeping analytics fresh and clean.

### Handling Schema Changes[​](#handling-schema-changes "Direct link to Handling Schema Changes")

Apps change, and so do your schemas. With this pipeline:

- Add a new column to your operational table?  
  Streamkap will pick it up and add it on the ClickHouse side too.
- Remove a column?  
  Depending on settings, you might need a migration—but most adds are smooth.

## Real-World Monitoring: Keeping Tabs on the Pipeline[​](#real-world-monitoring-keeping-tabs-on-the-pipeline "Direct link to Real-World Monitoring: Keeping Tabs on the Pipeline")

### Checking Pipeline Health[​](#checking-pipeline-health "Direct link to Checking Pipeline Health")

Streamkap provides a dashboard where you can:

- See pipeline lag (how fresh is your data?)
- Monitor row counts and throughput
- Get alerted if anything is off

Dashboard example: Latency graph, row counts, health indicators.

### Common Metrics to Watch[​](#common-metrics-to-watch "Direct link to Common Metrics to Watch")

- Lag: How far is ClickHouse behind SQL Server?
- Throughput: Rows per second
- Error Rate: Should be near zero

## Going Live: Querying ClickHouse[​](#going-live-querying-clickhouse "Direct link to Going Live: Querying ClickHouse")

With your data now in ClickHouse, you can query it using all those fast analytics tools. Here’s a basic example:

```
-- See top 10 active users in the last hour
SELECT user\_id, COUNT(*) AS actionsFROM analytics.eventsWHERE event\_time >= now() - INTERVAL 1 HOURGROUP BY user\_idORDER BY actions DESCLIMIT 10;
```

Combine ClickHouse with dashboards tools like Grafana, Superset, or Redash for full-featured reporting.

## Next Steps and Deep Dives[​](#next-steps-and-deep-dives "Direct link to Next Steps and Deep Dives")

This walkthrough just scratches the surface of what you can do. With the basics down, here’s what you can explore next:

- Setting up filtered streams (only sync some tables/columns)
- Streaming multiple sources into one analytical DB
- Combining this with S3/data lakes for cold storage
- Automating schema migrations when you change tables
- Securing your pipeline with SSL and firewall rules

Keep an eye on the[Streamkap blog](https://streamkap.com/blog) for more in-depth guides.

## FAQ and Troubleshooting[​](#faq-and-troubleshooting "Direct link to FAQ and Troubleshooting")

Q: Does this work with cloud databases?  
A: Yes! We used AWS RDS in this example. Just make sure you open the right ports.

Q: What about performance?  
A: ClickHouse is fast. The bottleneck is usually the network or the source DB’s binlog speed, but for most cases, you’ll see less than a second lag.

Q: Can you handle deletes, too?  
A: Absolutely. In upsert mode, deletes get flagged and handled in ClickHouse as well.

## Wrapping up[​](#wrapping-up "Direct link to Wrapping up")

There you have it—a full overview of streaming your SQL Server data into ClickHouse using Streamkap. It’s fast, flexible, and perfect for teams who need up-to-the-minute analytics without crushing their production databases.

Ready to try it?  
Head to the [Sign up page](https://app.streamkap.com/account/sign-up) and let us know if you want us to cover topics like:

- Upsert vs. Insert and the nitty-gritty of both
- End-to-end latency: how fast can you get your final analytic view?
- Performance tuning and throughput
- Real-world dashboards on top of this stack

Thanks for reading! Happy streaming.

- [Why Stream Data from SQL Server to ClickHouse?](#why-stream-data-from-sql-server-to-clickhouse)- [What You’ll Need to Get Started](#what-youll-need-to-get-started)
    - [Prerequisites](#prerequisites)- [Connection Info](#connection-info)- [Setting Up SQL Server as a Source](#setting-up-sql-server-as-a-source)
      - [Step 1: Creating a SQL Server Source in Streamkap](#step-1-creating-a-sql-server-source-in-streamkap)- [Creating a ClickHouse Destination](#creating-a-clickhouse-destination)
        - [Step 2: Add a ClickHouse Destination in Streamkap](#step-2-add-a-clickhouse-destination-in-streamkap)- [Upsert Mode: What Is That?](#upsert-mode-what-is-that)- [Handling Schema Evolution](#handling-schema-evolution)- [Building the Streaming Pipeline](#building-the-streaming-pipeline)
          - [Step 3: Set up the Pipeline in Streamkap](#step-3-set-up-the-pipeline-in-streamkap)- [Streaming in Action: What to Expect](#streaming-in-action-what-to-expect)
            - [Step 4: Watch the Data Stream](#step-4-watch-the-data-stream)- [Under the Hood: What’s Streamkap Actually Doing?](#under-the-hood-whats-streamkap-actually-doing)- [Advanced Options](#advanced-options)
                - [Upsert vs. Insert Modes](#upsert-vs-insert-modes)- [Handling Schema Changes](#handling-schema-changes)- [Real-World Monitoring: Keeping Tabs on the Pipeline](#real-world-monitoring-keeping-tabs-on-the-pipeline)
                  - [Checking Pipeline Health](#checking-pipeline-health)- [Common Metrics to Watch](#common-metrics-to-watch)- [Going Live: Querying ClickHouse](#going-live-querying-clickhouse)- [Next Steps and Deep Dives](#next-steps-and-deep-dives)- [FAQ and Troubleshooting](#faq-and-troubleshooting)- [Wrapping up](#wrapping-up)

Was this page helpful?