SQL Server Self-Service Kit : Gimme the details (part 1)

Article
09/06/2013

This is the first of two posts getting into more details about the “SQL Server Self-Service Kit”. It goes through 3 sample uses of the SQL Server Self-Service Kit, to highlight how the solution works and what the outputs are.

Table of content of the full series

Introduction post
- Overview and scenarios
- Download link
- What you get, and how each deployment type is handled
- Supported versions
- Looking ahead
Gimme the details (part 1) – this post
- 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

Sample execution #1 : Requesting a new dedicated instance without high availability

User experience in the Service Manager service catalog (optional)

Log entries in Orchestrator events

Farm was scaled out in VMM

Success email sent to the user

Sample execution #2 : Requesting a new highly-available database on a shared environment

For this example, let’s also look at which Runbooks are called one after another, so you can familiarize with how things are being called under the hood.

It all starts with a request, in my case using the Service Manager portal

The end to end process Runbook will go through that branch, first starting with placement (determining where to create the DB), then deployment (creating the DB) and then delegation (assigning rights to the requesting user)

Placement : The Runbook handling the placement is just using a variable to determine the target cluster. You can choose to customize this variable – as explained in the installation and configuration steps – or you could choose to customize this “empty shell” Runbook to include your own placement logic, querying another database or CMDB for example.

Deployment : This Runbook uses SQLPS cmdlets to create the DB, and just includes some specific logic to do it differently, depending on the target being a cluster or a standalone server (the same Runbook is called for the two deployment types)

Delegation : This very simple Runbook also uses SQLPS cmdlets to assign DB ownership to the requesting user. The user details were provided as input parameters in the main end to end process, either “manually”, or automatically when Service Manager or another ITSM solution is being used as the frontend for the process. When deploying instances, the user is given sysadmin rights on the instance.