Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

SQL Unit Testing Expand / Collapse
Author
Message
Posted Tuesday, April 07, 2009 10:26 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:01 PM
Points: 1,109, Visits: 540
Comments posted to this topic are about the item SQL Unit Testing

Alejandro Pelc
Post #692739
Posted Tuesday, April 07, 2009 10:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 11, 2012 3:05 PM
Points: 3, Visits: 18
is it possible to test ssrs reports?

Regards,
KarthikShanth.
Post #692755
Posted Wednesday, April 08, 2009 3:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:22 AM
Points: 1,052, Visits: 862
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?



Post #692872
Posted Wednesday, April 08, 2009 5:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, March 27, 2014 5:55 AM
Points: 449, Visits: 1,830
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
Post #692964
Posted Wednesday, April 08, 2009 5:53 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:01 PM
Points: 1,109, Visits: 540
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
Post #692965
Posted Wednesday, April 08, 2009 5:56 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:01 PM
Points: 1,109, Visits: 540
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
Post #692969
Posted Wednesday, April 08, 2009 7:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 6:00 PM
Points: 141, Visits: 291
This sounds like it could have many uses. Thanks for the contribution.

The more you are prepared, the less you need it.
Post #693091
Posted Wednesday, April 08, 2009 9:25 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:16 PM
Points: 80, Visits: 351
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
Post #693254
Posted Wednesday, April 08, 2009 9:50 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 31, 2014 3:01 PM
Points: 1,109, Visits: 540
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
Post #693288
Posted Thursday, April 09, 2009 3:32 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:22 AM
Points: 1,052, Visits: 862
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.



Post #693825
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse