Enabling Requesters to Select the Version of SQL Server to Install :

This article describes the procedure by which you can provide Service Portal users the ability to select a version of SQL Server to be installed on their requested VMs. To do so you'll use a custom attribute to let users select the desired version, call the appropriate configuration files from a network share where they are stored, and automate the installation. Please note that the commands provided are those which worked in the Embotics test labs, but they may need to be updated to match your version or licensing. As such, please use this example as a reference only.

Requirements

Creating SQL Configuration Files

You will need to create a configuration file for each version of SQL you want to enable users to automatically install. As such, you may want to reference Microsoft documentation concerned with this action for your available versions. As an example, let's look at how you would do this with SQL 2012, running the familiar interactive setup program. You do not need to complete the install in order to create the configuration file.

Run Setup.exe to start the SQL installation process.
Proceed through the wizard making the choices you'll want to see replicated.
On the Ready to Install page, copy the Configuration file path.
Paste the path into Windows Explorer. The file will open with the application assigned to open .ini files (by default, Notepad).
Save the file, noting the location. Make sure to save it as Type: All Files setting the .ini extension.
Cancel the SQL install.
Repeat this process for each version of SQL you will support.

Refer to Microsoft SQL documentation for information on the various settings you can configure. At a minimum, you will need to make sure the configuration file specifies a silent, non-interactive installation.

Preparing the vCommander Scripts

Download and extract the scripts to your vCommander application server. Embotics recommends storing all scripts called by vCommander in a single location, using sub-folders to identify the functions of particular scripts. With the scripts extracted to C: on the vCommander server, the file system will look like this:

C:\scripts\sqlinstall\MountImage.ps1
C:\scripts\sqlinstall\UnMountImage.ps1

Each of the scripts needs to be edited to include the fully qualified domain name or IP address of your vCommander application server, and the location of your encrypted credentials file.

Creating the Custom Attributes

This solution uses custom attributes exposed via the Service Portal to allow users to select the version of SQL they want installed.

Under the Configuration menu, choose Custom Attributes.
Click Add.
Enter a Name like SQL Versionand provide a meaningful Description.
Choose to apply the attribute to Form and select the List radio button to set the type.
Check Edit in Service Portal and then click Next.
Add the options you want to make available, using the Move Up and Move Down buttons to sort your list. Click Finish.

Creating the Service Catalog Entry

Next, create one or more Service Catalog entries to offer users the ability to deploy SQL Servers. The procedure below provides an example of doing so with the minimally required configuration. As with any vCommander service, you have a wealth of options available to do things like customize forms, use service-specific naming, etc. that are not explored here.

Under the Configuration menu, choose Service Request Configuration.
Click Add Service.
Provide a Name and meaningful Description for the service. Select an Icon and check any relevant categories. Click Next.
Click Add > VM or Template. Choose an image from the picker and provide the Name you want to appear in the Service Catalog. Click Add to Service then click Next.
Under the Infrastructure, update Name and Description as necessary. Also set a pattern for Deployed Name, choose a Customization Spec and/or Completion Workflow, and whether deployed VMs will be linked clones or not.
Switch to the Resources tab. Select the CPU Count, Memory, Disks and Network for the service. Make sure the resources are sufficient for all SQL versions being offered.
Switch to the Attributes tab. Click Add Attributes and select SQL Version and any other attributes you want applied, then click OK. Click Add Groups and select any groups of which the deployed VM should be a member.
Switch to the Form tab. Add the SQL Version attribute to the form, as well as any other form elements you require. Edit the SQL Version form element by enabling the Required checkbox, and click OK. Click Next.
Set your Deployment options as appropriate, and click Next.
On the Visibility page, use the controls to define who may request the service. Click Next, then Finish.

Creating the Completion Workflow

Finally, create the completion workflow to handle the automation of SQL Server installation.

Under the Configuration menu, choose Service Request Configuration.
Switch to the Completion Workflows tab and click Add.
Enter a meaningful name, such as Deploy Customizable SQL. Choose to Apply this workflow: after a VM is deployed and click Next.
On the Steps page, click Add > Wait for Event. Configure the step as follows:

Step Name: Wait for Customization
Step Execution: Always Execute
Wait For: Guest OS customization to complete
Wait Time: 600 seconds
Wait Time Exceeded: Mark workflow step as failed: do not proceed
Click Add > Wait for Event. Configure the step as follows:

Step Name: Confirm Networking
Step Execution: Always Execute
Wait For: Service to obtain IP address and DNS name
Wait Time: 300 seconds
Wait Time Exceeded: Mark workflow step as failed: do not proceed
Click Add > Execute Script. Configure the step as follows:

Step Name: Mount SQL ISO
Step Execution: Always Execute
Timeout: 300 seconds
Script Output: Capture script output as comment
When Step Fails: Mark workflow step as failed: do not proceed
Command Line: powershell.exe -ExecutionPolicy Bypass -command "&{c:\scripts\mountimage.ps1 "#{target.remoteId}" "#{target.settings.customAttribute['SQL Version']}"}"
Click Add > Guest OS > Create File and configure the step as follows:

Step Name: Create 2012 File
Step Execution: Execute when conditions are met (Click Edit and set the Condition #{target.settings.customAttribute['SQL_Version']} -eq 2012 as shown below.)

When Step Fails: Mark workflow step as failed: do not proceed
Credentials: Choose local credentials that will work on the deployed VM's guest Operating System
Contents: Provide the contents of the SQL configuration file for the SQL version matching your condition
Destination: c:\configurationfile2012.ini (use equivalent name for other versions of SQL)
Overwrite: Enabled

Add other conditional steps in this manner until you have a step for each supported version of SQL.
Click Add > Guest OS > Create File and configure the step as follows:

Step Name: Create CopyFile.ps1
Step Execution: Always execute
When Step Fails: Mark workflow step as failed: do not proceed
Credentials: Choose local credentials that will work on the deployed VM's guest Operating System
Contents:
[CmdletBinding()]
param(
[Parameter(Mandatory=$True)]
[string] $SQLVersion = $(Throw "Please provide SQL Version")
)

# copy SQL Server ini file
if ($SQLVersion -eq "2008")
{ $source = "c:\ConfigurationFile2008.ini"
$ini = "c:\ConfigurationFile.ini"
Copy-Item $source -Destination $ini
} elseif ($SQLVersion -eq "2012")
{ $source = "c:\ConfigurationFile2012.ini"
$ini = "c:\ConfigurationFile.ini"
Copy-Item $source -Destination $ini
} elseif ($SQLVersion -eq "2014")
{ $source = "c:\ConfigurationFile2014.ini"
$ini = "c:\ConfigurationFile.ini"
Copy-Item $source -Destination $ini
} else
{"The SQL Version could not be determined."
}
Destination: C:\copyfile.ps1
Overwrite: Enabled
Click Add > Guest OS > Run Program and configure the step as follows:

Step Name: Run Copy INI File
Step Execution: Always execute
Timeout: 300
Credentials: Choose local credentials that will work on the deployed VM's guest Operating System
Program Output: Capture program output as comment
When Program Fails: Mark workflow step as failed: do not proceed
Command Line:
powershell.exe -ExecutionPolicy Bypass -command "&{c:\copyinifile.ps1 "#{target.settings.customAttribute['SQL_Version']}"}"
Click Add > Guest OS > Run Program and configure the step as follows:

Step Name: Install SQL Server to VM
Step Execution: Always execute
Timeout: 1800
Credentials: Choose local credentials that will work on the deployed VM's guest Operating System
Program Output: Capture program output as comment
When Program Fails: Mark workflow step as failed: do not proceed
Command Line:
d:\setup /Q /ACTION=install /ConfigurationFile=c:\ConfigurationFile.ini /IAcceptSQLServerLicenseTerms=True /INSTANCENAME="#{target.deployedName}" /INSTANCEID="#{target.deployedName}"

(Make sure you set the correct path to the setup files, which will be the drive where you mounted the ISO)
Click Add > Execute Script and configure the step as follows:

Step Name: Unmount ISO File
Step Execution: Always execute
Timeout: 300 seconds
Script Output: Capture script output as comment
When Step Fails: Mark workflow step as failed: do not proceed
Command Line: powershell.exe -ExecutionPolicy Bypass -command "&{c:\scripts\unmountimage.ps1 "#{target.remoteId}"}"
Click Next and on the Assigned Components page, select the appropriate components for which this completion workflow should be run.
Click Next and Finish.