SQL Server Self-Service Kit – Updated for System Center 2012 R2 and SMA!

Article
10/23/2013

It’s been about a month since the first release of the SQL Server Self-Service Kit (SQLSSK), which provides System Center artifacts to automate deployment and self-service of SQL Server components.

As a sample solution, the SQLSSK supports deploying a database/instance on a new/shared standalone server, deploying a database/instance on a shared cluster, deploying a SQL Server-enabled environment of two virtual machines.

If you are not familiar with what the kit is and what it does, you can read all about it here.

The first release leveraged System Center 2012 SP1 components, including Virtual Machine Manager, Orchestrator, and optionally Service Manager for Self-Service. At the time, it felt natural to rely on the SP1 stack, since it’s what most of you are likely running in production today. Now, with System Center 2012 R2 being generally available since last week, some of you may soon want to achieve similar scenarios using new/updated technologies from the R2 wave, like Service Management Automation (SMA).

Well, fear not, we’re here to help! Today, I am happy to publish the SQL Server Self-Service Kit 2.0. I like to call this version the “SMA Edition”, since the deployment types from v1.0 are still present and are now executed with Service Management Automation (SMA) Runbooks. SMA is a new feature in System Center 2012 R2, and you can read all about it in this excellent post series from my peers Charles and Jim.

Also, this release includes instructions and scripts to enable the 2 remaining deployment types not covered in the first release: deploying a database/instance on a new cluster. This relies extensively on the work published by Kurt Scherer in this post, using the new shared VHD feature in Windows Server 2012 R2 and System Center 2012 R2. And, finally, some enhancements have been made to logging and to simplify configuration

Enjoy, and feel free to share your experience using this updated sample solution!

Note : Perhaps one of the main benefits of this v2.0 release is that you can easily compare how SMA Runbooks and Orchestrator Runbooks can be used to achieve the same scenarios. That does not mean you cannot use v1.0 with System Center 2012 R2. Be sure to review the section “What version of the SQL Server Self-Service Kit should I use?” below!

Download and installation instructions

The download is here, and updated installation/configuration follow in this post.

And the following blog post covers installation instructions, including how to enable the new clusters deployments , should you wish to do so:

SQL Server Self-Service Kit – Installation Instructions for the SMA Edition

Note : You will need Jim Britt’s excellent SMART for Runbook Import Export and Tool to import the Runbooks in your SMA install. The instructions cover this requirement.

Content and Overview

18 SMA Runbooks cover 9 different deployment scenarios:

4 “Shared” scenarios for standalone servers and clusters	Deploying a new DB or instance on a shared existing standalone server or cluster Under the hood, SMA leveragesSQL Server unattended installs and PowerShell scripts, to “drop” a DB or instance on an existing standalone machine or an existing cluster. When deploying an instance on a cluster, the Runbooks use the available disks in the cluster as a “pool”
2 “Dedicated” scenarios for standalone servers	Deploying a new dedicated instance or DB on a new standalone VM, as part of a “SQL Server virtual farm” Under the hood, SMA leverages a Virtual Machine Manager (VMM) service template and SQL unattended installs to create and scale out a farm of SQL Server instances. The “farm” is created if it does not exist yet, otherwise it is just scaled out. In the case of a database, the instance is deployed first, and then the DB is created.
2 “Dedicated” scenarios for clusters	Deploying a new dedicated instance or DB on a new cluster Under the hood, SMA leverages a VMM service template to deploy the cluster and SQL unattended installs to deploy a clustered SQL Server instance. The VMM service template is not provided because already well documented in this post (see the instructions), but cluster deployment scripts to be included in the service template are provided and have been tested. The Runbooks also include the code to deploy the service template and install SQL Server on the new cluster.
1 “SQL Server-enabled environment” scenario	This deploys multiple VMs including SQL Server Under the hood, SMA leverages the STEK template from my colleague Shawn Gibbs. You can then deploy an application in this “SQL Server-enabled environment”.

Here is a schema explaining how everything fits together:

At step 1, Runbooks are being executed from the SMA administration interface, which you can access through the Windows Azure Pack management portal. Runbooks could also be executed via PowerShell.

Launching a Runbook from the SMA interface looks like this (this example would deploy a new named instance on a shared cluster)

Launching the same Runbook through PowerShell would look like this (notice the way you pass parameters):

Note : In v1.0 of the SQLSSK, an optional management pack was provided for the Service Manager service catalog. This time, since the main focus of this first iteration of the SQLSSK for System Center 2012 R2 is to showcase deployments through SMA, no specific front end is provided. The solution is flexible enough to work with the Windows Azure Pack portal (stay tuned, we are going to publish some portal customization examples very soon) or with your own portal/process calling the SMA Runbooks.

At step 2, SMA Runbooks do all the magic of automation, calling the right System Center artifacts depending on the deployment type. These can be one or a combination of the following : Virtual Machine Manager service template, SQL Server unattended installation scripts, PowerShell/CMD scripts. The log shows the progress of our example installation, going through 4 typical steps : placement, deployment, delegation, notification.

At Step 3, the user is notified of the result of the deployment, explaining the location of the database/instance/environment that was deployed and delegated:

Of course, you could customize the email based on new services, or services being soon decommissioned in your organization.

Note : When it deploys Virtual Machines, the SQLSSK does not surface them into the “Virtual Machines” part of the WAP tenant portal : Rather, notifications are being sent at the SQL Server component level, since access is being granted at the database or instance level. In the next iteration of the SQLSSK, you can expect tighter integration with the Windows Azure Pack portal and features (including leveraging the SQL Server databases provisioning built-in feature).

Requirements for SQL Server Self-Service Kit v2.0 – SMA Edition

From a System Center perspective, the following System Center 2012 R2 components are needed:
- Service Management Automation (SMA) needs to be installed.
- Virtual Machine Manager is also used to deploy new virtual machines and services, when the corresponding deployment type is used.
Windows Azure Pack (WAP) will also be needed, so you can easily work with SMA Runbooks (the SMA management interface is part of the WAP management portal).
Windows Server 2012 R2 hosts are needed if you want to leverage the instructions to enable deployment of NEW clusters for databases/instances – since this deployment type specifically relies on the new Shared VHD feature.

What version of the SQL Server Self-Service Kit should I use?

Short answer would be : If you are starting to work with SMA or moving to SMA, using SQLSSK v2.0 (a.k.a. SMA Edition) with System Center 2012 R2 will make more sense. If you are an Orchestrator user, using SQLSSK v1.0 with either System Center 2012 SP1 or R2 will also work.

Apart from the new cluster scenarios, the same deployment types are covered by both versions, using different automation technologies. We wanted to make sure you have sample templates, scripts and Runbooks leveraging features from each System Center version.

The two main dependencies SQLSSK v2.0 has on System Center 2012 R2 are:

Service Management Automation (SMA)
The instructions to deploy new clusters, which require new features in Virtual Machine Manager

So, for example, you could even use SQLSSK v1.0 on System Center 2012 R2 and customize it to add the new clusters deployments from SQLSSK v2.0.

Confused? Here is a full breakdown on the compatibility matrix:

Self-Service:
- v1.0 provides an optional Service Manager management pack. This works with System Center 2012 SP1 Service Manager or System Center 2012 R2 Service Manager.
- v2.0 does not provide a specific GUI, and you can use the one you want, leveraging how integration was showcased in v1.0
Automation:
- v1.0 uses Orchestrator Runbooks. This works with System Center 2012 SP1 Orchestrator or System Center 2012 R2 Orchestrator .
- v2.0 uses Runbooks based on SMA, part of System Center 2012 R2.
VMM service template for standalone VMs:
- v1.0 and v2.0 ship the same service template, which can work with System Center 2012 SP1 Virtual Machine Manager or System Center 2012 R2 Virtual Machine Manager.
VMM service template for new clusters:
- v1.0 did not support this scenario
- v2.0 provides instructions for this scenario, which requires System Center 2012 R2 Virtual Machine Manager

Some tips and tricks worth highlighting

The last section from this blog post from the v1.0 release highlights some of the hurdles/tips/tricks seen during creation of this solution, and how they were solved. These learnings still apply to how version 2.0 works, with the exception of ”CredSSP” authentication : It is not needed anymore to enable the CredSSP server role on target servers, as the Runbooks do that for you. You might want to disable this in the script, should you wish to manage CredSSP on your own.