Managed Databases: Choosing Between Azure SQL Database, PostgreSQL Flexible Server and Cosmos DB, Backups and Connection Resilience

Version note: The examples in this post were run with .NET 10 (SDK 10.0.404), Entity Framework Core 10.0.15, Microsoft.Data.SqlClient 6.1.7, SqlPackage 170.5.96, a SQL Server 2025 container and Azure CLI 2.92.0. The connection and retry experiments ran against a local SQL Server; no database was created on Azure. Service behaviour comes from Microsoft Learn, and list prices from the Azure Retail Prices API (14 November 2026).

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, is moving to Azure SQL Database. We do the first thing Learn recommends: we add EnableRetryOnFailure() to Entity Framework Core in a single line, so transient connection errors are retried. Two questions. Which of the existing tests breaks after this one line? Second: someone misspelled the database name in the connection string. After how many seconds does the book details request return a 500? We measured both locally.

Three Ways to Run SQL Server in the Cloud

BookstoreDb is a SQL Server database, and there are three ways to run it on Azure. SQL Server on an Azure VM is IaaS: the operating system, the SQL Server version, patches and backups are your job, and in return the whole instance is yours. Azure SQL Managed Instance is a managed SQL Server at instance level: the right choice when you move an older application that uses instance features such as SQL Agent, cross-database queries, CLR and linked servers. Azure SQL Database offers a single database; the server is only a logical container. Bookstore doesn't need instance features, so our choice is SQL Database.

SQL Database has two purchasing models. In the DTU model, an older measure that bundles CPU, memory and disk access into a single number, you choose a Basic, Standard or Premium package. In the vCore model that Learn recommends, you choose the number of cores and the tier separately: General Purpose (remote storage, for most applications), Business Critical (local disks and extra replicas, low latency) and Hyperscale (large databases, fast scaling). In General Purpose there are two kinds of compute: provisioned (fixed vCores, billed per hour) and serverless (billed per second for the vCores used). A serverless database can stop itself after being idle for a while (auto-pause); in General Purpose that delay is at least 15 minutes. The first connection to a paused database gets error 40613, and according to Learn the database usually takes about a minute to wake up.

You have to account for the price difference. In West Europe the list price for General Purpose Gen5 (14 November 2026) is about 0.167 dollars per vCore per hour for provisioned and 0.574 dollars for serverless. A serverless vCore-hour costs more than three times as much; so unless the database is idle most of the day, provisioned comes out cheaper. For a dev environment there's the free offer: according to Learn, up to 10 General Purpose databases per subscription, each with 100,000 vCore seconds, 32 GB of data and 32 GB of backups free per month. When the allowance runs out, the database either pauses until the end of the month or starts billing; you choose. There's no SLA, so it's not for production.

PostgreSQL Flexible Server, Cosmos DB and the AWS Equivalents

Bookstore stays on SQL Server, but another team might choose PostgreSQL. Azure Database for PostgreSQL Flexible Server comes in Burstable, General Purpose and Memory Optimized tiers; the West Europe list price of the smallest Burstable B1ms server is 0.0199 dollars per hour. Backups are kept for 7 days by default, up to 35. You can stop a dev server, but according to Learn it stays stopped for at most seven days and then starts by itself. The old Single Server retired on 28 March 2025.

Cosmos DB isn't relational; it's a distributed NoSQL service that works with documents or key-value pairs instead of tables. It measures capacity in RUs, request units that capture the cost of reads, writes and queries: in the provisioned model you reserve RU capacity per second, in the serverless model you pay for the RUs consumed (0.305 dollars per million RUs in West Europe). One account per subscription gets the first 1,000 RU/s and 25 GB free. It makes sense for data read by key, with a frequently changing schema, written in several regions, such as a cart; for data that needs transactions and relations, like Bookstore's orders, SQL is the more natural fit. The table below lists the options with their AWS equivalents; RDS is AWS's managed relational database service, EC2 its virtual machine service, DynamoDB its key-based NoSQL service, and Aurora is AWS's own PostgreSQL- and MySQL-compatible engine.

Moving BookstoreDb: Bacpac

The simplest way to move a small database is a bacpac: a format that dumps the schema and data into a single file. We added SqlPackage to the repo's tool manifest as a local tool, so dotnet tool restore is enough. We exported BookstoreDb from the local SQL Server:

$ dotnet tool install microsoft.sqlpackage
Tool 'microsoft.sqlpackage' (version '170.5.96') was successfully installed. Entry is added to the manifest file dotnet-tools.json.
$ dotnet sqlpackage /Action:Export /SourceConnectionString:"$SOURCE" /TargetFile:BookstoreDb.bacpac
Connecting to database 'BookstoreDb' on server '127.0.0.1,52714'.
Extracting schema
...
Processing Table '[catalog].[Books]'.
Processing Table '[ordering].[Orders]'.
...
Successfully exported database and saved it to file 'BookstoreDb.bacpac'.
Time elapsed 0:00:12.09

We imported the file into a new database on the same server and checked the counts:

$ dotnet sqlpackage /Action:Import /TargetConnectionString:"$TARGET" /SourceFile:BookstoreDb.bacpac
...
Successfully imported database.
Time elapsed 0:00:18.39
Books StockItems LastMigration
----- ---------- -------------
20 20 20261114204035_AddBookCover

For a consistent export in production, Learn recommends either stopping writes or exporting from a copy of the database; rows written during an export can end up half in the file. A bacpac written to a blob is limited to 200 GB. For larger moves, or ones that need little downtime, there are options such as Azure Database Migration Service and transactional replication. Microsoft's old tool, Azure Data Studio, retired on 28 February 2026; Visual Studio Code's SQL Server extension is recommended instead.

The other half of moving is keeping the schema up to date. Bookstore.Api applies migrations on startup in Development; in production we don't do that. Running migrations as a separate step during deployment, with a separate identity, is the topic of the post "Migration'ların Deploy'daki Yeri: Expand-Contract, EF Core Migration Bundle ve Geriye Uyumlu Şema" in the Deployment Stratejileri series.

Backups, Restore and Failover

SQL Database takes backups by itself: weekly full backups, differential backups every 12 or 24 hours and transaction log backups about every 10 minutes. With point-in-time restore you can go back to any moment within the retention period. The period is 1 to 35 days, 7 by default. To keep backups longer, long-term retention keeps weekly, monthly and yearly backups for up to 10 years. A restore always creates a new database; it doesn't overwrite the existing one. To bring back orders a developer deleted by mistake, you take the rows from the restored copy and write them into the original database.

Where backups are stored is also a setting: geo-redundant by default, local or zone-redundant if you prefer. Geo-restore, opening a database from backup in another region, only works with geo or GZRS backups. Learn gives this option's RTO and RPO as "minutes or hours". For something faster there are active geo-replication and failover groups: a readable copy in a secondary region is kept continuously up to date. A failover group gives you a listener name that doesn't change after failover (<fog>.database.windows.net); the application's connection string stays the same. According to Learn, the RTO is typically under 60 seconds. In a zone-redundant configuration, losing a zone is handled in under 30 seconds with no data loss. These choices are the topic of post 11; what you need to know here is that all of them cost money.

The platform also applies maintenance updates by itself. By default, impactful updates aren't applied between 8 AM and 5 PM local time; you can choose a weekday or weekend night window. During maintenance, connections drop briefly. That brings us to the application side.

The Azure Side: an Entra-only Server and a Passwordless User

The script that creates the server and the database:

#!/usr/bin/env bash
# Azure SQL Database for BookstoreDb: Microsoft Entra-only authentication, serverless compute, backup retention.
set -euo pipefail

: "${AZURE_SUBSCRIPTION_ID:?Set AZURE_SUBSCRIPTION_ID to the target subscription}"
ENVIRONMENT="${1:?Usage: 08-sql.sh <dev|staging|prod>}"
LOCATION="westeurope"
RG="rg-bookstore-${ENVIRONMENT}-weu"
SQL_SERVER="sql-bookstore-${ENVIRONMENT}-weu"
DBA_GROUP="sg-bookstore-dba"

az account set --subscription "$AZURE_SUBSCRIPTION_ID"
DBA_GROUP_ID=$(az ad group show --group "$DBA_GROUP" --query id --output tsv)

# No SQL logins at all: only Entra identities sign in, and the server admin is a group, not a person
az sql server create --name "$SQL_SERVER" --resource-group "$RG" --location "$LOCATION" \
  --enable-ad-only-auth --external-admin-principal-type Group \
  --external-admin-name "$DBA_GROUP" --external-admin-sid "$DBA_GROUP_ID" \
  --minimal-tls-version 1.2

if [[ "$ENVIRONMENT" == "dev" ]]; then
  # Free offer: when the monthly allowance is used up, the database pauses instead of billing
  az sql db create --name BookstoreDb --server "$SQL_SERVER" --resource-group "$RG" \
    --edition GeneralPurpose --family Gen5 --capacity 2 --compute-model Serverless \
    --use-free-limit --free-limit-exhaustion-behavior AutoPause \
    --backup-storage-redundancy Local
else
  # Serverless between 0.5 and 4 vCores, never paused, replicas spread over availability zones
  az sql db create --name BookstoreDb --server "$SQL_SERVER" --resource-group "$RG" \
    --edition GeneralPurpose --family Gen5 --capacity 4 --min-capacity 0.5 --compute-model Serverless \
    --auto-pause-delay -1 --zone-redundant true --backup-storage-redundancy Zone
fi

# Point-in-time restore window: 14 days instead of the default 7
az sql db str-policy set --name BookstoreDb --server "$SQL_SERVER" --resource-group "$RG" \
  --retention-days 14 --diffbackup-hours 12

if [[ "$ENVIRONMENT" == "prod" ]]; then
  # Long-term retention: weekly backups for 8 weeks, monthly backups for 12 months
  az sql db ltr-policy set --name BookstoreDb --server "$SQL_SERVER" --resource-group "$RG" \
    --weekly-retention P8W --monthly-retention P12M
fi

The server has no SQL authentication at all: with --enable-ad-only-auth only Entra ID identities can sign in, and the admin isn't a person but the sg-bookstore-dba group. Dev uses the free offer; staging and prod use zone-redundant serverless that never pauses. The connection string from post 5 already contained Authentication=Active Directory Managed Identity;User Id=<clientId>; what's missing is a database user for that identity. Someone from the admin group creates it with T-SQL in the database. Why secret-free connections matter is covered in the post "Secret Rotation ve Sızıntı Müdahalesi: Kesintisiz Rotation, Sızan Secret'ta İlk 60 Dakika ve Secret'sız Mimari" in the Configuration ve Secret Management series.

CREATE USER [id-bookstore-dev-weu] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [id-bookstore-dev-weu];
ALTER ROLE db_datawriter ADD MEMBER [id-bookstore-dev-weu];

CREATE USER [id-bookstore-deploy-dev-weu] FROM EXTERNAL PROVIDER;
ALTER ROLE db_ddladmin ADD MEMBER [id-bookstore-deploy-dev-weu];
ALTER ROLE db_datareader ADD MEMBER [id-bookstore-deploy-dev-weu];
ALTER ROLE db_datawriter ADD MEMBER [id-bookstore-deploy-dev-weu];

The API's identity can read and write data but can't change the schema; the deployment identity that runs migrations is separate. The whole file is in the repo at infra/sql/08-app-user.sql. It passed a syntax check (SET PARSEONLY ON) on the local SQL Server, but the command itself doesn't run on a server without Entra ID:

Msg 37525, Level 16, State 1, Line 1
Command 'CREATE USER FROM EXTERNAL PROVIDER' is not supported as Azure Active Directory is not configured for this instance.

A note: the SQL Server provider of EF Core 10 uses Microsoft.Data.SqlClient 6.1.7, and Entra ID support is built into that package. With SqlClient 7.0 the Azure dependencies were moved out of the main package; when you move to EF Core 11, you can't connect with Active Directory Managed Identity unless you add the Microsoft.Data.SqlClient.Extensions.Azure package.

Connection Resilience: EnableRetryOnFailure

In the cloud, database connections drop now and then: failover, scaling, maintenance, a serverless database waking up. These are transient errors, and the same operation succeeds a few seconds later. EF Core's SQL Server provider handles this with an execution strategy. We moved the database registration into an extension method:

using Microsoft.EntityFrameworkCore;

namespace Bookstore.Data;

public static class DatabaseExtensions
{
    public static IServiceCollection AddBookstoreDatabase(this IServiceCollection services, string connectionString) =>
        services.AddDbContext<BookstoreContext>(options => options.UseSqlServer(connectionString, sql =>
            // Azure SQL drops connections during failover, scaling and serverless resume: retry those errors
            sql.EnableRetryOnFailure(maxRetryCount: 5, maxRetryDelay: TimeSpan.FromSeconds(10), errorNumbersToAdd: null)));

    // Development convenience: applies pending migrations (schema + seed) when the API starts.
    public static async Task MigrateDatabaseAsync(this WebApplication app)
    {
        await using AsyncServiceScope scope = app.Services.CreateAsyncScope();
        BookstoreContext db = scope.ServiceProvider.GetRequiredService<BookstoreContext>();
        await db.Database.MigrateAsync();
    }
}

The defaults are 6 attempts and at most 30 seconds of delay; since that's long for a web request, we chose 5 attempts and 10 seconds. The strategy treats only known error numbers as transient: 40613 (database currently unavailable), 40501 (service busy), 4060 (cannot open database), 1205 (deadlock) and similar. The first answer to the guess came from the tests:

failed Bookstore.Api.Tests.OrderingTests.Placing_an_order_reserves_stock_and_removes_the_cart (9ms)
  System.InvalidOperationException: The configured execution strategy 'SqlServerRetryingExecutionStrategy' does not support user-initiated transactions. Use the execution strategy returned by 'DbContext.Database.CreateExecutionStrategy()' to execute all the operations in the transaction as a retriable unit.

The order placement code reserved stock, created the order and deleted the cart in a single transaction. A retrying strategy can't know which step to resume from if an error happens in the middle of a transaction, so EF Core rejects transactions opened by the user. The fix is to put everything that belongs to one attempt inside a delegate run by the strategy:

using Bookstore.Catalog;
using Bookstore.Data;
using Bookstore.Inventory;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;

namespace Bookstore.Ordering;

public abstract record PlaceOrderOutcome
{
    public sealed record Placed(Order Order) : PlaceOrderOutcome;
    public sealed record CartNotFound : PlaceOrderOutcome;
    public sealed record CartEmpty : PlaceOrderOutcome;
    public sealed record OutOfStock(IReadOnlyList<string> Isbns) : PlaceOrderOutcome;
    public sealed record CartChanged : PlaceOrderOutcome;
}

public sealed class OrderService(
    BookstoreContext db, ICatalogService catalog, IInventoryService inventory, TimeProvider clock)
{
    // The retrying execution strategy re-runs the whole delegate after a transient error,
    // so everything that belongs to one attempt (reads, transaction, writes) lives inside it.
    public Task<PlaceOrderOutcome> PlaceOrderAsync(PlaceOrderRequest request, CancellationToken ct) =>
        db.Database.CreateExecutionStrategy().ExecuteAsync(
            request, (_, req, token) => PlaceOrderOnceAsync(req, token), verifySucceeded: null, ct);

    private async Task<PlaceOrderOutcome> PlaceOrderOnceAsync(PlaceOrderRequest request, CancellationToken ct)
    {
        // A failed attempt may have left tracked entities behind
        db.ChangeTracker.Clear();

        Cart? cart = await db.Carts.Include(c => c.Items).FirstOrDefaultAsync(c => c.Id == request.CartId, ct);
        if (cart is null)
        {
            return new PlaceOrderOutcome.CartNotFound();
        }
        if (cart.Items.Count == 0)
        {
            return new PlaceOrderOutcome.CartEmpty();
        }

        IReadOnlyDictionary<int, BookInfo> books =
            await catalog.GetByIdsAsync([.. cart.Items.Select(i => i.BookId)], ct);

        // Stock reservation, the order and the cart removal commit or roll back together.
        await using IDbContextTransaction transaction = await db.Database.BeginTransactionAsync(ct);

        ReservationResult reservation = await inventory.ReserveAsync(
            [.. cart.Items.Select(i => new StockLine(i.BookId, i.Quantity))], ct);
        if (!reservation.Succeeded)
        {
            return new PlaceOrderOutcome.OutOfStock([.. reservation.UnavailableBookIds.Select(id => books[id].Isbn)]);
        }

        Customer customer = await FindOrCreateCustomerAsync(request.CustomerName, request.CustomerEmail, ct);
        Order order = new()
        {
            Customer = customer,
            PlacedAtUtc = clock.GetUtcNow().UtcDateTime,
            Status = OrderStatus.Placed
        };
        foreach (CartItem item in cart.Items)
        {
            BookInfo book = books[item.BookId];
            order.Items.Add(new OrderItem
            {
                BookId = book.Id,
                Title = book.Title,
                UnitPrice = book.Price,
                Quantity = item.Quantity,
                LineTotal = OrderItem.CalculateLineTotal(book.Price, item.Quantity)
            });
        }
        order.Total = order.Items.Sum(i => i.LineTotal);

        db.Orders.Add(order);
        db.Carts.Remove(cart);
        try
        {
            await db.SaveChangesAsync(ct);
        }
        catch (DbUpdateConcurrencyException)
        {
            // The same cart was ordered by a parallel request in the meantime.
            return new PlaceOrderOutcome.CartChanged();
        }
        await transaction.CommitAsync(ct);

        return new PlaceOrderOutcome.Placed(order);
    }

    private async Task<Customer> FindOrCreateCustomerAsync(string name, string email, CancellationToken ct)
    {
        string normalizedEmail = email.Trim().ToLowerInvariant();
        Customer? customer = await db.Customers.FirstOrDefaultAsync(c => c.Email == normalizedEmail, ct);
        if (customer is null)
        {
            customer = new Customer { Name = name.Trim(), Email = normalizedEmail };
            db.Customers.Add(customer);
        }
        return customer;
    }
}

Two details. The attempt reads the cart itself, because an earlier read may no longer be valid in a new attempt. ChangeTracker.Clear() removes the entities a failed attempt left in the context. Learn mentions one more edge case: if the connection drops exactly during commit, you don't know whether the transaction was written, and a new attempt could create the order a second time. For that you can pass a function to the verifySucceeded parameter that checks whether the operation was really written. In our case deleting the cart limits this naturally: a second attempt can't find the cart and returns CartNotFound. The tests on the whole solution (two of the cover tests were skipped because Azurite wasn't running):

Test run summary: Passed!
  total: 34
  failed: 0
  succeeded: 32

The Real Cost of Retries: Two Layers

On to the second question. We started the API with a database name that doesn't exist and requested a book's details. SQL Server returned 4060 on every connection, and the strategy treated it as transient:

info: Microsoft.EntityFrameworkCore.Infrastructure[10404]
      A transient exception occurred during execution. The operation will be retried after 0ms.
      Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot open database "BookstoreDb" requested by the login. The login failed.
...
      A transient exception occurred during execution. The operation will be retried after 1035ms.
      A transient exception occurred during execution. The operation will be retried after 3026ms.
      A transient exception occurred during execution. The operation will be retried after 7631ms.
      A transient exception occurred during execution. The operation will be retried after 10000ms.
...
      Microsoft.EntityFrameworkCore.Storage.RetryLimitExceededException: The maximum number of retries (5) was exceeded while executing database operations with 'SqlServerRetryingExecutionStrategy'.
      Request finished HTTP/1.1 GET http://127.0.0.1:52718/api/books/9780441172719 - 500 - application/problem+json 52817.3700ms

The answer is 52.8 seconds. The delays add up to 21.7 seconds; where does the rest come from? To find out we ran a second experiment: we renamed the database temporarily, sent the request and gave the name back three seconds later. With the default connection string:

20:57:33 request sent
20:57:36 database back
20:57:43 response 200 after 10.655411s

The request succeeded, but there isn't a single retry line from EF Core in the log. Microsoft.Data.SqlClient did the retry itself: when it gets a transient error while opening a connection, it waits for ConnectRetryInterval and tries again. According to Learn the defaults are ConnectRetryCount=1 and ConnectRetryInterval=10 seconds. We repeated the same experiment with ConnectRetryCount=0 added to the connection string:

20:58:10 request sent
20:58:13 database back
20:58:22 response 200 after 12.228297s

$ grep "retried after" api.log
      A transient exception occurred during execution. The operation will be retried after 0ms.
      A transient exception occurred during execution. The operation will be retried after 1092ms.
      A transient exception occurred during execution. The operation will be retried after 3009ms.
      A transient exception occurred during execution. The operation will be retried after 7457ms.

This time EF Core did the retrying. That also explains the 52 seconds of the first experiment: inside every EF Core attempt, SqlClient waited another 10 seconds. The two layers stack on top of each other. What you choose depends on how long a request can wait: in a web request the total wait should be shorter than the client's and the load balancer's timeouts. One last observation: when we stopped the SQL container and sent the request, the error came back as a pre-login handshake failure; its error number isn't on the list, so there was no retry at all and the request returned 500 in 5.5 seconds. That's a local behaviour of Docker's port forwarding, but it shows that the strategy only retries errors it recognises.

In Production

Count the connection pool. By default SqlClient keeps a pool of up to 100 connections per connection string. With 10 replicas on Container Apps, each opening 100 connections, you can hit a low tier's session limit; check the tier's limits on Learn's resource limits page.

Don't go to production on the lowest tiers. The Basic tier's West Europe list price of 0.161 dollars per day looks attractive, but its disk access and log write rate are limited accordingly; on a campaign night that's where you'll choke first.

Narrow network access. A new server's firewall blocks everything. The "Allow Azure services and resources to access this server" option opens the door not only to your resources but to those of every customer on Azure. The right way is a private endpoint with public access turned off; that's the next post.

Common Pitfalls

Forgetting serverless wake-up time. The first morning request to an auto-paused dev database waits about a minute and gets 40613. Without retries the user sees an error; even with them, the first request is slow.

Turning on retries and forgetting transactions. If a code path without tests calls BeginTransactionAsync, you find out in production. Searching the project for BeginTransaction takes five minutes.

Never trying a restore. Having a backup isn't proof that it can be restored. Try point-in-time restore at least once in the dev environment and write down how long it takes.

Giving the application db_owner. If the API's identity gets the rights migrations need, a SQL injection bug can go as far as dropping tables.

A Small Experiment

★☆☆ Move the retry count and delay of EnableRetryOnFailure to configuration. Use 2 attempts in Development and 5 in production.

★★☆ Write the verifySucceeded function for PlaceOrderAsync: if the connection drops after commit, it should decide whether the order was written by checking whether the cart was deleted. Think about how you could test it.

★★★ Repeat the experiment from this post on your own machine and record, for four combinations of ConnectRetryCount and EnableRetryOnFailure, how many seconds it takes for a missing database to return a 500. Which combination would you pick?

In the next post we take BookstoreDb and the storage account holding the covers off the internet: Bookstore's network topology with a VNet, subnets, an NSG, private endpoints and private DNS zones.