Test Driven SQL Server Development with LegiTest

By:   |   Updated: 2016-07-14   |   Comments   |   Related: More > Testing


Testing SQL Server applications at many organizations is currently not automated, error prone and inefficient, meaning there is significant room for improvement.  Developers need to incorporate test driven development techniques to test the SQL Server database and data that supports data centric applications.  There are two primary testing scenarios:

  1. Test as a portion of the development process
  2. Validate production data to ensure the correct results are consistent

How can we improve our testing, data quality and management's confidence in our data?


Just as development cycles are iterative, the Development Team needs to test each iteration of their code to validate it is correct.  They also need checks and balances in place to validate production data as it flows between systems.  An example of this would be data moving from order entry systems to manufacturing, then accounting and ultimately to the data warehouse.  The Development Team needs a simple tool to build test cases for the SQL Server database and data that supports data centric applications then execute the test cases as code and data changes are made with immediate pass or fail results for each test.

To address these needs, Pragmatic Works has introduced LegiTest. LegiTest integrates with Visual Studio to build and execute test cases for SQL Server development needs on the relational engine and with the full Business Intelligence stack.  Once test cases are built with LegiTest they can be executed against each iteration of your code and data.  Further, LegiTest can be included with production processes such as Integration Services packages to validate data is properly flowing between systems.  LegiTest improves testing for SQL Server data centric applications with:

  • Standardization - Consistent testing methodology for any SQL Server data centric applications
  • Integration - Ability to incorporate automated testing directly in Visual Studio and your build process
  • Validation - Easily validate data, parameters, row counts and result set comparisons to ensure the quality of the data
  • Comprehensive - Validate ETL processes from source systems to staging and from staging to your data warehouse, or data integration between multiple systems
  • Monitoring - Production monitor for ETL processes and data movement to support daily operations
  • Comparison - Compare and verify the data flow between systems to include data sets with a tolerance range to validate the data
  • Universal - Support the full SQL Server stack (database engine, SSRS, SSIS and SSAS - Tabular and Multi-dimensional) and any OLE DB (i.e. Oracle, DB2, etc.), ODBC, NoSQL, RestAPI, SalesForce and cloud data sources


Why use LegiTest?

As Developers our time is precious, but we need to properly test the data driven applications we are responsible for delivering.  We cannot just hand code to the Testing Team and expect them to test every scenario. LegiTest will help with the following:

  • Automation - Fully automate testing which is a time consuming and monotonous processes
  • Industry Standards - Integration with MSTest and NUnit for industry standard testing following the Arrange, Act and Assert process
  • Continuous Integration - Ability to execute tests as with your continuous integration platform such as TFS, Team City, or MS Build
  • Ease of Use - Wizard based interface to begin testing with the ability to dissect and customize the tests as your needs evolve
  • Simplicity - Drag and drop LegiTest components from the Visual Studio tool box to the LegiTest designer
  • Scalability - Scale up the application to test larger data sets in test environments in preparation for production growth
  • Comparison - Ability to compare result sets (SQL Server, JSON, cloud, etc.) in a single interface without having to manually parse the data
  • Flexibility - Configure pre and post tasks to validate dependent conditions, clean-up objects, notify the team and more

Developers need to have confidence in code and data whereas management needs to have confidence in the products built by the Technology Team. From a business perspective, LegiTest enables the Technology Team to properly test in lower life cycle environments as well as validate and monitor production processes which are key to the organization's success.  This provides the business assurance that the data is validated and the data quality is accurate.


How do you use LegiTest?

LegiTest Integration with Visual Studio

When you start to work with LegiTest you create a new project in Visual Studio.  Once the interface loads, there are 4 key panes to work with including:

  1. Toolbox on the left where you can drag and drop components into the Test Steps section of the interface.
  2. Project Hierarchy with the Parameters, Server Integration, System Coverage and Test Suite.  In this pane the Target Framework of NUnit or MSTest can be selected.
  3. Test steps that are read from top to bottom and left to right in terms of precedence for LegiTest to perform the prescribed test.
  4. Element Editor that is context sensitive based on what is selected in the Test steps in pane 3. These values can be connection parameters, queries, keys, etc.


LegiTest Integration in Visual Studio

Figure 1 - LegiTest Integration in Visual Studio


Jumpstart building tests with the LegiTest Wizards

Pragmatic Works makes it easy to get started with LegiTest. Below is an example wizard to execute a LegiTest SQL Server Data Comparison between a source system and data warehouse.  As an example, let's say we need to validate donation values between our data entry system and data warehouse, which are two separate SQL Server instances.  To do so, we do following:

  • Step 1 - Left Data Set or Left Query
    • Data Entry System
    • Connection to SQL Server database
    • Query on the Donation table


  • Step 2 - Right Data Set or Right Query
    • Data warehouse system
    • Connection to the SQL Server database
    • Query on the Fact_Donation table


  • Step 3 - Mappings and Keys
    • EventID is the column that maps the data between the data entry and data warehouse


Below is the three step process in LegiTest:


LegiTest SQL Server Data Comparison Wizard

Figure 2 - LegiTest SQL Server Data Comparison Wizard


Walking through the three step wizard above generates the following test case:

LegiTest test built from the Wizard

Figure 3 - LegiTest test built from the Wizard


SQL Server Integration Services Testing with LegiTest

Similar to the testing for SQL Server Data Comparison, there is also a LegiTest Wizard to test SQL Server Integration Services (SSIS) Packages.  This Wizard includes specifying the source of the SSIS Package (File, SQL or Catalog), the Package and options (i.e. testing, performance, validation, etc.).  Let's break down each step in the wizard:

  • Step 1 - Specify Source
    • Determine the source of the SSIS Package
      • File System
      • SQL Server
      • Catalog


  • Step 2 - Select Packages
    • Select 1 or more SSIS Packages to test
    • Ability to browse the SSIS packages based on the source selected in Step 1


  • Step 3 - Enter Passwords
    • Enter any applicable user names and passwords needed for the SSIS Packages


  • Step 4 - Specify Options
    • Execution time is less than 10 seconds
    • Validate data is loaded by the data flows
    • Validate the Execute SQL Tasks
    • Execute Tasks as individual tests


LegiTest Wizard to test SSIS Packages

Figure 4 - LegiTest Wizard to test SSIS Packages

In our example, running the LegiTest Wizard will result in the following test case including:

  • Timing validation
  • Multiple assertions
  • Data Flow Task validation
  • Execute SQL Task validation
  • Data Comparison Grids
  • Numerous connections based on the SSIS Package needs
  • Assign the key columns


LegiTest SSIS test built from the Wizard

Figure 5 - LegiTest SSIS test built from the Wizard

Although the Wizard has created the test as prescribed, you still have full control to add additional logic by dragging and dropping components from the LegiTest Toolbox into the test case.  Same is true for updating parameters, you have the ability to select any task, assertion, asset, etc. and update it to meet your needs as they evolve.


LegiTest Test Explorer in Visual Studio

Once your tests have been defined with LegiTest, the tests can be executed directly in Visual Studio by accessing the Test Explorer which is generally on the top left of the Visual Studio interface.  On the Test Explorer interface you can run all tests or selected tests and see the results in the interface including the execution time.  Intuitively, the statuses are: blue for not run, green for success and red for failure.  On a failure, the results are displayed at the bottom of the interface to begin troubleshooting immediately.


Figure 6 - LegiTest Testing Status in Visual Studio 


LegiTest Server for Detailed Testing Statistics

Beyond LegiTest's integration with Visual Studio, LegiTest also has a server component (i.e. LegiTest Server) that drills into all of the details related to each testing component.  For example, starting at project level you can see the test hierarchy and drill into particular components to find out the detailed status.  At a detailed level, there are historical executions all the way down to the individual rows that generated a failure for the test.  This level of detail is a significant time savings when it comes to troubleshooting test cases and data issues.  This is also invaluable as a historical record to correlate when test cases began to yield unexpected results.


LegiTest Test Case Hierarchy Denoting Success and Failure Conditions

Figure 7 - LegiTest Test Case Hierarchy Denoting Success and Failure Conditions


LegiTest detailed test case information

Figure 8 - LegiTest Detailed Test Case Information

LegiTest System Coverage Reports 

As a checks and balances of your tests, LegiTest provides an interface in both Visual Studio (shown below) and with the LegiTest Server (not shown) to validate all objects requiring testing are included as well as determining the success rate.  In the Visual Studio example below, three objects are searched for the existing test.  The first two are included in the tests and the third is excluded.  This is a simple means to validate all needed objects are included in the tests and the associated success rate.


LegiTest Sytem Coverage Report

Figure 9 - LegiTest System Coverage Report


How do I start testing my data driven applications?

  1. Go check out all of the testing resources available for SQL Server from Pragmatic Works.
  2. Check out the online demo to see LegiTest in action for yourself.
  3. If the online demo is interesting for you download a fully functional version of LegiTest for 14 days to see how it can help in your environment.
  4. Contact the Pragmatic Works Team for assistance - their full knowledge of LegiTest will save you a lot of time during your evaluation and make sure you cover everything there is to see!
  5. Think about all of the SQL Server testing challenges you face and how LegiTest can save you time.
    1. Simple means to build test cases in Visual Studio, execute test cases with the push of a button and get immediate results
    2. Ability to test as a portion of the development process and include test cases to validate production processes
    3. Peace of mind that your application is tested and the data is accurate, based on your application logic
    4. Single means to test the full SQL Server stack (database engine, SSRS, SSIS and SSAS - Tabular and Multi-dimensional) and any OLE DB (i.e. Oracle, DB2, etc.), ODBC, NoSQL, RestAPI, SalesForce and cloud data source
    5. Historical record of testing and the detailed level results
  6. After your 14 day trial, gather all of your findings then communicate with your team and management about how you think LegiTest will save your team time on a daily basis.
  7. Determine the next steps for your team.
  8. Think about all that you can accomplish with your new found free time and having the business confident in your application and data.


Next Steps


MSSQLTips.com Product Editorial sponsored by Pragmatic Works, makers of LegiTest.

Last Updated: 2016-07-14

get scripts

next tip button

About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips

Comments For This Article


Recommended Reading

Clearing Cache for SQL Server Performance Testing

Attach Sample Database - Adventureworks in SQL Server 2012

SQL Query Stress Tool

Populate Large Tables with Random Data for SQL Server Performance Testing

AdventureWorks Database Installation Steps

get free sql tips
agree to terms