SQL08X/SQL08WG/SQL08D/SQL08W/SQL08S/SQL08E: Microsoft SQL Server Appliances - Test Plan

Preparation

Host platform

These tests must be launched from a remote host, that has an SSH access to the AppLogic controller.

Tools

All necessary tools are supplied within the test suite. You need SSH installed and ssh keyfile for your AppLogic grid to perform the tests.

SQL I/O

x86 binary package: http://www.microsoft.com/downloads/details.aspx?familyid=9a8b005b-84e4-4f24-8d65-cb53442d9e19

unixODBC

source package: http://vault.centos.org/5.0/os/i386/CentOS/unixODBC-2.2.11-7.1.i386.rpm

freeTDS

source package: http://mirror.centos.org/centos-5/5.3/extras/i386/RPMS/freetds-0.64-11.el5.centos.i386.rpm

Tests Summary

Configuration Tests

• Configuration propagation test: verify the SQL Server configuration is updated according to component configuration
• Database auto-creation test: verify start with an existing database, empty volume, maintenance mode
• Connection limit test: max connections, able to serve specified connections
• Memory management test: test simple and complex query configurations with different memory configurations
• Sync on write test: flush data to disk on database updates
• Client connection failure test: verifies that SQL can be accessed only through the IN terminal

Resource Tests

• CPU/Memory resource tests: tests SQL performance under various CPU/Memory configurations.

Endurance Tests

• Start-Stop test of the SQL appliance: verifies that appliance can be started and shut down multiple times
• Serial stress test: performs serial stress test with SQLIO

Benchmarks

• Benchmark: verifies the maximum SQL performance under various CPU/Memory/Bandwidth resources available
• Large database performance test: performs multi-insert/select batch operations

Running the Tests

To run the tests on AppLogic2.AppLogic grid, copy and un-compress the tests archive file to a directory of your choice. Launch the test suite running the sql-test.sh file. Test suite will import the test application, or prompt you to use an existing, created previously, one.

Example test aplication:

If you choose to create a new test application, the test suite will import a new application and configure it, assuming that the SQL instance that you want to test is located in the /proto/ catalog and in named SQL08y. If you want to test another instance of SQL, you can either copy it to the /proto/ catalog or open the test suite application in AppLogic GUI and manually drag the necessary SQL08y instance over an existing one.

Design

Structure

• IN gateway used to access MON statistics
• LINUX5 appliance used as a client for Microsoft SQL Server
• LINUX5 appliance used as a client for MON
• MON appliance used for monitoring statistics

The test script is fully automated and includes the ability to install the necessary test software on the LINUX5 instance. The user does not need to install any software on any of the appliances; the test script takes care of everything.

Test Details

• For successful operation, test suite must be the only application running on the grid, or some tests, mainly performance ones, may fail.

• Some tests, especially resource-intensive ones, may fail if you do not have enough resources available. This is not a SQL fault.

• Most of the tests should not be interrupted by a user. If it happened that you had to interrupt the test, stop the test application with "application stop" before proceeding to the next test.

Configuration propagation test

Reconfigures SQL with different settings and verifies that all settings in SQL Server configuration are updated accordingly

Test changes multiple properties of SQL, launches the application, verifies successful changes in SQL Server configuration and verifies that SQL is operational with these settings. Every possible value of every property is tested.

Database auto-creation test

Verifies database auto-creation

Test starts the SQL with empty volume formatted with ntfs, checks for successful new database creation and verifies SQL functionality. After that, test starts SQL with the same database volume and verifies that the existing database is used.

Connection limit test

Verifies connection enforcement

Test configures SQL to support a maximum of 20 simultaneous connections and verifies that connections limit is enforced.

Memory management test

Tests SQL configuration under various memory resources available

Test detects the amount of free memory available on the grid where the test application is running, and verifies proper update of the max_connections value. SQL is tested with 512Mb, 1Gb, 1.5Gb, 2Gb, 3Gb, 4Gb, 6Gb, 8Gb, 16Gb, 32Gb and 64Gb of memory, subject to available free memory on the grid. Any mismatches between reported property values and reference values are reported.

This test may fail at some point when there are not enough free resources available. This is not a SQL fault.

CPU/Memory resource tests

Tests SQL performance under various memory, cpu and bandwidth resources available

This test detects the amount of memory, cpu and bandwidth resources available on the grid, and tests the SQL performance with all possible combinations to the limits imposed by resource availability. SQL is tested with the following settings:

• Memory: 512Mb, 1024Mb, 1536Mb, 2048Mb, 3072Mb, 4096Mb, 6144Mb, 8192Mb, 16384Mb, 32768Mb, 65536Mb
• CPU: 0.10, 0.20, 0.40, 0.50, 1, 1.5, 2, 4, 8, 10, 12, 16, 32
• Bandwidth: 1Mbit, 10Mbits, 100MBits, 200MBits, 500MBits, 1Gbit, 1.5GBits, 2Gbits
• 10 clients, 100 transactions

Client LINUX5 appliance is configured with 512Mb of memory, 1 cpu and 500Mbit of bandwidth for this test. This test may fail if there are not enough free resources available on the grid to run all required appliances.

Client connection failure test

Verifies that SQL is accessbile only through the in terminal

This test tries to connect to SQL through other than in terminals. All attempts must fail.

Benchmark

Verifies maximum SQL performance

For this test SQL is configured with 2 cpus, 500Mbits of bandwidth and 2Gb of memory, and optimized settings (sync_on_write=no). Then the SQLIO test is used to verify SQL performance with 1,10,100 clients and 100,1000 queries.

This test may fail at some point when there are not enough free resources available. This is not a SQL fault.

Start-Stop test of the SQL appliance

Verifies that SQL could be safely started and stopped multiple times.

This test verifies that the SQL can be successfully started and stopped multiple times. It is an important test that verifies that all log rotation is performed properly. Amount of start/stop cycles may be specified and be unlimited (in this case the test can be interrupted using control-c).

Serial stress test

Executes SQLIO stress test in serial mode with a single connection.

Large database performance test

Executes multi-insert batch operations until database become a few gigabytes in size. Then, test verifies that SQL is still operational by selecting data from the database.

Tests 1-10

Run the most important tests (1-10) in automated mode without any details except success/failure messages

-- OlegSmolov - 26 Aug 2009