SQL08X/WG/D/W/S/E: Microsoft SQL Server Database Appliances

	At a Glance
	Catalog	system_ms
	Category	Database Appliances
	User volumes	yes
	Min. memory	512 MB (SQL08X/WG), 1G (SQL08D/W/S/E)
	OS	Windows
	Constraints	no
	Questions/Comments	Ask Forum

Functional Overview

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

SQL08y 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 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 stores its logs internally in its database.

SQL08y is not distributed with AppLogic. Please see the installation reference for instructions on creating SQL08y from a base windows server appliance.

Boundary

Resources

Note: Memory should be increased based on two main factors: number of concurrent users and database size. Typically, SQL08y can support about 50 concurrent users per 1G of memory. The larger the database, the more memory SQL08y should have for processing. For example, SQL08y 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

The data volume must exclusively dedicated to the SQL08y instance (it cannot be shared with other appliances) and must use the NTFS filesystem. Size of the data volume should be minimum 1GB.

Properties

Custom Counters

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

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 can be debugged via the standard Profiler, as well as 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, etc. 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, etc.
• user connection/disconnection

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

Typical Usage

Simple two-tier application (web database application)

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

Appliances in use:

• usr - input gateway
• iis - web server with active content (e.g., 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 (e.g., 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 that 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 help topic.

Scalable two-tier application (web database application)

The following diagram shows a typical usage of the SQL08y 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 towards 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 as well as 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 (e.g., 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 (e.g., 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 that 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 (e.g., 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 (e.g., 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 in order 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 that 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 appliance is a two-tier web application using a read-only database. In this case, SQL08x 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 and Links

• 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.
• SQL08y is designed for relatively straightforward database uses (no master-slave, replication, etc.). If you need a database with more advanced features, let us know.
• SQL08y 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 appliance itself.
• Volume requirements
  • Make sure 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 (e.g., larger) volume.
• The appliance connected to SQL08y 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 's in terminal should have the MSSQL client library in order to access the MSSQL server and submit requests.
• References:
  • http://www.microsoft.com/sqlserver/ -- MSSQL's web site

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

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

To see the full list of open source packages used in this appliance, please see its implementation design.

Related Documents

• Implementation Design
• Test Plan

Questions and Comments

To post a question or comment on this appliance, visit our forum.

-- OlegSmolov - 04 Feb 2009