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.

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.

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.

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.

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.

Comments

• Anonymous
  December 16, 2013
  This might seem like an overly obvious statement, but it is worth emphasizing: The early planning stages
• Anonymous
  March 18, 2014
  混合云策略的早期规划阶段是 IT 领导者和实施者最重要的讨论阶段，这似乎是显而易见的，但仍值得强调。
  去年大多数与我讨论过问题的CIO 告诉我，他们正在考虑在几年内转型到一个完全混合的环境 - 而且这一转型首先从评估其应用程序组合如何在这种基础结构内演化开始