MS-SQL server sql test automation

Question

I'm looking to create a batch of automated tests in SQL on MS-SQL server, these will include tests like - insert some records, call stored procedure, check certain conditions in various tables. There will be other tests which will just be complicated queries - ie. many look like unit tests but will include deep knowledge of the product.

It would be helpful if these tests could be run on a customer site as well as in the office with minimal interference and maximum portability.

I was thinking of just going down the route of a stored procedure for each test and writing out detailed text as the results for each FAIL, and just a "Test number 99 - PASS" otherwise.

Would a more formal / commercial test harness be helpful? Thoughts?

Answer 1

Another approach that might help with the portability is to run you SQL from an application test harness. If you use the SQL inline with this you have the ability to take the exe to any installation and just run it. To expand on using a testing framework there are several really good articles on Simple Talk about Unit testing sql code. They are good reads.

Sept 07 - http://www.simple-talk.com/dotnet/.net-tools/testing-times-ahead-extending-nunit/
March 08 http://www.simple-talk.com/dotnet/.net-tools/testing-times-ahead-extending-nunit/
Jan 2010 http://www.simple-talk.com/sql/database-administration/close-those-loopholes-lessons-learned-from-unit-testing-t-sql/

Answer 2

Use an established free open source framework like tSQLt? See tSQLt.org for more details. Tests are implemented as stored procedures and the framework is included as part of the database.

Answer 3

Visual Studio 2010 has some built in tools for unit testing SQL server. This article is a good read.

I know that Redgate also has some good tools for testing SQL server, especially around comparing tables or results of queries from two different versions.