Day 97: Deploying SQL Server 2012 using Chef – Part 4

In last week’s post, Day 92: Deploying SQL Server 2012 using Chef – Part 3,we modified our SQL Server 2012 Recipe to include a SQL Custom Configuration File. Additionally, we used two accounts for the SQL Services to be installed with.

In the final post of this series, we’ll be adding our Data Bag Item to our Chef Server. Additionally, we’ll be making a couple of changes to the SQL Server 2012 Recipe from last week before deploying it to our Windows Server 2012 R2 Host from the Chef Server.

 

Prerequisites

Upload your encrypted_sql2012_data_bag_secret file that we created back in Part 2 of this series to a Web Server in your environment. By storing this file on a Web Server, it doesn’t have to reside locally on any Windows Host you wish to deploy the SQL Server 2012 Recipe to.

 

 

Add new Data Bag Entries for the SCOM Accounts to the Chef Server

Log into the Windows Server 2012 R2 Host and launch a PowerShell Console. cd to your chef-repo directory. Run the following knife command to add a new Data Bag to your Chef Server.

 

A new JSON File will open up either notepad or notepad++ and show the following item:

 

Add in the usernames for your Operations Manager SQL Account and Operations Manager Data Reader Account (which we used last week) under the id section.
Make sure you do not forget the comma after each ordered pair!

 

Save and close the file. You should then see the following output from the PowerShell Console.

 

Run the command below to verify that the new Data Bag Items are on your Chef Server

 

You should get back something similar to what is shown below

 

 

Add new Data Bag Entries for the SCOM Passwords to the Chef Server

Run the following knife command to add the SQL Service Account Passwords to the SQL2012 Data Bag on the Chef Server

 

A new JSON File will open up and show the following items:

 

Add in the passwords for your Operations Manager SQL Account and Operations Manager Data Reader Account under the id section.

 

Save and close the file. You should then see the following output from the PowerShell Console.

 

Run the command below to verify that the SQL Service Account Passwords have been added to your Chef Server. Make sure to replace <USERNAME> with the name of the Windows User that you are storing the encrypted_sql2012_data_bag_secret file under.

 

You should get back something similar to what is shown below

 

 

 

Update the SQL Server 2012 Recipe

Below is an updated version of the SQL Server 2012 recipe used in last week’s post.

The Recipe above will perform the following actions:

– Create a Temp Directory at C:\Temp on the Windows Host
– Download the SQL Server 2012 ISO from a specified web URL.
– Download the SQL Server 2012 Custom Configuration File from a specified web URL.
– Mount the SQL Server 2012 ISO
– Install SQL Server 2012 with Default Settings
– Dismount the SQL Server 2012 ISO
– Delete the SQL Server 2012 ISO from the Temp Directory at C:\Temp
– Delete the SQL Server 2012 Custom Configuration File from the Temp Directory at C:\Temp

The following are the changes that were made to the Recipe this week:

– Importing the Data Bag Shared Secret Encryption File from a Web Server in our Environment.
– Added a check during the SQL Server 2012 Installation section that verifies SQL Server isn’t already installed before continuing.

Before you run the chef recipe (as shown below) make sure to replace the following variables at the top of the recipe with their respective values:

 

 

Upload the Recipe to the Chef Server

If you currently have SQL Server 2012 Standard installed on your Windows Host, make sure to completely remove SQL (including all features) before deploying SQL Server 2012 Recipe to it. The Recipe will still run, however; you won’t see anything happen as the recipe will check and verify that MS SQL is running.

If you would like to verify that the Recipe is still working after making your changes, you can run a test locally before deploying the Recipe to your Chef Server per the instructions in Part 3. Otherwise, you can continue below.

 

Run the following command to upload your SQL Server 2012 Cookbook from your Windows Host to the Chef Server

 

You should get back the following

 

Next, add the recipe to the Run List of the Windows Host by running the following command:

 

After about 10 to 20 minutes, open up services.msc and verify that the SQL related services are Running on the Windows Host.

SQL Server Installed and Running

 

 

 

Conclusion

In this last entry in the Series, we have demonstrated how deploy the SQL Server 2012 Recipe from a locally installed Chef Server. Additionally, changes were made to make the Recipe deployable to multiple Windows Hosts if required.

 

 

Previous Installments

100 Days of DevOps with PowerShell

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.