Reference Information › Appliance Catalog Reference Guide › System_ms Catalog › Database Appliance › SQL08: SQL Server Database Appliances

SQL08: SQL Server Database Appliances

Functional Overview

SQL08y_0N is a database appliance based on Microsoft SQL Server 2008 (http://www.microsoft.com/sqlserver/). It provides an easy way to add a Microsoft SQL database to any application.

SQL08y_0N stores the database on an application-defined volume configured on each SQL08y instance. The SQL08y database volume cannot be shared among multiple SQL08y instances.

SQL08y_0N clients access the configured database through the in terminal. The database requests are processed and completed back through the same terminal. SQL08y allows any valid user to access the database through the in terminal (the appliance has one pre-configured superuser role: standard 'sa'). SQL08y can be configured with the maximum number of concurrent connections supported through in.

The appliance connected to SQL08y is responsible for creating its own database and tables if they do not exist. Alternatively, an initial database can be manually copied to the data volume.

SQL08y_0N stores its logs internally in its database.

Important! SQL08y_0N is not distributed with CA AppLogic. See the installation reference for instructions on creating SQL08y_0N from a base windows server appliance.

Resources

SQL08Y 03

Express/Workgroup (x86)

Developer/Web/Standard/Enterprise (x64)

SQL08Y 08

Express/Workgroup (x86)

Datacenter/Web/Standard/Enterprise (x64)

Note: Memory should be increased based on two main factors: number of concurrent users and database size. Typically, SQL08y_09 can support about 50 concurrent users per 1G of memory. The larger the database, the more memory SQL08y_09 should have for processing. For example, SQL08y_09 should be configured with at least 1G of memory for a 10G database - more than 1G for better performance.

Terminals

The default interface is enabled. It is intended for diagnostics and troubleshooting (over SSH). Future versions of this appliance may disable SSH access.

User Volumes

Important: The data volume must exclusively dedicated to the SQL08y_09 instance (it cannot be shared with other appliances).The data volume for appliances based on Windows Server 2003 should be minimum 1GB of type ntfs03 and be at least 1 GB in size, while the data volume for appliances based on Windows Server 2008 should be of type ntfs08 and be at least 3 GB in size

Properties

Note: None of the SQL08y_0N properties are case sensitive excluding file names and paths.

Custom Counters

The SQL08y_09 appliance reports the following custom counters through the mon terminal. These counters belong to the SQL08y_09 counter group:

Important! The counter's pace in the appliance is set to 10s. If you are using graphs via the MON appliance to monitor these counters, the pace for the graphs need to be set to 10s or multiple of 10s.

Debugging a database application

SQL08y_09 can be debugged through the standard Profiler, and though the in interface. This is useful for tracking SQL statements, seeing how long it takes to execute SQL statements, seeing why an SQL statement is failing, and so on. The debug information includes:

• standard debug information exposed by MSSQL
• SQL statement processing and execution statistics (useful for queries)
• MSSQL statistics on SQL commands, database row-level access, and so on.
• user connection/disconnection

The debug information is stored in the SQL08y_09 logs and is available through MSSQL statistic views/stored procedures.

Simple two-tier application (web database application)

The following diagram shows a typical usage of the SQL08y_09 appliance in a two-tier web application geared toward a lot of users executing simple queries:

Appliances in use:

• usr - input gateway
• iis - web server with active content (for example, CGI scripts)
• sql - MSSQL database appliance

Client requests arrive on the usr gateway. The gateway forwards the requests to the iis server, which serves the request. When script (for example, ASP.NET or ASP) on iis need to access persistent data, it uses the sql appliance through the db terminal.

In this example, the database used with sql is not read-only and many users may access it through iis executing simple queries.

Example property configuration:

Note: The data volume must also be configured on sql and the content volume must be configured on iis. To create application volumes that can be used here, see the User Volumes topic.

Scalable two-tier application (web database application)

The following diagram shows a typical usage of the SQL08y_0N appliance in a two-tier web application in which the database is used to share state and data between multiple, load-balanced web servers (geared toward a lot of users executing simple queries). In addition, this example has a separate input for maintenance, through which an administrator can log in and access the database for maintenance and an input through which an administrator can read the MSSQL logs.

Appliances in use:

• usr - input gateway for user requests
• webs - web load-balancer for user requests
• iis1, iis2 - web servers with active content (for example, ASP.NET scripts)
• config - web server content storage
• sql - MSSQL database appliance
• logs - storage for web logs
• mail - SMTP-gateway for outbound mails
• admin - input gateway for access logs

Client requests arrive on the usr gateway. The gateway forwards the requests to the webs load balancer, which directs the request to one of the web servers ( iis1 or iis2). When scripts (for example, ASP.NET) on the web servers need to access persistent data, they use the sql appliance through their db terminals.

Administrator can log in through the admin gateway to the logs appliance and view the web server logs.

In this example, the database used with sql is not read-only and many users may access it through the web servers executing simple queries. Example property configuration:

Note:

• The data volume must also be configured on the sql appliance and the data volume must also be configured on the logs appliance.
• The admin appliance is not required for the operation of the two-tier application.

Simple two-tier application (decision support system)

The following diagram shows a typical usage of the SQL08y appliance in a two-tier web application implementing a decision-support system for a few users executing complex queries over a large database that is several GBs in size.

Appliances in use:

• usr - input gateway
• iis - web server with active content (for example, ASP scripts)
• config - web server content storage
• sql - MSSQL database appliance
• logs - storage for web error log files
• admin - input gateway for viewing log files

Client requests arrive on the usr gateway. The gateway forwards the requests to the iis server, which serves the request. When scripts (for example, ASP.NET) on iis need to access persistent data, it uses the sql appliance through the db terminal.

Using a browser, administrators connect to the admin gateway to view web logs. The admin gateway forwards the requests to the logs NAS appliance.

In this example, the database used with sql is not read-only and users may access it through iis executing complex queries.

Example property configuration:

Note: The data volume must also be configured on the sql appliance and the data volume must also be configured on the logs appliance.

Simple two-tier application (web read-only database application)

A less common usage of the SQL08y_0N appliance is a two-tier web application using a read-only database. In this case, SQL08x_0N in the previous simple application examples can be parameterized to use a read-only database (no modifications are allowed) by setting the read_only property to yes. This will disable various MSSQL features that are not needed for read-only databases which results in better performance. Example property configuration:

Notes

The appliance hostname (set to appliance instance name) cannot be longer than 15 characters. Setting the appliance instance name to more than 15 characters will result in a hostname truncated to the 15th character.

Note:

• SQL08y_0N is designed for relatively straightforward database uses (no master-slave, replication, and so on). If you need a database with more advanced features, let us know.
• SQL08y_0N allows any valid user to access the database (role 'sa' is pre-configured as superuser; new roles could be created); The database can only be accessed either through the in terminal or locally on the SQL08y_0N appliance itself.
• Volume requirements
  • Verify that the data volume is created with the ntfs filesystem; otherwise MSSQL will fail to start.
  • The volume contains only the database data. The data can be moved by copying all files to a new (for example, larger) volume.
• The appliance connected to SQL08y_0N is responsible for creating its own database and tables if they do not exist. Alternatively, you can manually copy the initial database to the data volume.
• The appliance or server connected to SQL08y_0N 's in terminal should have the MSSQL client library to access the MSSQL server and submit requests.
• References:
  • http://www.microsoft.com/sqlserver/ -- MSSQL's website

Open source and 3rd party software used inside of the appliance

SQL08y_0N uses the following 3rd party open source packages in addition to the 3rd party open source packages used by its base class WIN03x.