Banking on speed and performance: Why Banqsoft is upgrading to SQL Server 2022

Banqsoft’s proprietary software solutions cover a broad range of Fintech use cases, including car loans, leasing services, hire purchase, payment protection, consumer loans, and credit and debit cards, debt collection, ledger and invoicing services.

Nearly all Banqsoft products are built on the same IT platform, using an open, flexible architecture that increases efficiency and makes it easy for customers to add new products or expand market reach.

To support the always-on nature of digital finance, Banqsoft relies on Microsoft SQL Server as its data platform. Currently, the company uses SQL Server 2017 and 2019, Azure SQL Database, and Azure Storage, which gives it a flexible hybrid environment that meets data location requirements. “We use SQL Server for everything, from onboarding our customers to bank transactions to source recovery,” said Magnus Zetterberg, Technical Fellow at Banqsoft. 

Recently, Banqsoft launched a test of SQL Server 2022 as part of the Early Adoption Program. “Performance and concurrence are crucial to us, and we’re interested in all the new bells and whistles that support that,” explained Tomasz Kulczynski, Database Administrator at Banqsoft. “The fact that we can implement new features without changing code makes SQL Server 2022 the logical choice.” 

Supercharging query processing with memory grant feedback

With an eye to boosting system performance, Banqsoft tested several Intelligent Query Processing features and enhancements new in SQL Server 2022, starting with memory grant feedback. Memory grant feedback looks at query executions and determines if the query is using more or less memory than it was granted. As a query is executed multiple times, the system learns the amount of memory typically used and adjusts the grant based on previous executions. In SQL Server 2022, query plan feedback is persisted on disk, meaning feedback is retained across cache evictions and server restarts. Also, the algorithm now looks at more of the query history before advising, a boon for parameter sensitive plans and others with widely vacillating memory needs.

“Our database tables are large and require a lot of memory,” said Zetterberg.

“Our crucial processes are so fast with SQL Server 2022,” added Kulczynski. For example, the new Parameter Sensitive Plan optimization feature eliminates parameter sniffing issues and unlocks the ability to cache two or more plans for a query at the same time. Each plan is optimal for a subset of the parameter space. By choosing the optimal plan for each specific query, SQL Server 2022 ramps up performance without code changes.

Using contained availability groups for privacy and business continuity

Another new feature Banqsoft has tested is contained availability groups. This feature makes it possible to put all databases, user logins, permissions, and scheduled jobs related to different business functions or applications inside one availability group environment. By keeping databases separate, contained availability groups support Banqsoft’s privacy policies. They also eliminate the possibility of missing logins or missing jobs during a failover.

“With contained availability groups, we can create a login or a job once and not think about it again,” Kulczynski said.

Query Store is now on 24/7 for secondary replica workloads

Banqsoft was excited that Query Store, which helps users track performance history and understand how query plan choices impact performance, is turned on by default in SQL Server 2022. Banqsoft can also benefit from Query Store for Always On availability group secondary replicas. This feature enables the same Query Store functionality on secondary replica workloads that is available for primary replicas. When Query Store for secondary replicas is enabled, replicas send the query execution information normally stored in Query Store back to the primary replica. The primary replica then persists the data to disk in its own Query Store. 

“We direct a lot of our workload to secondary instances,” explained Kulczynski. “This feature will help us understand where resources are being consumed so we can improve the code. That should help things run more smoothly.”

Adding automation and blockchain to the roadmap

As part of the Early Adoption Program, the Banqsoft team has explored several other SQL Server 2022 features, including Azure Synapse Link for SQL, which automates data extraction from source operational systems. No custom extract, transform, and load (ETL) processes are required. “We really like what Synapse Link offers,” said Zetterberg. “It’s on our roadmap for sure.” Azure Synapse Link for SQL is part of the Microsoft Intelligent Data Platform, which fully integrates databases, analytics, and governance.

The team has also evaluated Ledger for SQL Server, which uses blockchain to create a tamper-evident record of all changes to the database. Also under consideration is multi-write replication, which automates last-writer wins rule for replication conflict resolution.

Taking advantage of Azure services

Banqsoft also sees great potential in other Azure services, such as Azure Active Directory authentication, which can be connected to SQL Server 2022. “We have partners we’d like to integrate with our system. It would be great to use active directory authentication to easily onboard them,” Zetterberg said. 

In addition, Banqsoft was an early adopter of Azure Stack HCI. The company runs HCI clusters in its data center for sandbox test scenarios and simulations. “With HCI, we see all the benefits of running in Azure, including cutting costs in our data center,” noted Zetterberg.

Delivering data as fast as possible

Based on its experience with SQL Server 2022 so far, Banqsoft plans to start the upgrade process with its data centers in Switzerland followed by those in Scandinavia.