Eight Azure SQL configurations you may have missed

Comments 0

Share to social media

Azure SQL Database is Microsoft’s Platform as a service (PaaS) offering for SQL Server. It eliminates hardware, virtual machine, and operating system management and has automatic tuning, high availability, auto-scale capabilities, high-performance options, and much more. It’s easy to create an Azure SQL Server Database, but you may not know about best practices and some of the newer features. This article covers some of the essential configurations of Azure SQL you should be aware of. It explains some of the configurations or leaves you with links explaining the details of the more complex configurations.

These are the items covered in this article:

Once upon a time

Many of the items in this article are about Azure SQL Security. Years ago, before the cloud providers existed, organizations published sites from their own on-premises environments. Usually, an application server would be deployed in a virtual network called DMZ, protected by a firewall. This virtual network would be isolated from the rest of the company network by a second firewall, creating a level of isolation and making only the DMZ accessible from the internet.

The SQL Server would be located inside the company network, outside the DMZ. The application server on the DMZ would be the only server which would have access to the SQL Server, creating a secure connection from the internet to the company data.

Back when the technology was way more complex and difficult, we had all this security, so why would we think that provisioning an Azure SQL Database with a public endpoint in a cloud provider to be a good practice? Shouldn’t we change the defaults to make the security tighter like it was in the past?

Serverless is more than auto-pause

The Serverless option in Azure SQL is well-known for the ability to pause the service when it’s not being used.

An image showing the auto-pause delay of Azure SQL Database serverless.

However, this ability has many drawbacks:

  • There is no way to trigger the pause. As a result, the pause will only happen after the server stays without any request for the entire time of the auto-pause delay. The auto-pause delay has the minimum value of 1 hour
  • Several Azure features, such as Azure SQL Defender, will make calls to the serverless instance and prevent the serverless from getting into pause.
  • Several features such as long-term backup retention and elastic jobs are not compatible with auto-pause.
  • The serverless feature can be implemented only on the vCore mode. At the time of this writing, the cheapest DTU mode you can provision is € 5,00/Month. The most affordable vCore mode you can implement is €200/Month, besides the serverless, of course. The vCore cost of a serverless implementation is also a bit more expensive than other provisioning modes.

All these details will make the auto-pause benefits limited, however, there is one additional configuration: The serverless provisioning is a flexible vCore model, and you can establishing a minimum and maximum vCore amount for the server. In fact, this configuration is enabled by default when you choose the Serverless provisioning.

An image showing the Max and Min vCores configuration of serverless

By doing so, the Azure SQL will have a flexible charge based on the number of vCores actually used between the minimum and maximum established. You can even leave the auto-pause turned on if you want to do so. Auto-scaling is a benefit not available to any other provisioning model.

The image below shows an example of this. The subscription is always charged for minimum number of vCores (assuming that auto-pause is disabled) or the number of vCores used, saving money when the maximum number of vCores is not being used.

An image showing the CPU active, inactive and paused time periods of serverless.

(Image from Serverless compute tier – Azure SQL Database | Microsoft Docs)

Being able to scale up and scale down according to workload makes Serverless an attractive option when choosing your provisioning model. If you choose vCores, then you should consider Serverless with a minimum and maximum configuration.

You should not enable access to Azure Services on the Firewall

It’s easy and tempting to turn on the Allow Azure services and resources to access this server setting. You only need to enable it, and you will no longer have firewall problems with any Azure Service.

An image showing the "allow Azure services and resources to access this server" setting.

The problem is what most people ignore: The firewall will not be opened only to the services from your company. The firewall will be opened to any Azure services; it doesn’t matter to whom it belongs.

One security layer will be completely removed from the Azure SQL when this option is enabled. Of course, you still have the authentication, but is this enough?

You should not open firewall rules on the portal for database users

The firewall configuration is a common need in Azure SQL configuration. However, many administrators ignore the fact there are two levels of firewall configuration: You can configure the firewall on the server level, or you can configure the firewall on the database level.

An image showing database and server firewall rules.

(Image from IP firewall rules – Azure SQL Database and Azure Synapse Analytics | Microsoft Docs)

This happens because the UI in the portal can only configure the firewall at the server level. The firewall configuration on the database level needs to be done using T-SQL.

An image showing the Security menu options including Firewalls and virtual networks.

An image showing the firewall rules for a server

A basic security rule is always to set the minimum permissions needed by a user. If you open the firewall on the server level, you are not following this rule. A more secure way to open the firewall for database users is to open the firewall only for the database, not on the server level.

The statements below may be useful.

Creating a database firewall rule

Listing database firewall rules

An image showing the results the query to find firewall rules

Delete a database firewall rule

Once you adopt the practice of opening database firewall rules, the user connection needs to specify the database when connecting. Connections on the server level will not be accepted.

Using SSMS, this configuration is not typical and can be found on the advanced connection settings.

An image showing SSMS connecting to a server and specifying the database name.

Application connection strings always contain the database name, so this is not a big difference.

References

The secondary server is accessible on Business and Premium tiers

The Read Scale-Out of the Business or Premium tiers is a recent feature, but it’s quite interesting. It’s straightforward to set when provisioning the database.

An image showing the Read Scale-out setting

Microsoft uses the Always On feature in these tiers to ensure high availability. The image below illustrates how the failover is automatically implemented.

An image showing the Always On AG notes with Failover from Primary to Secondary

Enabling Read Scale-Out to the secondary servers makes it possible to create a balance for your applications. Applications that need to write data to the database will connect to the primary server, while applications only reading data will connect to the secondary server.

This connection is defined by the connection string of the application. The application only needs to specify the parameter ApplicationIntent on the connection string. ApplicationIntent can be defined as ReadOnly or ReadWrite. This single parameter will define if the connection will be made with the primary or the secondary server.

You can test this feature using SSMS: Using the Advanced Options you can set the ApplicationIntent parameter to one of these values, make the connection to the server and identify the server you are connected to.

An image showing the ApplicationIntent = Readonly option in the Connection dialog

The following query will allow you to identify if you are connected to the primary or secondary server:

References

Your azure applications should not route packages through the public network

Your Azure SQL is inside Azure (is there a more obvious statement than this one?).

If the applications which will access your Azure SQL are also inside Azure, the communication between both should happen entirely inside Azure. This means the network packages exchanged between the application and the Azure SQL should flow entirely inside the Microsoft Network, never through public network.

Once again, it appears obvious, but this is not the default behaviour. If you only “establish the connection” in the simplest way, the connection will be done through the public internet.

The best way to identify if the connection is done through Microsoft Network or through public internet is the firewall: If the connection is blocked by the firewall or you need to open the firewall for the source IP’s, then the connection is crossing the public internet. When the connection is configured to be go through the Microsoft Network, the IP configurations of the firewall will be bypassed.

For example, if a virtual machine on Azure shows the screen of the image below when connecting to Azure SQL, this means the communication between both will happen using public Ips.

An image showing the New Firewall Rule dialog

The steps to ensure the communication through the Microsoft Network are:

  • You need a virtual network
  • You need to ensure your application is being executed inside this virtual network
  • Create a service endpoint between your Azure SQL and your virtual network

An image showing the Service endpoints

  • On the Azure SQL Server, include permission for the virtual network to access the server

References

Additional Details

Your application is in a Virtual Machine: Virtual Machines already include a virtual network when they are provisioned, you only need to ensure the creation of the service link to your Azure SQL

PaaS: Each PaaS service in Azure has a different way to be included inside a virtual network. You need to verify the correct procedures for the PaaS you are using. However, this is a feature that may not be included on the lowest cost service levels available. That’s when you will need to decide to pay a bit more for the security or only ensure a good configuration of the firewall.

An image showing the configuration for the Outbound Traffic

An image showing the options for configuring networking for your app

You can dismiss the Firewall all together

If you manage to configure all your applications to access Azure SQL from the Microsoft Network, either because the application is in the network or using network communication such as VPN connections, you can completely Deny public network access on Azure SQL.

AN image showing the Firewalls and virtual network for you server. Deny Public network access is highlighted.

Once this change is complete, Azure SQL can only access the database from inside your company’s virtual network on Azure or through VPN.

The following configuration details are critical to this scenario:

  • Service Endpoint doesn’t work because it’s intended to establish a route to a feature with public access enabled
  • You need to create a Private Link on your virtual network
  • Private Links are based on DNS zones. When a private link is created, a DNS Zone is created as well (except on custom scenarios).
  • Peered virtual networks need to be configured to use the same DNS Zone for DNS resolution
  • Gateway clients (such as VPN clients), need to be configured to redirect DNS resolutions to Azure DNS. This can be done in many ways.

This configuration is similar to the one found on-premises: The database server will be completely private.

References

You should avoid SQL Standard security

Although the SQL Standard authentication evolved a lot over the years, it’s still a simple login/password authentication. Recently the Azure SQL team included features to allow limiting the authentication to Azure AD Authentication, preventing the use of SQL Standard Security.

An image showing where to require Azure AD authentication only

Besides the configuration itself, you can use policies to control the use of SQL Standard authentication and identify which servers have this configuration disabled, identifying which servers are not compliant with the company rules on an enterprise level.

Here are some blogs I wrote about this subject:

RCSI configuration is ON by default

SQL Server has many isolation levels to control the integrity of the data.

However, in read-heavy applications, many developers have the terrible habit of using NOLOCK on all statements to avoid the contention of creating lock records on the server. The developers ignore the risk of incorrect results.

Over the years, Microsoft created a solution for SQL Server: Two new isolation levels, RCSI (Read Committed Snapshot Isolation) and Snapshot Isolation; both also called optimistic isolation levels.

RCSI is considered a special isolation level. It’s different from the other isolation levels because RCSI is configured as a database property. There is no need to change the code. Every transaction that arrives on the server using Read Committed isolation (the default) is converted to Snapshot Isolation. I’m using quotes here because it’s not exactly Snapshot Isolation. RCSI has some differences from Snapshot Isolation, and that’s why they are considered two different isolation levels.

The point is: Azure SQL uses RCSI by default. This is a big change for all existing applications. Any use of NOLOCK that you have in existing applications becomes useless and a problem for the application because it brings the usual issues of NOLOCK with no benefit, since the use of RCSI replaces all the possible benefits of NOLOCK.

You can check this default on Azure SQL running the following query:

An image showing the results of checking is_read_committed_snapshot_on

Migrating applications to Azure SQL and using RCSI are great opportunities to eliminate all NOLOCKs still found in your SQL code.

References

Summary

It’s very easy to start developing applications using Azure SQL. After the initial development, as this article illustrated, Azure SQL has some secrets to help you improve performance and security.

Developers may need the assistance of Solution Architects for the most complex steps, such as virtual networks, DNS, VPN, and Private Endpoint configuration. This highlights how the cloud requires all roles working together, as it was in the past, with on-premises environments.

Load comments

About the author

Dennes Torres

See Profile

Dennes Torres is a Data Platform MVP and Software Architect living in Malta who loves SQL Server and software development and has more than 20 years of experience. Dennes can improve Data Platform Architectures and transform data in knowledge. He moved to Malta after more than 10 years leading devSQL PASS Chapter in Rio de Janeiro and now is a member of the leadership team of MMDPUG PASS Chapter in Malta organizing meetings, events, and webcasts about SQL Server. He is an MCT, MCSE in Data Platforms and BI, with more titles in software development. You can get in touch on his blog https://dennestorres.com or at his work https://dtowersoftware.com