After 2 years of development, ScimoreDB version 4.0 is now officially released! New features enhance distributed cluster management, scalability and programmability.

ScimoreDB version 4.0 adds new scalability capabilities to meet new standards by growing NewSQL market; it includes significant enhancements in cluster management scaling reads and writes of large active datasets, monitoring, and self-healing. Now, scaling ScimoreDB is a matter of simple SQL command. Use SQL, to add or remove nodes, while cluster continues to be operational.

The ScimoreDB 4.0 adds new feature: the ability to prioritize the queries. It helps to deal with the issues like - one query kills all. The database dynamically prioritizes queries, and, when query becomes too aggressive, the engine will decrease ("throttle") the priority, preventing to capitalize the database power for a single query.

Database interoperability features improved schema and data importing from SQL Server, and, ScimoreDB platform independent .net provider targeting .NET 2.0 or .NET 4.0 frameworks.

The ScimoreDB is ideal for developers who meet scalability issues, especially when scale-up legacy RDBMS is no longer an option.

We gave a keynote on 26th of May, on the state of ScimoreDB v.4 together with a live database scalling demo. Check out embedded video below (running ~40 min., demo begins at 20 min.)

A year ago a few of us in Scimore were kicking around ideas for what features we should add into the next version of distributed database. The result is v4.0 Scimore distributed, a simple to use, fault tolerant, SQL, ACID compliant database. There are a bunch of reasons why we think it’s cool, but our favorite is how it helps people to store a large amount of data using SQL.

We launched a beta version with a wiki. To download database, see http://www.scimore.com/wiki and refer to installation page.

We'd love to know what you think.

ScimoreDB is shared nothing distributed database system. It can run 100s' of nodes, and, is highly available with toleration to the hardware failures. Database horizontally distributes data among partitions. Each partition has multiple data nodes (scimoredb.exe), where data is replicated within nodes in the partition. The partition is also called partition group to indicate there are group of the nodes in a partition. 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 limitations: 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 partition columns' 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[. To see partition hash ranges, execute SQL: select * from system.sysinstances, columns v_lower,v_upper.   

We are now working on version 4.0 – focusing on the ScimoreDB Distributed.

The main goals is to make ScimoreDB

• Build in fault tolerance, no more shared storage and clustering software required.
• Continuously running, dynamically add/remove machines while staying online
• Massive scalable (100+ machine clusters)
• User controlled clustering topology, enabling designing clusters for extreme safety, high writes and/or high reads.

With the improvements we want to maintain our current:

• ACID, to ensure the safety of your data.
• SQL / T-SQL, to keep low learning entry barrier.
• ADO.NET interface
• Upgrade from embedded/server to distributed by simple backup/restore

We are very interested in feedback and are looking for people who will try the early stages.

Please send us an email at support@scimore.com and we will reply with the details.

Since version 3.*.*.1933 stored procedures can return output parameters. Use optional OUTPUT keyword to indicate that the parameter is a return parameter. The OUTPUT variable must be defined during procedure creation as well as during use of the parameter.

Example using output parameters:

-- create procedures
create procedure _output_inner_test1 ( @myparam1 int,@myparam2 int output,@myparam3 int output)
as
begin
set @myparam2 = @myparam1 + 100
set @myparam3 = @myparam1 + 1000
end;

create procedure output_test1
as
begin
declare @in int = 100, @p1 int,@p2 int
execute _output_inner_test1 @in,@p1 OUTPUT, @p2 OUTPUT
if @p1 <> 200   RAISE EXCEPTION 'incorrect output'
if @p2 <> 1100 RAISE EXCEPTION 'incorrect output'
select @p1,@p2
end;

--run test
exec output_test1

Recursive Queries with CTE (Common Table Expressions)

Since 3.*.*.1927 db supports recursive CTE queries. Using recursive CTE it is possible to perform SQL queries on hierarchical data. The SQL syntax is identical to SQL Server and there are many articles about it. For example:

http://sqlblog.com/blogs/linchi_shea/archive/2009/04/16/recursive-sql-queries-how-do-they-work.aspx
http://msdn.microsoft.com/en-us/library/ms186243.aspx

Hierarchical relations can be expressed as a recursive single tables' join. For example, consider table:

create table objects
(
id int not null primary key,
name varchar,
parent_id int
);

And insert:

insert into objects(id,name,parent_id) values(1,'Cars',null);
  insert into objects(id,name,parent_id) values(2,'Porsche',1);
    insert into objects(id,name,parent_id) values(3,'911',2);
    insert into objects(id,name,parent_id) values(4,'Boxster',2);
    insert into objects(id,name,parent_id) values(5,'Cayman',2);
    insert into objects(id,name,parent_id) values(6,'Cayenne',2);
    insert into objects(id,name,parent_id) values(7,'Panamera',2);
  insert into objects(id,name,parent_id) values(8,'Aston Martin',1);
    insert into objects(id,name,parent_id) values(9,'DB7',8);
    insert into objects(id,name,parent_id) values(10,'DB9',8);
    insert into objects(id,name,parent_id) values(11,'Vantage',8);
    insert into objects(id,name,parent_id) values(12,'One',8);

Here we have created hierarchy using parent_id field that refers to the parent row: "Cars" is the root (no parent), 2 brands (Porsche and Aston Martin) belongs to "Cars" and each brand has a list of models. Now, define the recursive CTE SQL to read all porsche models:

WITH Cars(id,parent_id,name,path,level) AS
(
   --initialization. read porsche root
   SELECT id,parent_id,name,name,0 as level
   FROM objects
   WHERE id = 2 -- porsche root
   UNION ALL
   --recursive execution
   SELECT o.id,o.parent_id,o.name,concat(c.path,'/', o.name), c.level+1
   FROM cars c
      INNER JOIN objects o ON c.id = o.parent_id
)
select * from cars;

The result:
id    Parent_id  Name        Path                     Level
----------------------------------------------------------------------------------------
2                1  Porsche     Porsche                      0
3                2  911           Porsche/911               1
4                2  Boxster     Porsche/Boxster          1
5                2  Cayman    Porsche/Cayman         1
6                2  Cayenne   Porsche/Cayenne         1
7                2  Panamera Porsche/Panamera       1

Example using parameterized query and ordering:

Declare @id int
set @id = select id from objects where name = 'porsche';

WITH Cars(id,parent_id,name,path,level) AS
(
   --initialization. read porsche root
   SELECT id,parent_id,name,name,0 as level
   FROM objects
   WHERE id = @id
   UNION ALL
   --recursive execution
   SELECT o.id,o.parent_id,o.name,concat(c.path,'/', o.name), c.level+1
   FROM cars c
      INNER JOIN objects o ON c.id = o.parent_id
)
select * from cars order by name;

Hi,

My name is Marius Slyzius and I will be posting a series of the blobs about distributed database:

1. Installing cluster.
2. Create schema. Replicated/Partitioned tables. Execute distributed queries. DQL query plans.
3. Designing "true" shared-nothing schema. Pros/Cons
4. Setup failover/failback

Today is about how to install the cluster.

ScimoreDB distributed is a database where data is partitioned or replicated among X nodes. The node is a database server what was joined to a cluster by SQL command "ALTER CLUSTER". The number of nodes can be 2^n-1 = {3,7,15,31,63,...}. This number of nodes needed to form the binary tree, where a  node can send requests to 2 its childs and the childs to 2 their childs. For example we have cluster of 7 nodes and the client sends the query to node 4. In such case, the node 4 will send the query to childs 3,5 , and, each child will send to its childs 3->1,2 and 5 ->6,7. Everything happens in parallel. It is  similar to map/reduce, except that the node 4 don't need to send the request to all nodes, only to 2 its childs. The advantage of what is that we can do  reduce in parallel as well. For example, "select count(*) from mytable" sent by client to the node 4. Node 4 will start counting rows and sends SQL to 3 and 5 nodes.  Node 3 will start counting rows and pass to 1 and 2 nodes to count. Nodes 1 and 2 will count rows , but don't send sql anymore, since there are no childs.  The same will happen with node 5. Now, when node finished counting rows, the aggregation phase starts. In parallel, node 3 and 5 will receive the  counted rows from their childs and sum it with local row count. Each node did aggregation of local rows in parallel, and aggregation of aggregated data from  (1,2,6,7) on 3 and 5 in parallel too. Finally 4 will aggregate its counted rows with the aggregations from 3 and 5 nodes.

ScimoreDB uses DQL language to define how distributed query is executed. SQL is always converted to DQL. Before executing DQL on distributed DB it 
will be optimized for distributed query, for example, select rows only from a single node or all, add merge aggregators, stream splitters, etc...
To see DQL for SQL, press show query plan button in query window in the manager.

So, how to create distributed DB? First, you need to install db servers. I have created 4 batch files (to install 3 servers, uninstall 3 servers , start 3,and stop 3). All DB servers will be managed on a single PC, however, it could be 3 different machines preferably. The batch files located in "c:\db" folder together with scimoredb.exe (install ScimoreDB version 3 on machine and copy exe from there).

Install.cmd:
mkdir node1
mkdir node2
mkdir node3
scimoredb.exe -startup/instance=0 -startup/mode=1 -startup/console=1 -net/endpoint="localhost:999"    -db/cachepages=5000 -db/systables="c:\db\node1" -db/syslog="c:\db\node1"  -db/data="c:\db\node1"
scimoredb.exe -startup/instance=1 -startup/mode=1 -startup/console=1 -net/endpoint="localhost:1000"  -db/cachepages=5000 -db/systables="c:\db\node2" -db/syslog="c:\db\node2"  -db/data="c:\db\node2"
scimoredb.exe -startup/instance=2 -startup/mode=1 -startup/console=1 -net/endpoint="localhost:1001"  -db/cachepages=5000 -db/systables="c:\db\node3" -db/syslog="c:\db\node3"  -db/data="c:\db\node3"

Parameters:
-startup/instance={0,1,2} - instance id is the registry key to separate startup parameters of each DB on the same pc. HKEY_LOCALMACHINE\Software\Scimore\Nodes\[0] ... [2]. Also, instance id is added to the end of DB service name. {scimoredb-0,scimoredb-1,scimoredb-2}. Note, instance id's has nothing to do with the nodes in the cluster. If you install databases on distinct pc's set instance=0 for all them.

-net/endpoint="localhost:999-1001" - for single DB server, used only port the database listens. "localhost" has no meaning yet. Database listens on all IP addresses. However, when you join databases to the cluster, each database will become node and connect to each using  net/endpoint host:port value. In my case all 3 runs on the same machine, so it’s fine. But, if nodes are on different machine you need to specify correct either IP:port or host:port. Use "host:port" if IP might changes. Using "IP:port" you can specify different IP for comm between nodes and client/node (f.x. infinity band over socket network) for faster communication between nodes than clients to node(s). If the node can't be accessed anymore you need to update [endpoint] value for this node on all nodes. The information is stored in "system.sysinstances" table(nothing to do with -startup/instance). Again, if at least one node inaccessible you will not be able to connect and update the table with new values. To resolve it, start each node with the following parameters (first kill/stop node) and from cmd:
>scimoredb.exe -startup/instance=0 -startup/console=1 -net/mode=1 -net/endpoint="localhost:999"
When node starts it will ignore cluster setup because of net/mode=1 parameter and start single server listening on 999 port. Then, connect with the manager and update table sysinstances in system catalog with correct IP/Host/Port values: update system.systables set [endpoint] = "newIP:port" where instanceid=x.  Here, " instanceid" is not instance we used to install, but it is the node ID. Repeat it for each node. When done, restart services and you should be able to connect again.

-db/cachepages=5000 - number of the data pages to cache. Each page is 8kb, more you add better it is. The value can be changed in the registry, followed by service restart.

Uninstall.cmd:
scimoredb.exe -startup/instance=0 -startup/mode=2 -startup/console=1
scimoredb.exe -startup/instance=1 -startup/mode=2 -startup/console=1
scimoredb.exe -startup/instance=2 -startup/mode=2 -startup/console=1

-startup/mode=2 - tells to uninstall and delete data files. Startup/mode=20 will uninstall, but leave data files. Note: systables will contain cluster info and later if you install on top new instance without data overwrite, you won't be able to connect to db, because it will "think" it is in the cluster and try to access to other nodes listed in sysinstances table.

Start.cmd: start 3 databases on local pc.
net start scimoredb-0
net start scimoredb-1
net start scimoredb-2

Stop.cmd:
net stop scimoredb-0
net stop scimoredb-1
net stop scimoredb-2

Lets make a cluster now:
1. Stop scimoredb-0 service. Run uninstall.cmd. Check if scimoredb.exe process is not running, if it does kill it and run script again. Because, I have installed scimoredb with MSI and it did installed scimoredb-0 (0-instance), so I uninstall it. I could just create new instances {1,2,and 3} and leave 0 running.
2. Run install.cmd. The script will create 3 databases and each database will place system tables under Node1/2/3 folder. Then install finished, verify if NodeX contains files and if services scimoredb-{0,1,2} created.
3. Run start.cmd. Check if 3 scimoredb.exe processes running. Start manager and verify the connection to each database.
4. With manager connect to localhost/ port 999(database 0 on my pc) and in query window execute SQL command:
alter cluster
(
add endpoint 'localhost:1000',
add endpoint 'localhost:1001'
)
NOTE: make sure endpoint 'localhost:1000' matches endpoint value used when installed databases (-net/endpoint="localhost:999...).

Restart the manager. Connect to any database. Under "Cluster" tree node in the manager you will see 3 servers for each database/node you connected. Your 3 databases now in a cluster and you are ready to create databases/tables.

If you have trouble to create cluster, ideas/suggestion please don't hesitate to contact me:

m a r i u s     a t     s c i m o r e     d o t     c o m

We have had a few support mails regarding common issue when connecting to the server.

In order to connect, use "Server=myserver/ip..." instead of "Data Source=...." in the connection string. The "Data Source" is used to specify the embedded database path.  Here is a link for connection strings in scimoredb: http://www.scimore.com/howto/getstarted/connection-strings

As the new version approaches the final cut, we would like to highlight some of the features that were not documented yet:

Locking and multi version concurrency control (MVCC)
ScimoreDB uses locking to prevent readers to read uncommitted rows. By default, readers use MVCC and data modifications row level exclusive locks. Readers will only read last committed rows' version, if concurrent transaction modified row and not committed it yet.
Here can be potential issue with concurrent updates when index field(s) being updated (the row will be re-inserted). For example, let’s say reader transaction selects fields from the table based on indexed field field1, where field1 > 100. The query will look up the lowest row where field1 > 100 and continue reading all rows to the end of the index. At the same time, assume the update updates a single row where field1 = 500 to 50000. The database will mark the current row as deleted and insert new row much further in the index. So, when reader reach deleted row, it will read it, since modified transaction has not yet committed and when reach row with field1 = 50000, skip it, because transaction still not committed. Now, consider the transaction that modified row has committed right after reader has read field1=500. When reader will reach field1=50000, the row will be read too, since update transaction has been committed. In such case, the reader has read the same row twice: the old version field1=500 and the new field1=50000. Hence, this is not happens when update in place occurs (updating field that is not part of the index). To avoid such case, reader may add additional locking hints:
select * from mytable xlock row - turn off MVCC and use exclusive row level locks, like with update/delete/insert.
select * from mytable read consistent - when reader reach modified/new row that was not committed yet, it will wait for the transaction to commit.
Another solution is to use READ REPEATABLE transaction isolation level.

Transaction isolation levels.
The syntax either use:
begin tran <isolevel> ... commit/rollback

or

set transaction isolation level <isoLevel>; SQL command;

ScimoreDB supports 3 isolation levels (<isoLevel>):

1. READ COMMITTED - this is default isolation level. Readers will read only committed rows. Using locking hints you can optimize how to deal with modified/new not committed row as described above. The update/insert/delete will always use row-level locking and if row is modified by concurrent transaction will wait for commit.
2. READ REPEATABLE - the isolation level is similar to SNAPSHOT isolation level in SQL Server. When read repeatable transaction starts it will see all rows at the point it started and any further modifications will not be visible for the particular transaction. For example:
   1st transaction updated row from value 0 to 100, but not committed yet. When starts second READ REPEATABLE transaction, at this point the rows' value will be 0 if it will read, since the 1st transaction has not committed when 2nd started. 3rd transaction starts, modify another row, set value 1 to 101 and commit. Still, the 2nd transaction will not "see" the change. However, other new started transactions will see the change made by the 3rd transaction. If DB crash at this point, recovery will apply 3rd update and abort 1st transaction update, since it was not committed during crash.
   READ REPEATABLE uses row versioning to store old versions of modified rows located in "sysundo.dat" file. Therefore, “sysundo.dat” file may grow in size until commit.
   Using SET TRANSACTION ISOLATION LEVEL REPEATABLE READ will commit current transaction when the reader read all rows. For example, SQL batch:
   SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
   select * from mytable  - until the client read all rows, the read repeatable transaction will not commit and maintain row versioning in "sysundo.dat".
3. SERIALIZABLE - only 1 transaction at the time.

How to shring sysundo.dat & sysredo.log.
Sysredo.dat file is the transaction log. It always grows until checkpoint. By default, the checkpoint occurs every 5 minutes (it can be changed in the configuration). So, for example, single transaction inserting/updating/deleting many rows may noticeably increase sysredo.dat file size. Even, if after checkpoint the file space will be re-used, it might be ok for servers, but unacceptable for the embedded database. In such case you may execute: TRUNCATE TRANSACTION LOG sql command to truncate “sysundo.dat” and “sysredo.log” files. Note, the truncate transaction is serializable, i.e. no other transaction will run until it done and, also, the truncate will wait for all pending transaction to commit without allowing new once to start.

Disk space savings in the new engine.
The new engine uses much less disk space, usually 4-8 times less to compare with v2.5. This has been achieved by compressing of internal rows' attributes and using new b+tree splitting algorithm that does not pre-allocate 50% (8kb) disk space on page splitting.

Why *.dat files size never change in file explorer?
ScimoreDB opens files in exlusive mode, without Windows file manager cache. Therefore, the file size change is invisible in explorer. To see the current size of the table you can use manager, right click on the table, choose alter table... and then click on statistics tab, or, re-start the database.