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

Each SQL08y appliance is built using the following generic Windows Server appliance as a base class:

• SQL08X - WIN03S
• SQL08WG - WIN03S
• SQL08D - WIN0364DC
• SQL08W - WIN0364DC
• SQL08S - WIN0364DC
• SQL08E - WIN0364DC

Please, first create the Windows Server appliance WIN03y as described here.

Note that in the following instructions, the "y" placeholder in WIN03y is for the Windows edition that the appliance/app is based upon ("S" for Standard, "DC" for DataCenter). The "y" placeholder in SQL08y is for the SQL edition that the appliance is based upon ("X" for Express, "WG" for Workgroup, "D" for Developer, "W" for Web, "S" for Standard, "E" for Enterprise).

1. Create an application that contains an instance of WIN03y and branch the instance (referred to below as a singleton).
2. To speed up the appliance creation process, configure the singleton's default resources to cpu=1 and memory=1G:
   • select the singleton in the application editor
   • under the Appliance menu select Resources
   • modify the default resources as specified above
3. Resize the WIN03y boot volume to 8GB.
4. Edit the class and enable the external interface:
   • select the singleton in the application editor
   • under the Appliance menu select Modify Boundary
   • select the Interfaces tab
   • check the External Interface checkbox under Raw Interfaces
5. Edit the class and add a mandatory placeholder volume (name: data; mount on D:\).
6. Create an application volume to use temporarily as the placeholder volume (filesystem: ntfs03; size: 6GB).
7. Start the application and open up the graphic console of the singleton using the AppLogic GUI (on the Applications tab, select your application and login to the graphical console using the Login (graphic) button at the top of the page).
8. Login to the Windows appliance as the administrator.
9. If you have not already installed both Windows Installer 4.5 and the .NET Framework 3.5 SP1 do so now:
   • configure the external interface with usable network settings (this is the first enumerated network interface):
     • access Control Panel=>Network Connections=>Local Area Connection X=>Properties=>Internet Protocol (TCP/IP)=>Properties; click on Use the following IP address and enter usable values for your grid (IP Address, netmask, gateway, DNS server)
     • or, in the command shell:
       • netsh interface ip set address name="Local Area Connection X" static [ip-address] [netmask] [gateway] 1
       • netsh interface ip set dns name="Local Area Connection X" static [dns-ip]
   • open Internet Explorer, search for Windows Installer 4.5; download and install it (do not reboot)
   • open Internet Explorer, search for .NET Framework 3.5 SP1; download and install it (do not reboot)
10. Enable virtual memory for the appliance -- in a command shell:
    • C:
    • cd \WINDOWS\system32
    • cscript ./pagefileconfig.vbs /create /sys /vo d:
11. Restart the application
12. Install the desired version of SQL Server 2008. A complete distribution source should be used: ISO-image or a full redistributable package. If you are downloading an extractable archive of SQL Server, download and extract it on the D volume. When using an ISO-image, it may be mounted as a separate drive or extracted on D also. From there, it can be installed to C (download link for SQL Server 2008 Express redistributable).
13. Extract redistributable package as following:
    • SQLEXPRADV_x86_ENU.exe /Q /X:D:\
14. Use an appropriate version of the SQL setup configuration file (ConfigurationFile_x86.ini/ConfigurationFile_x64.ini) and launch the installation as following:
    • setup.exe /q /action=Install /configurationfile=D:\ConfigurationFile_x{86|64}.ini /MEDIASOURCE=D:\ /sapwd="manager" /ASSYSADMINACCOUNTS="%COMPUTERNAME%\Administrator" /SQLSYSADMINACCOUNTS="%COMPUTERNAME%\Administrator" /TCPENABLED=1
      Note that MEDIASOURCE should point on the root of the SQL installation folder.
15. Install the SQL msi. This self-executing installer can be downloaded from the grid controller through the default interface of the singleton. This interface has already been properly configured through DHCP.
    • To find the internal IP address of the controller, in a command line shell execute ipconfig /all and note the IP address of the DHCP server for the last enumerated interface (e.g., Local Area Connection 5).
    • Point IE at http://IP-address:8080/download/ and download the current Windows SQL msi (e.g., SQL_Windows-1.0.6-1.msi):
      • Right-click on the msi file and select Save Target As to download the file to the desktop.
    • Double-click on the msi file on the desktop to perform the installation.
    • Delete the msi file and empty the Recycle Bin.
    • Delete the following folders after IIS is installed (these are corrupted folders and need to be deleted in order to resize the volume at a later time):
      • WINDOWS\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\
      • WINDOWS\assembly\GAC_MSIL\IEExecRemote\2.0.0.0__b03f5f7f11d50a3a\
16. Clean-up drive D: remove all SQL-related installation files, including ConfigurationFile.ini.
17. In a Cygwin bash shell remove the file /appliance/passwd.stamp; this ensures the appliance will generate a new Administrator password on the first boot after instantiation.
18. Stop the application and edit the class:
    1. change the class icon color to red
    2. change the class name to SQL08y
    3. change the class description (e.g., SQL Server Appliance - based on Windows Server 2003 Enterprise Edition R2 32/64-bit and SQL Server 2008 Enterprise (v1.0.0-1))
    4. change class category to Database Appliances
    5. remove the external interface
    6. modify the terminals:

       Terminal Name	Direction	Protocol
       in	In	any
       mon	Out	cce

    7. add the following properties:

       Name	Type	Default	Constraints
       read_only	String	off	on,off
       max_connections	Integer	(empty)	min = 0, max = 999999
       sa_password	String	(empty)
       user_db_name	String	(empty)
       user_login	String	(empty)
       user_password	String	(empty)

    8. Set the appliance resources:

       Resource	Minimum	Express Maximum	Enterprise Maximum	Default
       CPU	0.1	1	8	0.25
       Memory	512 MB	1 G	64 G	1 G
       Bandwidth	1 Mbps	2 Gbps	2 Gbps	250 Mbps

19. It is recommended to verify the health of the singleton's boot volume by executing the following command from the web shell: vol fscheck name_of_volume
20. Move SQL08y to a catalog (3tera recommends system_ms).
21. The SQL08y appliance is now ready for use in your AppLogic applications.

-- Main.OlegSmolov - 08 Apr 2009