How to deploy SQL Server as a service to your private cloud

By John Joyner

Takeaway: John Joyner explains the time-saving procedure of deploying SQL Server as a service-oriented application via Microsoft’s Virtual Machine Manager.

Most IT pros do not enjoy installing Microsoft SQL Server. The SQL setup user interface has more clicks, review screens, and waits between sub-steps than just about any other Microsoft server product! Add the SQL service pack and update roll-up after that, and you could be looking at a few hours to manually set up an instance of SQL server. Removing the chore of deploying the server product is just one benefit of changing the way you architect SQL servers in your organization.
The information needed to customize your specific SQL service instance is a small subset of the information needed to configure a whole SQL server or cluster of SQL servers–so less information and “head work” is needed to deploy new SQL instances. As a side benefit, deploying your SQL servers from service templates will automatically document and standardize your SQL servers moving forward.

Efficient mingle of virtualization and application

Microsoft added application deployment support to the new release of System Center 2012 Virtual Machine Manager (VMM). VMM natively deploys instances of Microsoft SQL Server, as service-oriented applications, to private clouds managed by VMM. This is a great private cloud story for Microsoft, blurring the line between the virtualization platform and the application. Here are some additional advantages and scenarios enabled by deploying and managing SQL Server services this way:

• Reduces errors (and risk) in large scale, repetitive, and over-time application deployment scenarios.
• Conceptually enables future switch-out of the back-end SQL service to cloud SQL service providers such as Windows Azure, with little to no application re-architecture.
• Include SQL Server as a Service in VMM metered charge-back reports; manage SQL resource demand and consumption in a new way that is service-provider oriented.
• VMM storage classification can make efficient use of Storage Area Network (SAN) resources by consistently locating new instances of SQL server in pre-assigned storage pools that are optimized for SQL workloads.

There are three (3) broad steps in the process to deploy SQL Server as a Service to your Microsoft-managed private cloud. A pre-requisite step to this procedure is that you have installed SQL server in ‘sysprep’ mode inside a VM which was then sysprep’ed. In the rest of this article, we’ll (1) create a SQL Server Profile in VMM that uses the VHD image from the sysprep’ed VM, then (2) create a VMM Service Template that includes the SQL Server Profile as an application, and finally (3) deploy an instance of the service (a new VM running SQL Server) to a private cloud in a few clicks.

Step 1 - Create a SQL Server Profile

The process of deploying SQL Server as a Service with System Center 2012 VMM starts with creating one or more SQL Server Profiles that contain the settings you would specify during a manual installation of a SQL Server instance. Figure A shows the detail on one of the configuration pages of the Create SQL Server Profile wizard. An arrow highlights where you specify in the wizard whether you want to use Windows or mixed mode SQL authentication, and if the latter, what you want the powerful SA (SQL administrator) password to be.

Create a SQL Server Profile for each unique SQL configuration you will deploy.

Step 2 - Create a VMM Service Template

In a previous article, I covered the basics on how to create a service template. Another new feature in System Center 2012 VMM is the ability to install and configure applications inside the guest VM. These are added as properties to the VM Template. Microsoft’s SQL Server has full support in this release of VMM as a selectable application to bundle with a VM to make a complete stack and deliver that as a service.
Microsoft leverages the fact that SQL Server itself is sysprep-aware. You pre-install SQL with the sysprep advanced option in your VM image before running sysprep in the operating system (OS) of the VM. After deploying a fresh VM instance, VMM runs SQL setup (with the configuration settings provided in Step 1 above) for you–after joining the VM to the domain, thus completing the other half of the SQL sysprep process.
The SQL Server service template, seen in Figure B diagram view, includes in the center, a single machine tier. That tier consists of one VM that includes a SQL Server Profile deployment to install as part of this VM instance.

The virtual machine tier of the SQL Server Service application.

There are just of couple of special things about the SQL Server service template. First, is the addition of the SQL Server Profile as an application to install in new VM instances for this service. Second, is selecting a VM Template as the basis for the machine tier that includes a ‘sysprep-ed’ installation of SQL Server in the VHD-based image.

Step 3 - Deploy the service

Finally comes the amazing part, deploying a domain-joined, dedicated instance of SQL Server in three (3) clicks.

1. Select the Service Template for the SQL Server service and click the Configure Deployment button in the ribbon.
2. Type your name for this SQL Server application instance (this will not be the name of the VM), and in the drop-down list, optionally change which private cloud to deploy the service into, and click OK.
3. A diagram view of the service that is about to be deployed will pop up. If everything looks OK, click the Deploy Service button in the ribbon.

Then after a lunch break, observe your new Windows Server service VM running SQL Server and ready for production use.

Figure C

VMM does all the work to deploy the SQL Server application in a ‘parallel execution step’.

Downsides in this release:

• No support for automatic deployment of clustered SQL instances or support for other clouds, such as Windows Azure.
• Only the database and reporting services (SSRS) components of SQL server can be installed automatically (this is a limitation of SQL sysprep, not VMM). If you need to install SQL analysis services (SSAS), you need to do that manually after an automatic service deployment. TIP: If you need the SQL Management Studio (admin console) on the SQL server VM, you can pre-install just that part, after sysprep install of the base SQL product, and before sysprep of the VM.