Setup LAB: 03 Installation of SQL Server 2012


In this post, I will start with the SQL Server 2012 installation. Prior to that, I will create an OU named TestLAB in AD, which will contains all the service accounts, lab users, servers. Apart from that I will also install the management tools for active directory. The below set of scripts will install the AD management tools & create all the OU & sub-OUs.

# Install the Active Directory Tools
Install-WindowsFeature RSAT-ADDS -IncludeAllSubFeature -IncludeManagementTools
# Import Module Active Directory
Import-Module ActiveDirectory
# Create a new OU named TestLAB
New-ADOrganizationalUnit -Name "TestLAB" -Server "Win12DC01" -Verbose
# Create sub-OU ServiceAccounts inside the TestLAB OU
New-ADOrganizationalUnit "ServiceAccounts" -Path "OU=TestLAB,DC=SKGLAB,DC=LOCAL" -Description "Lists all Service Accounts" -Verbose
# Create sub-OU TestServers inside the TestLAB OU
New-ADOrganizationalUnit "TestServers" -Path "OU=TestLAB,DC=SKGLAB,DC=LOCAL" -Description "Lists Test Servers" -Verbose
# Create sub-OU LABUsers inside the TestLAB OU
New-ADOrganizationalUnit "LABUsers" -Path "OU=TestLAB,DC=SKGLAB,DC=LOCAL" -Description "Lists LAB Users" -Verbose

Now that, I have created all the OUs, I am going to create the service accounts for SQL Server installation, along with a group for DBAs & an user account for the SQL Server installation.

# Create a Windows group for the LAB administrators
New-ADGroup -Name "BLRDBA" -Path "OU=LABUsers,OU=TestLAB,DC=SKGLAB,DC=LOCAL" -Description "Members of SQL DBAs" `
-GroupScope DomainLocal -GroupCategory Security -Verbose
# Create a Windows User account
$password = Read-Host ("Enter the password") -AsSecureString
New-ADUser -Name skganguly -GivenName Sudeepta -Surname Ganguly -DisplayName "Sudeepta Ganguly" -SamAccountName "skganguly" `
-Path "OU=LABUsers,OU=TestLAB,DC=SKGLAB,DC=LOCAL" -ChangePasswordAtLogon $false -CannotChangePassword $true `
-Description "MS SQL Server DBA" -PasswordNeverExpires $true -AccountPassword $password -enabled $true -Verbose
# Create a service account for SQL Server 2012 installation
$password = Read-Host ("Enter the password") -AsSecureString
New-ADUser -Name SQLSvc-SQL2012 -GivenName SQLSvc-SQL2012 -Surname SQLSvc-SQL2012 -DisplayName "SQLSvc-SQL2012" -SamAccountName "SQLSvc-SQL2012" `
-Path "OU=ServiceAccounts,OU=TestLAB,DC=SKGLAB,DC=LOCAL" -ChangePasswordAtLogon $false -CannotChangePassword $true `
-Description "SQL Server 2012 Service account" -PasswordNeverExpires $true -AccountPassword $password -enabled $true -Verbose

Configure the VM for SQL Server Installation

Once the VM is started, I have added an additional VHD to the VM, where the SQL binaries will be installed. Added the newly created VM to the SKGLAB domain & added SKGLAB\BLRDBA group as a member of the local administrator group on the server. The user account SKGLAB\skganguly is a member of BLRDBA group. I am going to use this user account to install the SQL Server 2012 on this server.

Configure Local Policies

Open Server Manager, Go to Tools, click on Local Security Policy. Expand Local Policies, select User Rights Assignments. From the list of available policies, add the SQL Server service account to the below two policies as shown in the screenshot.

  • Lock pages in memory
  • Perform Volume Maintenance tasks

image

Configure SPN for SQL Service Account

Once the local policies are configured, next we are going to configure the SPN for the SQL Server service account. I am going to create the below two SPNs on this server.  

SETSPN -S "MSSQLSVC/KRISHNA01.SKGLAB.LOCAL" "SKGLAB\SQLSvc-SQL2012"

SETSPN -S "MSSQLSVC/KRISHNA01.SKGLAB.LOCAL:1433" "SKGLAB\SQLSvc-SQL2012"

Once the SPN is configured, I can verify it by executing the below command. The desired result is shown in the below screenshot.

SETSPN -L "SKGLAB\SQLSVC-SQL2012"

image

Now, I will start the SQL Server 2012 installation.

Installing SQL Server 2012

I am going to use the below script to complete the SQL Server 2012 installation. I have modified a existing configuration file to complete this installation. The SQL Server Media is mounted on the CD-ROM drive of the VM. Update the password for sa & service accounts, and run the below script to start the SQL Server installation.

#  SQL Server 2012 Setup Command  #
#----------------------------------

# During the installation, the SQL Server media is mounted on Drive D:

# The Base Command for unattended installation of SQL Server 2012

# Define Variables
$SetupLocation = "D:\Setup.exe"
# Provide the Database Engine Service Account Password
$SQLSVCPASSWORD = "xxxxxxxx"
# Provide the SQL Agent Service Account Password
$AGTSVCPASSWORD = "xxxxxxxx"
# Provide the SA Password
$SAPWD = "xxxxxxxx"
$ConfigFileLocation = "c:\TEMP\SQLConfig.ini"

# Change the current folder location to C:\Temp
Set-Location "c:\TEMP"

# To start the SQL Server Installation
& "$SetupLocation" /IACCEPTSQLSERVERLICENSETERMS `
/SQLSVCPASSWORD="$SQLSVCPASSWORD" /AGTSVCPASSWORD="$AGTSVCPASSWORD"`
 /SAPWD="$SAPWD" /CONFIGURATIONFILE="$ConfigFileLocation"


The configuration file used during this installation can be found here. The installation of SQL Server 2012 completed successfully. I am going to build two more servers with the same configuration and complete the SQL Server installation.

Happy Learning Smile !!!

Advertisements

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Automation in SQL Server, How To, PowerShell, Setup LAB, SQL Server, SQL2012, TestLab and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s