Migrate to ClickHouse

Migrate from SQL Server to ClickHouse

Replatform analytical workloads from SQL Server to ClickHouse. We have delivered these migrations with observed cost savings of 50% or more alongside large improvements in query latency.

How the migration works
Sub second
Queries over billions of rows, from columnar storage and vectorised execution.
50%+
Cost savings observed in the migrations we have delivered from SQL Server, OpenSearch and Snowflake.
100s
Of concurrent users served with consistent latency, so analytics can go into your product.
Anywhere
Open source. Run it on any cloud, on premise or as ClickHouse Cloud.
Why teams move

Why SQL Server users come to us

SQL Server is different from the other transactional databases we migrate from. It is widely used for analytical workloads in its own right, through columnstore indexes, Analysis Services and years of reporting built directly on top of it. So with SQL Server the conversation is usually a genuine migration rather than an offload.

The pressure tends to come from two directions. Query latency creeps up as the data and the number of users grow, and cost climbs with it, particularly on Azure SQL or managed instances where the only lever is a bigger tier. Licensing adds another layer on top of the compute.

We have completed several of these migrations and the results have been consistent: cost savings of 50% or more in practice, and query performance that moves from seconds to milliseconds. That is why we expect this to remain one of the most common migration routes to ClickHouse.

Sound familiar?
  • [01]Analytical queries and reports are slow despite columnstore indexes and query tuning.
  • [02]Azure SQL or Enterprise licensing cost rises with every increase in tier or core count.
  • [03]Concurrency is limited, so dashboards degrade as more users come online.
  • [04]Historical data is archived or summarised to keep the database within budget.
  • [05]The platform is tied to one vendor and one cloud, with little room to negotiate.
What changes

SQL Server to ClickHouse: faster, cheaper, simpler

ClickHouse is the fastest open source analytical database on the market. These are the differences that matter most for teams coming from this platform.

[01]

50%+ lower cost, observed not modelled

In migrations we have delivered the combination of better compression, far more efficient query execution and no per core licensing has cut the running cost by more than half.

[02]

Latency measured in milliseconds

The same reporting queries that took seconds on SQL Server return in milliseconds on ClickHouse, with no need to pre build cubes or aggregates for each report.

[03]

Hundreds of concurrent users

ClickHouse is designed to serve many simultaneous queries over large datasets, which makes it a credible backend for customer facing analytics in a way SQL Server was not.

[04]

Open source, run anywhere

ClickHouse runs on Azure, AWS, GCP, on premise or as ClickHouse Cloud. There is no licence to true up and no lock in to a single vendor.

How we migrate

Accelerated migration with CDC

For SQL Server we use Supermetal, a high performance CDC engine that reads the transaction log and replicates into ClickHouse with an initial snapshot followed by a continuous stream of changes. It runs as a single binary alongside the source. T-SQL views, stored procedures and reports are translated to ClickHouse SQL as part of the work, and we validate every migrated report against the original before cut over.

  1. 01

    Assess

    We review the schemas, the query workload and the cost of the current platform, then agree the scope, the target architecture and the business case before anything is built.

  2. 02

    Design the ClickHouse schema

    Tables are redesigned for columnar storage rather than copied across one to one. Sorting keys, partitioning, codecs and materialised views are chosen against your real queries.

  3. 03

    Load and replicate

    Historical data is bulk loaded, then changes are streamed continuously so ClickHouse stays in line with the source for as long as the two need to run side by side.

  4. 04

    Run in parallel

    Dashboards, applications and reports are pointed at ClickHouse and validated against the source. Performance and cost are measured rather than assumed.

  5. 05

    Cut over

    Workloads move across in stages, the old platform is scaled down and the remaining spend is retired. Nothing is switched off until the numbers reconcile.

  6. 06

    Support

    After go live we can monitor the environment, tune it as the workload grows and provide an escalation path under SLA through our proactive support service.

Tooling we use
Supermetal

A high performance CDC engine from supermetal.io that ships as a single binary and replicates from Postgres, MySQL, SQL Server, Oracle and MongoDB into ClickHouse, including self hosted clusters.

ClickPipes

The managed ingestion service built into ClickHouse Cloud. Its CDC connectors take an initial snapshot and then stream every insert, update and delete, with no infrastructure for you to run.

We are tool agnostic. The right choice depends on whether you are going to ClickHouse Cloud or self hosted, how much control you want over the pipeline and what else is in the stack. Zero downtime is the norm, not the exception.

Beyond the migration

One database, many use cases

Moving off SQL Server is usually the start. Because ClickHouse is so versatile, the same platform tends to pick up more workloads once it is in place.

Real time analytics

Dashboards and embedded analytics over data that is seconds old, served to many users at once.

Data warehousing

A warehouse that is fast enough to query directly, without the overnight batch or the cube layer.

Observability

Logs, metrics and traces at a fraction of the cost of Datadog, Splunk or Elastic, with ClickStack on top.

Time series

Market data, IoT, telemetry and events at hundreds of thousands of rows per second with long retention.

Search and security analytics

Full text search, SIEM and threat hunting over high volume event data using plain SQL.

Agentic AI

A data platform fast and cheap enough for agents that turn one question into dozens of queries.

Related work

Migrations we have delivered

Automotive

Migrating An Automotive Analytics Platform From SQL Server To ClickHouse

Replatforming a dealer management analytics product from SQL Server to ClickHouse, delivering observed cost savings of 50%+.

Manufacturing & Retail

Helping A Food Manufacturer And Retailer Deliver A Real Time Data Warehouse Using ClickHouse

Migrating 20 years of sales data from a legacy Oracle data warehouse to ClickHouse, resolving performance and memory issues while accelerating business intelligence.

Further reading

Ready to move off SQL Server?

Start with a short call. We will look at your schemas, queries and current spend, tell you honestly whether ClickHouse is the right fit, and put a number on the performance and cost you can expect.

Support after go live