Database Testing
 

Database Testing

Database performance problems can make you look bad:
In an age where response time and accelerated access to information is quickly becoming a company's most critical issue, slow or poor performing SQL transactions can cause misinformed users to lay the performance blame squarely on you or your application's shoulders. When thousands of SQL transactions and procedures are involved, the performance issues become exponentially difficult to analyze. Pinpointing the problem can be as difficult and time consuming as finding a needle in a haystack.

To solve this problem effectively, DBAs and IT staff need to be able to capture, analyze and optimize the SQL that is passed between the database and your application and your users. Furthermore, you want to load and stress test both the database and the application to ensure that the addition of extra users does not introduce other performance problems.

What is database testing?

1) Scalability testing, performance monitoring, pinpointing performance anomalies, validating Service Level Agreements.

2) The overall objective of database testing is to establish the interoperability capabilities between database servers and their respective clients in different IS systems. Underlying this are the assumptions that within an IS system, data, regardless of how it is accessed or displayed, is stored within a structured database, and that a country might desire to share a portion of this data with another country’s IS.

Objectives:

·Immediately identify bottlenecks so that they can quickly be addressed,

·Tune versions of your database applications,

·Validate Service Level Agreements,

·Provide for projected growth scenarios,

·Meet the reliability and scalability demands of the most challenging business environments.

Steps involved in the database testing:-

  • Understanding database applications
    • Testing the application vs. the DBMS
    • Data storage, listing and mapping
    • Back-end vs. front-end testing
  • General database basics
    • Flat and relational databases
    • Normalizing data to forms
    • Table relationships
  • Testing with structured query language (SQL)
    • SQL coding standards
    • SQL basics: select, insert, update and delete
    • Useful queries for data verification

Case study:

Test Procedures

To assist testing, the all testers will provide a sample schema and database. The databases accessed should be single table (non-relational), and consist of no fewer than 50 records. Each tester will provide a schema of the database table used, together with any other specifics needed by the other testers, such as database name, port, etc, to facilitate connections.

Also, as part of preparation for these tests, all testers should identify particular software items they might need to connect to other database systems, and ensure those items are brought to the Exercise. (E.G.ODBC drivers for databases to be tested).

The testing will proceed under three loose stages:

  1. Attempting to access data in IS A from a client in IS* B (through, e.g. ODBC connections).
  2. Adding "Gateway" applications to the database server that would facilitate other clients access (e.g. Web Server with Database connectivity).
  3. Performing manual dumps of data from IS A, to IS B for importation into another database system.

*Is: Information Services

Detailed Tests
Test 1.

Using any ODBC compatible client application part of the current baseline of IS A, IS A should attempt to access all records within the designated database on IS B. This test should be performed with the existing software configuration of both IS’s (including currently loaded ODBC drivers)

Criteria:
Green: The client application of IS A successfully connects to the database of IS B, and the data is available for view or transfer.

Amber: The client application of IS A successfully connects to the database of IS B, but the particular database to be accessed is unavailable for view due to security, or other constraints.

Red: The client application of IS A cannot open a connection to the database system of IS B.

Test 2.

Repeat Test 1, after attempting to load any other ODBC drivers required by IS A or B, and after making other configuration changes (e.g. creating additional database log in accounts) as deemed to be required.
Criteria:

Green: The client application of IS A successfully connects to the database of IS B, and the data is available for view or transfer.

Amber: The client application of IS A successfully connects to the database of IS B, but the particular database to be accessed is unavailable for view due to security, or other constraints.

Red: The client application of IS A cannot open a connection to the database system of IS B.

Test 3

This test proceeds under the assumption that the server of a particular IS either has a constituent web server application, or is capable of adding one to its configuration. The test will highlight the ability to provide database access from one IS to another, utilizing on a client a web browser, and on a server a web server in conjunction with a database. To proceed with this test, any method of web to database access (API, CGI, etc) will be permitted, as long as local technical support to create these interfaces is available, and all actions can be fully documented.

Step 1: Add a web server application to IS of country A.

Criteria:

Green: A web server can be added, and is accessible from a web browser using a TCP/IP connection.

Amber: A web server can be added, but it’s performance and accessibility across a TCP/IP network is intermittent.

Red: A Web server cannot be added, or it cannot be accessed across a TCP/IP network.


Step 2:Add a web client (browser) to IS of country B.

Criteria:

Green: A web browser is already part of the current baseline of Country B’s IS, or the browser is added without problem.

Amber: A web browser is added to a client on IS B, but needs additional configuration to connect to a web server of IS A.

Red: A browser cannot be added to a client of IS B, or is added but cannot connect to a web server of IS A.

Step 3 : Add either an API (Application Programming Interface), CGI (Common Gateway Interface), or other proprietary package to enable web server connections to the database of IS on country A. Create a series of HTML pages that utilize these interfaces and connections to provide for performing a query of the sample database on Country A’s IS. Have a web browser on Country B’s IS open this page and attempt to perform a query.

Criteria :

Green: The web browser on Country B’s IS can open the page, input a desired query, execute it, and receive a valid number of returns from the database of Country A’s IS.

Amber: The web browser on Country B’s IS can open the page, input a desired query, execute it, and receives an incorrect return from the database of Country A’s IS.

Test 4.

The object of this test is to demonstrate lowest common denominator interoperability between database systems of differing IS’s. If no automated process exists for accessing data through combinations of clients, servers, or third party middleware, this test should demonstrate the ability for an IS database to perform regular "extracts" from its system of designated data to pass onto another nation’s IS, which could then, given a valid schema for this data, import it into it’s own database, providing access through it’s own client system. This method is currently in use by several differing IS systems, especially of different classification levels, to provide for the basic ability to exchange information.

Step 1: Perform a dump or extraction of the data in the database of IS A in a structured format (IDBTF, text delimited, etc), and provide this information, along with a full schema for the data, to Country B. Country B should attempt to load this information into either an existing database, or to create a new database for its storage. A client on Country B’s IS should then tries to access the information.

Criteria :

Green : The data format can be analyzed and manipulated as necessary by Country B, to enable it’s importation into a database on Country B’s IS. This data can be accessed without difficulty from a Client application on Country B’s IS.

Amber : The data can be successfully imported into Country B’s IS database, but is unavailable from a client on Country B’s IS, due to differences in the nature of the data.

Red : The data can not be imported into a database on Country B’s IS.