How to close database connections to SQL Server during integration testing teardown.

Whenever I need to run integration tests that involve SQL Server, I have a standard approach that I've evolved since 2007. It involves

  1. setting up a SQL Server Express database before each test
  2. running the test
  3. tearing down the database
I've explained the basic process in details in my Outside-In Test-Driven Development Pluralsight course. You can also see it in action in this GitHub repository.

One problem with that approach is that SQL Server doesn't allow you to delete a database if it has existing connections.

Turn off connection pooling #

I usually solve the problem by turning off connection pooling. For an integration test suite, this is fine. I usually use integration testing to verify functionality - not performance.

Turning off connection pooling is easily done by setting the flag to false in the connection string:

Server=(LocalDB)\MSSQLLocalDB;Database=Booking;Integrated Security=true;Pooling=false

This means that when you get to the teardown phase of the test, you can issue a DDL statement to the master database:

IF EXISTS (SELECT name
    FROM master.dbo.sysdatabases
    WHERE name = N'Booking')
DROP DATABASE [Booking]

When connection pooling is turned off, no other connections are open when you attempt to do that, and the database (here named Booking) is deleted.

Forcibly close other connections #

Recently, however, I ran into a testing scenario where connection pooling had to be turned on. When you turn on connection pooling, however, the above DROP DATABASE statement fails because at least one connection from the pool is still connected to the database.

To solve that issue, I forcibly close other connections during teardown:

IF EXISTS (SELECT name
    FROM master.dbo.sysdatabases
    WHERE name = N'Booking')

BEGIN
    -- This closes existing connections:
    ALTER DATABASE [Booking]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    DROP DATABASE [Booking]
END

Surprisingly, turning on connection pooling like this makes the integration tests slower. I suppose it's because throwing other connections off the database involves a bit of negotiation between the server and the clients, and that takes some time.

While slower, it does enable you to run the integration tests with connection pooling turned on. When you need it, you need it.

Summary #

You can run integration tests against a SQL Server Express database. People do it in various ways, but I've found that setting up and tearing down a pristine database for each test case is a robust and maintainable solution to the problem.

SQL Server will not, however, allow you to delete a database if other connections exist. The easiest and fastest solution to that problem is to turn off connection pooling.

Sometimes, you can't do that, so instead, you can expand your database teardown script so that it closes existing connections before it deletes the database.


Comments

This sounds like a great approach. I have been on projects with tests that involved the database, but none of them were designed as well as this. I will be sure to come back to this post when we add a database to my current project.

My understanding is that SQL Server Express and LocalDB are not the same thing. Are you using SQL Server Express or LocalDB? Do you prefer one over the other for this database testing approach of yours?

2020-07-25 19:58 UTC

Tyson, thank you for writing. It's not really my area of expertise. I use the one bundled with Visual Studio, so I suppose that's actually LocalDB, and not SQL Server Express.

2020-07-26 13:33 UTC


Wish to comment?

You can add a comment to this post by sending me a pull request. Alternatively, you can discuss this post on Twitter or somewhere else with a permalink. Ping me with the link, and I may respond.

Published

Monday, 20 July 2020 07:20:00 UTC

Tags



"Our team wholeheartedly endorses Mark. His expert service provides tremendous value."
Hire me!
Published: Monday, 20 July 2020 07:20:00 UTC