Real SQL Server with Testcontainers: Container Lifecycle, Migrations and Cleanup Between Tests

Version note: The code in this post was built and run with .NET 10, xUnit.net v3 4.0.3, Testcontainers for .NET 4.16, Respawn 7.0, Entity Framework Core 10.0.15 and SQL Server 2025.

Guess First

At the end of post 1, one test failed because two tests shared the same table. The fix is "every test starts with a clean database", but there are at least three ways to get there. I tried all three with the same ten tests. Each test adds a book and expects to see exactly one book in the list, so it can only pass if isolation really works:

public static async Task CreateAndExpectSingle(HttpClient admin, string isbn)
{
    HttpResponseMessage created = await admin.PostAsJsonAsync("/api/books", new CreateBookRequest(isbn, "Isolation", 10m, 1));
    created.EnsureSuccessStatusCode();
    List<BookDto>? books = await admin.GetFromJsonAsync<List<BookDto>>("/api/books");
    Assert.Single(books!);
}

The first way: empty all tables with Respawn before every test. The second: run each test inside a TransactionScope and leave it without committing. The third: create a fresh database with migrations for every test. Which is fastest over the ten tests, which is slowest? And which one runs wrong on the first try without raising any error of its own?

The Answer: Three Ways, Three Costs

The total times I measured, including the host's first start:

The fastest is transaction rollback, but on the first try nine of the ten tests failed and rows left by the tests piled up in the database. The reason is below; the short answer is that the ambient transaction does not reach the request inside TestServer unless you set it up to. A database per test is the most robust but slowest way; every test applies the migrations from scratch and starts a new host. Respawn sits between the two, but used with a shared database and test classes running in parallel it corrupts the test results. Where Bookstore ends up is a combination of these: a database per test class, and Respawn before every test inside the class.

The Container Lifecycle

The fixture that manages the container becomes simpler in this post; it no longer applies migrations itself, it only keeps SQL Server running and builds connection strings for a given database name:

using System.Diagnostics;
using Microsoft.Data.SqlClient;
using Testcontainers.MsSql;

[assembly: AssemblyFixture(typeof(Bookstore.Api.IntegrationTests.SqlServerFixture))]
[assembly: Trait("Category", "Integration")]

namespace Bookstore.Api.IntegrationTests;

public sealed class SqlServerFixture : IAsyncLifetime
{
    private readonly MsSqlContainer container = new MsSqlBuilder("mcr.microsoft.com/mssql/server:2025-latest").Build();

    public TimeSpan StartupTime { get; private set; }

    public async ValueTask InitializeAsync()
    {
        Stopwatch stopwatch = Stopwatch.StartNew();
        await container.StartAsync();
        StartupTime = stopwatch.Elapsed;
    }

    // A separate database per test class, on the same server
    public string ConnectionStringFor(string database) =>
        new SqlConnectionStringBuilder(container.GetConnectionString()) { InitialCatalog = database }.ConnectionString;

    public async ValueTask DisposeAsync() => await container.DisposeAsync();
}

Passing the image name to the builder's constructor is becoming mandatory in Testcontainers 4.x. The parameterless new MsSqlBuilder() still compiles but gives this warning:

warning CS0618: 'MsSqlBuilder.MsSqlBuilder()' is obsolete: 'This parameterless constructor is obsolete and will be removed. Use the constructor with the image parameter instead: https://github.com/testcontainers/testcontainers-dotnet/discussions/1470#discussioncomment-15185721.'

Spelling out the image is a good habit anyway: tests should use whatever SQL Server version runs in production, and the test engine should not change silently because the library's default changed. Bookstore targets SQL Server 2025; in the container, SERVERPROPERTY('ProductVersion') returned 17.0.5035.5.

The StartAsync call pulls the image (if needed), starts the container with a random host port and waits until the module's readiness check finishes. For this, the MsSql module runs SELECT 1 with sqlcmd inside the container; you see those lines one after another in the log. In this measurement the container took 7.4 seconds to become ready. The random port matters: when two CI jobs or two developers run tests on the same machine at the same time there is no port clash, and GetConnectionString() always builds the connection string with the right port.

The module sets the ACCEPT_EULA environment variable the SQL Server image requires. So when you start SQL Server with Testcontainers, you are accepting Microsoft's license terms; using the Developer edition for testing and development falls within those terms.

Cleaning up the container works in two layers. In the normal flow, DisposeAsync stops and removes the container. If the test process crashes or is stopped in the debugger, that call may never happen; for that, Testcontainers starts a small helper container called Ryuk along with the first container. When Ryuk loses its connection to the test process, it removes that session's labeled containers itself. That is why you see testcontainers/ryuk in docker ps output.

Apply Migrations, Not EnsureCreated

There are two ways to create the schema at test startup: Database.MigrateAsync() and Database.EnsureCreatedAsync(). The second is shorter and tempting because it works without migrations. To see the difference, I added a migration to Bookstore with raw SQL that has no counterpart in the EF Core model:

protected override void Up(MigrationBuilder migrationBuilder)
{
    // Raw SQL: not part of the EF Core model, only the migration knows about it
    migrationBuilder.Sql("ALTER TABLE [Books] ADD CONSTRAINT [CK_Books_Price_Positive] CHECK ([Price] > 0);");
}

I created the schema three different ways in three separate databases and tried to save a book with a negative price:

Migrate: DbUpdateException -> The INSERT statement conflicted with the CHECK constraint "CK_Books_Price_Positive". The conflict occurred in database "Probe_21974a9df87c439fa740e917a55441bf", table "dbo.Books", column 'Price'.
EnsureCreated: negative price saved
EnsureCreated+Migrate: SqlException -> There is already an object named 'Books' in the database.

EnsureCreated builds the schema from the current state of the model, not from the migrations. Raw SQL in migrations, data fixes, views and stored procedures are never applied, and no __EFMigrationsHistory table is created. The result: the test accepts a record that the production database rejects. On top of that, applying migrations later to a database created with EnsureCreated blows up when the first migration tries to create the tables again. Using MigrateAsync in integration tests has one more benefit: the migrations themselves are tested on every run by being applied to an empty database.

Cleaning Up Between Tests with Respawn

Respawn is a small library written by Jimmy Bogard. It reads foreign key relationships from the database metadata, computes the order in which to delete tables once, and then runs DELETE statements in that order on every call. According to the README, it uses DELETE rather than TRUNCATE because truncation requires dropping foreign keys, and ordered deletes came out faster in the author's measurements. In version 7.0 the package does not depend on Microsoft.Data.SqlClient directly; you pass a connection from the SqlClient in your project.

In Bookstore I put Respawn inside the factory, because the factory now owns its own database:

public sealed class BookstoreApiFactory(SqlServerFixture sql) : WebApplicationFactory<Program>, IAsyncLifetime
{
    // Every factory instance (that is, every test class) gets its own database
    private readonly string connectionString = sql.ConnectionStringFor($"Bookstore_{Guid.NewGuid():N}");
    private Respawner? respawner;

    public string ConnectionString => connectionString;

    public async ValueTask InitializeAsync()
    {
        DbContextOptions<BookstoreDbContext> options = new DbContextOptionsBuilder<BookstoreDbContext>()
            .UseSqlServer(connectionString)
            .Options;
        await using (BookstoreDbContext db = new(options))
        {
            await db.Database.MigrateAsync();
        }

        await using SqlConnection connection = new(connectionString);
        await connection.OpenAsync();
        respawner = await Respawner.CreateAsync(connection, new RespawnerOptions
        {
            // If the migration history is deleted, the next MigrateAsync tries to build everything again
            TablesToIgnore = ["__EFMigrationsHistory"]
        });
    }

    public async Task ResetDatabaseAsync()
    {
        await using SqlConnection connection = new(connectionString);
        await connection.OpenAsync();
        await respawner!.ResetAsync(connection);
    }

    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        builder.UseEnvironment("Testing");
        builder.UseSetting("ConnectionStrings:Bookstore", connectionString);
        // The ConfigureTestServices part is the same as in post 2
    }
}

Because the factory implements IAsyncLifetime, xUnit v3 also calls InitializeAsync when it creates it as a class fixture: the database is created, migrations are applied and Respawn computes the delete order. Test classes trigger the cleanup before every test:

public sealed class BookCatalogTests(BookstoreApiFactory factory) : IClassFixture<BookstoreApiFactory>, IAsyncLifetime
{
    public async ValueTask InitializeAsync() => await factory.ResetDatabaseAsync();

    public ValueTask DisposeAsync() => ValueTask.CompletedTask;

    // tests...
}

Cleaning up before the test rather than after is a deliberate choice. If a test fails, the data it leaves behind is the first place to look while debugging; the next test cleans up at its own start anyway. With this setup, the search test I narrowed to Assert.Single(books!, b => b.Isbn == ...) in post 1 can be written with Assert.Single(books!) again and passes alongside the "2nd Edition" test.

Transaction Rollback: Fast, but Conditional

The fastest method in the measurement is to run the test inside a TransactionScope and leave it without calling Complete(). The SQL connections the API opens enlist in the ambient transaction, and everything is undone when the scope is disposed. The working version:

[Theory]
[InlineData("978-0201633610")]
[InlineData("978-0201485677")]
[InlineData("978-0321127426")]
public async Task Each_test_sees_only_its_own_book(string isbn)
{
    // Let TestServer run the request with the test's ExecutionContext so the ambient transaction reaches the API
    factory.Server.PreserveExecutionContext = true;
    using TransactionScope scope = new(TransactionScopeAsyncFlowOption.Enabled);

    HttpClient admin = factory.CreateClientFor(TestUser.Admin);
    HttpResponseMessage created = await admin.PostAsJsonAsync("/api/books",
        new CreateBookRequest(isbn, "Rollback", 10m, 1), TestContext.Current.CancellationToken);
    created.EnsureSuccessStatusCode();

    List<BookDto>? books = await admin.GetFromJsonAsync<List<BookDto>>("/api/books", TestContext.Current.CancellationToken);
    Assert.Equal(isbn, Assert.Single(books!).Isbn);
    // scope.Complete() is not called: Dispose rolls the transaction back
}

The critical line is PreserveExecutionContext = true. TransactionScope carries the ambient transaction with an AsyncLocal-like mechanism, while TestServer by default runs the request independently of the test's ExecutionContext. When I set the line to false, two of the three tests failed:

failed Bookstore.Api.IntegrationTests.RollbackCatalogTests.Each_test_sees_only_its_own_book(isbn: "978-0201633610") (36ms)
  Assert.Single() Failure: The collection contained 3 items
  Collection: [BookDto { Id = 1, Isbn = 978-0321127426, Title = Rollback, Price = 10,00, Stock = 1 }, BookDto { Id = 2, Isbn = 978-0201485677, Title = Rollback, Price = 10,00, Stock = 1 }, BookDto { Id = 3, Isbn = 978-0201633610, Title = Rollback, Price = 10,00, Stock = 1 }]

The API's connections never enlisted in the transaction, every record was committed, and the scope's rollback undid nothing. The error message comes not from the rollback but from the next test seeing extra rows; run on their own, the tests all look green. The second trap is where the scope is created: in my first attempt I opened the scope in the test class's async InitializeAsync, and the rollback did not work there either. An ambient transaction set inside an async method does not flow back to the caller when the method returns; the scope must be opened inside the test method.

The third limit is more fundamental. If the test keeps its own connection open to prepare data while the scope is active and the API opens a second connection, two connections have joined the same transaction and SqlClient tries to promote it to a distributed transaction. In the environment for this post the result was PlatformNotSupportedException: This platform does not support distributed transactions. On Windows there has been support through MSDTC since .NET 7, but TransactionManager.ImplicitDistributedTransactions is false by default and the promotion attempt still ends in an exception. And in the E2E block the API will run in a separate process or on Kestrel; there the test process's transaction can never reach the API. Rollback is a good option only for narrow tests running inside TestServer with a single connection.

A Database per Test and Parallelism

To show why I open a separate database per class despite Respawn's speed, I connected two test classes to the same database; both clean up with Respawn before every test, and xUnit v3 runs different classes in parallel by default. In all three runs, 20 of the 20 tests failed:

Assert.Single() Failure: The collection contained 2 items
Collection: [BookDto { Id = 5, Isbn = 978-9-2, Title = Isolation, Price = 10,00, Stock = 1 }, BookDto { Id = 6, Isbn = 978-8-5, Title = Isolation, Price = 10,00, Stock = 1 }]

The two classes clean up at almost the same moment, insert at the same moment, and each sees the other's book. If one class's cleanup lands in the middle of another's test it gets worse: the test cannot find the row it just inserted. A shared database with Respawn is only safe if you put all database tests in the same xUnit collection and run them one by one, which means giving up parallelism entirely.

A database per test solves the problem at the root, but the price is steep: about 350 milliseconds per test in the measurement, mostly migrations. For a 300-test suite that is more than 100 seconds on top of the tests themselves. A database per class is the middle ground: migrations and Respawn setup happen once per class, parallelism between classes is kept, and tests inside a class run sequentially anyway. With this setup Bookstore's whole suite of 11 tests took 10.9, 13.7 and 30.4 seconds in three consecutive runs; the jump at the end came from container startup and shows that this time can vary even on the same machine.

In Production

CI needs a runner with Docker. GitHub's hosted Linux runners come with Docker; Windows runners cannot run Linux containers, so the integration test job should run on a Linux runner. The setup details are in the GitHub Actions series post ".NET CI with GitHub Actions: setup-dotnet, global.json, NuGet Cache, Test Reports and Coverage". The SQL Server image is a few GB; if it isn't cached on the runner, every run pulls it again. Measure image pull time separately from container startup and make it visible in the pipeline log.

Reusing the container between runs (WithReuse(true)) looks tempting for local development. The Testcontainers documentation marks the feature as experimental and describes an important side effect: with reuse on, Ryuk is disabled and nobody removes the container automatically. I tried it in Bookstore with three consecutive runs:

startup 6,3 s, leftover Bookstore_ databases: 0
startup 6,2 s, leftover Bookstore_ databases: 3
startup 6,2 s, leftover Bookstore_ databases: 6

There are two lessons. First, the gain is smaller than I expected: the fixture's DisposeAsync stops the reused container, the next run starts it again and still waits for SQL Server to come up. Second, databases from earlier runs pile up; the test data no longer belongs to the run. Use reuse only knowingly and with a cleanup strategy; never use it in CI. After the experiment I had to remove the container by hand with docker rm.

That is also one reason to name test databases with a Guid rather than a fixed name: with several factories in the same container, several runs, or a reused container, names never clash.

Common Pitfalls

Not checking the image's architecture. The manifest of mcr.microsoft.com/mssql/server:2025-latest contains only linux/amd64. That is fine on x64 Windows and Linux machines; but if someone on the team uses an ARM-based laptop or you have an ARM-based CI runner, the container either runs there under emulation and noticeably slower, or doesn't run at all. Before moving to a new runner type, check the platforms the image supports with docker buildx imagetools inspect.

Starting the container inside a test. Writing await using MsSqlContainer container = ... in a test method adds the 6–7 second startup from the measurement to every test. Container in an assembly fixture, database in a class fixture, cleanup at the start of each test: each cost is paid at the right level.

Creating the schema with EnsureCreated. The experiment above showed that a check constraint from a migration never existed in the test. The integration test's job is to use production's real schema.

Letting Respawn delete the migration history. If __EFMigrationsHistory is not in TablesToIgnore, the first cleanup deletes the migration records too; the next MigrateAsync call on the same database tries to create the tables from scratch and stops with "There is already an object named 'Books'". If you have seeded reference tables (country list, currencies), add them to that list as well.

Not noticing that the ambient transaction silently does nothing. If you choose the rollback method, add a test or fixture step that checks no rows are left in the database at the end of the suite; when PreserveExecutionContext is forgotten, the error shows up in another test with a misleading message.

A Small Experiment

★☆☆ Run Bookstore's test suite twice on your own machine and write the value of SqlServerFixture.StartupTime to the test output. Measure the difference between the first run without the image and with it.

★★☆ Build a variation that connects all test classes to a single database and runs them one by one with [Collection("Database")]. Compare the suite time with the database-per-class setup; guess how the gap changes when the number of test classes grows to 20, then measure.

★★★ To bring down the cost of a database per test, apply the migrations once to a "template" database and create a copy of it for every test (hint: BACKUP DATABASE + RESTORE DATABASE ... WITH MOVE, or a database snapshot). Compare the copy time with the migration time.

The database is now real, clean and isolated between tests. But Bookstore's order flow touches a payment provider, a shipping API and an SMTP server. In the next post we fake these external dependencies at the HTTP and SMTP boundary: payment and shipping with WireMock.Net, email with a fake SMTP server, time with FakeTimeProvider, and failure scenarios where the resilience pipeline really kicks in.