Features

SQL Support

Supports:

  • T-SQL (easy to port to/from SQL Server)
  • Stored procedures
  • Joins: inner, outer, left, right
  • Sub-selects
  • Common Table Expressions (CTE)
  • Free text
  • Multi table updates/deletes

Doesn't support:

  • Cursors
  • Views
  • Triggers
  • User defined data types
  • CLR procedures

Security

ScimoreDB security model supports users, database roles and performance roles. Windows authentication is supported too. Privileges can be granted/denied at database role or per particular user level. When installed, database is not secured. Security can be enabled by few SQL commands. For more read security documentation.

Connectivity

Database can be accessed using C++ library or .NET provider. Check how to samples for more details .

C++ Client

C++ library consist of Scimore.Data.ScimoreClientNative.lib and Scimore.Data.ScimoreClientNative.dll files.

A C++ application must link with lib and place DLL together with applications' executable.

C++ library allows asynchronous SQL execution, using callbacks for completion notification.

.NET Provider

The .NET provider default version targets .NET4.0 Framework. It means it can be used with VS2010 only. However, .NET 2.0 provider is available too as the separate download.

The provider runs on both x86/x64 (All CPU). No additional assemblies are required for client/server or embedded db access.

Transactions

Transactions are ACID compliant using D2PC.

Dynamic two-phase commit protocol (D2PC) used to coordinate the transaction final state. Since transaction coordinators chosen dynamically, in an event of failures, is not possible to enlist the failed transactions. Therefore, we extend D2PC with an additional phase - In-Doubt.

In a distributed environment, each involved node will execute transaction locally, and, then when ready to commit, will communicate with other nodes to agree the final transaction state. If communication is broken the local node can no longer decide should it commit or abort. Therefore, it goes to In-Doubt state. The In-Doubt state means that all transactions' activities will be frozen - no reads/writes, don't commit/abort. The In-Doubt state activation can be triggered by the cluster configuration changes, failover, sudden loss of network connections, unexpected client connection termination, etc... There is a guarantee, that if at least one node made transaction In-Doubt, it will be eventually set In-Doubt on all nodes, unless the node is voted off. When all nodes set their local transaction to In-Doubt, a separate program controlled by nodes "ScimoreAgent", globally will look at the states of local transactions and decide the final transaction state.

To ensure consistency, the modified row is protected with the row-level lock. The combination of two-phase commit and row locks may lead to distributed deadlocks. The occurrence of distributed locks periodically (100 ms) checked by ScimoreAgent, and, if found, the victim transaction will be aborted.

Selects by default use multi version concurrency control (MVCC) protocol. The distributed query optimized will load balance MVCC queries, such, some transactions will access one node per partition group and other transactions other node. It's like RAID, writes goes to all nodes per partition, and reads from any node.

Query prioritizing

The database has been designed for processing many concurrent transactions in parallel. This may lead to cluster overloading, when demand of resources (memory, disk, network, CPU) is greater than available on the system.

To deal with the problem, ScimoreDB allows assigning priorities to users/queries. The priority [1,100] sets the aggressiveness of accessing the resources. For instance, reporting queries may complete slower (when running with the low priority), while other, e.g. business logic, must maintain minimum response latency at all times.

Additionally, the query priority decreases "throttle" when using too many resources per transaction. Such a single clients' transaction will not affect performance of other concurrent transactions with the same priority. Worse the query is; the priority should decrease. The throttling decreases the priority using exponential decay half-life, where configured number of resources must be consumed before priority half. The throttling parameters can be set per query/user. Also throttling can be disabled.

Partitioning/Sharding

Database is shared nothing cluster. The cluster made of "partitions", each partition handles a set of horizontally partitioned data. The partition reads/writes data to nodes. Each node is executable (scimoredb.exe), and, nodes that belong to the same partition will replicate all data changes to each other. It's like a RAID 1, where each node is hard disk, except there can be more than 2 nodes. If one or more node(s) become unavailable, the remaining partition nodes will vote to take them offline in the cluster (as long as voters can make the majority). The cluster can have maximum 1024 nodes; the partition can have max 32 nodes. The maximum partitions are limited only by max nodes. So, if partitions have only 1 node, there can be max 1024 partitions per cluster.

How partitioning works? The cluster horizontally partition rows according tables' column hash value (partition columns set in CREATE TABLE command). The cluster uses hash range to identify the partition owning the row. The maximum clusters' hash range is [0, 1024[, therefore hash value is additionally modulus divided by 1024. Each partition group is responsible for the particular hash range. For example, a cluster with a single partition group, the partition hash range will be [0, 1024[. With 2 partition groups, the ranges are [0,512[ and [512, 1024[.

Parallel execution

Each SQL/DQL is additionally optimized for the best communication strategy within the transaction. The transaction may involve a single node, 2 nodes (node redirects to node), or many nodes. When transaction/query requires more than 2 nodes, the tree communication and execution optimization will be applied. For example, let's assume the cluster of 256 nodes and the query that needs aggregate all rows on all nodes. The tree execution optimization will start with a single node(0) which will start processing the query locally and send the query to node (1) and node(2) in parallel. While node(0) is processing, the nodes(1,2) each will start processing locally and pass the query to other neighboring nodes in parallel too. Each node that sends the query to 2 other nodes will become a sub-coordinator, which will merge/aggregate results from the neighboring nodes with the local result and pass it to the parent sub-coordinator.

The advantage of tree execution processing is that instead of a single node communicate with all 255 nodes, it will communicate only with 2 neighboring nodes, and neighbors with their neighbors in parallel, achieving maximum query parallelization.