SQL Server Self-Service Kit : Gimme the details (part 1)
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.
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.
Service Request in Service Manager
End to end process when deploying a SQL Server-enabled environment
Subroutine handling the actual service template deployment via PowerShell