Huntertrak959

Free online SQL Test tool for easy SQL query learning and testing. Oracle 11g MySQL 5.6 MS SQL Server 2019. FREE online SQL query test tool. SQLStress is a tool to stress test a Microsoft SQL Server installation. Its main purpose is to find infrastructure problems within an installation. It can also be used for hardware sizing, system tuning or benchmarking. The focus is on simplicity.

This article is the third in a series of four where I share my findings on baselining, monitoring, stress testing and performance tuning. It builds on the foundations of my previous articles on baselining and monitoring.

Having a monitoring system in place is an important step in making you feel comfortable with what goes on in your production system. But what about that new release that is in the pipeline to be deployed in 2 weeks? Your monitoring system will only tell you what went wrong after it is too late. Of course the end user has tested this new release already, and he or she is convinced that it is exactly what they need, and that they need it urgently. But what if this “miracle” release —which developers are probably still developing — has some hidden features like table scans, (dead)locking issues and more of these fun side effects? Can you predict how this new release will behave in a production environment after being deployed? If you can, please teach me, because I can’t.

I believe the only way you can minimize performance issues (because you’ll never succeed in avoiding them completely) is to test the new release in a “production alike” environment. That’s why we need to organize stress tests.

The question is how to generate a comparable amount of load on your system. Of course, you can mobilize everyone in your company to start simulating a user for a certain period of time. But what if your manager is not keen on this idea (and he probably isn’t)? What if you need to perform identical test scenarios because your first results were not perfect (what are the chances they are?). In other words, you need an automated tool to simulate a variable number of users.

In the ideal world, a stress test scenario should test your entire application architecture covering all business processes. Not only is your database subject to this test but also your network, all external systems and of course your application servers. This implies that your automated tests process should simulate a variable number of users on production-identical hardware, conforming to business specifications.

But, this “full option” stress test environment has a price label attached to it. Not only the investment in the identical hardware needed but also especially the investment in time and knowledge needed to setup these test procedures can become quite costly. Your business will want to see hard figures before they will agree to invest the money. How to define a business plan to justify these costs is far beyond the scope of this article but the following points of interest should certainly be in it.

• How business critical is your application? How much money will your company loose if your application has limited or no availability?
• What are the costs of additional hardware? Can this hardware be shared with other applications?
• What are the costs of a third party tool that can simulate virtual users?
• How many man-days will have to be spent on setup and maintenance of the environment?

If you cannot convince your business of the need of a full option stress test scenario, there is still plan B:

• Only the database will be stress tested.
• No third party tool required.

This is not ideal but focusing on the database for a stress test scenario is not such a bad idea. Your database plays a very important role when it comes to scalability requirements. Where a number of scale out solutions exist for your application farm (add an extra node to the farm and the load gets balanced automatically), scale out is seldom an option for your database, so you need to get it right from the start.

This is what I like to call plan B and this is what this article will cover. I’ll provide a self-made solution that, with its limitations, can give you a good indication of how your database will react, before it goes into production.

Also in this article, I won’t describe fancy third party tools but I’ll concentrate on Microsoft out-of-the-box tools like SQLIO coupled with a few lines of C#.

This document is applicable to SQL 2000 as well as SQL 2005 unless otherwise specified.

Step 1: Define Your Business Specifications

Before I start covering all the technical processes, I want to stress the importance of well-defined business specifications. Your business, although often the source of all evil, remains your customer who knows best what the requirements are of the product you are building.

You need to get reliable answers to questions like:

• What are your most critical business processes and how often are they processed (e.g. number of sales activities per day, number of client requests per day, etc.)
• What is regarded (required) as acceptable response times from the online application? How about reports?
• How much data must be kept available on the live database (over 1 month, 1 year, 5 years?). Can data be archived? How and when?
• How many concurrent application users are expected?
• Are there periods of more than average usage? (E.g. Daily between 9:00 A.M. and 10:00 A.M. /yearly ’round about Christmas.) What business processes will be impacted the most?
• How much data latency is acceptable for reports?
• Etc.

For a number of these questions, the service level agreement (SLA) you defined with your customer can be consulted. If there is still no SLA, this is the time to start making one.

By: Haroon Ashraf | Updated: 2017-12-15 | Comments (8) | Related: More >Testing

Problem

As a database developer I would like to implement an advanced and cost-effectivedatabase unit testing framework that can help my team in early bug detection anddoes not require context switching from SQL to any other tool/language and is suitablefor the following scenario: when all my database development work is done throughSSDT (SQL Server Data Tools).

Solution

The solution is to get your databases equipped with tSQLt and start writing andrunning unit tests for your database(s) without even leaving the Visual Studio IDE providedthe preferred database development toolset in your organization is SSDT (SQL ServerData Tools).

Recap: Answering Some Basic Questions about tSQLt

Some of the basic things that may come to mind before tSQLt implementation havealready been answered in detail inmy previous tip, in case you missed it a quick recap is as follows:

• Why tSQLt for advanced database unit-testing?
• Writing tSQLt unit-tests is same as writing T-SQL scripts so no contextswitching is needed you stay within SQL.
• Other benefits include isolation, transactions, continuous integration andcross-database objects testing.
• How is tSQLt Testing Framework Cost-Effective?
• tSQLt is an open-source project so it is free to use commercially and itis also underlying unit-testing framework behind many third party visual databasetesting tools.
• Apart from the cost-effectiveness and support for advanced database unit-testing,tSQLt framework architecture has been optimized to use for TDD (Test-DrivenDevelopment).

Recap: Creating and Running tSQLt Tests Generally

Inmy previous tip the steps to create and run tSQLt tests steps are mentioned.In short a tSQLt unit-test is ultimately a test class which is actually a schemawhich helps us to group relevant tests together while calling a test class or allthe tests in all test classes is simply calling a stored procedure so nothing newto learn apart from T-SQL.

Implementing tSQLt Framework with SSDT vs SSMS

The steps to implement tSQLt unit-testing with SSDT are different than SSMS implementationdue to the following reasons:

tSQLt Implementation Scenario

In order to implement tSQLt consider the following scenario:

• You want to implement tSQLt as your database unit-testing framework whileall your database development, debugging, testing, deployment and refactoringis done through SSDT (SQL Server Data Tools).

Setup: Database Unit-Testing with tSQLt using SSDT

Now that your team has finally decided to use tSQLt for advanced database unit-testing you are going to start implementing the testing framework on your database(s) whichare managed through SSDT.

According totSQLt.ORG the tSQLt Test Framework is compatible with SQL 2005 (Service Pack) andall versions above it.

Using OfficeSuppliesSample Database Project (Mimicking Transactional DatabaseProject)

We are using OfficeSuppliesSample database project to mimic main transactionaldatabase which contains a simple version of customer-product-order scenario foroffice supplies as follows:

Apart from the above three objects (tables) there is a procedure to add customerand view to see the orders.

Note: The database (OfficeSuppliesSample) was originally created in previoustip to mimic traditional online sales systems in a simplest manner can also be referencedin upcoming tips, so please feel free to get familiar with it.

Pleasedownload and run the source code below to create and populate OfficeSuppliesSampledatabase:

The database is ready to be used now.

Writing tSQLt Unit-Tests against OfficeSuppliesSample_Data

The best way to implement tSQLt Testing Framework to write unit-tests againsttransactional database project (which results into OfficeSuppliesSample_Data database)is to have another database project containing unit-tests.

So we are looking forward to have the following database projects:

1. OfficeSuppliesSample_Data
2. OfficeSuppliesSample_Tests

Creating OfficeSuppliesSample Database Project (Mimicking Transactional DatabaseProject)

Since SSDT (SQL Server Data Tools) uses naturally declarative database development,we begin with a database project followed by a database in a sandboxed environment.

SSDT provides complete isolation from changes done by other developers by providinga fully functional version of the main database in the form of a Project with bothconnected and disconnected development flavors that takes database development,testing, refactoring and deployment to next phase.

So the first thing that needs to be done (keeping in mind SSDT is used for databasedevelopment) is to create OfficeSuppliesSample Database Project and we can easilysimulate it by creating the project from the database, however, please bear in mindeither you create a database with basic objects and then create project from itor begin with database project and start adding objects both ways are acceptable.

Start a new solution and create a new SQL Database Project asshown:

Now right click on OfficeSuppliesSample database project and click on Importand then click on Database…

Next Connect to OfficeSuppliesSample database and click Start and then clickFinish.

Next check the database project in the solution explorer it must have been populatedwith OfficeSuppliesSample database objects.

Scripting OfficeSuppliesSample Data in Database Project

Reference data can be added to the project in the form of scripts to be centrallymanaged by a Post Deployment Script.

If we debug the project to deploy changes the debug database (OfficeSuppliesSample_Data)will have no data.

In order to create and populate OfficeSuppliesSample_Data database please createa new folder “Reference Data”.

Unzip thescripts below and put them under “Reference Data” folder using windowsexplorer:

Now In the solution explorer right click on “Reference Data” folderselect “Add” and then select “Existing Item…”:

Next select all the scripts in the “Reference Data” folder (you recentlyunzipped) and as a result they will become part of the project:

Before these scripts can run we have to make sure that SQLCMD Mode has been turnedon which can be achieved by clicking on the SQLCMD Mode in any script pane in thetop right:

Creating and Populating OfficeSuppliesSample_Data database from Project

You can set your Debug Database as Local Dev SQL database by checking Propertiesof your database project and then under “Debug Tab” pointing to yourdesired SQL instance:

Since the data scripts have been kept simple, we are checking the deploymentoption “Always re-create database” so that rerunning the data scriptsshould not result in any key violation.

It is important to set the Target Platform the same as your SQL Server Instanceversion as in our case it is SQL 2012:

Press F5 to deploy changes to the debug database and see OfficeSuppliesSample_Data(debug database) getting populated.

Now using SQL Server Object Explorer view data navigate to OfficeSuppliesSample_Dataand right click on “dbo.OrdersView” under Views and then click on “ViewData” to see the tables populated by the post deployment script:

Creating OfficeSuppliesSample_Tests database Project based on TSQLT Objects

The first step to implement tSQLt is to download it fromtSQLt.ORG.

Please see the attachedtSQLt zip file download which was done quite a while ago, but still matchesthe latest version available on website:

Create a temporary database “TSQLT_Temp” to hold the tSQLtClass.sqlscript output that generates tSQLt objects:

Then run the tSQLtClass.SQL script in the TSQLT_Temp database to create tSQLtobjects:

Next create a new database project called “OfficeSuppliesSample_Tests”:

Set the Target Platform the same as that of main database:

Then right click on OfficeSupplieSample_Tests and click on the “Importfrom Database” option:

Then connect to the TSQLT_Temp database and get all tSQLt objects from this databaseinto the project (OfficeSuppliesSample_Tests):

Setting Target Connection for Test Project (OfficeSuppliesSample_Tests)

Adding Master Database and Main Database Reference

Since the test database is going to refer to the main database objects for testingwe have to add the following database references:

1. Master Database
2. OfficeSuppliesSample_Data

Right click on OfficeSuppliesSample_Tests project and select “Add DatabaseReference” then the master database as follows:

Next add the OfficeSuppliesSample_Data reference as follows:

Enabling CLR for tSQLt Test Runs

One of the requirements for tSQLt tests to run successfully is to make sure thatCLR is enabled at the Server Level which can be easily done by creating a Pre-Deploymentscript using the following code:

Automating tSQLt Test Runs for Debug Database

Sql Server Stress Test Tool Free Tool

All the tSQLt tests can be run by the following script:

In order to automate the running of tests as soon as the project is debuggedwe need to put it into a Post-Deployment script.

Now Debug the OfficeSuppliesSample_Tests database project to see tSQLt blanktests run:

Creating Tests: Database Unit-Testing with tSQLt using SSDT

Now both main and test database projects along with their databases are readyand it’s time to write and run unit-tests.

Create a new folder “Tests” in OfficeSuppliesSample_Tests projectand then create “CustomerTests” sub-folder under the “Tests”folder.

Real Time Scenario for Adding New Customer Test

Now keeping in mind the main tables of the OfficeSuppliesSample, what if youhave been asked to test these basic requirements:

• In your system (database) an admin must be able to add a new Customer.
• At a very basic level we need to make sure the following things:
  • Customer object (table) exists
  • AddCustomer object (stored procedure) exists

Create a New Test Class (CustomerTests)

Let's create a Test Class first by creating a schema under the CustomerTestsfolder of the test project:

Then write the following code:

Create a test to check if Customer table exists

Create a unit-test by creating a new stored procedure under CustomerTests schemawith the following code:

Create a test to check if AddCustomer procedure exists

Next create another stored procedure in the test project to check that the AddCustomerprocedure exists as shown below:

Cached

The script to create a test to check if the Product table exists is as follows:

Running All Tests

Making sure the test project is selected and press F5 to start debugging:

We can see above that all tests have passed.

tSQLt Design-Time Test Automation Scenario

It is easier to achieve test automation at design time for the developer by simplylinking a database project with a test project that means debugging the databaseproject should result in a BUILD error if tests fail.

This can be achieved by choosing the “Multiple startup projects”option under Solution > Common Properties > Startup Project as shown below:

Let's change the procedure “AddCustomer” to “AddCustomer2”and then deploy the changes to the debug database by pressing F5:

Sql Query Tools Free

There is no need to start the unit-tests separately as they run automaticallywhen the main database project is debugged and this results into a BUILD failureif the tests do not pass.

Now rename the stored procedure “AddCustomer2” to “AddCustomer”and debug the main project again to see all goes well:

Next Steps

• Create a fresh database project and test the project from scratch and thenadd objects to the main database project and simple tests to the test databaseproject and see how it goes.
• Create some solid unit tests to check functionality of the objects suchas adding a new customer, product or order.
• Explore the tSQLt officialwebsite to develop basic and advanced skills of creating and running tSQLt tests.
• Please keep watching MSSQLTips.com as more tips about tSQLt are on theirway.

Sql Server Stress Test Tool Free Download

About the author

Haroon Ashraf's interests are Database-Centric Architectures and his expertise includes development, testing, implementation and migration along with Database Life Cycle Management (DLM).
View all my tips