Let's start by installing manager GUI application and .NET provider. Go, to download page, download x64 MSI package and install. During installation, uncheck the "Server" feature. After installation, verify in task manager that scimoredb.exe is not running.
Next, install cluster of 6 nodes on 3 machines. Follow installation instructions here.... And verify the cluster is operational. Open manager, connect to any installed node and execute:
SELECT * FROM SYSTEM.SYSINSTANCES
The output must be 6 rows, listing all nodes (scimoredb.exe's) in the cluster.
Now, create the first ScimoreDB distributed database. In the managers' query window , paste the database creation SQL script:
create database demo; go; use demo; go; create table customer ( c_id bigint not null partition primary key, c_name char, c_zip char ) go; create table products ( p_id bigint not null primary key, p_name char, p_price money not null ) go; create table orders ( o_id autobigint not null primary key, o_c_id bigint not null partition, o_p_id bigint not null, o_amount int, o_date datetime not null default(getdate()), o_price money not null, constraint fk_customer foreign key(o_c_id) references customer(c_id), constraint fk_products foreign key(o_p_id) references products(p_id) ) go; create procedure create_order ( @c_id bigint, @p_id bigint, @amount int) as begin declare @price money set @price = (select p_price*@amount from products where p_id = @p_id) insert into orders(o_c_id,o_p_id,o_amount,o_price) values (@c_id,@p_id,@amount,@price) end go; create procedure cust_orders( @c_id bigint) as begin select count() 'orders', sum(o_price) 'price' from customer join orders on (c_id = o_c_id) where c_id = @c_id group by c_id end go; create procedure popular_products as begin select p1.p_id,p1.amount,p2.p_name from ( select top 10 p_id, sum(o_amount) amount from products join orders on (p_id = o_p_id) group by p_id order by sum(o_amount) desc) p1 join products p2 on (p1.p_id = p2.p_id) end
Next, populate the demo database by executing SQL script:
use demo; go; begin tran declare @i int = 0 while @i < 100000 begin insert into customer values(@i,concat('customer-',@i),cast((rand()*9999) as int) + 1) insert into products values(@i,concat('product-',@i),cast((rand()*1000) as money)) set @i = @i+1 end commit
And now is the time to get hands dirty. Create VS2010, .NET40, c# project. In the project, add reference to scimore.data.scimoreclient.dll (the .NET provider usage is identical to SQL Server), start 30 threads (or more threads; hammer db as you can, no mercy, you are evaluating) where each executes
ScimoreCommand.ExecuteReader() using either SQL:
declare @c_id bigint = cast((rand()*100000) as int) declare @p_id bigint = cast((rand()*100000) as int) declare @amount int = cast((rand()*100) as int) execute demo.create_order @c_id,@p_id,@amount declare @c_id bigint = cast((rand()*100000) as int) exec demo.cust_orders @c_id
We suggest one thread running the reporting SQL.
Surround ExecuteReader() by try/catch, since, if cluster configuration changes or nodes killed, some temporary errors will appear. Might be good idea to print them to console window.
Finally, run the stress application and gather the stats. The stress application could record queries/sec, or, execute monitor queries in the manager:
select sum(tran_sec) from system.mtransactions select sum(inserts_sec) inserts_sec, sum(selects_sec) selects_sec, from system.mtables
For more options read Monitoring page.
Next, let's expand the cluster. First, create additional 6 stand-by nodes on another 3 machines. Follow the previous example, except don't execute CREATE CLUSTER. Instead, connect with manager to any node in the cluster and execute:
ALTER CLUSTER go ADD PARTITION GROUP REPARTITION WITH 0 ( add endpoint('machine4:16250'), add endpoint('machine5:16251') ); ADD PARTITION GROUP REPARTITION WITH 1 ( add endpoint('machine5:16250'), add endpoint('machine6:16251') ); ADD PARTITION GROUP REPARTITION WITH 2 ( add endpoint('machine6:16250'), add endpoint('machine4:16251') ); go; COMMIT CLUSTER
Of course, before executing cluster alter, start stress application to watch how the cluster change, affects the performance.
While running stress application, shutdown any machine. The stress application will receive a few errors, but after 20 sec, the cluster should heal itself.