SQL Server vs Azure SQL

Recently, I helped a customer with a seasonal website that was having performance problems after moving it to Azure.  Last year the site was hosted on-premise and ran fine, but this year he moved the application to Azure and just “felt like” it was running slower.

I did a basic analysis of his Azure footprint.

Redis Cache, Azure SQL Server, 3 WebApps, 2 storage accounts.  The customer has ramped Azure SQL up to the P4 pricing tier to try and increase DB performance.

At first I considered the underlying storage account.  When running SQL Server on a Virtual Machine (Iaas) the type of storage account you choose can significantly affect performance!

Microsoft warns:

Standard Storage has varying latencies and bandwidth and is only recommended for dev/test workloads. Production workloads should use Premium Storage.

Looking at the customer’s storage accounts they were all standard!

But wait, the customer is using Azure SQL, NOT Sql on a VM.  So does the storage matter?  Not with Sql Azure…

3 tiers of service are available:

Service tier Target workloads
Basic Best suited for a small database, supporting typically one single active operation at a given time. Examples include databases used for development or testing, or small-scale infrequently used applications.
Standard The go-to option for most cloud applications, supporting multiple concurrent queries. Examples include workgroup or web applications.
Premium Designed for high transactional volume, supporting many concurrent users and requiring the highest level of business continuity capabilities. Examples are databases supporting mission critical applications.

Sql Azure measures performance in DTUs – Database Transaction Units

You can customize your instance depending on your desired performance level.

Basic service tier

Service tier Basic
Max DTUs 5
Max database size 2 GB
Max in-memory OLTP storage N/A
Max concurrent workers 30
Max concurrent logins 30
Max concurrent sessions 300

Standard service tier

Service tier S0 S1 S2 S3
Max DTUs 10 20 50 100
Max database Size 250 GB 250 GB 250 GB 250 GB
Max in-memory OLTP storage N/A N/A N/A N/A
Max concurrent workers 60 90 120 200
Max concurrent logins 60 90 120 200
Max concurrent sessions 600 900 1200 2400

Premium service tier

Service tier P1 P2 P4 P6 P11 P15
Max DTUs 125 250 500 1000 1750 4000
Max database size 500 GB 500 GB 500 GB 500 GB 1 TB 1 TB
Max in-memory OLTP storage 1 GB 2 GB 4 GB 8 GB 14 GB 32 GB
Max concurrent workers 200 400 800 1600 2400 6400
Max concurrent logins 200 400 800 1600 2400 6400
Max concurrent sessions 30000 30000 30000 30000 30000 30000

DTUs?

OK, so how many DTU’s do I need?  How is this measured?  Luckily Microsoft provides a DTU Calculator

This tool can help you analyze the workload of your Sql server over a 1 hour period to gather performance metrics.

You can feed this data into the DTU Calculator.  After clicking the calculate button, you’ll see several charts which provide an analysis of your database resource consumption. The charts depict the percentage of time (based on your measurements) that your database’s resource consumption fits within the limits of each Service Tier and Performance Level.  You can review CPU, Iops, and Log individually as well as collectively to better understand which metrics affect the performance of your database.

Azure also provides Sql Db Elastic pools.  SQL DB elastic pools provide a simple cost effective solution to manage the performance goals for multiple databases that have widely varying and unpredictable usage patterns.

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

In a nutsell, Azure SQL scalability levels are off the charts!  You can Scale SQL server from 1 small instance with 5 DTUs at $5 / month to P15 with 4000 DTUs at $16,000 / month.

Based on this customers usage patterns – 40-50 users and 100 more on the webapp.  This SQL server was way over scaled at P4!  But why was it running so poorly?

Luckily Azure SQL also provides some utilities to help look into your Azure Sql database performance

perfoptions

Selecting your Azure Sql database in the Azure Portal , find the Support & Troubleshooting section.  The performance overview and recommendations can help you.  In this customer’s case:

dbreccomend

The system was recommending I add indexes, I can even enable Automatic Tuning and have it create the indexes for me next time!

Indexes are critical to relational database server performance.  It wasn’t clear how the database got moved from on-premise last year to in Azure this year, but since they converted it to Sql Azure, they probably re-created the database schema and imported the data.  Sql Azure and Sql have different levels of flexibility. They must have forgot the indexes!

I recommended the customer apply the suggested indexes after hours to improve performance.  I also recommended he move back to an S2 or S3 instance size which should work fine for his workload once the database is tuned properly.

The next day the customer reported better performance.

Should you use Sql Azure or Sql Server?

When you create a database in Sql Azure, you have no control over database files and disk placement. The service imposes resource thresholds so that any bad behavior doesn’t upset the neighbors. With Azure SQL Database, you can move in quickly, but you can’t play music too loud or tear down any walls. If you need a low-maintenance environment with basic functionality, Azure SQL Database may be right for you.

SQL Server

SQL Server, on the other hand, is like owning your own home. It offers full functionality and the freedom to build your environment any way you like.  With that freedom comes responsibility, however. IT staff must administer all infrastructure, including the physical hardware, network, disk configuration, SQL Server configuration, backups, security and disaster recovery. If your organization requires a high degree of control and has the expertise to do it, traditional SQL Server may be your best choice.

SQL Server on Azure VM

Want to eliminate some of the hassle of upkeep and maintenance? SQL Server on an Azure VM offers a hybrid solution. The hardware and operating system provisioning and maintenance is done for you, but the SQL Server itself is fully under your control. Like owning a condo, you can paint the walls and renovate the kitchen, but never have to worry about mowing the lawn or fixing the roof. For those that want the best of both worlds, SQL Server on an Azure VM offers a viable solution.

The choice depends on what you need, when you need it, and how much you are willing to invest. Azure SQL Database, on-premise SQL Server, or SQL Server on Azure VM: one of them will work for you.

Leave a Reply

Your email address will not be published. Required fields are marked *