April 7, 2009 at 10:26 pm
Comments posted to this topic are about the item SQL Unit Testing
Alejandro Pelc
April 7, 2009 at 10:55 pm
is it possible to test ssrs reports?
Regards,
KarthikShanth.
April 8, 2009 at 3:23 am
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?
April 8, 2009 at 5:49 am
RichardB (4/8/2009)
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?
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.
Regards
GermanDBA
Regards,
WilliamD
April 8, 2009 at 5:53 am
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
Alejandro Pelc
April 8, 2009 at 5:56 am
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
Alejandro Pelc
April 8, 2009 at 7:44 am
This sounds like it could have many uses. Thanks for the contribution.
The more you are prepared, the less you need it.
April 8, 2009 at 9:25 am
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
Mike Byrd
April 8, 2009 at 9:50 am
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
Alejandro Pelc
April 9, 2009 at 3:32 am
GermanDBA (4/8/2009)
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.
Only issue being that it's a 1.5TB database... so around a day to restore. :w00t:
April 9, 2009 at 4:22 am
RichardB (4/9/2009)
GermanDBA (4/8/2009)
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.Only issue being that it's a 1.5TB database... so around a day to restore. :w00t:
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.5TB
A quick explanation with example can be found here : http://blogs.technet.com/mscom/archive/2007/08/08/using-sql-2005-snapshots-as-a-rollback-procedure.aspx, 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).
regards
GermanDBA
Regards,
WilliamD
April 14, 2009 at 4:32 am
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.
April 14, 2009 at 6:19 am
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
Alejandro Pelc
April 14, 2009 at 7:36 am
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. 😉
April 14, 2009 at 8:29 am
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
Alejandro Pelc
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply