Azure SQL Database Q&A with SQL Community

Over the last couple of months since I presented and attended a few Azure SQL Database presentations, I have noticed that there are recurring questions. I think some of these are fantastic questions and worth repeating here. Please feel free to ask your questions here by leaving a comment at the end of this post.

Q & A

Q: How do I choose which Service Tier and Performance Level? DTU number seems to be too arbitrary.

A: Use Database max size to start with to pick the appropriate Service Tier; then simulate / execute the typical workload and adjust as necessary which can be done at any time. [Demo on how to change Service Tier / Performance level, which is also documented as “SQL Database options and performance: Understand what’s available in each service tier”]

Assuming your current database size is 500GB, you’ll need to go with Premium as illustrated below. The next thing is to choose which performance level, i.e. P1, P2, etc. The process of provisioning Azure SQL DB is not too different from provisioning a server for SQL Server. You’ll start with a rough estimate and you adjust. The great thing about Azure SQL DB is that you can change to a different Pricing Tier (service / performance level) at any time you wish to.

Q: Is there a development environment for testing out / development?

A: No. But we offer free 30 day trial.

Q: How different is it from normal SQL Server?

A: It’s almost parity and most new features are rolled out on SQL Database first which helps users to learn/explore about the features earlier. Check out Azure SQL Database Transact SQL Differences and Azure SQL Database General Limitations and Guidelines for more information. Some of the new features, e.g. Query Store, which are introduced in SQL 2016 (try the CTP version), are available on Azure SQL DB already. In fact, development of new features is tested and released to Azure SQL Database first before the features reach on-premise.  So, Azure SQL DB can be used as a learning platform without having the hassle of spinning up your own SQL Server instance.

Q: We found problems with Azure SQL DB availability last month. It led us to spend hours on figuring out if it was us or the service was really down. We eventually found out that the service was down. It takes us ages before we get to talk to someone about the incident; compared to AWS where the issues can be resolved in minutes.

[Other people from the audience (SolidQ)] I never experienced any issue with SQL DB. May be it’s luck?

A:  We have dedicated teams to mitigate issues as quickly as possible especially related to availability. We are also working on how to surface this information better. There is also Check Health tool (see below) in the Settings blade under Support & Troubleshoot of the SQL Database.

General Azure SQL Database availability status is here: https://azure.microsoft.com/en-us/status/

Q: What if I reach the limit of DTU or the limit of Database max size?

A: You will receive error messages when database max size is reached. When DTU limit is hit, latency will increase, incoming requests might be rejected and you will eventually receive error messages associated to these. To mitigate: you can setup alerts to avoid reaching this limit. [Demo on setting up alert is shown which is also documented here]

Also, you can expand or upgrade to the next service tier to be able to upgrade your database size / DTU limit easily via the portal too (or via T-SQL ALTER DATABASE .. MODIFY, REST API with ServiceLevelObjectiveID, Powershell).

Q: My company (SolidQ) runs a number of events per year and we use Azure SQL DB to store the data. We tend to upgrade the service tier / performance level during the events and scale it back down post events.

A: Yes, this is pretty normal and you can do scale up/down as many times as you want to reduce the bill.  In fact, if you have many databases that have similar patterns of scaling up and down on regular basis, it may be a good idea to use elastic pool.

Q: When we change the performance level up and down, is the pricing for the service tier rate at hourly granularity? i.e. if I’m using S0 and changing it to Basic, do I get charged S0 for the whole day?

A: It is prorated hourly as quoted below (extracted from SQL Database FAQ)

How does the usage of SQL Database show up on my bill?

SQL Database bills on a predictable hourly rate based on both the service tier + performance level for single databases or eDTUs per elastic database pool. Actual usage is computed and pro-rated hourly, so your bill might show fractions of an hour. For example, if a database exists for 12 hours in a month, your bill will show usage of 0.5 days. Additionally, service tiers + performance level and eDTUs per pool are broken out in the bill to make it easier to see the number of database days you used for each in a single month.

Q: What if the collective databases in the (physical) server outgrows the server capacity?

A: Azure has a smart “load-balancing” that we can balance the capacity for databases to the other physical servers well.

Q: I want to estimate what my current workload on SQL Server looks like if migrated to Azure SQL Database?

A: You can check out DTU Calculator. Please note that it will consider your physical server’s utilization as well as the database workload. DTU calculator will most likely NOT yield good estimation on multi-purpose servers (e.g. your laptop). DTU calculator provides a few different options of service tier and explains how each of them can serve the CPU, IOPs and Data activities based on the perf counter files you upload.

Q: I am using Extended Events to capture deprecated usage on my SQL Server instance. How do I get the details of where the deprecated features were called from? 

A: You can add a “sql_text” action which can display where they were called from. [See example here; Below is an example of Watch Live Data output of an event session that captures sql_text of deprecated feature]

Q: I can’t use the UI for Extended Events (XEvents) for Azure SQL DB in SSMS. Why?

A: Please use the latest version of SQL Server 2016 SSMS preview. This should allow you to manage XEvents using the UI from SSMS.

Q: For some reason, when joining event_session_address from sys.dm_xe_database_sessions and sys.dm_xe_databae_session_targets and placing a WHERE clause, there is no row returned. Why?

A: There is a currently bug in the Public Preview version that the team is currently working on fixing. Please use CAST to BINARY(8) as a workaround for now. See below as an example.

SELECT
     s.NAME,
     CAST(t.target_data AS XML).value('(EventFileTarget/File/@name)[1]', 'VARCHAR(MAX)') fileName
FROM sys.dm_xe_database_sessions        s
     INNER JOIN sys.dm_xe_database_session_targets t
     ON CAST(s.address AS BINARY(8)) = CAST(t.event_session_address AS BINARY(8))
WHERE
     t.target_name = 'event_file'

Q: Is there SQL Agent Job?

A: Unfortunately, there is not. However, if you are using Elastic Pool, there is Elastic Jobs that you can run against the databases within the pool. If you have your own local SSIS and SQL Server Agent in your local environment, you can potentially do something clever to workaround this existing limitation. (I’ll write a blog post on this soon).

Q: Are all the nifty features such as Index Advisor, Query Store and Query Performance Insights available for all Service Tiers / Performance Levels?

A: Absolutely. There are more that I didn’t get a chance to talk about today, which are Threat Detection and Transparent Data Encryption.

Q: Does Azure SQL Database support Windows Login?

A: Azure SQL DB supports SQL Login. However, there is an on-going work on being able to link your enterprise’s Active Directory to Azure SQL DB via Azure Active Directory service. The feature is currently in preview.

Wrap Up

Azure SQL Database is a platform as a service offering very similar features to SQL Server. I have seen major improvement on portal, features and availability in the last 2 years. If you look at the updates from the Azure team, these are the three areas that the team is focusing on these days.

Further Reading

• Azure SQL Database Monitoring and Performance Tuning
• Using Extended Events (XEvents) on Azure SQL Database