Condividi tramite

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

  • Articolo

“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.

Comments

  • Anonymous
    October 23, 2013
    Hi Bruno, Firstly thanks for doing this, this is exactly what I have been looking for. I am looking at basing a solution for a client on this and someone mentioned Windows Azure pack. I am now doing some reading and trying to find out what exactly WAP entails and how it differs in what it can over me with respect to your SQL self service kit. You mention including use of it in your next release. Can you elaborate at all on the differences between the two in terms of the "scenarios" wrt deployment? regards Kevin

  • Anonymous
    October 31, 2013
    Hi Kevin This is a great question! The Windows Azure Pack (WAP) was released this month as part of the Windows Server 2012 R2 and system Center 2012 R2 wave. It contains a set of portal and APIs to provide a multi-tenant self-service and cloud computing experience on-premise, similar to what you get in Windows Azure. In fact. the WAP and Windows Azure portals have the same look and feel! You can read more about WAP here : www.microsoft.com/.../default.aspx As a product, WAP includes some scenarios covered by the SQL Server Self-Service Kit sample solution. In WAP, there are built-in features to deploy databases on a shared environment, as well as a gallery where you can provide templated VMs including SQL Server (we have many examples available here : blogs.technet.com/.../application-management-service-models-web-platform-installer-gallery.aspx) The SQL Server Self-Service Kit solution includes a few additional deployment types - like deploying a new DB on a new server or a new named instance on a new or shared server - , all end to end inclusive of approvals and pre-post processing, through Runbooks and possibly your portal of choice. With each release of the SQL Server Self-Service Kit, we're trying to showcase different ways of doing things, like with the v2.0 ("SMA Edition") showing how you could use Service Management Automation as the automation engine as well (see here blogs.technet.com/.../sql-server-self-service-kit-2-0-powered-by-system-center-2012-r2-and-sma.aspx). With WAP now available, you can expect the next update for the SQL Server Self-Service sample solution to showcase tighter integration with WAP, so you could use one method or the other, depending on your needs.

  • Anonymous
    February 21, 2014
    A couple weeks ago, my colleague Michael Greene posted a terrific set of VMRole Gallery Items for Collaboration

  • Anonymous
    May 23, 2014
    Very Interesting. I am going to deploy this kit and update you. Thank you so much. Lot of DBA will be benefited