SQL Server Self-Service Kit : Provisioning SQL Server as a Service with the System Center stack

“Can I use System Center to provision SQL Server components?”… “Can I provide database as a service to my business users or application owners?”…

The ability to automate SQL Server provisioning is probably one of the top customer requests I’ve heard about over the last 12 months!

The goal of this post is not only to confirm that System Center can indeed provide this capability, but also to provide a downloadable sample solution – the “SQL Server Self-Service Kit” – that you can reuse and adapt to your own needs.

Feel free to share your experience using this sample solution!


Overview and Scenarios

A typical SQL Server component request might consist of a combination of these:

  • SQL Server database or SQL Server instance
  • Dedicated or not (i.e. do you want this to run on your own isolated machine/VM, or on a “shared” environment)
  • Highly available or not (i.e. should the component be deployed on a cluster)

Alternatively, someone may also be interested in getting a multi-machine environment including SQL Server, where an application could be dropped.

Taking into account all the combinations, this leaves us with a certain number of deployment types, 9 of them actually!

image

The downloadable solution associated to this blog post covers these scenarios, and we’ll now explain how they work, and the prerequisites to use them in your own environment.

Beyond automating the deployments themselves, you might also be interested in the self-service functionality, and the solution also provides an optional example in that area.

 

Get the sample solution!

clip_image002

Make sure you go through the 2 deep-dive posts, including “Gimme the details (part 2)”, which covers the installation and configuration instructions for this sample solution.

 

What do you get, and how is each deployment type handled?

The package you will download includes different components:

At the fabric level, 18 Orchestrator Runbooks cover 7 different deployment scenarios:

4 “Shared” scenarios

  • Deploying a new DB or instance on a shared existing standalone server or cluster
  • Under the hood, Orchestrator leverages SQL Server unattended installs and PowerShell commands, to “drop” a DB or instance on an existing standalone machine or an existing cluster. When deploying an instance on a cluster, the Runbooks use the available disks in the cluster as a “pool”

2 “Dedicated” scenarios

  • Deploying a new dedicated instance or DB on a new standalone VM, as part of a “SQL Server virtual farm”
  • Under the hood, Orchestrator leverages a Virtual Machine Manager (VMM) service template and SQL unattended installs to create and scale out a farm of SQL Server instances. The “farm” is created if it does not exist yet, otherwise it is just scaled out. In the case of a database, the instance is deployed first, and then the DB is created.

1 “SQL Server-enabled environment” scenario

  • This deploys multiple VMs including SQL Server
  • Under the hood, Orchestrator leverages the STEK template from my colleague Shawn Gibbs. You can then deploy an application in this “SQL Server-enabled environment”.

At the process level, an optional Service Manager management pack showcases the following scenarios:

•Service catalog webparts for self-service

•An approval process for “dedicated” scenarios (Runbooks automatically approve “shared” requests)

•Automatic discovery of user executing the request, for notifications and granting user rights to deployed DB/instances

Alternatively, you can choose to just use the fabric components (Runbooks and service template), and integrate them with your own self-service. We will see that the Service Manager integration has been crafted so that it has no hard dependencies : Should you wish to use them, the SM-enabled Runbooks monitor service requests of a specific type and in a specific condition, update these requests and/or call Orchestrator Runbooks. You can read more details on what the SM integration adds into the solution, in the deep dive posts (see links below).

 

image
Runbooks

image
Service Template

image
Service Requests

 

Note : As a careful reader, you may have noticed that I mentioned 9 deployment scenarios, and 7 are covered in the solution. The 2 deployment types not handled by the SQL Server Self-Service Kit v1.0 are deploying deploying a DB or instance on a new cluster. This has been added in the v2.0 of the SQL Server Self-Service Kit, and you could still use the Orchestrator Runbooks from v1.0 and add the new cluster templates discussed in v2.0 – see the “Update” note at the end of this post.  Also remember you can adapt the Runbooks to map your processes, since you may elect to support only some of these deployment types, depending on your uses cases.

I want to learn more!

Fear not! Here are two additional posts going over more details regarding the SQL Server Self-Service Kit:

  • Gimme the details (part 1)

    • Sample execution #1 : Requesting a new dedicated instance without high availability
    • Sample execution #2 : Requesting a new highly-available database on a shared environment
    • Sample execution #3 : Requesting a “SQL Server-enabled” environment
  • Gimme the details (part 2)
    • What do I get if I use the Service Manager integration?
    • Using the package : Installation and configuration
    • Some tips and tricks worth highlighting

 

Oh, and we also have a video for you, going through a use case (new database on
a new dedicated server):

[View:https://www.youtube.com/watch?v=ziNa2wxbbhI]

Supported versions

These deployment scenarios were tested with Windows Server 2012 and SQL Server 2012.

System Center 2012 SP1 was used, including the Orchestrator, Virtual Machine Manager and Service Manager components.

Note that similar principles should apply to earlier versions of SQL Server too, with a different set of unattended (INI) files for examples.

Update: This first release works with both 2012 SP1 and 2012 R2 components of System Center. An updated SQL Server Self-Service Kit (v2.0) has been released and is available here, It is also called “SMA Edition”, because it showcases how you can achieve similar scenarios using the new Service Management Automation (SMA) feature from System Center 2012 R2, and explains how to add the two remaining “new cluster” scenarios. You can refer to section named “Which version of the SQL Server Self-Service Kit should I use?” in the previous link, for clarification on the best use cases for each release.