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
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"
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 !!!