Closing database connections during test teardown by Mark Seemann

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

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

• ← Previous
• Archive
• Next →