Automate SQL Server Testing with Profiler, SQLCMD and SSIS
By: Jeremy Kadlec | Updated: 2007-11-14 | Comments (5) | Related: More > Testing
One of the Edgewood Solutions team members recently spoke at a testing conference held in Washington, DC by the name of Verify. During the presentation, a few different questions were asked about automating database testing with the native SQL Server 2005 tool set. In this tip, we will outline some options to automate database testing and verification with Profiler, SQLCMD and SQL Server Integration Services.
Conducting thorough testing of a database driven application or automated (database) process can easily be a full time job by itself. Trying to automate the testing process typically results in an application of one type or another in order to streamline manual repetitive steps. Typically the testing process follows these steps:
- Identify the applicable code
- Build test cases
- Test an application build
- Verify results
- Report status to team
Based on this general process, let's see how some of the native SQL Server tools can help with some of these specific processes.
Identify the applicable code - Profiler
The best native tool for capturing the code issued by an application, user or host is Profiler. SQL Server Profiler has been available for the last few versions of SQL Server and provides a mechanism to capture the T-SQL commands issued against specific SQL Server instance, then save the results to a table or file for further analysis. In addition, Profiler has the means to filter the data being collected so that only specific users or host traffic is captured. Here are some quick steps to setup a Profiler session:
Launch SQL Server 2005 Profiler
|Navigate to Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler.
Start a New Trace
|Navigate to File | New Trace... | Authenticate to the SQL Server you want to monitor
General Tab - Configurations
|Specify the name and configure the results to be saved to a table.
Events Selection Tab - Configurations
|In order to review all events, enable the check box for 'Show all events' and 'Show all columns' on the bottom right of the screen.
Depending on how the testing is being conducted, it might make sense to filter the Profiler session by a combination of the application name, host name, login name, spid, etc in order to capture only a specific set of code.
Once you have filtered the settings then select the Profiler events that are needed based on the type of code that is being captured.
Once you have made these configurations or others as desired, then press the 'Run' button to begin the Profiler session.
Testing and Profiler Results
|Begin to test the application and the results in Profiler will be something like the following:
Be sure to review the code captured to ensure only the needed commands are included in your test cases. It might be a good idea to cross check the code captured versus the steps taken by the user or application. Then save out the code to separate files based on the logical progression in your application.
In addition, one item to keep in mind here is that the code being captured by Profiler is anything passed to SQL Server. So this provides the opportunity to separate testing the front end application from the back end code and database. Be sure to test the front end as well to ensure the front end behaves as expected and does not throw any errors when columns are added\removed, objects change names or security changes. With the information captured from the Profiler session these scenarios will probably not be caught.
Test an application build - Option 1 - SQLCMD
Once you have the needed code as an input, then the next step is to be able to specify an input file with the code and an output file with the results. This is where SQLCMD comes into play. SQLCMD is a Windows command line interface into a SQL Server 2005 instance where you can specify a server name, instance name, database, user name, password, input file, output file, etc. Let's take a look at an example set of code to call SQLCMD at a Windows command line:
SQL Server 2005 - SQLCMD Example
|sqlcmd -S JTKLaptop -d CustomerDB -E -i C:\TestScripts\sqlcmd_input.txt |
Here is a brief explanation of the code parameters:
- sqlcmd - Command
- -S JTKLaptop - SQL Server name and instance (default instance in this example)
- -d CustomerDB - Database name
- -E - Trusted user connection
- -i C:\TestScripts\sqlcmd_input.txt - Input file
- -o C:\TestScripts\sqlcmd_output.txt - Output file
- Listing of all of the SQLCMD command line options - sqlcmd Utility
SQLCMD is offers a great deal of functionality beyond the set of parameters listed in this example, but the greatest value may be the simplicity. Once you have your code captured (input), the learning curve is small to be able to run SQLCMD and determine if the process was successful or not.
Here are a few tricks with SQLCMD in the input file:
- Add PRINT statements in order to determine the process
- If possible, do not add SET NOCOUNT ON statements to your code because you will not be able to determine if rows were inserted, update or deleted
- If you have SET NOCOUNT ON statements then you may want to issue SELECT statements before and after the code changes to be able to validate the process
Test an application build - Option 2 - SQL Server Integration Services
To turn up the functionality, SQL Server Integration Services (SSIS) is the next native tool available to assist with the automated testing. SSIS is traditionally an extraction, transformation and loading tool (ETL), but does have the functionality to execute scripts and add conditional logic in a rich graphical interface. In a nutshell, it is necessary to do the following with SSIS in order to setup a package:
- Build connections
- Input data (text file)
- Output data (text file)
- SQL Server database
- Execute the needed scripts
- The Execute SQL Task might be the best approach to house the T-SQL test cases.
- Review the output
- The results could be saved to a table, text file or reviewed as a log for the process.
Here is an example using SSIS package to execute automated testing built in the Business Intelligence Development Studio (BIDS):
SQL Server 2005 Integration Services Example
SSIS is a very powerful tool and has a great deal of functionality. The explanation in this tip is just the beginning, so check out the SQL Server Integration Services tips category. Depending on the testing that is needed, SSIS might fit the bill and improve the overall functionality or might be more tool for the task. You be the judge and consider SQLCMD or SSIS to assist in the testing automation.
- Depending on the database testing you need to accomplish, using Profiler, SQLCMD and SSIS might be a viable alternatives to meet your needs.
- Although all of the tools are freely shipped with SQL Server, depending on your proficiency with the tools, you might need to invest some time learning them or work with your DBAs to capture the necessary code then work to automate the process.
- If you are responsible for testing SQL Server based applications at your organization, we would love to hear about approaches you use to automate the testing. Please add your comments to the forum via the URL below.
- Check out these related resources:
- Stay tuned for an upcoming tip on replaying a Profiler session.
- Many thanks to everyone who attended the recent session by the Edgewood Solutions team at the Verify conference. Hopefully we were able to share as much knowledge as we gained from learning about some of your testing challenges with SQL Server. Hope to see you next year!
Last Updated: 2007-11-14
About the author
View all my tips