Homelab - Deploying SQL 2012 Enterprise using Puppet

Homelab - Deploying SQL 2012 Enterprise using Puppet

· by CodyDe · Read in about 8 min · (1572 words) ·

Wow, finally, my first homelab post!

My homelab is my primary test bed before implementing things in my production environment. All of my vRealize Orchestrator (vRO) workflows are built and tested here first in “work like” conditions, before I promote them into development at work.

One of the most recent items I've been working on is a Database as a Service (DBaaS) offering, Essentially, this offering would leverage several Microsoft SQL 2012 servers to allow our end users to provision individual databases to a host. This is great for your clients who don't want to deploy a full size SQL server and deal with all those costs from both a virtual infrastructure perspective and most of all, SQL licensing.

The problem is - I'm lazy. I've installed MANY SQL servers, and the idea of doing 3 more in my lab didn't have me excited. Yeah, I could do it via Powershell, or other scripted methods. With that said - we're installing SQL in my enterprise utilizing Puppet, and I'm terribly unfamiliar with it's “inner workings” at this point. I saw this as a great opportunity to throw puppet in the lab, and see if I could figure out how to build SQL servers with it. Afterall, that's what our labs are for, right?!

This is a Puppet noob, seeing if he can figure out how to do something many other people have done, in hopes of getting exposure to the platform. I'm sure there are better ways to do what I end up doing. This was just an example of what “can” be done.

Free Licenses…kind of…but it works!

I headed over to PuppetLabs and signed up for their trial. The trial grants you 10 complimentary licenses, and access to the Puppet Enterprise Console. We won't be able to download the “Enterprise Supported” modules, but we still have access to thousands of community driven modules on the site!

After signing up for the trial, I was able to pull down the tarball for installing Puppet. I have a CentOS 7 template in my lab, which I spun up an instance to leverage for my PuppetMaster/Console/DB under a monolithic deployment.

vSphere Puppet VM Hey its a Puppet VM!

I upped the Memory and CPU probably a little more than needed. I have capacity, who cares :)

Once the machine deploys, we create entries in our DNS to match the hostname “humblepuppet01.humblelab.com” in this case. We connect via WinSCP and copy our Puppet Enterprise installer over to /tmp/

WinSCP to Puppet Master Hey its WinSCP'ing all the things!

We'll switch over to /tmp/ and untar our enterprise installer and start the installation. The screenshots don't show us getting prompted for the guided install - but we choose yes and proceed forward!

Puppet installation text

After completion, we're prompted with a message indicating the install was successful, and to browse to our FQDN and port 3000 to continue the configuration. Make sure your Linux firewall is configured to allow you to connect inbound. In a private homelab you may disable it entirely - but I wouldn't recommend it.

Install complete! Install complete

Ah…A familiar friend…a WEB GUI!

When we connect to https://humblepuppet01.humblelab.com:3000 were presented with a a pretty straight forward installation process. We'll select Monolithic, and fill in our appropriate details. The screenshots below don't show the administrative password setting box - but know that you'll need to set that. Once the installation completes you'll select the option to start using Puppet Enterprise. It takes about 10-15 minutes to complete at most.

Select Monolithic Select Monolithic Installation

FQDN Configurations Enter appropriate configuration details

Proceed forward, ignoring the size error in my case… Proceed forward, ignoring the size error in my case…

Completed Installation Complete!

Back to our command line and Putty

We'll need to find a SQL module. For the sake of the blog, we're going to speed this along a bit. A fair amount of googling led me to the “puppet module search sql” command to search Puppet Forge from within our VM. In my search, I found the jriviere/windows_sql module, which fit the requirements. Coupling this with the jriviere/windows_isos module we are able to mount the install ISO and install directly from it.

We install our content with the following commands

puppet module install jriviere-windows_sql puppet module install jriviere-windows_isos

Puppet Forge jriviere/windows_sql

Puppet Module Installs Lets install some modules - jriviere/windows_sql and jriviere/windows_isos

With these modules installed - we're now ready to install the puppet agent on our Windows Server 2012 R2 Instance

Oh hi Windows, i'm going to introduce you to Puppet

This is a quick section. We're going to use the official puppet agent MSI installer and install Puppet on a Windows 2012 R2 server. Ensure that as you progress through the next screens, you enter your Puppet Master name as seen in the screenshot below.

[Puppet Master FQDN Enter your Puppet Master FQDN

With our agent installed, we'll head back into our Puppet console from earlier. Select the Nodes / Inventory tab. Select the Unsigned Certificates screen and we'll see our Puppet node. Accept the node. This system is now managed by Puppet!

Sign Puppet Node Accept to sign your Puppet Node Cert

What the fact, let's classify a node!

With our Agent installed, and our modules installed on our Puppet Master, its time to classify our node. We're going to create a SQL classification to work with, and add our new Windows Server to that classification so the Modules run against it!

First, lets create our classification. Head to Nodes / Classification in the Puppet Enterprise Console. Enter SQL and select Add Group with the values I have set in the screenshot.

Node Classification Classify that node!

If all went according to plan - you should be able to select our SQL classification and see node matches of “1”. Great progress!

A couple things to check if your not getting any results -

  • Verify that your time settings are correct by installing NTP

  • After verifying your time is correct, log into your soon-to-be SQL box and run (from an administrative prompt…) “puppet agent -t” to force a sync

We're getting there…

So now we have Puppet talking to our Windows Server. We also have Puppet classifying our node correctly so its ready to accept the SQL installation bits. In order to move forward, we'll need a ConfigurationFile.ini for installing SQL. This ini file contains the predefined parameters for installation of SQL; things like the baseline configuration options as well as installation locations and accepting the pesky licensing agreement. I've included a sample of what mine looks like here - make sure you make changes to yours to match your environment. Creating one is extremely easy - google it :)

Configuration INI Sample Configuration File - update to match YOUR environment

I chose to store items like this in C:\Temp - we'll be needing that location soon enough!

Now, we'll head back into the Puppet Console and select Notes > Classification > and our newly created sql classification. Select the “Classes” tab, and type in SQL. We'll select windows_sql followed by “Add Class” on the right.

We're now going to define out the parameters for installation. These are important as puppet is going to be using these to do everything from Mounting the ISO, to configuring the SQL installation. I've included the key items that I needed to include in the screenshot below! Someone more knowledgeable than I might be able to tweak these in a different way - but its what worked for me!

A couple things to note…

* You'll obviously need a copy of the SQL server ISO stored on a network drive. I used a random fileshare server I have in my lab.

* Choose a password that meets the appropriate complexity requirements

* Make sure no pesky firewall rules are blocking your file share connectivity

SQL VariablesThese values worked for me, some tweaking for your environment may be necessary!

With those set, we will Commit our changes (bottom right corner, extremely important!) And we're ready to send our Puppet agent out on a run!

What a long strange trip it's been…time to deploy!

Log into your soon to be SQL box, and run the “puppet agent -t” command from an administrative prompt. If you followed the steps correctly - you should see something similar to the below firing off!

Agent Run Screenshot Run ‘puppet agent -t’ to force the puppet agent to run, or wait for it its timed run to commence automatically

With any luck - about 15 minutes later, you should be presented with a successful SQL installation. The real beauty of this is now you've done all the heavy lifting. If you want to install additional SQL servers, its as simple as dropping the ConfigurationFile.ini file into C:\Temp and adding additional nodes to your SQL classification. The Puppet agent will run at pull down your SQL installation off the network drive.

Success!Successful puppet run in 704 seconds!

One item I will note - I ran into some issues getting SQL to accept the Windows Security mode where it would add my Windows ID in. I'm sure its an easy fix - and I just need to swap around some values - but it wasn't critical to this blog post. I was able to successfully login with the SA password I configured in the Puppet console - and all services were running!

Services RunningIts so pretty!

That's all I got for this one folks! Hope this helps some people out and gets many of you exposed to Puppet. It's a powerful tool and this just barely scrapes the surface of what it can do!

Stay safe out there!