SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Alejandro Pelc / SQL Unit Testing / Latest Posts

RE: SQL Unit Testing

dbunittest — Mon, 13 Jul 2009 20:11:16 GMT

Hi, I would like to share another database unit test tool-AnyDbTestAnyDbTest is one outstanding DB unit testing and data validating tool.(Official website, http://www.anydbtest.com).Writing unit and component tests for objects with external dependencies, such as databases or other objects, can prove arduous, as those dependencies may hinder isolation. Ultimately, effective white-box tests isolate an object by controlling outside dependencies, so as to manipulate its state or associated behavior. If you are an app developer, you can utilize mock objects or stubs to for controlling outside database dependency. But if you are a DB developer, these mock and stub frameworks cannot give you any helps.AnyDbTest provides an elegant solution for controlling a database dependency within applications by allowing developers to manage the state of a database throughout a test. With AnyDbTest, a database can be seeded with a desired data set before a test; moreover, at the completion of the test, the database can be placed back into its pre-test state. Automated tests are a critical facet of most successful software projects. AnyDbTest allows developers to create test cases that control the state of a database during their life cycles; consequently, those test cases are easily automatic, as they do not require manual intervention between tests; nor do they entail manual interpretation of results. The best way to see how AnyDbTest works is to read Quick Start.

RE: SQL Unit Testing

RichB — Fri, 01 May 2009 04:41:38 GMT

Haha fair enough, sneaky ninja action win.:hehe:

RE: SQL Unit Testing

WilliamD- — Wed, 29 Apr 2009 07:16:10 GMT

Hi RichardB,I was only making it clear to anyone reading the rest of the thread that SQLBackup or Quest are not the bad guys when it comes to a slow restore. Of course 1.5 TB will take a while to restore, but the two tools will restore this volume quicker than a native restore.It is also quite important to remember that Database Snapshots are dependant on Enterprise Edition or Developer Edition, as I mentioned in a previous post, whereas 3rd Party tools can help for the other editions.P.S. I am the original poster that suggested the use of database snapshots. I just changed my username on the forums from GermanDBA to WilliamD a few days after posting to this topic. My quoted responses remain with the title GermanDBA though.

RE: SQL Unit Testing

RichB — Wed, 29 Apr 2009 07:04:04 GMT

[quote][b]WilliamD (4/27/2009)[/b][hr]Hi RichardB,that is not Quest's fault, it will most likely be down to slow proc. or I/O problems (you did say they are dev boxes).I don't think there is much difference between SQL 2000 and 2005 with respect to using Quest/SQLBackup etc. They just use the VDI and pretend to be a tape system and consume data much faster than a tape could.Would be interesting to see if there is much difference if you move your dev boxes to 2005.[/quote]Whooomph.Way to miss the point.A 1.5TB database takes a long time to restore. Especially on a low spec box.The point about 2005/8 as GerbilDBA mentioned is that it gives us the snapshot option.

RE: SQL Unit Testing

WilliamD- — Mon, 27 Apr 2009 02:33:53 GMT

Hi RichardB,that is not Quest's fault, it will most likely be down to slow proc. or I/O problems (you did say they are dev boxes).I don't think there is much difference between SQL 2000 and 2005 with respect to using Quest/SQLBackup etc. They just use the VDI and pretend to be a tape system and consume data much faster than a tape could.Would be interesting to see if there is much difference if you move your dev boxes to 2005.

RE: SQL Unit Testing

RichB — Tue, 21 Apr 2009 08:58:44 GMT

[quote][b]WilliamD (4/9/2009)[/b][hr]1. Either use the Database Snapshot and restore from that: really fast, because only the changes ...2. Use SQLBackup from Redgate, we use it and it is screaming fast compared to native backup and [/quote]Aye - once we get off 2000... we use Quest at the mo - still an 11 hour restore on our slow dev boxes!

RE: SQL Unit Testing

Ale Pelc — Tue, 14 Apr 2009 17:02:44 GMT

Hi Jonathan,I see your point, I misunderstood the "integration test" line. Actually, it's a good observation, and I partially agree with you. If you just run the test as if, then it's more like a smoke test than a unit test. But you must consider that SQL is different that C# or .NET (I'm far away of being an expert in those areas) on the flexibility that unit test provides.If you have a stored procedure that fails, and you execute it in Management Studio or other SQL client, you'll get the error and the line that fails. To have the same functionality on the unit tests, you must catch the error on the C# code because if not you'll only get the line that errored in the unit test, not the one in the SP. Using the try catch will give the test the exact line in the SP that failed, even if your SP built a dynamic query.I'm sure that there are some other tools down there that do the same or even more than this, and I'll see if I can get my hands in some of them and make a comparison. By the way, thanks for the tip. I'll try to read some Ambler.Thanks,Alejandro

RE: SQL Unit Testing

Jonathan Starr-211380 — Tue, 14 Apr 2009 14:40:37 GMT

Unit tests are specific in nature. A unit test determines whether a unit of code meets a certain specification. When unit tests fail they should point to a single line of code that is improperly written. Typically each unit of code will have mutiple unit tests to do proper testing.What you are doing is running a load generated by Profiler against a database. You are running integration tests (or smoke tests) in this case as this test is not specific in nature, does not point to a single line of code when it fails, etc.I am primarily a C# / ASP.NET / Ruby developer. If you want to do true unit tests for SQL I suggest you read some Ambler. He's good in this area.

RE: SQL Unit Testing

Ale Pelc — Tue, 14 Apr 2009 14:33:43 GMT

Hi Jonathan,I'm confused about what you mean with integration tests. Why you think this is the case ? It's true that I used it to test different systems, as mentioned in the article, but the hole idea is to test SQL SPs. I'm not making any unit test on reports, etc.Perhaps I'm missing something, but I think the tool is about unit testing and depending the way you use it, you'll have good unit tests or just simple tests.Thanks,Alejandro

RE: SQL Unit Testing

Jonathan Starr-211380 — Tue, 14 Apr 2009 13:39:02 GMT

This is interesting - but it's not generating unit tests - it's generating integration tests which are not diagnostic, and do not run quickly, or cover all functionality.

RE: SQL Unit Testing

Ale Pelc — Tue, 14 Apr 2009 08:29:19 GMT

Hey Brian,totally agree about the complexity. I guess the first thing to do before using this is knowing how deep you want / can go. For brand new projects is easier, but if you want to implement it on an existing, complex one, then it'll be hard.I think one approach for existing projects is configuring the basic test, just testing that SPs won't crash, and then start digging into the data. But as you said, this can be a new article itself...cheers,Alejandro

RE: SQL Unit Testing

Brian Jones-401554 — Tue, 14 Apr 2009 07:36:11 GMT

Alejandro,I agree. The biggest problem with deep unit testing SPs is testing the results - it's not good enough to just check the output from the SP, you have to check that the SP has altered the data as you expect it. If your SPs are simple, then this is fairly easy, and obviously if all they are doing is returning some data then that is also easy. However, if there is some complexity involved, say for instance some involved transaction recording, then you may end up writing "unit testing" SPs to verify the original ones. I guess that's another article altogether. ;-)

RE: SQL Unit Testing

Ale Pelc — Tue, 14 Apr 2009 06:19:09 GMT

Hi Brian,This is a good observation, and I agree with you about the system testing if you just run it out-of-the-box.This tool saves tons of time writing the queries from a trace file, but for deep unit testing you must use Asserts and Try/Catch in the C# code. By doing this, you can test the SPs passing valid/invalid parameters and validate your error handling and output. Then you'll be using unit tests and system tests all together.Of course, you can use it just the way the tool writes the C# file and you'll have a bunch of test to your database just to validate that nobody broke the code. Also you can combine this with other unit testing tools (web unit testing, C# tests, etc) and create a robust unit testing environment.Cheers,Alejandro

RE: SQL Unit Testing

Brian Jones-401554 — Tue, 14 Apr 2009 04:32:35 GMT

I'm not sure if this is really unit testing - it's more system testing. My understanding of unit testing is that you are looking at a specific routine (or maybe stored procedure) and ensuring that the results of that routine are as expected. Unless you spend time creating check SQL to ensure that the call to the SP has done what you expect, then all you are doing is syntax checking in case of database schema changes, and maybe performance testing.However, it is very useful to do a full system test, so I can see big advantages in this in that you need only run through a system test once (the first time and whenever there are logic flow changes) and then re-run it in the future as part of a pre-deployment test, saving time and effort.

RE: SQL Unit Testing

WilliamD- — Thu, 09 Apr 2009 04:22:06 GMT

[quote][b]RichardB (4/9/2009)[/b][hr][quote][b]GermanDBA (4/8/2009)[/b][hr]you could either backup the database, run the test and then restore the backup or take a snapshot of the database and restore that after the unit test.[/quote]Only issue being that it's a 1.5TB database... so around a day to restore. :w00t:[/quote]Ahaa.... two solutions for that. 1. Either use the Database Snapshot and restore from that: really fast, because only the changes are stored in a sparse file so these would be 'rolled back' instead of restoring the entire 1.5TBA quick explanation with example can be found here : [url]http://blogs.technet.com/mscom/archive/2007/08/08/using-sql-2005-snapshots-as-a-rollback-procedure.aspx[/url], otherwise BOL / Google.2. Use SQLBackup from Redgate, we use it and it is screaming fast compared to native backup and restore, with the added benefit of compressing the backups to save disk space.@1 - Database snapshots are for Enterprise Edition only. However, we are talking about unit tests, so you can do your unit tests on SQL Server Developer Edition (same feature set as Enterprise, but for development use only and available for $50 or so IIRC).regardsGermanDBA

RE: SQL Unit Testing

RichB — Thu, 09 Apr 2009 03:32:41 GMT

[quote][b]GermanDBA (4/8/2009)[/b][hr]you could either backup the database, run the test and then restore the backup or take a snapshot of the database and restore that after the unit test.[/quote]Only issue being that it's a 1.5TB database... so around a day to restore. :w00t:

RE: SQL Unit Testing

Ale Pelc — Wed, 08 Apr 2009 09:50:11 GMT

Hi Mike,The test will validate if it runs without errors. Because you're executing stored procedures or t-sql commands, if there's an error, the unit test will fail, so the overall test will fail (it'll show you, for instance, 10 test ok and 1 failed). Also, when you configure the test in a test solution in visual studio, you can configure the maximum execution time, and if it takes more than that time, the test will fail.About the data, the whole idea of unit testing is testing all the different aspects of your database, including sending bad data just to validate your error handling. Of course, you must customize the output to do that, inserting some c# code...Finally, I don't know if I would run unit tests on a 150 GB database just to validate the changes. I'll do that to test performance, but not for reviewing that changes didn't break existing procedures. Of course, every scenario is different...Thanks !Alejandro

RE: SQL Unit Testing

Mike Byrd — Wed, 08 Apr 2009 09:25:33 GMT

Interesting article and very timely for me. My big question is how do you detect errors/slow running queries etc? Do you need to run profiler again to detect database problems? Is there any means to detect when the newly revised code doesn't error, but is inserting/updating incorrect data? When you have a 150gb OLTP database, it is difficult to determine problems down at the row/column level. Any ideas on this?Cheers,Mike Byrd

RE: SQL Unit Testing

Andrew Peterson-472853 — Wed, 08 Apr 2009 07:44:00 GMT

This sounds like it could have many uses. Thanks for the contribution.

RE: SQL Unit Testing

Ale Pelc — Wed, 08 Apr 2009 05:56:59 GMT

Hi karthikshanth,You can actually test the calls to the database, so in a certain way you are testing the SSRS backend.If you want to test the reports, there's a free tool called WatIN (http://watin.sourceforge.net) you can use to do that. It uses the IE and records all the activity in the page. It has its limitations but you can make it work.Thanks,Alejandro

RE: SQL Unit Testing

Ale Pelc — Wed, 08 Apr 2009 05:53:36 GMT

Hi there,GermanDBA is right, you should create some sort of backup/restore strategy or include the DB creation / data population prior to run the tests and then just drop the database

RE: SQL Unit Testing

WilliamD- — Wed, 08 Apr 2009 05:49:48 GMT

[quote][b]RichardB (4/8/2009)[/b][hr]sounds interesting.The main issues I guess would be around identities/autoincrements and anything with password in the call! Do you have some way of resetting the database after?[/quote]Hi RichardB,you could either backup the database, run the test and then restore the backup or take a snapshot of the database and restore that after the unit test.This would guarantee a database being in the same state for each unit test run.RegardsGermanDBA

RE: SQL Unit Testing

RichB — Wed, 08 Apr 2009 03:23:45 GMT

sounds interesting.The main issues I guess would be around identities/autoincrements and anything with password in the call! Do you have some way of resetting the database after?

RE: SQL Unit Testing

karthikshanth — Tue, 07 Apr 2009 22:55:52 GMT

is it possible to test ssrs reports?

SQL Unit Testing

Ale Pelc — Tue, 07 Apr 2009 22:26:34 GMT

Comments posted to this topic are about the item [B]SQL Unit Testing[/B]