Data store architecture approach

Azure SQL Database and Azure Storage

Distance teaching and Mobile Learning is uses Azure SQL Database and Azure Blob storage as our persisted storage solution. To be highly available and distributed system we employ specific principles and approaches.

De-normalization

In order to optimize for read performance versus write performance we tend to denormalize all data and use multiple writes. It is OK for write to be slower as long as we optimize for read performance.

Object Serialization

SQL Server and Azure SQL Database have native JSON functions that enable to parse JSON documents using standard SQL language. This is equivalent to the collections that you can find in classic document databases. Read more here.

We try to store serialized objects alongside with query-able keys:

create table DTML.Users (
    ID bigint primary key identity,
    SchoolID bigint,
    UserData nvarchar(max),

    userName AS JSON_VALUE(UserData , '$.userName'),
    index ix_userName  (userName),
    index ix_userName  (SchoolID)
);

Minimizing use of SQL Stored procedures

Repositories layer in DTML is written in C# and can be scaled horizontal. It can be unit tested and verified locally without need for integration tests. We hold all business logic in that layer and avoid using stored procedures except for the rare case needed for performance optimizations. However, no store procedure (even if used) should hold business logic.

Performance considerations

Backed storage is historical bottleneck for high performance systems. To avoid common pitfalls:

1. Never call SQL commands in the loop

2. User Immutable List to avoid real-time conversions

3. Ensure all queries are executing against indexed columns

Selectively use DB Transactions

As we follow our principle to optimize for read operations, the writes might require multiple SQL calls to prepare and store data in different tables. To ensure data consistency consider using SQL Transactions. Be thoughtful when to use them. See Combining several operations into one transaction within the same context section.