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!
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!
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 |
|
2 “Dedicated” scenarios |
|
1 “SQL Server-enabled environment” scenario |
|
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).
|
|
|
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:
-
- 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
-
- 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.