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

 

image_thumb9[1]

User experience in the Service Manager service catalog (optional)

image

Log entries in Orchestrator events

image_thumb16

Farm was scaled out in VMM

image_thumb14

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.

image_thumb19

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

image_thumb67

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)

image_thumb2

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.

image_thumb9

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)

image_thumb11

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.

image_thumb13

Email output

image

Logs in Orchestrator (notice how the ITSM request was auto-approved in that case, since it was a “shared” request and not a dedicated one)

 

Sample execution #3 : Requesting a “SQL Server-enabled” environment

This is leveraging the “STEK” multi-tier deployment.

image_thumb24

Service Request in Service Manager

image_thumb65

End to end process when deploying a SQL Server-enabled environment

image_thumb27

Subroutine handling the actual service template deployment via PowerShell