Standardized QA approaches for SQL Server

Question

Post reply

Standardized QA approaches for SQL Server

imani_technology

SSCrazy Eights

Points: 8200
More actions
March 9, 2020 at 2:23 am

#3732368

Are there any frameworks out there to standardize, organize, or streamline the process of SQL QA testing?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

what kind of QA testing? unit testing? performance testing? basic smoke tests? scalability testing?

all of these use different tools

MVDBA

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 2

imani_technology

SSCrazy Eights

Points: 8200

March 10, 2020 at 1:29 pm

#3732796

Essentially unit testing and performance testing.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

for unit testing of database code then Redgate has SQL Test, It's fairly cool and has a code coverage calculator (so that you can tell your boss that you tested 100% of the code)

as for performance testing, you can use sql stress to see how the system behaves under duress, but it won't be your code that is executed.

If you are familiar with Selenium and cucumber then you could script up a trace replay that executes for example 500 times at 1 second intervals. you should get lots of locking and you can monitor the behaviour of the server while it is running

if you want to performance test a single proc then just put some good old fashioned logging in create a table - procname,starttime,endtime - put a parameter on the proc (@loggingmode or some other name) and only use the logging when you need it.

But by far and away the best thing you could investigate is azure devops - you can have a build pipeline, test cases and load tests (caveat - load test have been deplecated and will not be available after 31 march - no idea what will replace it)

I guess that there are better ways, but I'll leave that to the other experts

MVDBA

Sue_H SSC Guru Points: 90860 More actions · Answer 4

imani_technology wrote:

Essentially unit testing and performance testing.

For unit testing, you may want to look at tsqlt - an open source unit testing framework: tsqlt

Sue

Steve Jones - SSC Editor SSC Guru Points: 736232 More actions · Answer 5

Articles here on tsqlt - https://www.sqlservercentral.com/categories/tsqlt

Microsoft has a unit testing framework, which can work with SQL Server: https://docs.microsoft.com/en-us/sql/ssdt/walkthrough-creating-and-running-a-sql-server-unit-test?view=sql-server-ver15

dbunit - http://dbunit.sourceforge.net/

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 6

Thank you all.

Thank you all for your responses. Now that I've reviewed, I think I need to add more information.

1. The SQL developers build their code. These are usually, but not always, stored procedures.

2. The developers then perform their own unit tests. They are supposed to make their tests and results available.

3. The QA team then looks at the T-SQL code the developers unit tested. QA should run the same tests that the developers ran and verify that the results are the same.

4. What else should the QA team look for? What frameworks and tools exist to streamline QA testing? Performance is part of it, but QA has no control over indexes, table structure, etc.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 7

imani_technology wrote:

Thank you all.
Thank you all for your responses. Now that I've reviewed, I think I need to add more information.
1. The SQL developers build their code. These are usually, but not always, stored procedures.
2. The developers then perform their own unit tests. They are supposed to make their tests and results available.
3. The QA team then looks at the T-SQL code the developers unit tested. QA should run the same tests that the developers ran and verify that the results are the same.
4. What else should the QA team look for? What frameworks and tools exist to streamline QA testing? Performance is part of it, but QA has no control over indexes, table structure, etc.

QA run the same tests as the dev team? that seems pointless. the point of a unit test is "does my code work or fail under given circumstances" such as does 1+1=2

are you referring to functional testing (maybe data driven)?

with regard to QA making indexes etc... nooooo. QA report the issue, the DBA and developer fix it based on the data the QA provides (hopefully it's a trace and a query plan.... just to make it easy for us)

MVDBA

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 8

In 20 years of using SQL Server, I've never dealt with a QA team just for T-SQL code. What exactly should that team be testing and how?

And I don't think the QA team is testing for data quality beyond whether the "after" code has the same data results as the "before" code. So again, what should QA test?

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 9

imani_technology wrote:

In 20 years of using SQL Server, I've never dealt with a QA team just for T-SQL code. What exactly should that team be testing and how?
And I don't think the QA team is testing for data quality beyond whether the "after" code has the same data results as the "before" code. So again, what should QA test?

Oh boy, this is a big topic....

Unit tests done at build time (either at a build server or local machine) - preferably in a devops build pipeline, all data rolled back so that it is re-usable

2nd layer - functional tests (also with rollback tran ) which is where you check processes (such as procs that might call other procs, security issues etc and row counts)

3rd layer - integration testing - does the system work with the other systems it needs to (linked server for example)

4th layer (very much done at the start of a project) - performance and scale testing

if you need resources then look for ISEB ISTQB foundation level testing. I swear that it is a real eye opener.

MVDBA