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
- setting up a SQL Server Express database before each test
- running the test
- 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:
This means that when you get to the teardown phase of the test, you can issue a DDL statement to the
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.
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.
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?
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.