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

Base Class

The WIN03y appliance image is used as the base class for SQL08y appliance.

Class Volumes

SQL itself is not sensitive to the volume configuration and will work on most any setup. Besides the SQL Server services, it does not require any additional software to be able to start. .NET Framework 3.5 SP1 and Microsoft Installer 4.5 are the only dependencies for SQL. The setup chosen for the 1.0.5 release is as follows: - boot volume, 7GB (SQL08X? /WG) or 8GB size (SQL08D? /W/S/E), mounted as "C:", writable, instantiable. The boot volume contains a basic Windows installation as the one present on the WIN03 appliance.

In summary, SQL's boot volume contains:

• Microsoft Server 2003, as base OS image (as installed on the WIN03 appliance)
• SQL Server 2008 database engine version 10.x, for data storage and query processing
• PHP 5.2.10 Win32
• Microsoft SQL Driver for PHP v1.1
• .NET Framework 3.5 SP1
• sshd, used for logging into the component through the default interface

In addition to the class volume, SQL requires a user-supplied data volume that is used to store the actual database: - data volume, user-defined size (minimum 1GB) mounted as "D:", writable, placeholder.

Contents of the release directory:

Exported appliance images:

• SQL08X-version.tar.bz2
• SQL08WG-version.tar.bz2
• SQL08D-version.tar.bz2
• SQL08W-version.tar.bz2
• SQL08S-version.tar.bz2
• SQL08E-version.tar.bz2

SQL MSI: install this to re-create the appliance:

• SQL_Windows-version.msi

SQL automated build script: use this to automatically build SQL appliance

• SQL-build-version.tar.bz2
• SQL64-build-version.tar.bz2

SQL automated test suite: use this to test SQL appliance

• SQLx32x64-test-version.tar.bz2

Theory of Operation

SQL consists of one main background service called SQLServer. This daemon is loaded in the background when SQL is started.

In addition to the service above, there are several other processes that SQL runs in the background when SQLServer is started:

• SQL Analysis Services
• SQL Integration Services
• SQL Full-text Filter Daemon Launcher
• SQL Server VSS Writer

SQL clients must use the MSSQL protocol to communicate with the SQLServer service. Microsoft provides an ODBC-library which is used by a majority of the clients. In addition, there are other implementations of the protocol for many other types of clients such as JDBC.

Configuration

General

SQL is configured using SQL Server Enterprise Manager and modifying system configuration database, which contains all of the SQL configuration settings. There are multiple SQL properties that affect multiple SQL Server settings; start script changes those by executing sql-statements from a command line, using properties supplied by AppLogic.

The table below shows the mapping of SQL properties to SQL Server settings. For any SQL Server setting not in the table and not mentioned in this section, SQL leaves it as its default value.

Auto-creation

When SQL is configured to auto-create the database (user_db_name is defined), it first checks whether the database exists on the configured data volume.

Users

SQL configures SQL Server to allow any valid database user ('sa' is pre-configured and could be used here) to access the database in any other way - locally or remotely. Only connections through the in terminal are accepted. The client of SQL has to connect under 'sa' user, or create own roles and users granting appropriate rights to them.

Appliance Implementation Details

The SQL appliance contains the following files in the /appliance folder on its boot volume. These files are used to implement the appliance:

Important Notes

• The D:\ folder must be owned by Administrator.

-- OlegSmolov - 26 Aug 2009