SQL Server powers critical business operations across every industry, including hospital scheduling systems, retail order platforms, and financial applications. These transactional databases must provide high, consistent performance with large data volumes.
Organizations often struggle to quickly ingest business-critical data into a data warehouse or data lake without disrupting production environments. Building and maintaining performant data pipelines for high-volume use cases is fraught, at best, risking stoppages, bugs, and downtime of all kinds. Organizations need near-100% uptime guarantees for backend databases that support critical operations and for data pipelines that deliver analytics.
To address these struggles, Fivetran is introducing the SQL Server binary log reader, a new ingestion method designed for sensitive, high-volume database environments. It delivers high-speed, low-impact change data capture by reading changes directly from SQL Server’s transaction log, keeping data flowing to your warehouse without disrupting day-to-day operations.
What is the binary log reader, and when should you use it?
The binary log reader is an incremental update mechanism purpose-built for high-volume, performance-sensitive SQL Server environments that reads transaction logs directly to replicate data to a target system with minimal impact on source performance. This method is designed for environments that require:
- Minimal overhead on your SQL Server instance: Your SQL Server is already doing heavy lifting for core applications, and you need to minimize additional CPU and I/O load.
- Constant inserts, updates, and deletes: Tables for retail orders, EMR updates, financial transactions, IoT events, and so on are rarely idle, and you need to shrink or eliminate batch windows.
- Latency is important for operations: Executives, clinical staff, ML pipelines, and applications expect fresh data immediately.
- Ability to scale as needed: Large tables, high write rates, or numerous merge operations won’t slow the binary log reader down.
If your SQL Server database is already running at capacity, powering time-sensitive business operations, or processing millions of transactions daily, it is challenging to build a data pipeline to replicate these changes downstream to your data warehouse or data lake without interfering with the systems that run on the databases.
Another consideration is the cost of these operations, both from a load perspective and from a financial perspective, when processing large data volumes in the cloud. The binary log reader helps users keep costs down and databases up.
How it works: Reading directly from the transaction log
Data integration with SQL Server commonly uses SQL Server’s native change tracking (CT) and change data capture (CDC) mechanisms, both of which write changes to dedicated tables. Fivetran supports both change detection mechanisms. These replication methods are effective for many environments and are fully supported in Fivetran, but sometimes users need something that can meet even more stringent requirements.
The SQL Server binary log reader introduces an additional option: it reads committed transactions directly from the SQL Server transaction log using a Fivetran-provided DLL. At a high level, the binary log reader:
- Captures changes from the source: The binary log reader uses a Fivetran-provided DLL to read committed transactions directly from SQL Server’s transaction log files. Because the log is written as part of SQL Server’s normal durability and recovery workflow, Fivetran can observe changes without introducing a heavy query load.
- Parse inserts, updates, and deletes: The DLL parses change events from the logs with minimal processing overhead.
- Replicate to your destination: Changes are efficiently written to your data warehouse or data lake, maintaining data integrity and order.
This log-based approach is the least intrusive replication method because it operates outside the database's query processing engine. Your production workloads remain unaffected, and data continues to flow to your analytics environment.
How the binary log reader is changing database replication
In internal testing, it has delivered a throughput of up to 88 MB/s, enabling rapid replication of large, busy SQL Server databases. Unlike query-based approaches that consume significant database resources, log-based replication operates independently, and your database performance remains unaffected due to minimal resource consumption. The upshot is superior performance with minimal impact.
Enterprise-grade reliability
The binary log reader includes advanced capabilities designed for production environments:
- Automatic fallback: If online transaction logs become unavailable, the binary log reader automatically falls back to backup files, ensuring continuous replication.
- Always On Availability Groups (AOAG) support: Seamlessly replicate from high-availability SQL Server configurations
- Backup file reading: The binary log reader reads directly from backup files when needed, providing flexibility in how Fivetran accesses transaction data.
- Automatic schema migration: Schema changes in your source database are automatically detected and applied to your destination, eliminating manual intervention and preventing pipeline disruptions.
Built for complex environments
Whether you're running SQL Server on-premises, in the cloud, or in hybrid configurations, the binary log reader handles the complexity. It supports modern SQL Server architectures while maintaining the simplicity Fivetran is known for – no custom scripting, no manual maintenance.
A faster path to real-time SQL Server analytics
With the SQL Server binary log reader, we’re giving teams a more efficient way to move mission-critical SQL Server data into modern analytics platforms. Whether you’re powering operational dashboards, training models on fresh data, or consolidating enterprise systems, this release ensures you can do it with speed, reliability, and minimal impact on the database that keeps your business running.
If you run SQL Server-based applications and need access to data in a data warehouse or data lake, try our binary log reader.
[CTA_MODULE]
