Implementing Trading Rules on SQL Server

When a customer places an order, our trading platform evaluates numerous rules before it is sent to the exchange for execution. The rules could be related to the type of order being placed, instrument being traded, the customer’s margin, or even some attributes about the customer. The data for these rules are fast changing, and need to reflect on the trading systems as soon as they happen. These rules should also be evaluated at low latency, high availability and scale.

In this post we discuss the implementation details of one such rule, where we track the customer’s authorisations to sell shares from his DMAT account, also known as eDIS. This is a security feature, where any stocks sold by the customer should be authorised, at the depository, in our case the CDSL. Through a screen that looks something like the one below, customers enter their secret TPIN and OTP, and provide authorisation to debit shares from their account. If a sell transaction is successful, we use this authorisation to debit the shares, and pay the same to the exchange. We allow shares in holdings to be sold only after we have received the confirmation for this authorisation¹

This rule will be evaluated at thousands of TPS, as customers are selling the shares from their holdings. We implemented this rule on a Golang based microservice. The customer’s authorisation is tracked on an In-Memory OLTP Database in SQL server, with AlwaysOn for High Availability (HA). The microservice itself is a simple low latency application, and hence will not be covered much in this post. We will be covering the database features used for building these trading rules. We will start with why we chose the database, followed by the latency aspects of the database and finally it’s high availability features.

Choice of database

In order to evaluate the trading rules, we needed a database that supports the following primary requirements -

1. InMemory data access for low latency — sub millisecond.
2. Durability to handle server restarts
3. Replication for high availability — should be able to recover in under 90 seconds (99.9% availability).
4. Geo Replication, since we also need Disaster Recovery.

and the following secondary requirements

1. A way to perform bulk updates through Apache spark and other big data tools.
2. Easy way to manage secondary (non oltp) queries — secondary indexes.

We did consider Redis for the same. While Redis does support the primary features, it has limited support for bulk data update of its complex structures like hashes (requiring pipelining but has limited interface support on spark-redis), and lacks structures like secondary indexes (or ACID compliant forms of the same).

InMemory OLTP in SQL server supports all the primary requirements, along with the secondary requirements, in the form of -

1. An SQL interface for bulk updates, which can be used in spark-jdbc and
2. Immediately consistent secondary indexes

So we chose SQL server to implement these rules. We will now cover the InMemory database of SQL Server which gives us the speed and durability, followed by the HA features.

InMemory OLTP

SQL server’s InMemory OLTP, was introduced in SQL Server 2014, under the code name Hekaton. It is an InMemory database built using Latch/Lock free algorithms, thus supporting a high degree of concurrency when updating the data. While traditional disk based tables write both the index and the data to disk, In-Memory OLTP writes only the transaction log durably to disk, thus saving a lot on the I/O. The logs are periodically merged (as a combination of Data and Delta files) to remove older updates, thus keeping the overall space requirement minimal.

The right index — Hash or Range

To achieve the low latency responses expected from a trading system, it is not enough that the data reside in memory, it needs to be accessed fast through the right indexes too. InMemory OLTP provides two forms of indexes — Hash and Range.

Hash Index — Creating this index internally creates a hash table, which enables data lookup in near constant time for sql queries with only equality in the filter. So queries of the form “select * from table where col1 = XXX and col2 = YYY” are very efficient when implemented over a Hash Index (of col1 and col2). Hash Indexes also have a smaller memory footprint than range indexes. We prefer to use HashIndex for all our mission critical queries.

SQL server does not support dynamic resizing of Hash Indexes. The size of the hash table (bucket size) needs to be provided at the time of index creation (or modified through a DDL query). It is usually recommended to have the bucket size twice the length of the table, rounded upwards to the nearest power of 2. A “bigger“ bucket size, hurts less than “too small” a bucket size. We can also use the stats around the chain length from sys.dm_db_xtp_hash_index_stats to decide on the right size for the hash table.

Range Index — This is implemented in SQL server through a modified form of B-Tree called the Bw-Tree, which is latch/lock free. This index is more efficient for accessing ranges of data, usually for maintenance queries like “delete from table where authorization_date <= 2022–10–31”. The range index is called non clustered index in Microsoft documentations.

Overall, simple data accesses through either of these indexes should result in sub millisecond latencies, similar to other in-memory databases, though Hash Indexes have a slight advantage over range indexes.

Natively Compiled Stored Procedures

SQL server has a feature to natively compile the stored procedures (SP) into dlls. This reduces the parse and planning time for the queries, and is recommended for frequently used SPs. It does come with a few limitations like needing to recompile the SPs when the table schema changes. In comparison with the overall latency of the microservice (due to network lag, computations within the service etc.), the benefit from this feature was insignificant, and we do not use this extensively.

We will now discuss how we improved the availability of the SQL server.

High Availability and Disaster Recovery

In addition to being low latency and scalable, the trading system needs to be available throughout the trading hours. This is achieved by setting up redundant SQL servers, on separate server hardware. Any server failures, should result in an automatic failover to a slave instance, which then starts serving the customers. Sometimes, these servers need to be available even in the face of datacenter failures (disaster), requiring the data to be geo replicated to a failover datacenter.

High Availability AlwaysOn Failover Cluster

Windows server clusters provide a technology called Windows Server Failover Clustering (WSFC), to support HA failover. It uses a quorum based algorithm, to decide on one of the SQL servers in the cluster as the master node. When there is a failure and the master becomes unreachable, the remaining servers decide on the new master through a quorum based vote. The newly decided master will start serving the client requests. Since any of the slave nodes could handle the requests on master node’s failure, such a cluster should be setup for synchronous replication, to ensure all slave nodes are up to date.

There are primarily two ways to achieve a failover with minimal downtime

1. Moving a master ip around within a subnet, to the active master (simple to implement)
2. Updating the DNS CNAME with the ip for the active master (provides subnet redundancy also).

On successful failover, client is expected to reestablish connection with the new server. Since our service is built on Golang, it was taken care of underneath the covers, with minimal intervention. For other clients, the details of the individual client libraries being used, need to be reviewed, to see if it works as expected (e.g. jdbc). It also helps to simulate failures under stress traffic to see how the client behaves, and tune the settings as needed.

High Availability Single Node Failure

Disaster Recovery through Distributed Availability Group

In order to support datacenter resiliency, we keep an entire datacenter on standby. The microservice is deployed on both datacenters and SQL servers are deployed on the standby datacenter through a Distributed Availability Group. The data is replicated to the standby datacenter asynchronously (synchronous can be implemented but costs a lot of latency). On failure in the primary datacenter, traffic (including customer traffic), is redirected to the standby datacenter. Since the data is replicated in an asynchronous fashion, there is a possibility of data loss, and the overall failover is a manual process. We will cover data recovery, and process automation for the same in a future post.

High Availability Data Center Failure

Conclusion

The microservice helps our customers place orders safely, at a very low latency and high availability. It would not have been possible without the support from our SRE team, who helped us with the infrastructure, databases, and performance tests, and the development team for EDIS. We would want to take this opportunity to thank them.

¹Customers who have provided Power of Attorney (POA) or Demat Debit and Pledge Instruction (DDPI), will not be asked to for a separate authorisation.