Use case to sync SQL server with memstate #113

[AlexSenchenko]

Hi,

Is it valid use case for using to sync memstate with SQL server tables?
How it can be achieved?

Hi @AlexSenchenko

If the question is "Can I use SQL Server as a backing store?" the answer is yes, use the Memstate.SqlStreamStore storage provider. This means that the command journal can be persisted to any RDBMS supported by SqlStreamStore

For replication/sync there are a few different patterns that can be applied depending on the requirements. Is it one-way or two-way replication? If one-way, which direction? Sync or async replication? Consistent or eventually consistent?

I've just activated the discussions feature on GH, can you add a question there elaborate on what kind of replication you have in mind?

[AlexSenchenko]

@rofr I am looking for one-way sync: SQL Server -> MemState
So, memstate will always have the latest portion of data from the SQL server.
Use case of this scenario is to speed up API, which will not use an extra call to the database.

So my answers are:

1. One-way - as it is simpler.
2. Async-replication - as it faster
3. Strong consistency - as only one server.

[rofr]

async and strong consistency is not an easy combination :) Async means allowing the replica to lag behind, you are sacrificing consistency for performance.

[rofr]

Here's one way to do it. For each table you want to replicate, create a trigger in the db that captures the change and writes to an events table. In the process where memstate is running, poll the events table and update the in-memory model.

Now you can start migrating reads from the db to memstate.

A nice thing about this pattern is that you can start from zero and grow the in-memory model gradually.

To rebuild state during startup you can either load memstate normally from backing storage or do an initial read from the database tables. If reading from the database you don't need any backing storage for memstate.

[AlexSenchenko]

@rofr Do you any sample or start point for implementing this approach?

[rofr]

No but I will try to put a proof of concept together during the holidays.

[rofr]

Here's an embryo to get started on the sql side, note that this could be generated from an Entity Framework model.

-- An example table
create table Product(
	Id int identity(1,1) not null primary key,
	Name varchar(max) not null,
	Description varchar(max) not null,
	UnitPrice decimal(18, 2) not null,
 )
go

create table MemstateChangeLog (
   Id int not null primary key identity(1,1),
   TableName varchar(max) not null,
   Operation char(1) not null, -- 'U' or 'D'
   TransactionId int not null default current_transaction_id(),
   JsonData varchar(max)
)
go

create trigger OnProductsUpserted
on Product after insert, update as

declare @json varchar(max)
select @json = (select p.* from product p join inserted i on p.Id = i.Id for json auto)

insert MemstateChangeLog(TableName, Operation, JsonData)
values ('Product', 'U', @json)
go

create trigger OnProductsDeleted
on Product after delete as

declare @json varchar(max)
select @json = (select id from deleted for json auto)

insert MemstateChangeLog(TableName, Operation, JsonData)
values('Product', 'D', @json)
go

-- Setup completed, now lets do some inserts, updates and deletes

-- should be empty
select * from MemstateChangeLog

insert product(name, description,unitprice) values('1kg Cod', 'Lean white fish', 200)
insert product(name, description,unitprice) values('1kg Salmon', 'Pink and healthy', 150)

update product -- multiple rows affected
set unitprice = unitprice * 1.1
where Id < 5

delete product where id > 999980

-- And the results...
select * from MemstateChangeLog

Id	TableName	Operation	TransactionId	JsonData
1	Product	U	1395787	[{"Id":1000002,"Name":"1kg Cod","Description":"Lean white fish","UnitPrice":200.00}]
2	Product	U	1395798	[{"Id":1000003,"Name":"1kg Salmon","Description":"Pink and healthy","UnitPrice":150.00}]
3	Product	U	1395801	[{"Id":4,"Name":"Product 3","Description":"Product description 3","UnitPrice":6779.63},{"Id":3,"Name":"Product 2","Description":"Product description 2","UnitPrice":9306.11},{"Id":2,"Name":"Product 1","Description":"Product description 1","UnitPrice":9900.22},{"Id":1,"Name":"Product 0","Description":"Product description 0","UnitPrice":8803.96}]
4	Product	D	1395809	[{"id":1000003},{"id":1000002},{"id":999990},{"id":999989},{"id":999988},{"id":999987},{"id":999986},{"id":999985},{"id":999984},{"id":999983},{"id":999982},{"id":999981}]

[rofr]

The c# bits to query the change log could perhaps look something like this:

var lastId = 42; // you need to keep track of the last position
while (true)
{
   var sql = $"SELECT * FROM MemstateChangeLog WHERE Id > {lastId}";
   var reader = new SqlCommand(sql, conn).ExecuteReader(sql, lastId);
   while(reader.Next())
   {
      lastId = (int) reader["Id"];
      //todo: update the model
   }
   await Task.Delay(delay);
}

The model itself can be as simple as:

public class MyCache
{
   public Dictionary<int,Product> Products{get;} = new Dictionary<int,Product>();
}

and the intitial loading something like:

var sql = "BEGIN TRAN; SELECT * FROM PRODUCT; SELECT Current_transaction_id(); COMMIT TRAN;";
var reader = new SqlCommand(sql).ExecuteReader();
while (reader.Next()) myCache[reader["Id"]]  = ProductFromRow(reader);
reader.NextResultSet();
reader.Next();
var transactionId = (ulong) reader[0];

//query to get the id to start synching from
var lastIdSql = $"select top 1 Id From MemstateChangeLog where TransactionId > {transactionId} order by Id";

[rofr]

DISCLAIMER: This is just an idea typed straight into the comment box and needs to be validated.

[rofr]

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server?view=sql-server-ver15
ChangeData Capture (CDC) and Change Tracking could be useful as well but that would lock into SQL Server. The approach above could be used with any RDBMS