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

AGTSVCACCOUNT="<DOMAIN\USERNAME>"
ISSVCACCOUNT="<DOMAIN\USERNAME>"
ASSVCACCOUNT="<DOMAIN\USERNAME>"
ASSYSADMINACCOUNTS="<DOMAIN\USERNAME>" "<DOMAIN\USERNAME>"
SQLSVCACCOUNT="<DOMAIN\USERNAME>"
SQLSYSADMINACCOUNTS="<DOMAIN\USERNAME>" "<DOMAIN\USERNAME>"

NOTE: For the account that is being set to the Report Server NT Service, set this to
Operations Manager Data Reader Account.

RSSVCACCOUNT="<DOMAIN\USERNAME>"

 

 

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.

knife data bag edit SQL2012 sql_service_account_usernames -z

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

{
  "name": "data_bag_item_SQL2012_sql_service_account_usernames",
  "json_class": "Chef::DataBagItem",
  "chef_type": "data_bag_item",
  "data_bag": "SQL2012",
  "raw_data": {
    "id": "sql_service_account_usernames",
    "sql_server_service_username": "DOMAIN\\USERNAME",
    "sql_sysadmins_username": "DOMAIN\\USERNAME"
  }
}

 

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!

{
  "name": "data_bag_item_SQL2012_sql_service_account_usernames",
  "json_class": "Chef::DataBagItem",
  "chef_type": "data_bag_item",
  "data_bag": "SQL2012",
  "raw_data": {
    "id": "sql_service_account_usernames",
    "sql_server_service_username": "DOMAIN\\USERNAME",
    "sql_sysadmins_username": "DOMAIN\\USERNAME",
    "scom_sql_username": "DOMAIN\\USERNAME",
    "scom_sql_data_reader_username": "DOMAIN\\USERNAME"
  }
}

 

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

Saved data_bag_item[sql_service_account_usernames]

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

knife data bag show SQL2012 sql_service_account_usernames -z

 

You should get back something similar to what is shown below

id:                            sql_service_account_usernames
scom_sql_data_reader_username: DOMAIN\USERNAME
scom_sql_username:             DOMAIN\USERNAME
sql_server_service_username:   DOMAIN\USERNAME
sql_sysadmins_username:        DOMAIN\USERNAME

 

Add New New Encrypted Data Bag Entries for SCOM Passwords

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

knife data bag edit SQL2012 sql_service_account_passwords --secret-file C:\Users\<USERNAME>\.chef\encrypted_sql2012_data_bag_secret -z

 

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

{
  "id": "sql_service_account_passwords",
  "sql_server_service_password": "<PASSWORD>",
  "sql_sysadmins_password": "<PASSWORD>",
}

 

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.

{
  "id": "sql_service_account_passwords",
  "sql_server_service_password": "<PASSWORD>",
  "sql_sysadmins_password": "<PASSWORD>",
  "scom_sql_password": "<PASSWORD>",
  "scom_sql_data_reader_password": "<PASSWORD>"
}

 

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

Saved data_bag_item[sql_service_account_passwords]

 

 

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

knife data bag show SQL2012 sql_service_account_passwords --secret-file C:\Users\serveradmin\.chef\encrypted_sql2012_data_bag_secret -z

 

You should get back something similar to what is shown below

id:                            sql_service_account_passwords
scom_sql_data_reader_password: <PASSWORD>
scom_sql_password:             <PASSWORD>
sql_server_service_password:   <PASSWORD>
sql_sysadmins_password:        <PASSWORD>

 

 

Update the SQL Server 2012 Recipe

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

#
# Cookbook Name:: SQL_Server_2012_STD_x64
# Recipe:: default
#
# Copyright (c) 2014 Ryan Irujo, All Rights Reserved.

# Declaring Variables
secret_key            = Chef::EncryptedDataBagItem.load_secret("C:\\Users\\<USERNAME>\\.chef\\encrypted_sql2012_data_bag_secret")
usernames             = data_bag_item('SQL2012', 'sql_service_account_usernames')
passwords             = Chef::EncryptedDataBagItem.load('SQL2012', 'sql_service_account_passwords', secret_key)
iso_url               = "http://<WEB_SERVER>/<SQL_SERVER_ISO_FILE>"
iso_path              = "C:\\Temp\\<SQL_SERVER_ISO_FILE>"
sql_config_file_url   = "http://<WEB_SERVER>/SQL_Server_2012_Custom_Config.ini"
sql_config_file_path  = "C:\\Temp\\SQL_Server_2012_Custom_Config.ini"
scom_sql_act          = usernames['scom_sql_username']
scom_sql_dr_act       = usernames['scom_sql_data_reader_username']
scom_sql_password     = passwords['scom_sql_password']
scom_sql_dr_password  = passwords['scom_sql_data_reader_password']
sql_agent_svc_act     = "NT AUTHORITY\\Network Service"

# Creating a Temporary Directory to work from.
directory "C:\\Temp\\" do
	rights :full_control, "#{scom_sql_act}"
	inherits true
	action :create
end

# Download the SQL Server 2012 Standard ISO from a Web Share.
powershell_script 'Download SQL Server 2012 STD ISO' do
	code <<-EOH
		$Client = New-Object System.Net.WebClient
		$Client.DownloadFile("#{iso_url}", "#{iso_path}")
		EOH
	guard_interpreter :powershell_script
	not_if { File.exists?(iso_path)}
end

# Download the SQL Server 2012 Custom Configuration File from a Web Share.
powershell_script 'Download SQL Server 2012 Custom Configuration File' do
	code <<-EOH
		$Client = New-Object System.Net.WebClient
		$Client.DownloadFile("#{sql_config_file_url}", "#{sql_config_file_path}")
		EOH
	guard_interpreter :powershell_script
	not_if { File.exists?(sql_config_file_path)}
end

# Mounting the SQL Server 2012 SP1 Standard ISO.
powershell_script 'Mount SQL Server 2012 STD ISO' do
	code <<-EOH
		Mount-DiskImage -ImagePath "C:\\Temp\\en_sql_server_2012_standard_edition_with_sp1_x64_dvd_1228198.iso"
        if ($? -eq $True)
		{
			echo "SQL Server 2012 STD ISO was mounted Successfully." > C:\\Temp\\SQL_Server_2012_STD_ISO_Mounted_Successfully.txt
			exit 0;
		}
		
		if ($? -eq $False)
        {
			echo "The SQL Server 2012 STD ISO Failed was unable to be mounted." > C:\\Temp\\SQL_Server_2012_STD_ISO_Mount_Failed.txt
			exit 2;
        }
		EOH
	guard_interpreter :powershell_script
	not_if '($SQL_Server_ISO_Drive_Letter = (gwmi -Class Win32_LogicalDisk | Where-Object {$_.VolumeName -eq "SQLServer"}).VolumeName -eq "SQLServer")'
end

# Installing SQL Server 2012 Standard.
powershell_script 'Install SQL Server 2012 STD x64' do
	code <<-EOH
		$SQL_Server_ISO_Drive_Letter = (gwmi -Class Win32_LogicalDisk | Where-Object {$_.VolumeName -eq "SQLServer"}).DeviceID
		cd $SQL_Server_ISO_Drive_Letter\\
		$Install_SQL = ./Setup.exe /q /ACTION=Install /SQLSVCPASSWORD="#{scom_sql_password}" /AGTSVCPASSWORD="#{scom_sql_password}" /ASSVCPASSWORD="#{scom_sql_password}" /ISSVCPASSWORD="#{scom_sql_password}" /RSSVCPASSWORD="#{scom_sql_dr_password}" /IACCEPTSQLSERVERLICENSETERMS /CONFIGURATIONFILE="#{sql_config_file_path}"
		$Install_SQL > C:\\Temp\\SQL_Server_2012_STD_Install_Results.txt
		EOH
	guard_interpreter :powershell_script
end


# Dismounting the SQL Server 2012 STD ISO.
powershell_script 'Dismount SQL Server 2012 STD ISO' do
	code <<-EOH
		Dismount-DiskImage -ImagePath "#{iso_path}"
		EOH
	guard_interpreter :powershell_script
	only_if { File.exists?(iso_path)}
end


# Removing the SQL Server 2012 STD ISO from the Temp Directory.
powershell_script 'Delete SQL Server 2012 STD ISO' do
	code <<-EOH
		[System.IO.File]::Delete("#{iso_path}")
		EOH
	guard_interpreter :powershell_script
	only_if { File.exists?(iso_path)}
end

# Removing the SQL Server 2012 Custom Configuration File from the Temp Directory.
powershell_script 'Delete SQL Server 2012 Custom Configuration File' do
	code <<-EOH
		[System.IO.File]::Delete("#{sql_config_file_path}")
		EOH
	guard_interpreter :powershell_script
	only_if { File.exists?(sql_config_file_path)}
end

 

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:

secret_key = Chef::EncryptedDataBagItem.load_secret("C:\\Users\\<USERNAME>\\.chef\\encrypted_sql2012_data_bag_secret" 
iso_url = "http://<WEB_SERVER>/<SQL_SERVER_ISO_FILE>" 
iso_path = "C:\\Temp\\<SQL_SERVER_ISO_FILE>"
sql_config_file_url   = "http://<WEB_SERVER>/SQL_Server_2012_Custom_Config.ini"

 

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:

chef-client --local-mode --runlist 'recipe[SQL_Server_2012_STD_x64]'

 

 

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

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