Azure SQL offers up a world of benefits that can be captured by consumers if implemented correctly.  It will not solve all your problems, but it can solve quite a few of them. When speaking to clients I often run into misconceptions as to what Azure SQL can really do. Let us look at a few of these to help eliminate any confusion.

You can scale easier and faster

Let us face it, I am old.  I have been around the block in the IT realm for many years.  I distinctly remember the days where scaling server hardware was a multi-month process that usually resulted in the fact that the resulting scaled hardware was already out of date by the time the process was finished.  With the introduction of cloud providers, the ability to scale vertically or horizontally can usually be accomplished within a few clicks of the mouse.  Often, once initiated, the scaling process is completed within minutes instead of months.  This is multiple orders of magnitude better than the method of having to procure hardware for such needs.

The added benefit of this scaling ability is that you can then scale down when needed to help save on costs.   Just like scaling up or out, this is accomplished with a few mouse clicks and a few minutes of your time.

It is not going to fix your performance issues

If you currently have performance issues with your existing infrastructure, Azure SQL is not going to necessarily solve your problem.  Yes, you can hide the issue with faster and better hardware, but really the issue is still going to exist, and you need to deal with it.  Furthermore, moving to Azure SQL could introduce additional issues if the underlying performance issue is not addressed before hand.   Make sure to look at your current workloads and address any performance issues you might find before migrating to the cloud.  Furthermore, ensure that you understand the available service tiers that are offered for the Azure SQL products.   By doing so, you’ll help guarantee that your workloads have enough compute resources to run as optimal as possible.

You still must have a DR plan

If you have ever seen me present on Azure SQL, I’m quite certain you’ve heard me mention that one of the biggest mistakes you can do when moving to any cloud provider is not having a DR plan in place.  There are a multitude of ways to ensure you have a proper disaster recovery strategy in place regardless of which Azure SQL product you are using.  Platform as a Service (Azure SQL Database or SQL Managed Instance) offers automatic database backups which solves one DR issue for you out of the gate.  PaaS also offers geo-replication and automatic failover groups for additional disaster recovery solutions which are easily implemented with a few clicks of the mouse.

When working with SQL Server on an Azure Virtual machine (which is Infrastructure as a Service), you can perform database backups through native SQL Server backups or tools like Azure Backup.

Keep in mind that high availability is baked into the Azure service at every turn.  However, high availability does not equal disaster recovery and even cloud providers such as Azure do incur outages that can affect your production workloads.  Make sure to implement a disaster recovery strategy and furthermore, practice it.

It could save you money

When implemented correctly, Azure SQL could indeed save you money in the long run. However, it all depends on what your workloads and data volume look like. For example, due to the ease of scalability Azure SQL offers (even when scaling virtual machines), secondary replicas of your data could be at a lower service tier to minimize costs.  In the event a failover needs to occur you could then scale the resource to a higher performing service tier to ensure workload compute requirements are met. Azure SQL Database offers a serverless tier that provides the ability for the database to be paused.  When the database pauses, you will not be charged for any compute consumption.  This is a great resource for unpredictable workloads.

Saving costs in any cloud provider implies knowing what options are available as well as continued evaluation of which options would best suit your needs.

It is just SQL

Azure SQL is not magical quite honestly.  It really is just the same SQL engine you are used to with on-premises deployments.  The real difference is how you engage with the product and sometimes that can be scary if you are not used to it.  As a self-proclaimed die-hard database administrator, it was daunting for me when I started to learn how Azure SQL would fit into modern day workloads and potential help save organizations money.  In the end, though, it’s the same product that many of us have been using for years.

Summary

In this blog post I’ve covered five things to know about Azure SQL.  It is a powerful product that can help transform your own data ecosystem into a more capable platform to serve your customers for years to come.  Cloud is definitely not a fad and is here to stay.  Make sure that you expand your horizons and look upward because that’s where the market is going.

If you aren’t looking at Azure SQL currently, what are you waiting for?  Just do it.

© 2020, John Morehouse. All rights reserved.

The post 5 Things You Should Know About Azure SQL first appeared on John Morehouse.

Sometime having the right command in place opens up new doors to test things, like a failover for example.  In this post we will take a look at a new ability that has recently surface within the Azure eco-system to help manage fail-overs.  Let’s jump to it.

High availability is a crucial component for data professionals, even when operating in a cloud environment such as Azure.  Thankfully, Microsoft Azure Platform as a Service (PaaS) is architected in a way that offers high availability for service right out of the gate.  This helps to ensure that your databases, such as Azure SQL Database and Azure SQL Managed Instances, are always available without having to lift a finger.  What even better is Microsoft now offers the ability to manually control a failover over for these resources which gives data professionals more granular control.

Previously, the service would manage this aspect and Microsoft would initiate the failover if needed.  But what if I wanted to test the failover to see how my applications would react?  Would a failover impact my end users?  There was not any way to test this even though the service offers a high level of availability. Thankfully that has changed and we can now control, to a degree, failovers for Azure SQL Platform as a Service resources, including Azure SQL Database, Elastic Pools, and SQL Managed Instances.

How can we manage a high availability failover in Azure SQL PaaS?

To facilitate the failovers, you must do this through some type of command line interface. This means either PowerShell, Azure CLI, or a REST API call.  There is currently not a way to manage this through the portal.  In the future we could possibly see such capability, but I do not know if or when that would come to fruition.  For the purposes of this post, we will look at PowerShell.

There are three powershell cmdlets that will failover Azure SQL resources.

Invoke-AzSQLDatabaseFailover

This cmdlet will failover an individual database.  If the database is involved within an elastic pool, the failover will not affect the entire pool and will only affect the database itself.  In testing, failing over a database involved with an elastic pool did not affect the databases membership in the pool.   Furthermore, if the database is within an Availability Zone, the database will be failed over to a secondary zone and all client connections will be redirected to the new primary zone.

It is also worth noting that there is a “-ReadableSecondary” switch that would instead a failover the readable secondary.  Since you could be using a readable secondary to off-load read workloads it would make sense to test how its failover would impact those workloads.

Invoke-AzSQLElasticPool

This cmdlet will failover an entire elastic pool which means all the databases within the pool will failover.  This cmdlet will be handy if you are utilizing elastic pools to help minimize Azure costs but still want to test a failover.

Invoke-AzuSQLInstanceFailover

Like it’s the two predecessors, this cmdlet will failover a SQL Managed Instance.  It also has a readable secondary switch that you can utilize to failover the readable secondary.

Are there any limitations?

With great power comes great responsibility and such is the case here.  Given the intrusive nature of the failover within the Azure eco-system, it stands to make sense that you can only failover the resources every so often.  Currently, at the time of this post, the documentation states you can only failover every 30 minutes.  However, during testing things, I got a different error message that states it’s every 15 minutes.

I have given feedback to Microsoft regarding this discrepancy and they were able to get it resolved and the documentation will be updated to reflect a 15 minute duration between failover events.

What else would this help fix?

Even with the highly durable infrastructure that Microsoft has built, there are occasions where hardware issues arise where the service might not failover.  While failing over to a DR solution (such as active geo-replication or automatic failover groups) would help to resolve it, if things are configured correctly that is more intrusive to the application.  By having the ability to failover, customers can now initiate a failover when hardware issues surface without having to implement their disaster recovery solutions.

Summary

Microsoft continues to enhance and improve the Azure SQL eco-system.  By having the ability to control and test failovers for Azure SQL resources just further provides a deeper level of control for data professionals.  If you are utilizing Microsoft Azure or even planning on moving Azure, I highly recommend you get familiar with how this feature works so that you can verify with certainty how your applications will handle a database high availability failover.

© 2020, John Morehouse. All rights reserved.

The post Azure SQL Offers Manual Failover for PaaS Resources first appeared on John Morehouse.

Sometime having the right command in place opens up new doors to test things, like a failover for example.  In this post we will take a look at a new ability that has recently surface within the Azure eco-system to help manage fail-overs.  Let’s jump to it.

High availability is a crucial component for data professionals, even when operating in a cloud environment such as Azure.  Thankfully, Microsoft Azure Platform as a Service (PaaS) is architected in a way that offers high availability for service right out of the gate.  This helps to ensure that your databases, such as Azure SQL Database and Azure SQL Managed Instances, are always available without having to lift a finger.  What even better is Microsoft now offers the ability to manually control a failover over for these resources which gives data professionals more granular control.

Previously, the service would manage this aspect and Microsoft would initiate the failover if needed.  But what if I wanted to test the failover to see how my applications would react?  Would a failover impact my end users?  There was not any way to test this even though the service offers a high level of availability. Thankfully that has changed and we can now control, to a degree, failovers for Azure SQL Platform as a Service resources, including Azure SQL Database, Elastic Pools, and SQL Managed Instances.

How can we manage a high availability failover in Azure SQL PaaS?

To facilitate the failovers, you must do this through some type of command line interface. This means either PowerShell, Azure CLI, or a REST API call.  There is currently not a way to manage this through the portal.  In the future we could possibly see such capability, but I do not know if or when that would come to fruition.  For the purposes of this post, we will look at PowerShell.

There are three powershell cmdlets that will failover Azure SQL resources.

Invoke-AzSQLDatabaseFailover

This cmdlet will failover an individual database.  If the database is involved within an elastic pool, the failover will not affect the entire pool and will only affect the database itself.  In testing, failing over a database involved with an elastic pool did not affect the databases membership in the pool.   Furthermore, if the database is within an Availability Zone, the database will be failed over to a secondary zone and all client connections will be redirected to the new primary zone.

It is also worth noting that there is a “-ReadableSecondary” switch that would instead a failover the readable secondary.  Since you could be using a readable secondary to off-load read workloads it would make sense to test how its failover would impact those workloads.

Invoke-AzSQLElasticPool

This cmdlet will failover an entire elastic pool which means all the databases within the pool will failover.  This cmdlet will be handy if you are utilizing elastic pools to help minimize Azure costs but still want to test a failover.

Invoke-AzuSQLInstanceFailover

Like it’s the two predecessors, this cmdlet will failover a SQL Managed Instance.  It also has a readable secondary switch that you can utilize to failover the readable secondary.

Are there any limitations?

With great power comes great responsibility and such is the case here.  Given the intrusive nature of the failover within the Azure eco-system, it stands to make sense that you can only failover the resources every so often.  Currently, at the time of this post, the documentation states you can only failover every 30 minutes.  However, during testing things, I got a different error message that states it’s every 15 minutes.

I have given feedback to Microsoft regarding this discrepancy and they were able to get it resolved and the documentation will be updated to reflect a 15 minute duration between failover events.

What else would this help fix?

Even with the highly durable infrastructure that Microsoft has built, there are occasions where hardware issues arise where the service might not failover.  While failing over to a DR solution (such as active geo-replication or automatic failover groups) would help to resolve it, if things are configured correctly that is more intrusive to the application.  By having the ability to failover, customers can now initiate a failover when hardware issues surface without having to implement their disaster recovery solutions.

Summary

Microsoft continues to enhance and improve the Azure SQL eco-system.  By having the ability to control and test failovers for Azure SQL resources just further provides a deeper level of control for data professionals.  If you are utilizing Microsoft Azure or even planning on moving Azure, I highly recommend you get familiar with how this feature works so that you can verify with certainty how your applications will handle a database high availability failover.

© 2020, John Morehouse. All rights reserved.

The post Azure SQL Offers Manual Failover for PaaS Resources first appeared on John Morehouse.

There are a number of knobs and switches that are available to database administrators that can be used to enable better performance.  There are three options in particular that this blog will be discussing, trace flag 4199, the database scoped configuration QUERY_OPIMIZER_HOTFIXES and the qeury hint ENABLE_QUERY_OPTIMIZER_HOTFIXES. Understanding how these options function will give you a hand up on ensuring the query optimizer is running as optimally as possible.

Let’s take a look at the three options.

Trace Flag 4199

In dealing with any trace flag, it is vital that you understand as to what the trace flag will enable or disable and how it pertains to your individual workloads.  Some trace flags are benign, such as TF3226 which removes successful backup messages from the log, others can be quite intrusive and cause havoc.   Make sure you review any documentation on the respective trace flag prior to implementation.

The official verbiage from Microsoft on trace flag 4199 is:

“Enables Query Optimizer (QO) fixes released in SQL Server Cumulative Updates and Service Packs.

QO changes that are made to previous releases of SQL Server are enabled by default under the latest database compatibility level in a given product release, without trace flag 4199 being enabled.” 

What this really implies is that if your database is at the most current compatibility level for your version of SQL server, then the query optimizer will utilize all of the QO hotfixes up to the release to market (RTM) version but nothing beyond the RTM version.

For example, if you are running SQL Server 2017 CU20 the database is set at the appropriate compatibility level (140), and TF4199 is disabled, the query optimizer will only utilize any hotfixes for it up to 2017 RTM.   Any query optimizer hotfix that was delivered in subsequent cumulative updates will not be utilized.

If you enable trace flag 4199, this instructs SQL Server to implement any query optimizer hotfix that was delivered after the RTM version.   Note that enabling this trace flag will be applied across all databases.  It’s an all or nothing trace flag as most trace flags are.

The table below (from Microsoft documentation explains the matrix of outcomes when dealing with trace flag enabled or disabled.

If you are running in Azure SQL Database, you don’t have the ability to enable this particular trace flag, which is where the database scoped configuration becomes useful. If you are running on Azure SQL Managed Instances, you can enable the trace flag just like you would if on-premises.

Enabling this trace flag would also require a restart of the SQL Server services if you want to persist it upon a restart. You can enable this trace flag by setting up a startup parameter in the SQL Server Configuration Manager.  You can check for the existence of trace flags by using DBCC TRACESTATUS().

QUERY_OPIMIZER_HOTFIXES

Enabling trace flag 4199 is a global trace flag for the entire instance.  This means that it’ll apply the configuration across all databases.  What if you have a mixture of databases that may or may not be able to take advantage of any hotfixes?  Enabling the trace flag could cause issues.  This is where the QUERY_OPIMIZER_HOTFIXES database scoped configuration comes in to play.

This configuration allows you to enabled query optimizer hotfixes post the release to market version for any specific database. This also allows you to enable it for Azure SQL Database since we don’t have the ability to enable the trace flag within that product.

ALTER DATABASE SCOPED CONFIGURATION QUERY_OPTIMIZER_HOTFIXES = ON;

The query above has to be executed within the context of the individual database.  You can also enable this configuration in Azure SQL Managed Instances databases.

ENABLE_QUERY_OPTIMIZER_HOTFIXES

If you didn’t want to enable these hotfixes at the instance level or the database level, there’s a third option.  You can also enable it as a query hint.  Keep in mind that the query optimizer will want to pick the best execution plan possible.  Use query hints as sparingly as possible and make sure to ask yourself if you are really smarter than the optimizer.  If the answer is no, you aren’t smarter than the optimizer then don’t use a query hint.  So, it goes without saying that my recommendation is to not use query hints unless that’s the only way to solve an issue.

SELECT * FROM Person.Address 
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION ( USE HINT ('ENABLE_QUERY_OPITMIZER_HOTFIXES'));
GO

Summary

In this post I’ve shown three methods on how to implement the most recent query optimizer hot fixes.  Before putting any of them into production, make sure that you verify your respective workloads again them.  Make sure to also review all of the cumulative updates or service packs since initial release to see what query optimizer issues were fixed.  You might be missing out of a fix that will help solve a performance problem.

© 2020, John Morehouse. All rights reserved.