July 1, 2009 at 12:09 am
Comments posted to this topic are about the item What is the Best Way to Test SQL?
July 1, 2009 at 6:47 am
Good editorial Tony, but one line you have in there jumped out at me...
"...few DBAs and Database Developers have the required .NET skills..."
I would really love to know just how true this statement is - just out of curiosity - because in our work you must have both and though we have not posted for openings in a year or more, when we did, .NET skills were one of the top requirements for any DBA who is going to work in any of our companies. In fact (and it may just be our organization), any DBA who did not have some .NET familiarity, to me, is not a DBA. I mean, thats is kind of like hiring a carpenter who knows it all, except how to use a hammer and nails.
What good is it to tune SQL and then find someone setup a data connection in .NET incorrectly? I mean, isn't SQL the backend for ASP.NET and Winforms .NET projects - as it is in our organization? So, tuning SQL and not knowing the proper methods and best practices in .NET sounds about as useful as tuning up a car that has no wheels. Engine runs great, but cant go anywhere.
I don't know whether I am just old and therefore old-fashioned, but what good is any database knowledge when you don't have a clue about the "presentation layer" and how it operates - whether its Web ASP work, or good old Winforms work.
Our DBAs work with our developers and we test in our QA departments - but all those people have knowledge of both SQL and .NET. I don't see how you can separate those two things. Sounds kind of like my cooking ability - I am great at cutting up vegetables, boiling water, opening packages - but I cant produce even a good toaster waffle! :w00t:
July 1, 2009 at 6:59 am
On the main subject, I've had trouble in some places getting devs to take the time to test anything beyond what they themselves write, and even then getting them to test it by doing anything more than firing up a copy of the modified/new web page and trying a few inputs has been a major effort.
I have my own test systems for database code changes. They're pretty thorough.
On the subject of knowing .NET and T-SQL, I have to say that I know enough VB.NET to be able to read it, and enough C# to recognize it, but not much beyond that. Used to be fairly sharp with VBA, but that was years ago. On the analogy of tuning a car, I'm the guy who can rebuild your engine from scratch, but who won't touch the body work. You can chose to go to a guy who can do both, but I can pretty reliably assert that he won't do as good a job on the engine as I will, nor will he do as good a job on the body as a dedicated specialist would on that.
Outside the analogy, the best work I've done as a T-SQL Architect and Dev, was where I specialized in the database, and two other people specialized in the .NET parts, and we produced a heck of a great system.
That's not to say that some few people can't be really good at both, but given the same resources (intelligence, training time, experience, etc.), the more specialized a person is, the better results he'll usually get in that specialty. That's one of the major foundations of human civilization.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2009 at 7:36 am
GSquared (7/1/2009)
On the subject of knowing .NET and T-SQL, I have to say that I know enough VB.NET to be able to read it, and enough C# to recognize it, but not much beyond that.
Excellent comments Gus, and I agree. On the quoted part, I would fall into this same category. Help (F1, Intellisense, google) is my best fiend when dealing with .NET.
That being said, if you're going to be working in SSRS (or CLR programming), you really do need to learn at least the basics of .NET (if I remember correctly, in SQL 2005, it is VB.NET; while in SQL 2008 it can be either VB/C# .NET).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 1, 2009 at 7:58 am
What has attracted me to testing frameworks is the prospect of "automated and repeatable" tests. I want to be able to make a change to a subsystem, run the test suite and see pass and fail on all the essential features of the subsystem. If everything passes, then I can proceed with more confidence. Also, others can do the same. So, I've been using TSQLUnit for projects that go beyond basic and can benefit from "automated and repeatable."
While setting up the tests, I have to keep the longer-range goals of "automated and repeatable" in mind--because the initial tests for the existence of objects adds development overhead in the short term to meet those longer-term goals.
In the TDD (test-driven development) theory I've been exposed to (from Alistair Cockburn--Crystal Clear) tests are to be designed to fail until the minimum feature is put in place at which point development ceases. This keeps development very focused and organized as one feature or tightly-coupled feature set is developed at a time. This benefit alone helps justify the added effort for me.
So in practice my first test for say developing a stored procedure is to create a test for the existence of the stored procedure. All that's been developed at this point is a decision about the name of the stored procedure. Not very exciting. However, if someone down the line changed the name, the test would fail and bring the change to attention.
Questions at this point arise for me. It's one thing to check for the existence of a table. But what about a check for the existence of each column along with the data types, constraints, collation and so forth? I haven't to this point set up a lot of tests for DDL statements, but rather rely on a different tool for comparing versions of objects: Red Gate SQL Compare. I might try more DDL tests now that I'm familiar with INFORMATION.SCHEMA.
I find the TSQLUnit set up and tear down very useful. I can create a set of test rows here, either hand-picked or built from another tool such as Red Gate SQL Data Generator for testing extremes in values data types and handling of NULLs.
Also, I build tests for the existence and proper implementation of check constraints by running insert, update and delete operations on test rows and then check to make sure only those operations that should result in an insert, update and delete actually do.
Tests for performance is an interesting area. Tests are best done if discreet--so performance tests should be separate. These probably belong in their own "suite." Again, the set up and tear down features of a framework come in handy, along with the ability to build large sets of rows. The repeatable nature of tests can really help with performance testing in conjunction with SQL Profiler and other built-in SQL Server tools.
I haven't found a good way to automate tests for concurrency for T-SQL within a T-SQL framework--and perhaps that's just as well. Perhaps it's better done externally with a simulation of front-end application use.
At the end of the day, tests can't replace decision logs and documentation--so those belong there as well.
With the use of a framework, though, the days of relying on someone's afterthought ad-hoc manual tests are gone. Later if a bug is found or an enhancement is needed, more tests are added to the suite and away we go.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
July 1, 2009 at 8:14 am
I'd tend to agree with Gus. I think lots of DBAs don't know .NET very well.
Blandry, I think you're in a place where you are looking for more well rounded people. There are tons of DBAs that just administer servers, they don't do, or even deal with, any development.
July 1, 2009 at 10:19 am
Personally, I am a huge fan of the DbFit tool (hence: http://www.sqlservercentral.com/articles/Testing/64636/). I like it because it not only provides repeatable, automated testing (both from GUI and from command-line), but it is a very flexible framework that allows for most situations. Granted, to be clear we are talking about functional testing, not performance testing; Unit testing does not deal with performance. But compared to the *Unit (or would that be %Unit ;-)) frameworks, DbFit does not require any modifications to your DB in order to test. You do not need to create any test procs nor any setup / tear-down procs. All of that is contained within the test itself which by default is a transaction that gets rolled-back at the end (although you are allowed to break out of the automatic transaction for more control). We use DbFit quite a bit where I work as well as regular FitNesse for our Continuous Integration and it works quite well. And again, our database (both schema and procs) has no knowledge of the DbFit tests even existing; they are completely isolated. Just yesterday I created a DbFit test page that connected to one database and ran a query while storing the result set in a variable, and then on the same page it connected to another instance and ran another query against our data warehouse and stored that result set and then at the end of the test I compared both result sets to make sure that the source data was being moved to the warehouse server and aggregated correctly. This was quite easy to do and exists entirely in a single DbFit wiki page. I am even able to create temp table and temp procs in the tests for the purpose of the test that simply go away at the end of the test.
And the framework is so easy to use that in most cases our QA folks create the DbFit tests themselves since it requires just basic SQL knowledge. They have set up some elaborate tests that pass variables from a top-level page down to sub-pages, etc.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
July 1, 2009 at 1:08 pm
"...few DBAs and Database Developers have the required .NET skills..."
I heartily agree witih this statement. And from my experience I would add:
"...few application developers have the required database and SQL skills. I've been on both side of the argument for many years, so can speak fairy well to this.
I spend a large portion of my time researching and finding fixes for logic and performance issues with SQL written by developers. On the other hand, I wouldn't expect them to tolerate my dwindling or extinct front-end efforts.
Just this week I heard the common developer tech-babble: "Well, it only fails at the end of each quarter". In the meantime, the end-user was without data for ten seperate days in the month of June.
July 1, 2009 at 1:16 pm
Yes, lots of DBA's don't know .Net very well. I know probably the least. But I'm a *&^% good DBA and I do far more than rerunning failing jobs and doing backups. I'll offer my SQL skills against anyone in our organization. We build servers, manage patching, debug failed jobs, fix bad data from poorly written code, and fix bad SQL code that developers can't. It ain't an ivory tower.
July 1, 2009 at 2:01 pm
blandry (7/1/2009)
"...few DBAs and Database Developers have the required .NET skills..."I would really love to know just how true this statement is - just out of curiosity - because in our work you must have both and though we have not posted for openings in a year or more, when we did, .NET skills were one of the top requirements for any DBA who is going to work in any of our companies. In fact (and it may just be our organization), any DBA who did not have some .NET familiarity, to me, is not a DBA. I mean, thats is kind of like hiring a carpenter who knows it all, except how to use a hammer and nails.
"Jack of all trades, master of none." Seriously, there's so much in SQL and so much in .net I don't see how someone can know both really, really well. Specialise in one and have a basic understanding of the other or know both relatively well.
Quite a few DBAs come from the sysadmin side and therefore know SQL and windows admin but have never opened a dev tool.
I came from the dev side, but I haven't written front end code seriously in about 5 years. I can hack up a quick app if I need to, but it takes ages and real .net devs laugh at my efforts. These days I spend all my time in the DB engine and there's still large parts of that I don't have time to get familiar with
GSquared (7/1/2009)
On the analogy of tuning a car, I'm the guy who can rebuild your engine from scratch, but who won't touch the body work. You can chose to go to a guy who can do both, but I can pretty reliably assert that he won't do as good a job on the engine as I will, nor will he do as good a job on the body as a dedicated specialist would on that.
Agreed. Often I find small shops want the one person who can do both, because they either don't have enough work to 2 people busy, or they can't afford or justify 2 staff where one can do both roles.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2009 at 4:50 pm
Over the past five or so years, encouraging progress has been made with regard to testing .NET application code. Unit testing tools such as NUnit and xUnit have moved from the XP fringes into the mainstream, and most IT departments are at least starting to become aware of the importance of thorough unit testing, as well as regression testing, load/performance testing, integration testing, and so on. I wish I could say that the same progress had been made with testing SQL.
I think most big companies especially software engineering companies run formal testing process where a developer do just unit testing all other tests are run by QA team.
Why is this? Some Database Developers with C# skills have adapted established NUnit techniques to test their database code. However, few DBAs or Database Developers have the required .NET skills, making this a hard path to follow. In addition, this approach represents another instance of the "object-relational mismatch", with an object-oriented tool being used to test a relational model.
I do a subset of both SQL Server and .NET as in Asp.net using C#, I don't know anything about Winforms, SMO, Replication and other areas of SQL Server which I think are less related to application development. What I don't know in both is by choice so I can be good at what I choose to do that is one of the reasons I think Microsoft needs to separate SSIS to ETL and admin because both are not related.
I also think SSIS package running transforms for data from Oracle, Sybase, DB2 and MySQL can be called in OSLO which can be consumed by all applications in the Microsoft platform. Implementation detail will not be complicated because there are the five to six transaction implementation by Microsoft.
Kind regards,
Gift Peddie
July 2, 2009 at 6:47 am
Solomon Rutzky (7/1/2009)
Granted, to be clear we are talking about functional testing, not performance testing; Unit testing does not deal with performance.
Solomon suggests that unit testing doesn't deal with performance features. Is this a universally-understood rule? Thank you.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
July 2, 2009 at 12:48 pm
Bill Nicolich (7/2/2009)
Solomon suggests that unit testing doesn't deal with performance features. Is this a universally-understood rule? Thank you.
Hey there, Bill. I guess you ultimately want someone else to answer that question since I will only confirm what I said ;-), but I can add to it first.
It seems that there is a general misunderstanding in the DB world of what Unit Testing is. Unit Testing is supposed to test a very specific piece of functionality. Given a set of inputs and outputs it can be determined whether or not a particular set of logic is working as expected. This is irrespective of how quickly and/or efficiently it works. It is just functional testing on a particular method / function / procedure.
A further complication is that true Unit Testing tests ONLY that piece of logic regardless of any external dependancies that piece of logic might have. For instance, when doing Unit Testing of a C# method, if that method makes a DB call or a call to another method, those external calls are "mocked" with given return values so that the piece of logic being Unit Tested is truly isolated. There is no way (that I know of) to "mock" external calls within a Stored Procedure. So if you test a Stored Proc that calls another Stored Proc or a User-Defined Function, then you are not doing true Unit Testing since you will really be testing the Proc you intended to test plus anything it calls. And even if you could "mock" external Proc or Function calls, what would you do in the case of a Trigger. And the issue of whether or not data constitutes an external dependancy is another story, but maybe not as important because it can be, to a degree, isolated within the scope of a transaction.
So in the DB realm we do functional testing (that sometimes people call Unit Testing) the best we can. And again this is why I like DbFit: it facilities easy, and repeatable / automated testing without requiring any updates to the DB for testing. You do not need to add any testing procs to the DB in order to test. This also makes the tests very portable since you can simply change connection strings and not have to worry about "deploying" test procs and keeping those up to date in each environment & server.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
July 2, 2009 at 2:29 pm
To everyone following this train of thought, I think maybe the reality is that there are any number of ways of testing SQL, and I suspect that each of us has our favorite means and/or the means we are most competent to use. Some require additional software, some require additional knowledge and/or intuitive capabilities. I would submit that whatever the tool or level of abstraction, the intuitive ability of the tester is the most critical of all.
Although many if not most of you might not agree with me, my tool of choice is still SQL itself. I've tried other solutions myself, had worked with teams using various testing and debugging packages, and what I've seen is that the tools only go so far and are never as effective as plain old breaking the SQL into elements and making sure we understand exactly what each piece is doing.
One of my functions as an 'oldtimer' has been to mentor a number of folks new to the DBA world, and we don't depend on tools that the company may or may not decide to and continue to purchase/license or whatever. You need to understand SQL and be able to function whether tools are available of not. And that is how you find the most hidden of bugs or performance problems.
July 2, 2009 at 2:33 pm
The newest MCTS exam 70-433 Microsoft SQL Server 2008 Database Development test so far to my knowledge is completely silent on test-driven development techniques.
The team system of Visual Studio has had tools for creating test rows for a while and has versioning which can document database objects in an all-at-once sense. Many including myself don't have a team system license. I use the Red Gate tool to build rows.
I don't know that there are many resources found at MSDN or other Microsoft-sponsored learning/reference resources on TDD for database development either.
It looks like we're still in the wild wild west of sorts on this. The original question is a good one because there's not a lot of authoritative sources right now to opine on the matter.
Another question that I'd like to broach is the question of "to what extent should we test and when?"
It would be a stretch to say that everyone should write a test before they write any code for database development, period.
Tests assume solid requirements. In the early stages, requirements might be fluid and specific tests might not be available. If we're working in Oslo and using MSchema, creating entities and defining relationships and having our T-SQL code expressed for us, then we're writing code before developing tests for it and the schema is changing with relative frequency. So I think there's an obvious collision going on between test-driven development and the newer model-driven development techniques.
Alistair Cockburn, one of the founders of the Agile movement says this: "It is clear to most people by now that no one methodology will fit every software project." This can be applied to test-driven development as well. I think there's "too light" and "too heavy" an application of TDD for any given project.
If you're working on a nuclear reactor with a dozen other database developers simultaneously, then you might want to write all tests first before creating anything. Maybe you'd go to INFORMATION.SCHEMA and test for the existence of your table, all your columns with their data types, constraints and so forth before you actually create the table. But it might seem odd to write a test first for a table when you're the only developer working on a simple application where there are no bad effects to speak of from a bug.
I think Cockburn's Crystal "mineral analogy" can guide us. Minerals have hardness and color--or two axes. The color changes from clear to yellow, orange, red, magenta and blue as then number of developers and size of the application go up. Let's say that's the X axis. The Y axis is "hardness" which describes how critical the system is. A nuclear reactor is critical along with other life-critical applications--so those would be "hard." Applications get softer from there the less that's on the line due to a bug.
So perhaps less formal testing is sufficient for smaller, less critical projects. That said, I think all database developers would benefit from being able to write formal tests and use a framework where they can repeat tests for when a project calls for it.
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply