Application Management – SQL Server using STEK

I constantly hear around the water cooler and in different places, ‘wouldn’t it be nice to have a SQL server service as a VMM service template?’ and my reply is almost always the same thing every time, ‘we do.’ A few months back I created a series of Service Template Examples I nicknamed STEK ‘Service Template Example Kit’. In this kit I considered 2 basic premises when building it. As a developer at heart I wanted to first have a way to rapidly deploy environments in which I could develop and test on. This is why there are multiple architectures in the example kit representing multiple tier application design principles. The other consideration was simply how can I offer a base for production systems to be built from, kind of starter kits. I called it the ‘just add apps’ approach of building templates with the assumption again that multiple tiers need to be considered. As a developer in the .NET world I didn’t consider that a developer would want a SQL server without some form of application server with it. I learn something new every day so why would now be any different.

image

Figure 1 STEK - Service Template Example Kit

 

Since blogging about STEK I have blogged about adding applications to it and even given examples to follow. In one particular scenario I use the example kit to deploy DotNetNuke community edition with the accompanying SQL server database in form of a DACPAC for the schema and a T-SQL file for populating data and creating appropriate permissions.

To offer a SQL Server service to your client base it requires a few things, first a service that is deployable, repeatable, and manageable. Step one, download STEK and follow the appropriate steps to sysprep the two required VHD’s. One for the operating system stand alone and another for the operating system / SQL server in prepped state.

To help, here are where you can find step by step instructions on the sysprep process. For SQL sysprep click here. I also used a PowerShell script from the TechNet gallery to perform the sysprep for me and it can be found here.

Once you follow the directions as part of the tool kit you have 4 new service templates in your SC VMM console ‘Library: Service Templates :’ view. You’ll notice 2 templates include a SQL server in them.

image

Figure 2 STEK Templates in Library View

 

To create the SQL only service template simply right click on the ‘2-Tier Scalable Web Server w SQL’ template in the consoles service template view select copy. This will create a copy of the template which preserves the name but modifies the templates ‘Release’ version to include the word ‘copy’. Right click on that new copy and select open design view.

In this designer view let’s select the very top level box which defines the name and release. This will open the properties in the properties edit window in the bottom of the designer view.
clip_image002

Figure 3 Modify the template in designer view

 

Here let’s modify the name, release and description which suits your needs. Next let’s right click on the ‘Single Scalable Tier’ and delete then let’s right click on the HTTPVIP (note: may be named different in you environment) load balancers VIP template and delete.

clip_image001

You now have a Single in stance SQL Server service template that can be automated through PowerShell, Orchestrator, App Controller and many other self-service solutions. The simple changes I would advocate when automating are the server name as this template will simply increment ServiceVM##### as the name and maybe add the user to the admin rights of SQL if it is a lab or test deploy. It would also be a good idea to create multiple examples with different resources levels like CPU and memory as the Silver, Gold and Platinum offering model, but this is true for all the STEK templates.

I do hope you find this useful as a single instance SQL server that can be used in development and test labs and in smaller deployments where the data requirements minor. But more importantly I hope this highlights how to extend the Service Template Example Kit to fit a wider range of requirements, as with many of the Service Templates and methods I blog about they can be extended to meet your needs.