CQRS

CQRS stands for Command and Query Responsibility Segregation, a pattern that separates read and update operations for a data store. Implementing CQRS in your application can maximize its performance, scalability, and security. When the number of reads on the database is higher than the writes which is the case for most of the applications we can separate the read and write databases.

In CQRS we use commands to update data and queries to read data.

• Commands should be task-based, rather than data-centric. ("Book hotel room", not "set ReservationStatus to Reserved")
• Commands may be placed on a queue for asynchronous processing, rather than being processed synchronously.
• Queries never modify the database. A query returns a DTO that does not encapsulate any domain knowledge.

For greater isolation, you can physically separate the read data from the write data. In that case, the read data can use its own data schema that is optimised for queries. For example, it can store a materialised view of the data, in order to avoid complex joins or mappings. It might even use a different type of data store. For example, the write database might be relational, while the read database is a document database.

SQL Server Always On

By using this feature, You need to make a primary and secondary replica (could be multiple secondary replicas), Once the Always On feature is configured, the Second replicas are automatically updated based on Primary replica updates. This also provides HADR (High Availability Disaster Recovery) feature, if the primary replica goes down, the secondary replica will be active and play the primary replica role.

An HA availability group is a group of databases that fail over together. A read-scale availability group is a group of databases that are copied to other instances of SQL Server for read-only workload.

Event Sourcing

Some implementations of CQRS use the Event Sourcing Pattern. With this pattern, the application state is stored as a sequence of events. Each event represents a set of changes to the data. The current state is constructed by replaying the events. In a CQRS context, one benefit of Event Sourcing is that the same events can be used to notify other components — in particular, to notify the read model. The read model uses the events to create a snapshot of the current state, which is more efficient for queries. However, Event Sourcing adds complexity to the design.

Using the stream of events as the write store avoids update conflicts and maximises performance and scalability. The read model is typically a highly denormalised view. These views are tailored to the interfaces and display requirements of the application. Because the event store is the official source of information, it is possible to delete the materialised views and replay all past events to create a new representation of the current state when the system evolves or when the read model must change. The materialised views are in fact a durable read-only cache of the data.

A materialised view is a database or view that contains the results of a query. For example, it may be a local copy of data located remotely, or maybe a subset of the rows and/or columns of a table or join result or maybe a summary using an aggregate function. This is a form of caching the results of a query. Materialised views that store data based on remote tables are also known as snapshots.

As with any system where the write and read stores are separate, systems based on this pattern are eventually consistent. There will be some delay between the event being generated and the data store is updated.

Events should be pure commands without any response other than control status like successful, failed, etc.