Table of Contents
1. Summary
Day in and day out, organizations run their businesses with transactional applications and databases. Given the potential for transactions at all hours and from a variety of devices, transaction rates and volumes have soared. A key to any transactional application—whether it is processing bank transactions, keeping a gaming platform in sync, or monitoring health conditions—is processing the high volumes of data at high speed. The time it takes to access or write data creates latency and delays action. Latency is particularly relevant to read- and write-intensive applications in the cloud, where database latency, plus latency created by network throughput, API/ microservices calls, and other processes compound.
There are a variety of databases available to the transactional application. Ideally, any database would have the required capabilities; however, depending on the scale of the application, and the chosen cloud, some database solutions can be prone to delays. Recent trends in information management see organizations shifting their focus to cloud-based solutions. In the past, the only clear choice for most organizations has been on-premises data—often using an appliance-based platform. However, the costs of scale are chipping away at the notion that this remains the best approach for all, or some, of a company’s transactional needs. The factors driving data projects to the cloud are many, but the advantages, like data protection, high availability, and scale, are realized with a fully-managed cloud deployment. In many cases, a hybrid approach serves as an interim step for organizations migrating to a larger cloud architecture.
This report outlines the results from a GigaOM Transactional Field Test derived from the industry-standard TPC Benchmark™ E (TPC-E) to compare two fully-managed cloud SQL Server offerings: Amazon Web Services Relational Database Service (AWS RDS) and Microsoft Azure SQL Database. Both are based on Microsoft SQL Server, however, there are some distinct differences in the two cloud offerings other than performance.
The results of the GigaOM Transactional Field Test are valuable to all operational functions of an organization such as human resource management, production planning, material management, financial supply chain management, sales and distribution, financial accounting and controlling, plant maintenance, and quality management. The underlying data for many of these today are in SQL Server, which is also frequently the source for operational interactive business intelligence (BI).
The parameters to replicate this test are provided. You are encouraged to compile your own representative queries, data sets, and data sizes, and test compatible configurations applicable to your requirements.
2. Fully-Managed Cloud SQL Server Offerings
Relational databases are a cornerstone of future data environments. While alternative SQL platforms are growing with the data deluge, and have their place, workload platforming decision-makers today highly consider and usually choose the relational database, and for good reason. Since 1989, Microsoft SQL Server has proliferated to near-ubiquity as the relational database of choice for the original database use case – On-Line Transactional Processing (OLTP) – and beyond. Now fully functional versions are offered as a service, taking complete advantage of the cloud. These managed cloud offerings provide predictable costs, cost savings, a fully managed infrastructure, fast response times, and strong non-functionals.
Microsoft Azure SQL Database
Azure SQL Database is a managed cloud solution delivering continuous updates, upgrades, and optional deployment across multiple availability zones with multiple replicas for high availability and disaster recovery by default. As a managed service, SQL Database frees you up from managing time-consuming database administration tasks including provisioning, backups, software patching, monitoring, and hardware scaling.
Each SQL database has an associated Microsoft server query compatibility level that allows intelligent query processing of the SQL database to be compatible with the specific version of SQL Server. Azure SQL Database can now run at compatibility level 150 using an evergreen code base. Compatibility level 150 has a number of performance improvements, including the batch mode for rowstore feature which improves the performance of CPU-bound queries without requiring column store indexes and also memory grant feedback for row mode execution, which automatically adds memory to queries that previously spilled to disk on subsequent executions1. Azure SQL Database also has additional features including built-in intelligence that optimizes performance and security, including:
- Data discovery and classification of sensitive data
- Vulnerability assessment providing insight into your security state
- Advanced threat protection that detects outlier and anomalous usage behavior
- Automatic tuning for indexes based on AI and machine learning
- Intelligent insight tools to monitor database performance and uncover the root-cause of any issues
Amazon Web Services Relational Database Service (AWS RDS)
Amazon RDS for SQL Server is also a managed service that allows users to set up, operate, and scale a SQL Server deployment in Amazon Web Services, while freeing users from daily database administration tasks.
Organizations can implement SQL Server with a “License Included” licensing model. You do not need separately purchased Microsoft SQL Server licenses. “License Included” pricing is inclusive of software, underlying hardware resources, and Amazon RDS management capabilities. With Amazon RDS, you can deploy SQL Server from 2012 up to 2017 including Express, Web, Standard, and Enterprise editions. Amazon RDS for SQL users are limited to SQL Server 2017 (v. 14.x) at this time. Amazon RDS for SQL is currently limited to Microsoft server query processing compatibility level 140.1
AWS RDS offers hourly pricing with no upfront fees or long-term commitments. In addition, you also have the option to purchase Reserved DB Instances under one or three-year reservation terms. Organizations may choose their own SQL Server license contract in reserved instance pricing, but give up some of the included AWS managed services.
With Amazon RDS for SQL Server, you can deploy a single replica using its multiple availability zone (Multi-AZ) feature. Also, with their other database options, like Aurora, you can have more than one.
1A full list of features and differences between Compatibility Levels 150 and the prior Level, 240, can be found at https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing
3. Field Test Setup
GigaOM Transactional Field Test
The GigaOM Transactional Field Test is a workload derived from the well-recognized industry-standard TPC Benchmark™ E (TPC-E). The workload was modified, i.e. transaction mix, from the standard TPC-E benchmark for ease of benchmarking and as such, the results generated are not comparable to official TPC Results. From tpc.org: “TPC Benchmark™ E (TPC-E) is an OLTP workload. It is a mixture of read-only and update intensive transactions that simulate the activities found in complex OLTP application environments. The database schema, data population, transactions, and implementation rules have been designed to be broadly representative of modern OLTP systems. The benchmark exercises a breadth of system components associated with such environments.” The TPC-E benchmark simulates the transactional workload of a brokerage firm with a central database that executes transactions related to the firm’s customer accounts. The data model consists of 33 tables, 27 of which have the 50 foreign key constraints. The results of TPC-E are valuable to all operational functions of an organization, many driven by SQL Server and frequently the source for operational interactive business intelligence (BI).
Field Test Data
The data sets used in the benchmark were generated based on the information provided in the TPC Benchmark™ E (TPC-E) specification.
For this testing, we used the database scaled for 80,000 customers. This scaling determined the initial data volume of the database. For example, a total of 80,000 customers is multiplied by 17,280 to determine the number of rows in the TRADE table: 1,382,400,000. All of the other tables were scaled according to the TPC-E specification and rules. On AWS RDS we allocated 1,024 GiB of storage—which was more than enough for this workload.
Besides the scale factor of 80,000 customers, the test offers a few other “knobs” we turned in order to determine the database engine’s maximum throughput capability. These included the following factors and the settings we used for both AWS and Azure platforms:
We completed five test runs that lasted a duration of two hours each for both platforms. The results are shared in the Field Test Results section.
Database Environments
Selecting and sizing compute and storage for comparison can be challenging —particularly across two different cloud vendors’ offerings. There are various offerings between AWS and Azure for mission-critical workloads. As you will see below, at the time of testing and publication, there was not an exact match across the offerings. There are no exact matches in processors or memory.
We considered the variety of offerings on AWS. For example, the M4 instance class is touted as a “balance of compute, memory, and network resources,” while R4 is the most similar but still different with a description of “optimized for memory-intensive and latency-sensitive database workloads, including data analytics, in-memory databases, and high-performance production workloads.” Thus, R4 seemed a suitable instance class to use for AWS RDS.
On the Azure side, we expect customers to gravitate towards SQL Database Business Critical (BC) offerings which are described as “offering balanced and scalable compute and storage options for data applications with fast IO and high availability requirements.” Thus, we decided on R4 for AWS RDS and BC for Azure SQL Database.
Our approach was to find the “nearest neighbor” best fit. The challenge was selecting a balance of both CPU and memory. R4.16xlarge on AWS has 64 vCPUs and 488 GiB memory. Azure offers a 64 core instance in BC_Gen5_64 that has 326 GB of memory, which is 33% less than the R4.16xlarge. Therefore, we chose the BC_Gen5_80 instance, which has more CPUs than R4.16xlarge, but less memory at 408 GB. This was our best, most diligent effort at selecting hardware compatibility for our testing. Results may vary across different configurations and again, you are encouraged to compile your own representative queries, data sets, data sizes, and test compatible configurations applicable to your requirements. All told, our testing included two different database environments. For more information on storage type and IOPs, please reference the footnote.
We typically prefer to test platforms with standard “out-of-the-box” configurations. However, there are a few differences in the SQL Server defaults provided with each cloud. For equivalency, we enabled several SQL server settings in RDS that are standard defaults in Azure. Azure SQL Database enables Transparent Data Encryption (TDE) by default. TDE encrypts at rest with real-time I/O encryption and decryption of data and log files. Also, Azure SQL Database uses read-committed snapshot isolation, which uses row versioning to provide statement-level read consistency. RDS defaults to only the read-committed SQL Server default. Finally, we enabled Query Store in RDS which is, again, enabled by default in Azure SQL Database.
4. Field Test Results
This section analyzes the transactions per second (tps) from the fastest of the five runs of the GigaOm Transactional Field Test (derived from the TPC-E) described above.
Azure SQL Database Transactions per Second (tps)
Using Azure SQL Database (RTM) – 12.0.2000.8 with Size XL on Instance Type BC_Gen5_80 with 3 SUTservers, 80,000 customers, 600 users, a 400,000 Pacing level, MaxDOP of 8. In 5 runs, the fastest run was 1022 tps.
AWS RDS SQL Server 2017 Transactions per Second (tps)
The most compatible version on AWS was using Microsoft SQL Server 2017 (RTM-CU13-OD) (KB4483666) – 14.0.3049.1 (X64) on AWS with Instance Type db.r4.16xlarge, and adding parameters multiAZ=on, transparent data encryption=on, and read-committed snapshot isolation=on. There were 3 SUTservers, 80,000 customers, 600 users, a 400,000 Pacing level, and MaxDOP of 8. In 5 runs, the fastest run was 998 tps.
5. Price Per Performance
The price-performance metric is price/throughput (tps). This is defined as the cost of running each of the cloud platforms continuously for three years divided by transactions per second throughput uncovered in the previous section.
Pricing Details:
Azure: Single Database, Business Critical, Gen 5, 80 vCore, East US region, 1024 GB storage,732 hours/mo
AWS: db.r4.16xlarge, Enterprise Multi-AZ, US East (Ohio) region, 32,000 provisioned IOPs, 1024 GB storage, 732 hours/mo
AWS 3-yr reserved instance: total compute cost divided by 36 months + storage + IO
Note: Prices do not include support costs for either Azure or AWS.
Each platform has different pricing options. Buyers should evaluate all of their pricing choices, not just the ones presented in this paper.
With the license included, the monthly cost of Microsoft Azure comes to $40,043.71. The monthly cost for AWS comes to $65,239.43. With Azure Hybrid Benefit, the Azure monthly cost is $18,083.71. With a 3-year reserved instance, Azure is $30,156.22 per month for our workload and AWS is almost double at $58,575.94. Azure offers a 3-year reserved instance with Azure Hybrid Benefit at $8,256.22 per month, which is substantially the least expensive route.
Azure SQL Database Price/Performance
The Azure SQL Database price/performance, defined as the cost of running the fastest run 1022 tps continuously for three years divided by transactions per second throughput is $1,410.04.
AWS RDS SQL Server Price/Performance
The AWS RDS SQL Server price/performance, defined as the cost of running the fastest run 998 tps continuously for three years divided by transactions per second throughput is $2,352.85.
Azure SQL Hybrid Benefit Price/Performance
The Azure SQL Database price/performance, defined as the cost of running the fastest run 1022 tps continuously for three years divided by transactions per second throughput is $636.78.
AWS RDS SQL Server 2017 Reserved Instance Price/Performance
The AWS RDS SQL Server price/performance, defined as the cost of running the fastest run 998 tps continuously for three years divided by transactions per second throughput is $2,352.85.
Azure SQL 3-year Reserved Instance Price/Performance
The Azure SQL Database price/performance, defined as the cost of running the fastest run 1022 tps continuously for three years divided by transactions per second throughput is $1,061.88.
AWS RDS SQL Server 2017 3-year Reserved Instance Price/Performance
The AWS RDS SQL Server price/performance, defined as the cost of running the fastest run 998 tps continuously for three years divided by transactions per second throughput is $2,119.10.
Azure SQL Hybrid Benefit with 3-year Reserved Instance Price/Performance
The Azure SQL Database price/performance, defined as the cost of running the fastest run 1022 tps continuously for three years divided by transactions per second throughput is $290.72.
AWS RDS SQL Server 2017 3-year Reserved Instance Price/Performance
The AWS RDS SQL Server price/performance, defined as the cost of running the fastest run 998 tps continuously for three years divided by transactions per second throughput is $2,119.10.
6. Conclusion
This report outlines the results from a GigaOM Transactional Field Test derived from the industry-standard TPC Benchmark™ E (TPC-E) to compare the same fully-managed SQL Server offering or two cloud vendors: Amazon Web Services Relational Database Service (AWS RDS) and Azure SQL Database. Both are based on SQL Server.
Using Azure SQL Database (RTM) – 12.0.2000.8 with Size XL on Instance Type BCGen5_80 with 3 SUTservers, 80,000 customers, 600 users, 400,000 Pacing level, MaxDOP of 8, in 5 runs the fastest run was 1022 tps. The price/performance, defined as the cost of running each of the cloud platforms continuously for three years divided by transactions per second throughput is $1,410.04.
The most compatible version on AWS was using Microsoft SQL Server 2017 (RTM-CU13-OD) (KB4483666) – 14.0.3049.1 (X64) on AWS with Instance Type db.r4.16xlarge, adding parameters multiAZ=on, transparent data encryption=on and read-committed snapshot isolation=on. In 5 runs the fastest run was 998 tps at a price/performance of $2,352.85.
We have learned that the database, along with the cloud, matters to latency which is the killer for important transactional applications. Microsoft SQL Azure presents a compelling proposition for the modern transactional workload.
7. Disclaimer
Performance is important but it is only one criterion for a business-critical database platform selection. This test is a point-in-time check into specific performance. There are numerous other factors to consider in selection across factors of Administration, Integration, Workload Management, User Interface, Scalability, Vendor, Reliability, and numerous other criteria. It is also our experience that performance changes over time and is competitively different for different workloads. Also, a performance leader can hit up against the point of diminishing returns and viable contenders can quickly close the gap.
The benchmark setup was informed by the TPC Benchmark™ E (TPC-E) specification. The workload was derived from TPC-E and is not an official TPC benchmark nor may the results be compared to official TPC-E publications. The workload executed using the following setup, environment, standards, and configurations.
GigaOM runs all of its performance tests to strict ethical standards. The results of the report are the objective results of the application of queries to the simulations described in the report. The report clearly defines the selected criteria and process used to establish the field test. The report also clearly states the data set sizes, the platforms, the queries, etc. used. The reader is left to determine for themselves how to qualify the information for their individual needs. The report does not make any claim regarding the third-party certification and presents the objective results received from the application of the process to the criteria as described in the report. The report strictly measures performance and does not purport to evaluate other factors that potential customers may find relevant when making a purchase decision.
This is a sponsored report. Microsoft chose the competitors, the test, and the Microsoft configuration. GigaOM chose the most compatible configurations for the other tested platform and ran the testing workloads. Choosing compatible configurations is subject to judgment. We have attempted to describe our decisions in this paper.
8. Appendix
9. About Microsoft
Microsoft (Nasdaq “MSFT” @microsoft) enables digital transformation for the era of an intelligent cloud and an intelligent edge. Its mission is to empower every person and every organization on the planet to achieve more.
Microsoft offers Azure SQL Database. To learn more about Azure SQL Database visit https://azure.microsoft.com/services/sql-database/.
10. About William McKnight
William McKnight is a former Fortune 50 technology executive and database engineer. An Ernst & Young Entrepreneur of the Year finalist and frequent best practices judge, he helps enterprise clients with action plans, architectures, strategies, and technology tools to manage information.
Currently, William is an analyst for GigaOm Research who takes corporate information and turns it into a bottom-line-enhancing asset. He has worked with Dong Energy, France Telecom, Pfizer, Samba Bank, ScotiaBank, Teva Pharmaceuticals, and Verizon, among many others. William focuses on delivering business value and solving business problems utilizing proven approaches in information management.
11. About Jake Dolezal
Jake Dolezal is a contributing analyst at GigaOm. He has two decades of experience in the information management field, with expertise in analytics, data warehousing, master data management, data governance, business intelligence, statistics, data modeling and integration, and visualization. Jake has solved technical problems across a broad range of industries, including healthcare, education, government, manufacturing, engineering, hospitality, and restaurants. He has a doctorate in information management from Syracuse University.
12. About GigaOm
GigaOm provides technical, operational, and business advice for IT’s strategic digital enterprise and business initiatives. Enterprise business leaders, CIOs, and technology organizations partner with GigaOm for practical, actionable, strategic, and visionary advice for modernizing and transforming their business. GigaOm’s advice empowers enterprises to successfully compete in an increasingly complicated business atmosphere that requires a solid understanding of constantly changing customer demands.
GigaOm works directly with enterprises both inside and outside of the IT organization to apply proven research and methodologies designed to avoid pitfalls and roadblocks while balancing risk and innovation. Research methodologies include but are not limited to adoption and benchmarking surveys, use cases, interviews, ROI/TCO, market landscapes, strategic trends, and technical benchmarks. Our analysts possess 20+ years of experience advising a spectrum of clients from early adopters to mainstream enterprises.
GigaOm’s perspective is that of the unbiased enterprise practitioner. Through this perspective, GigaOm connects with engaged and loyal subscribers on a deep and meaningful level.
13. Copyright
© Knowingly, Inc. 2019 "SQL Transactional Processing Price-Performance Testing" is a trademark of Knowingly, Inc. For permission to reproduce this report, please contact sales@gigaom.com.