In SCVMM 2012 we gain the ability to have SCVMM deploy and install SQL for us as a part of a service deployment. I’m going to cover how to prepare a VM with a SQL preinstall image, as well as how to have SCVMM deploy and configure the SQL instance, and then load a database package on that instance as a part of a service deployment. I’m also going to cover a less well known feature of SCVMM 2012: pushing a database as a part of a service deployment to an existing SQL server. Finally, to save you the time of testing them, I’m going to cover a few additional ideas for deploying SQL with SCVMM that we’ve tested that don’t work.
Creating a VM image with a prepared SQL instance:
To start you’ll need to have an existing syspreped Windows 2008 R2 image in your library. You’ll also need to have the SQL 2008R2 installation files extracted to a directory that is accessible from the VM you’ll be creating. (we’ll need to copy this to the VM)
First deploy a VM from the base operating system image:
Give it a name like SQL Base
Set the hardware up appropriately for SQL. Make sure you connect the network to a network that can access your SQL install location
Set the local admin password on the configure operating system screen. Remember you wont have access to domain joined run as accounts unless you domain join the machine.
Next deploy the machine to either a host or a cloud depending on your configuration.
If necessary set a computer name for cloud deployment
Click next, Next and create to confirm and create the VM. Wait for the VM to deploy and then start and connect to it.
Login with the Admin password created earlier.
Create a directory on the root disk to hold the SQL installation files. Make note of the directory name, you will need this later.
Copy the SQL installation files to this directory. You only need to copy the architectures that you’ll actually use, in this case x64.
Run Setup.exe from the local folder, accept that it is going to enable the .net framwork role and click ok
(alternately scroll down a bit for the command line setup call)
Wait a moment and then at the splash page select “Advanced”
Click Image preparation of a stand alone instance of SQL Server
Click Install to install the support files
Accept the license terms and click next
Select the features you’d like preinstalled. Typically I select them all to make imaging simpler.
Enter an instance ID (this is not the instance name, but rather an identifier for this installation). Make note of this, you will need it later.
Click Next, Next, Prepare, Close. and then close the installation splash screen.
You’ve now installed a prepared instance of SQL. Alternately you can install this prepared instance from the command line by executing the following from within the SQL setup directory, again make note of the instance ID:
Setup.exe /q /ACTION=PrepareImage /FEATURES=SQL,RS /InstanceID = SQL1 /IACCEPTSQLSERVERLICENSETERMS
Now run Sysprep.exe from the c:\windows\system32\sysprep directory.
Select Enter System out of Box Experience (OOBE), check the Generalize checkbox and select shutdown. Click OK.
This will sysprep the VM and shut it down to prepare it for being deployed by VMM.
Next, Locate the VHD file from the now shut down VM (Mine was is my CSV volume) and copy it into your Library share manually. Rename it to something descriptive. Optionally, compress the VHD file using hyper-v manager. Refresh the library and delete the original VM and VHD file from VMM.
You should now have the syspreped Windows 2008R2 SP1 + SQL 2008 R2 vhd in your library.
Creating a SQL profile:
Next we need to create a SQL profile. The SQL profile tells VMM the settings to use to complete the SQL installation at deployment time. It can include a SQL settings file to set additional options that are not requested in the wizard.
Click on the library tab, then click create –> SQL Server Profile
Give the profile a meaningful name and description
Click SQL Server Configuration, then click add SQL Server Deployment
(Note, if you prepare multiple Instance IDs on the image prior to sysprep, you can deploy multiple instances of SQL with VMM on the same machine by adding multiple SQL Server Deployments here)
Provide a deployment name, Instance Name, Instance ID (from the preparation stage above), and product key (if needed, some media has the product key embedded in which case leave this blank). Select a run as account to perform the installation under.
Click configuration. Enter the path to the local install directory on the VM (where you copied the SQL install files to)
Add the SQL server administrators groups.
Set the SA password and security mode if needed.
Check Use TCP/IP for remote connections if needed.
Specify a configuration file location for advanced configuration settings.
Click Service Accounts, Select the run as accounts to use for each service account requirement
Congradualtions, you’ve create a SQL Server Profile.
Next we need to create a VM template that includes this SQL Server profile.
Click Create VM Template
Select “Use Existing VM template or a virtual hard disk stored in the library”
Click browse, select the VHD file copied into the library earlier.
Click next. Enter a name and description for this VM template.
Setup the hardware configuration as needed. Ensure you select the cloud compatibility profile for hyper-v
Click next. Enter the OS settings, including an Identity String, and Admin password/account (Admin account/password is required for SQL specialization), Product key (if needed), any roles or features to install and domain membership.
Alternately you can use an existing OS profile.
Configure any applications that will be installed alongside this SQL instance. (I typically leave this blank so that the template can be used by multiple applications). Click next
Select the SQL Server Profile created earlier
Click next, click create.
Your done, you now have a VM template that, when deployed in a service, will deploy a fully installed instance of SQL Server 2008R2. It is important to note, you must deploy the VM template as a part of a service for VMM to install SQL. Otherwise it will ignore the SQL specialization section of the template.
Creating a simple service template to deploy the SQL server and SQL package
Click Create service template on the library screen in VMM.
The Service Template designer opens and asks you to choose a pattern. Choose single machine. Name the template and provide a release.
You now have the designer open. Drag the VM Template from the left that you created for SQL to the Single tier box and let it go.
You should see the settings from the template populate the tier. Click add logical network from the toolbar and select a network to connect the VM to.
Click the connector and connect NIC 1 to the network.
Click save and validate
Click the machine tier and correct any validation errors.
Click save and validate again and then to deploy the service click configure deployment. To deploy at another time simply close the designer.
Give the deployed service a name, click ok.
A new designer opens. Any deployment time settings are requested on the lower left. If everything looks correct click deploy service.
Click the deploy button
Your service is deploying. Once complete you will have deployed a single VM with a configured instance of SQL. You can extend service templates to include many additional roles included deploying SQL DAC packages, web deployment packages, server app-v packages, and even deploying a DAC package to an existing physical SQL server.
Now for some things that don’t work:
Deploying the SQL image separately from the prepared vhd. VMM requires that SQL be prepared and installed on the VHD prior to sysprep. You can not perform a script based deployment of the preparation prior to VMM attempting to customize the SQL instance because it will run the script after it attempts this customization and fail. You also can not install SQL to a second disk, detach this disk and then attach it to another syspreped VM. SQL preparation does write registry keys and the specialization process looks for those.