How it works



SQLFed is a tool allowing MS-SQL to transactionally federate horizontally, by way of hub and spoke model (or “star” topology). The solution is table-specific, allowing for some tables to be federated and others not. It is aware of the need for certain tables to have identity-integrity or primary-key-integrity which is centrally generated, and has facilities in-built for that purpose.

SQLFed follows a Multi-Leader Asynchronous-Replication model, with the ability for every Node to read/write against the same data at the same time - but with a central hub to manage data operations requiring consistency across the Federation. There is an integrated global ordering model, following FedMaster’s clock system. All writes are full-row writes, pushing the Federation toward an eventual-convergence model.



SQLFed operates through a single common trigger and a single common job, existing on all federated servers. These operate against COMMANDS and REMOTECOMMANDS tables in the SQLFed database, all of which exists on every federation server. Nodes exchange transactions via FedMaster, which is itself a role-designated Node. Each node individually interacts with FedMaster, and not with other nodes.

A single transaction applied to a Federated table flows as follows:

The purpose of pr_sync_trigger is to create a string of executable tSQL to reproduce the row-level transaction executed at Node 1, so that it can be reproduced on all other nodes.

Every node has Linked Servers defined for FederationMaster and FederationMaster2 - where FederationMaster2 is a standby node in the federation, receiving updates as any other node. These linked servers allow each Node to communicate directly with the Primary and Secondary FedMaster servers. All interactions between Nodes and FedMaster are via these linked servers.

At the network level, these aliases are established and managed so that Operational teams can designate which server holds each role. These can also be handled by SQL teams using SQL Aliases.

Each node will attempt to maintain communication with both nodes - be aware that taking either node down can cause an interruption in SQLFed processing.

Each row in a Federated table has a syncGUID field with a default value of NEWID(). Rows across Nodes will all have the same syncGUID value. This is a key used by SQLFed to ensure data integrity, and is the means by which SQLFed issues its INSERT / UPDATE / DELETE statements. Tables should be well indexed around the syncGUID field to ensure optimal SQLFed performance.



Key Features

SQLFed can run on any version of MS-SQL, including Express, Web and Standard. SQLFed allows for Enterprise levels of redundancy and scale on commodity hardware and less exotic MS-SQL versions.

With SQLFed, any data you write on one federated server is instantly present on all federated servers.

  • Not all tables need to be federated.
  • Any number of databases on a given server can be involved in the federation.
  • Hub and Spoke model
  • Distributed IDENTITY columns are supported
  • A common "Synchronize" database and job are deployed to all participating servers
  • A trigger is deployed to each federated table which acts to capture actions against that table to be federated
  • The "Synchronize" database holds each atomic change
  • A continuously running job pushes changes up to FedMaster, while at the same time pulling down and executing changes from around the Federation
  • All changes are time-indexed by FedMaster to maintain integrity
  • Central "FedMaster" node orchestrates operations, and is typically not also actively supporting your workload
  • FedMaster role can be assumed by any node, at any time
  • All nodes are Accessible and Read Write at all times
  • High-latency, low-reliability links are supported
  • Tolerant of mixed servers, editions and versions
  • Ability to designate tables where INSERTs must occur centrally to maintain integrity

Node offline operation is possible for a period of many hours, depending on configuration. SQLFed is designed to be tolerant of weak inter-datacenter links, with the ability to gracefully support nodes with disparate link-speeds-to-master, retries, and even to support recovery of fully interrupted connections (ie, temporary VPN drops).

SQLFed allows for operations against data in-flight - for example, if data must be redacted or encrypted or transformed in a subset of the Federation, that action can be taken as the data is being supplied by FedMaster to the Node. This makes it possible to have read-only Reporting, QA or Development nodes in the federation with redacted real-time data.

SQLFed operates on any version of MS-SQL, and can inter-operate on mismatched SQL versions - as well as across any tSQL compatible database systems (all the nodes don’t have to be MS-SQL). It is purely a data-federation system.

SQLFed operates entirely within SQL Server. There are no outside executables. There are no limits to the number of tables which can be synchronized, the number of nodes, or the number of transactions supported. These are limited only by the quality of the FedMaster hardware, and link speeds between equipment.

The purpose of SQLFed is to enable the creation of large scale distributed database systems, far greater in scale than could be possible using large single systems - and far less expensive, utilizing commodity hardware. With this model, SQLFed makes it possible to support very high workloads on very inexpensive MS-SQL licensing with high levels of redundancy at very low hardware costs.

SQLFed is proven and simple technology

100% of SQLFed is in MS-SQL in the form of jobs, stored procedures, tables and triggers. There are no black boxes or outside executables. Manage it like any other part of your SQL infrastructure.

Installation and Ongoing Support options available for Enterprise customers.

We're all familiar with the pros and cons of Microsoft's Clustering, Replication, Mirroring, Always On and Log Shipping solutions. But what if your load is bigger than that? Or if deploying many Enterprise instances is not right for you? SQLFed is the first truly new choice in years.

SQLFed is a response to a need for inexpensive, redundant, reliable, fault tolerant and scalable horizontal federation options for Microsoft SQL - that work in the real world. Designed and developed by SQL Architects and DBAs, SQLFed offers a lightweight and easy to manage scaling option.

There are no inherent performance caps or limits

Scale horizontally by adding MS-SQL servers without limit.

SQLFed can easily span geographically diverse datacenters, and run on a spread of servers of disparate speeds and configurations and MS-SQL versions.

A single FedMaster node can easily support 30 or more highly active federated nodes and 10,000+ Transactions per Minute (TPM).

SQLFed on Express

On Express, in the absence of a SQL Job Agent, SQLBeat's FreeAgent can be used. This is a like-for-like implementation of SQLAgent that you can interact with via MSDB commands. Effectively this is a Job Agent for Express.