Home > Storage > PowerFlex > White Papers > Dell APEX Block Storage for Public Cloud: Microsoft SQL Server Multi-Subnet Failover Clustering Deployment > Linear scaling of SQL Server 2022 database instances across multi-AZs
For the performance optimization and to make optimal use of resources, three different SQL Server objects were created and each object was placed on a different node. Each SQL Server object has two SQL databases. A total of six databases running on three SQL Server cluster objects.
With this configuration enables to enforce the maximum stress on the resources including CPU, memory, and database volumes. To record the best possible results from the stress testing, see Instance compute configuration details.
Testing started with one database and one SQL Server object and with each iteration there was an addition in the following order as shown in Table 2.
AZ | AZ1 | AZ2 | AZ3 | |||
Node | SQL Server Node 1 | SQL Server Node 2 | SQL Server Node 3 | |||
SQL Server Instance | 1 | 2 | 3 | 4 | 5 | 6 |
Database | TPROC-C1 | TPROC-C2 | TPROC-C3 | TPROC-C4 | TPROC-C5 | TPROC-C6 |
1 | x | - | - | - | - | - |
2 | x | x | - | - | - | - |
4 | x | x | x | x | - | - |
6 | x | x | x | x | x | x |
This section focuses on scalability testing up to six databases across three SQL Server nodes in three AZs. Each SQL Server instance was populated with 1000 GB of data. HammerDB was deployed and configured on a separate EC2 load generation instance to simulate a real time client/server workload. For configuration detail of instances, see Test Configuration Details section.
Metrics | 1 x SQL Server 2022 Instance | 2 x SQL Server 2022 Instance | 4 x SQL Server 2022 Instance | 6 x SQL Server 2022 Instance |
Database read I/O requests | 64,000 | 121,900 | 219,700 | 356,100 |
Database file read latency (millisecond) | 0.6 | 0.6 | 0.7 | 0.9 |
The following figure shows that the TPROC-C workload database IOPS increase with the number of SQL server 2022 database instances, while the average database read response time remained under one millisecond. A total of 356,100 IOPS and an average database read latency of 0.9 milliseconds with the six databases in AWS using Dell APEX Block Storage for Public Cloud. The IOPS increased linearly with each additional database instance added into Dell APEX Block Storage for Public Cloud.