Day 92: Deploying SQL Server 2012 using Chef – Part 3

In last week’s post, Day 87: Deploying SQL Server 2012 using Chef – Part 2, we demonstrated how to utilize Chef Data Bags when Deploying SQL Server 2012 Standard.. In that post, we demonstrated how to store sensitive data, such as Account Passwords, in Encrypted Data Bags, and utilized that information in the Chef Recipe for Deploying SQL Server 2012 Standard.

This week we’ll be modifying the Recipe from last week to work with a Custom Configuration file for installing SQL Server 2012. Additionally, the SQL Installation will be using two accounts for the SQL Services to run-as. This is to give you an idea of how this Recipe could be further customized to deploy SQL Server for Enterprise Solutions like System Center Operations Manager.

 

 

Prerequisites

The Recipe below has been configured as if you were installing SQL Server 2012 Standard for Operations Manager 2012 R2. As such, you need to create two Windows Domain Accounts for this recipe:

– An Operations Manager SQL Account, i.e. – DOMAIN\scom_sql
– An Operations Manager Data Reader Account, i.e. – DOMAIN\scom_dr

Remember, this is to get the process of installing SQL Server 2012 Standard to get ready for Operations Manager Database. Normally additional Domain Accounts are required to fully install SCOM; however that is beyond the scope of this post.

Since the Recipe will now be using a Custom Configuration File to install SQL Server, you need to copy the SQL_Server_2012_Custom_Config.ini (hosted on Github Gist) to the Web Server that you’ve been using for this recipe.

Before adding the file to your Web Server, you need to make the following modifications to the file

 

 

Add new Data Bag Entries for SCOM Accounts

We need to modify the existing data bags we created last week to add the usernames for the Operations Manager SQL Account and the Operations Manager Data Reader Account.

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 open up the SQL2012 Data Bag for editing.

A new JSON File will open up either notepad or notepad++ (whatever you setup as the default editor for Chef last week) and show the following items

 

Add in the usernames for your Operations Manager SQL Account and Operations Manager Data Reader Account under the raw data section.
Make sure you do not forget the “,” 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 changes have been made locally

 

You should get back something similar to what is shown below

 

Add New New Encrypted Data Bag Entries for SCOM Passwords

Run the following knife command to open up the SQL2012 Data Bag for editing.

 

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 raw data section.Notice that your passwords at this
point are showing up in clear text allowing you to make changes easily after/during password reset changes.

 

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

 

 

Run the command below to verify that the changes have been made locally

 

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

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:

 

Run the Recipe in Local Mode

Once you have put in the respective variable values you can deploy the recipe locally on the Windows Host by running the chef-client command below:

 

 

Note that the recipe will take between 20 and 30 minutes to run.

Once the command has finished running, you should be able to open up services.msc and see the SQL related services Running.

SQL Server Installed and Running

 

Conclusion

This week we have demonstrated how to deploy SQL Server 2012 Standard by including a SQL Custom Configuration File in the Recipe. Additionally, we demonstrated how we could deploy this recipe using Domain Credentials using Encrypted Data Bags.

Next week we will go over how to deploy this Chef Recipe from your Chef Server.

 

Previous Installments

100 Days of DevOps with PowerShell

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Leave a Reply