What Azure SQL driver works best for me

For languages like C#, C++, Node.JS, or Python, multiple options are available for drivers, depending on platforms and application needs, so it’s important to understand what combinations are available and recommended.

Programming languages like Perl, PHP, and Python are providing lightweight wrappers and interfaces around native ODBC drivers (e.g., pyodbc, DBI, etc.). To support all newest features available in latest Azure SQL releases (e.g., Always Encrypted, Data Classification, AAD authentication, etc.), Microsoft ODBC Driver 17 for SQL Server (or higher) is recommended. This driver is available for most major Linux distros and releases, in addition to Windows. Packaged with this driver are also traditional SQL Server client utilities like sqlcmd and bcp. Installation procedures for the ODBC driver largely depend on your target operating system and distribution, and all details can be found at this URL: https://aka.ms/azuresql-odbc-install.

As typical in the Java space, JDBC drivers don’t provide native connection pooling capabilities so many external libraries are available on the market. While we’re not endorsing any particular one, HikariCP (https://aka.ms/hikaricp) is one we often encounter when working with customers connecting to Azure SQL instances and has proven to be fast and reliable.

Connectivity aspects

While a managed instance is automatically associated with an Azure Virtual Network at creation time, and public connectivity is optional, for individual Azure SQL Database instance is actually the opposite, and you should rely on Azure Private Link capability to connect your application deployed on one of the Azure services that support VNETs via a private endpoint and on a completely isolated network traffic path.

Server- and database-level firewall rules are designed to define what ranges of IP addresses (coming from public Internet connectivity or from within various Azure services) can establish a connection with Azure SQL Database single instances. If database-level firewall rules exist (today, these can be created through T-SQL commands only), those will be evaluated to understand if a client connection is coming from an allowed range; otherwise, server-level rules (valid for all database instances associated with that virtual server and defined through T-SQL, PowerShell/CLI, or Azure Portal) will be checked. If you decide to trust all network connections coming from an Azure service, then there’s a check box option on the portal to “Allow Azure services and resources to access this server” to simplify your settings.

VNET Service Endpoint is a feature designed to guarantee that network connections targeting a given Azure SQL server will be accepted only if they come from one or more VNET/Subnet pairs where your applications are deployed.

On the application side of the connection, a VNET Network Security Group can be created and associated with a Virtual Machine’s NIC or an entire Subnet to make sure they can only connect with a given range of IP addresses and ports where your Azure SQL instances reside.

With Proxy, you have maximum flexibility to connect to your instance from anywhere using FQDN server name and just port 1433, but these connections will always go through a Gateway front-end layer that will increase network latency (usually not good for chatty applications).

Redirect policy instead establishes connections directly to the node hosting the database, reducing latency, and improving application throughput, but it does require specific port ranges (11000–11999) to be open between your application host and the database.

Retry logic

This logic can be as simple as just retrying opening a connection, after a previous tentative has failed returning a certain exception, for a fixed amount of times and with some delay between retries.

A trickier use case for retry logic is when an operation ultimately modifies database state, like inserting a new record or updating one or more existing ones. If a transient error happened while this command was under execution, the application will be responsible for deciding if the previous attempt failed before or after the database was effectively modified. In this case, in fact, client applications cannot just blindly re-execute previous commands, as there’s no guarantee data wasn't already modified (think about a bank account’s transaction as an example). Retry logic needs to ensure that either the previous transaction was completely committed or that the entire operation was rolled back; otherwise, the database could remain in an inconsistent state. Basically, retry logic for transactional database code can be quite complex and will typically only apply for those use cases where your data modification code is completely idempotent (i.e., can be executed multiple times without necessarily modifying database state).

If correctly modeled, the database will help you in making sure data is consistent: for example, an order with an already existing number will not be allowed to be inserted. That said, you will still need to handle the returned error, and thus implementing solid retry logic will significantly improve your application reliability and stability.

Transient and persistent errors

A key aspect of implementing a robust retry logic mechanism is to intercept and decode what errors the application should interpret as transient and what are instead permanent (and retry logic won’t be able to help).

As mentioned previously, there are several categories of conditions and events that could be categorized as transient, from underlying hardware failures and automatic reconfigurations to temporary resource exhaustion. Transient issues could also happen at different layers in the stack: think about a temporary glitch in software defined networking! While these episodes can be rare and very short in time, it’s very important to proactively address them by adopting proper coding practices. A comprehensive explanation of typical connectivity issues at different layers is offered in Azure SQL’s public documentation at this link: https://aka.ms/tciasb.

Custom code or reusable libraries

As you notice, a lot of “plumbing” and complexity is required for a quite simple database operation to make it resilient to transient errors. Imagine if this should be repeated for every method and interaction that your application has with its data layer! Plus, this lacks any option for consistently configure parameters like number of retries, fixed or incremental delays, or even what exceptions should be considered transient vs. permanent.

Luckily, over the years, a number of reusable libraries have been created covering pretty much every programming language and framework to encapsulate that plumbing code into configurable mechanisms that developers can use to make their applications more reliable.

For .NET applications , one of the most known retry logic libraries is Transient Fault Handling (TFH) Application Block, originally part of Microsoft Enterprise Library framework, that has been recently ported to .NET Core and is freely available on NuGet for downloads at this URL: https://aka.ms/eltfhc.

In this fragment (you can find the complete example in the companion GitHub repo), we configure retry strategies’ details in a configuration file, essentially defining all the parameters like number of retries, retry intervals, and so on.

You may want to define more retry strategies in your applications and use them depending on the kind of database operation you want to retry. For example, for less frequent data retrieval operation, you may want to define a more aggressive retry strategy with higher number of retries, while for a different use case, you may want to step back and quickly return the error to the end user so that he or she can make a different decision based on that.

Next step is to define what error detection strategy you want to use. TFH provides out of the box a class called SqlDatabaseTransientErrorDetectionStrategy which encapsulates the logic for detecting the most common error codes that Azure SQL will emit when facing a transient error. In this example, we instead created a custom strategy by creating a class that implements ITransientErrorDetectionStrategy interface, as we wanted to test our retry logic with some non-transient errors.

You then create a RetryPolicy instance by combining your retry strategy and transient error detection class, and that will provide the ExecuteAction() or ExecuteAsync() method to effectively wrap database access code.

Retry policy class also exposes a Retrying event that you can subscribe to and be notified when retry logic is intercepting a transient error and retrying an operation.

Another popular library in the .NET space is Polly (https://aka.ms/avnp) which also provides features that cover other app reliability aspects by implementing resiliency patterns like Circuit Breaker, Timeout, Bulkhead Isolation, and Fallback in addition to just Retry.

In this example, we use Tenacity to decorate a Flask method interacting with our database called getorders() , and we retry three times with a fixed interval of 10 seconds in case of an exception. Instead of specifying in decorator’s attributes what exception to retry with, we’re instead wrapping pyodbc methods with a try/except block and checking if the exception has anything to do with database access. In that case, we’re checking if underlying database error code is contained in the list we’re maintaining for retriable error and, if that’s the case, we’re just raising that exception so that the @retry decorator can do its job of automatically retrying the method until it succeeds or it should stop trying as the max number of attempts has been reached.

Connectivity best practices

Let’s start from a very basic one: in a database application, latency matters! This means that, for performance reasons, it’s important to make sure your application code runs as close to your database as possible, no matter what Azure service it will be deployed on. This is especially true for applications that are executing a lot of database interactions or roundtrips, where this latency can easily become more impactful than real processing time.

At the very least, you should make sure your application gets deployed in the same region as where your database instance is. This may also have some architectural implications in case, for example, you’re designing a highly reliable, cross-region solution. This means that you need to plan for failing over not only your data layer but also your application tier accordingly to minimize latency impact in case of a malfunctioning of your primary site.

Along the same line, it’s also important to understand overall Azure SQL connectivity architecture (explained in the official docs: https://aka.ms/sdmica) and make sure that, if your application tier is running in an Azure service like Virtual Machine, App Service, or Azure Kubernetes Service, as an example, it is leveraging the Redirect connection policy, which means that your application will communicate directly with the node hosting your database instance instead of passing through the Gateway layer for every single interaction. If your app is executing anything more than only a few queries every minute, this option will make a significant difference from a performance perspective, and the trade-off required is just to make sure that ports in the range of 11000–11999 are open in networking configuration where your client code resides.

While it may sound trivial, another recommendation related to connectivity is to make sure your code is effectively opening a connection with an Azure SQL database instance as late as possible before executing some meaningful command, and it’s closing that connection as soon as results are consumed. Most driver libraries are, in fact, designed to leverage Connection Pooling, a mechanism that will help you balance between the cost of opening a brand-new physical connection (e.g., a TCP socket) with a remote service, which always comes with a given millisecond overhead, and the cost of keeping too many connections always open as that will increase the amount of resources (memory, worker threads, etc.) consumed on the service side.

Connection pooling works at the process level and keeps a physical connection opened for a certain amount of time even if in your application code you explicitly called a close or dispose method, so that if a new request to open a connection with the same connection string parameters will be executed later, the existing physical connection will be reused instead of opening a brand new one.

Thanks to this approach, in a canonical web application or web API scenario, it’s not uncommon that, even if thousands of users are accessing a given page, only a few tens of real database connections are kept open at any given time, significantly reducing the overhead generated for Azure SQL database instances.

Generally speaking, in most scenarios where a multi-threaded application is executing a conventional database workload (queries, transactions, etc.) against an Azure SQL instance, it is recommended to leverage Connection Pooling for performance reasons. The only exception to this general rule is where your application really needs to carefully control how specific operations are executed against the database. A good example for that are Data Definition Language (DDL) commands (CREATE, ALTER, DROP that will be discussed later, that work on data structures instead of data itself) that your application may issue against the database, where usually one connection at time is executed and commands are serialized on that same connection.

As mentioned, most existing drivers are providing this capability out of the box and even enabling it by default, like .NET Data Provider for SQL Server, but there’s an important exception. In the Java space, historically, connection pooling has been a separate implementation from JDBC drivers so SQL Server’s one doesn’t provide functionality.

Thankfully, there are many external packages offering that capability for your Java application, and one of the most known is certainly HikariCP (https://aka.ms/hikaricp), as mentioned before. It’s important to notice though that, generally speaking, Java drivers have some challenges in detecting what are usually referred as “stale connections” or client-side connection objects that have lost underlying connectivity with a database instance due to a transient issue, without trying to execute a test command (by explicitly invoking java.sql.Connection.isValid() which pings the database every time to make sure the connection is opened). In other drivers, this is usually performed at a lower level by checking the state of a TCP socket, but Java native APIs have issues with that. A similar problem could happen while a command is executed, and a resultset is under consumption by your application code. The recommendation here is to carefully configure both your JDBC driver and your connection pooling classes with proper timeouts to avoid that the application can hang forever if a transient error happens at the wrong time. All the details about these configurations are further explained in an article at this URL: https://aka.ms/jdbc-connection-guidance.

Other high-level languages and frameworks like Python and pyodbc may be suffering from the same transient connectivity issues, and the same approach and guidance is also recommended.

Data access frameworks

Although basic abstractions will generally take control of every possible aspect of your database interactions, they may require a lot of boilerplate code in your application to transform objects and structures representing higher-level entities in your logic into rows and columns within your database structure.

Over the years, a number of libraries and frameworks have been created to simplify this issue while still letting you control how your code interacts with your data. As a practical example, in the .NET space, ADO.NET Provider for SQL Server provides from the very first release, together with SqlConnection, SqlCommand , and SqlDataReader classes (representing what is usually referred to as the “connected” portion of SqlClient library), a number of other classes are provided to interpret and navigate through query results in a more “object-oriented” fashion, but also to take care of persisting back to the underlying database whatever change has been made to original data. I’m referring to classes as SqlDataAdapter and DataSet (typed or untyped). DataSet is an in-memory representation of resultsets populated from the scratch in your code or returned from one or more queries to the database and provides additional logic around offline change tracking, data validation, and relationship management between entities. SqlDataAdapter acts as a “connector” between DataSets and database objects and can both automatically generate T-SQL commands that take changes applied to in-memory data and persist them in the back-end database or leverage existing commands and Stored Procedures to control all aspects of these database operations for performance or concurrency reasons. DataSets can also be automatically generated from a database schema and become fully typed objects exposing internal resultsets as named collections (e.g., Customers, Orders, etc.) instead of rows and columns. To discover more about these options, you can find complete coverage at this link: https://aka.ms/dnfado.

In this simple example, you can see how SQLAlchemy lets us define in-memory representation of our application entities and how they map to database tables. Using an app-level SQL-like syntax, we can then specify our query containing advanced operations like joins, filters, aggregations, projections, and so on, and SQLAlchemy classes will translate this into a SQL syntax specific for Azure SQL, but you could easily port the same code to connect to other supported database systems as well. While SQLAlchemy can do much more (we’ve just scratched the surface here), its most advanced features more than just a data access framework belong to the realm of Object-Relational Mappers, which is the topic of the next section.

ORMs

One of the first and most successful libraries in this space is Java’s Hibernate (https://aka.ms/horm) appeared in the early 2000s, with the goal of providing a better experience than Enterprise Java Beans entities to persist data into databases without necessarily using SQL commands. Over the years, these libraries became much more powerful and complex (for some people, even too complex, so that alternatives like “micro-ORM” libraries have been created) to cover other aspects of the data access tier like modeling, security, and scalability.

Microsoft’s own ORM for .NET world is Entity Framework (EF), and its more recent release is EF Core (https://aka.ms/ghdnefc). EF Core works with several back-end database stores like SQL Server, Azure SQL Database, SQLite, Azure Cosmos DB, MySQL, PostgreSQL, and other databases through a provider plug-in API. It’s the result of 10+ years of development in this space and provides features like LINQ queries, offline change tracking, batched updates, and database schema management through a feature named “migrations.”

As you can notice in the complete example (see GitHub repo for that), it’s interesting to see how our WWImportersContext class overrides the OnConfiguring and OnModelCreating methods of its base class to do exactly what their names imply: configuring the way our context communicates with the database and defining a model where our entities map to respective database tables. We also configured the logging infrastructure to show how the resulting T-SQL code automatically generated by the context looks like. Pretty straightforward, isn’t it?

Entity Framework Core can do much more, and you can start familiarizing with all these capabilities through this free Microsoft Learn online course (https://aka.ms/lmpsefc).

MicroORMs

This example shows how MicroORMs like Dapper can be a good compromise between using base SqlConnection, SqlCommand, and SqlDataReader classes and a more sophisticated, but complex, solution like Hibernate or Entity Framework for your data access layers.