...building hybrid clouds that can support any device from anywhere
“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!
A typical SQL Server component request might consist of a combination of these:
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.
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.
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.
Fear not! Here are two additional posts going over more details regarding the SQL Server Self-Service Kit:
Gimme the details (part 1)
Oh, and we also have a video for you, going through a use case (new database on a new dedicated server):
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.
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?
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.
Very Interesting. I am going to deploy this kit and update you. Thank you so much. Lot of DBA will be benefited