SQLFabric

Figure (1) below illustrates the high level architecture. The following section provides a brief introduction to the technical features.

Features

The following highlight the key features of the product:

Operate at memory speeds

Unlike disk oriented, centralized traditional database architectures, data structures and indexes are optimized for main-memory. The focus is on optimizing distributed data access rather than disk IO optimization. Tables can be replicated across several nodes in memory synchronously or can be partitioned horizontally across many nodes in memory, with redundancy. Unlike traditional databases, there are no single points of contention - no disk seeks to worry about, no CPU or network contention to be concerned with. When the cluster capacity (CPU or memory) exceeds a certain threshold simply add more servers. The additional servers can automatically rebalance the data and behavior to linearly scale.

Optionally on disk also

Replicated or partitioned tables can purely be managed in memory or both in memory and on disk. The design for how data is managed on disk is fundamentally very different than disk oriented databases where each update results in at least 2 writes to disk - one to a "redo" or "undo" log file and then to the data files. In SQLFabric, each member that hosts data manages its own disk files. Table update operations are appended to rolling log files only without the need to seek to a disk location and an algorithm that doesn't require a flush all the way to disk. All query operations primarily operate using memory indexes and in most cases do not require any disk access. Writes are buffered by the IO subsystem in the kernel allowing the IO scheduler to merge and sort disk writes to achieve the highest possible disk throughput. Any risk introduced due to catastrophic machine failure is mitigated by making sure at least 2 machines are synchronously writing to the disk buffers. The result is throughput that comes close to the maximum disk transfer rate on each node in the cluster.

Middle tier cache that synchronizes with your data store

SQLFabric can be used as a first class data store or can be used as a front end cache on top of one or more existing databases. The product provides a caching framework with capabilities to read-through (load from the database on a cache miss), write-through (synchronously apply the change in the database before changing the cache) and write-behind (asynchronously enqueue all changes and transfer in batch to the database). The ability to asynchronously write back to the database in batches allows applications to operate at very high speeds and not be constrained by database contention or availability.
When configured as a cache, the tables can be configured to evict the least frequently used rows when the entire data set cannot fit into distributed memory managed by SQLFabric. The product supports multiple eviction strategies including expiry of data based on time.

Ensure continuous availability for data (within and across data centers)

Any table can be configured to be replicated or partitioned with one or more redundant copies. When working with replicated tables, the number of replicas can dynamically be increased or decreased. A table can be replicated across all the servers or just within a sub-set of the servers. Similarly, when data is partitioned multiple copies of the data can be maintained. In SQLFabric, all data changes are propagated synchronously to the replicas using an ACK based protocol that ensures that the data has safely made it to the replica and has been applied to the local data store successfully. Continuous availability without introducing any application pauses requires the underlying distributed system to detect failures like process/machine crash, unresponsiveness or even a network partition very quickly and to take remedial action. SQLFabric uses multiple TCP and UDP based protocols to detect failure conditions within a predictable time period and uses a coordinator to maintain distributed system view consistency. i.e. making sure that when a server departs abnormally, all the other servers recognize this departure atomically to maintain coherency across the system.

SQLFabric tries to maintain the configured redundancy level for tables automatically even when exposed to node failures. i.e. if a failed server doesn't re-appear within a configurable time window, the lost copy of data is resurrected among the other members assuming sufficient capacity is available.

SQLFabric provides 3 levels of protection for application data.

1. In-memory replication
2. Optional storage to disk: Each replica stores the data to disk concurrently and the system can recover from the disk store even if the entire cluster were to go down gracefully.
3. Asynchronous replication to remote clusters for disaster recovery: All data changes can be replicated asynchronously and in batch to one or more remote clusters allowing the application clients to failover in case the entire cluster were to go down or become unreachable. Unlike traditional solutions for disaster recovery (DR) where the backup site is on standby, SQLFabric supports active-active clustering over the WAN boundaries as well. This means application clients could be read and writing to either cluster and could failover from one to the other. Given the asynchronous nature of replication, applications should be able to cope with the potential for inconsistency at the time of failover.

Dynamically grow or decrease your cluster size

Like mentioned earlier, SQLFabric provides support for an elastic data cluster - servers can dynamically come and go to serve a growing or shrinking demand pattern. When a new server announces its availability, SQLFabric initiates a non-blocking algorithm to figure out which data buckets should be moved to the new server without introducing any contention points to current application clients. The data is first copied and all updates on the data being copied are simultaneously applied to the new member to ensure data coherency. With built in instrumentation that captures throughput and latency metrics, CPU/network/memory utilization, applications can be programmed to sense changing performance patterns and proactively provision extra resources and trigger rebalancing. The end result is predictable data access throughput and latency without the need to overprovision capacity.

Route your processing logic to data

Applications execute stored procedures in the database to move the data intensive logic close to where the data resides to get performance benefits. Besides supporting execution of stored procedures (like in common relational databases), SQLFabric extends the concept by allowing applications to hint the data the procedure is dependent on.
When hints are provided, instead of randomly selecting a server to execute the procedure on, the server that owns the data is automatically selected for execution. SQLFabric servers execute in a Java virtual machine and all stored procedure is required to be written in Java. These stored procedures are executed in-process to where the data resides providing direct access to data in process heap.

SQLFabric can also be used in an embedded data server within popular containers like Spring or JEE. When used within a spring container for instance, aspects could be developed such that bean method invocation could transparently be routed to the node with the data set required by the bean.

Parallelize your data intensive operations with HA

When stored procedures are invoked, the application hint can be a single key, a set of keys or a set of filters ("where" clauses).
If the data set is partitioned across several servers then the procedure invocation is parallelized to the servers that host the hinted data. Each parallel execution can stream its results back to a coordinating server which in turn can aggregate the results using a application supplied callback. This is similar to the "map-reduce" pattern popularized by Google to parallelize data intensive operations and a reduction phase to compute a single result. The application that invokes the stored procedure is abstracted away from all this parallelization and reduction. Any failures in procedure execution (such as server crash or a problem within SQLFabric) will automatically result in idempotent re-execution of the procedure. This means, now, application behavior along with data can be highly available.

When should I use SQLFabric?

The primary motivation for the use of SQLFabric is performance - throughput and/or latency, scalability and availability. Additionally, if you are trying to distribute a Relational Database across a cluster of machines, such as a grid or cloud, SQLFabric's distributed, shared-nothing architecture can simplify your deployment while offering linear scalability.
If your data set is rather small, your application concurrent access requirements are low and the application workload is mostly just queries, then, you might just be able to fit all the data in the database buffer cache.

The common deployment patterns for SQLFabric range from a embedded distributed SQL cache to being the primary database for your application. The combination of distributed data management and parallel behavior execution provides a platform for building extreme transaction processing applications (XTP).

The answers to the following questions will likely determine if your application is a candidate for SQLFabric:

1) Is the data set partitionable? SQLFabric heavily relies on horizontal data partitioning to spread the data across multiple servers. When tables are partitioned, rows from multiple tables are typically colocated based on foreign key relationships or based on a shared partitioning key (in one or more columns) that is common across the tables. When complex queries with joins are issued, SQLFabric prunes the execution to one or more servers based on the filter conditions. When joins are involved, SQLFabric 1.0 will only be able to execute the join if the joined rows are colocated. Even though multiple strategies exist to execute complex join queries that operate on non-colocated data, this typically will involve using stored procedures that might break down the query into multiple queries. SQLFabric can parallelize query execution across the data servers, but, the joined rows have to be colocated.

2) Is your application bottlenecked on DB writes? One of the big advantages with SQLFabric is that all data can be managed in memory with one or more copies of data spread across the cluster. And, even when writing to disk, SQLFabric doesn't seek to a disk location nor flushes all the way to disk. Relational databases typically will seek and flush every transaction to disk and the maximum throughput will typically be capped at about 200 writes/sec on commodity disks. Compare this to about 30K transactions/sec achievable with SQLFabric with just a pair a servers (one primary and a backup). Note that even the use of solid state drives may not make a big difference given the contention points created by locking strategies in relational databases.

3) Is scale-out important? Most applications have to deal with sporadic loads where the demand is far higher than steady state. Your concurrent load might be low today but expected to grow over time or worse be unpredictable and driven by market conditions. In such situations, you want your design to be horizontally scalable - especially your data tier.

4) If the application is read-mostly, is the data set large enough? If the data set can be partitioned across many nodes in-memory, then, the computational intensive queries can be load balanced across many servers providing a lower and more importantly a predictable response time for the queries. Note that queries that involve joins, function invocations, aggregations and ordering can result in scans over large data sets and create temporary data sets consuming CPU and space. When the concurrent query load is somewhat unpredictable, i.e. the complexity of the queries cannot be determined, then, a database that seems to perform well in tests may not exhibit the same characteristics when all concurrent activity results in complex queries being executed. By partitioning and the ability to increase the capacity on demand, SQLFabric can help mitigate this problem.

5) Does your stateful application need high availability? High availability (HA) is all about the pursuit of continuous availability. Thinking about continuous availability requires redundancy in application behavior execution mechanisms, redundancy in access to the data or the manipulation of shared data, let alone dealing with network failures. As explored above, SQLFabric provides multiple levels of protection for data and behavior to realize a design for continuous availability.

6) Is your data set too large to fit in distributed main-memory?
SQLFabric data structures used by the query engine are primarily optimized for memory and not disk access. All keys and indexes are always managed in memory. So, if your data set size is disproportionally large compared to your memory capacity, you may not gain from a performance standpoint with SQLFabric. If other factors like scaling with commodity servers and availability are still important, SQLFabric will be of value.

Common deployment topologies

Embedded cluster

SQLFabric data members can be embedded within your application JVMs. You can embed SQLFabric as a clustered database as part of the Application server cluster (like Tomcat or Oracle WebLogic). There is no need to manage any external processes and becomes a very simple deployment architecture. Applications can switch from using the embedded mode to one where data is managed in SQLFabric servers just by switching to a different JDBC URL.
The embedded mode database is similar to using an embedded database like Derby(Java DB) or H2, except now the data can be replicated and partitioned and the application controls explicitly if the tables are only in memory or disk or both.
All the embedded data members form a peer-2-peer cluster with direct connectivity to each other. The embedded model is very attractive especially when storing session state or state such as web content that is frequently accessed and can be accessed directly from within the process heap.

Client server

When the number of client processes is very large or come and go, the embedded approach may not scale. Instead, data can be hosted in a farm of SQLF servers (called FabricServers) and the clients can load balance the connections across the data fabric cluster. All fabric servers become part of a single distributed system and have direct connectivity to each other but the clients only connect to one server at a time. The client driver when using this mode is very light weight (< 1MB) and is the only option for non-Java SQLF drivers (ADO.NET).

Multiple clusters across WAN

The client-server model can be extended by asynchronously replicating all or subset of the tables to other remote clusters (distributed systems). Effectively, one can achieve infinite scaling by daisy-chaining multiple distributed systems. This model will typically be used either for data visibility across multiple data centers or as a mechanism for disaster recovery (DR) - clients will normally connect to the SQLFabric cluster in the local data center but if visibility to the entire cluster is lost, then, the client can failover to the DR cluster.

Working with existing relational database( SQL distributed cache )

When SQLFabric is used as a database cache, these are the choices:

1) Lazy cache loading: Here application query requests are first attempted on SQLFabric and on a miss a 'loader' implementing RowLoader is invoked that can fetch the data from a backend database. It is important to note that SQLFabric only supports primary key based queries when the table is lazily loaded. This is because it is impractical (too expensive) to determine the subset of the rows missing in SQLFabric and be able to construct a query to the backend database that only fetches the missing rows. It is also typical for applications to configure LRU caching where the cache optimizes the available memory by evicting the least frequently used rows. Applications can only issue primary key based queries on tables that are configured for eviction. All updates can be synchronously or asynchronously written back to the database using a 'writer' or the built in DBSynchronizer. For tables that are identical in structure to the backend database, applications can asynchronously "write behind" by merely configuring the DB URL for the back end database.

2) Preload database: Load the entire database at bootstrap time into SQLFabric and if all updates are going through SQLFabric there is no need to re-synchronize the state from the backend database. The data bootstrapping process is outlined below. With this choice, the tables are loaded with the entire data set from the backend database and all queries can directly be executed on SQLFabric data nodes.

3) Preload "hot" data and lazy load the rest: If the data set in the backend database is very large, a better option would be to maintain all the "hot" tables in SQLFabric and lazy laod the historical data into "historical" tables. With this design, the entire supported SQL syntax can be used on fully populated "hot" tables, but, only primary key based queries can be issued on historical tables. Typically, these historical tables will be configured for LRU eviction.

4) Preload "hot" data and virtualize the rest: SQLFabric supports creation of virtual tables - these are implemented as virtual functions that look like a table to the application. These virtual tables can be used in queries and return result sets that can be joined with data from real tables.

SQLFabric can also be integrated in OR mapping products like Hibernate and provide a L2 cache - entire query result sets can be cached in SQLFabric. Updates done using the Hibernate API will be synchronously propagated to the database and query result sets will be invalidated in SQLFabric.

Application developers can use a tool like DDLUtils to export existing relational database schema, transform this to SQLFabric and import the new schema along with data into a SQLFabric cluster.

How is it different than traditional relational databases?

It is important to note that unlike traditional databases, not all operations with SQLFabric are constrained by ACID properties. By providing the control to users, users get to make the choice based on their performance, availability and consistency requirements. Even though consistency and integrity of data is key element to the design, the design assumes abundant memory and relaxes some of the transactional properties. Read the 'key features' description above for more differences.

How is it different than GemFire Enterprise (object data fabric) and object data grids?

SQLFabric uses GemFire Enterprise as its underpinning and benefits from the years of testing and reliability built into the distribution sub-system. SQLFabric incorporates a more sophisticated SQL query engine that compiles a query plan into byte codes and also has a much more sophisticated cost based optimizer. The configuration and deployment model with SQLFabric is simpler and intuitive to anyone with experience and background with relational databases. Unlike some of the other popular data grids, GemFire SQLFabric (and ObjectFabric) offer native persistence and recovery capabilities. The product can be used as a distributed data store.

The product is based on standards like SQL, JDBC and ADO.NET making it very straightforward to adopt in existing applications based on relational databases. The configuration and deployment is also much simpler and the product can be used in a large eco-system of other products and frameworks - Object relational mapping tools (Hibernate, NHibernate, etc), schema editing and database management tools (SquirrelSQL), DB replication products, CDC (Change data capture from external relational databases), Spring JDBC, etc.
Applications that can stick to standard SQL syntax supported by SQLFabric can easily migrate to/from other relational databases.

How does it compare to in-memory databases?

Main memory database offerings were fundamentally designed to keep all data in memory in a single process space. Even though, most support replication for failover, they are similar to relational databases where all updates are routed through a single primary. The underlying design doesn't support partitioning data sets across a farm of servers, let alone allow the cluster size to grow or shrink at runtime.

Main memory databases work great when the data set size is small and the concurrent access requirements are modest. SQLFabric data and processing is distributed in as many servers as required to handle the volume and load.

How does it compare to other "cloud" or "no sql" databases?

NoSQL and Cloud databases provide scalability and high availability properties for web applications but typically at the expense of data consistency. For instance, Amazon's Dynamo uses a eventually consistent transaction model and others only allow a single entry update as part of the transaction.
SQLFabric's design, though driven by horizontal scalability and availability, imposes fewer restrictions. For instance, transactions can span multiple tables but does require the involved rows to be colocated in a single partition.
'NoSQL' databases either only provide key based access or use a proprietary syntax for queries. And, those that do support queries do not support joins. The premise behind SQLFabric is to capitalize on the power of SQL as an expressive, flexible, very well understood query language, but alter the design underpinnings in common databases for scalability and high performance. By providing ubiquitous interfaces like JDBC and ADO.NET the product becomes significantly easier to adopt and integrates well into existing eco-systems.
SQLFabric, in a vein similar to "key-value" databases, is highly optimized for key based access but doesn't limit applications from doing complex queries involving joins, functions, aggregations and grouping. In most cases, query response times will be significantly better than disk oriented databases.