Building Windows Domain and Installing SQL server and Windows Cluster

This is first post in the multi post series for running a SQL test lab on laptop. To begin we need to enable hyper-V feature in windows. Hyper-V is shipped along with windows 10 but is not enabled by default. To enable HyperV we can use GUI, PowerShell or cmd line.

Using GUI:  From the Desktop use   image to open up the Cortana search Menu, Type Windows Features and press Enter and you will have below window open.

Check the Hyper-V Box to install all the features and click ok. Once this is done  you will have to reboot your system for changes to your network adapter to take affect.

image

Now you can download the ISO’s and other tools required to help us achieve the first goal i.e. Building a Windows Domain with two nodes. all the links are hyperlinked with the tool and ISO image names in first post of this series.

https://sqlx86.com/2018/06/11/setting-up-a-sql-server-lab-on-your-laptop/

Once the tools are downloaded run automatedlab.msi to install it on your machine. Install instructions are at https://github.com/AutomatedLab/AutomatedLab

Once AutomatedLab msi is installed place the iso files downloaded from previous post in the \LabSources\ISOs folder. I have it installed on my D drive so in my case it looks like this

image

You can also verify the ISO file by running following command in Powershell

Get-LabAvailableOperatingSystem –Path D:\LabSources\ISO’s

This shows that the ISO files have been picked up by the AutomatedLab module .

Craig Porteous  has provided a wonderful contribution in building the scripts for AutomatedLab.msi and this just works wonders with little to no manual intervention while the lab is being installed.

I have modified the script to do following task at one go.

  1. Install Windows Domain with 4 child nodes
  2. Install Failover cluster on the 4 child nodes
  3. Install SQL server on 4 Child nodes
  4. Enable Always on Group on 4 child nodes
  5. Copy databases from local machine to Virtual machines and restore on each server
  6. Install dbatools module on 4 child nodes

Lab Setup for Windows Domain with 4 cluster nodes and SQL server AG

#—————————————-

# Author: Craig Porteous

# Modified by : Saurabh Srivastava

#  Create Date: 16/10/2017

# Modified Date : 06/21/2018

# This creates a domain with 4 SQL Servers on failover cluster and enables AG on SQL nodes

# & restores Sample DBs using the dbatools

# module

#—————————————-

# Prerequisites

#———————————–

# Install dbatools module locally

# > Install-Module dbatools

#———————————–

#Define the Lab name

$labname = ‘somelab’

#Domain based on Lab name

$domainName = “$labName.com”

#create an empty lab template and define where the lab XML files and the VMs will be stored

New-LabDefinition -Name $labName -DefaultVirtualizationEngine HyperV

#Set installation user

Set-LabInstallationCredential -Username someadmin  -Password Somepass1

#Create domain definition with the domain admin account

Add-LabDomainDefinition -Name $domainName -AdminUser someadmin -AdminPassword Somepass1

#Define the network range

Add-LabVirtualNetworkDefinition -Name $labName -AddressSpace 192.168.15.0/24

#Define an External, Internet connection

Add-LabVirtualNetworkDefinition -Name External -HyperVProperties @{ SwitchType = ‘External’; AdapterName = ‘Ethernet’ }

#defining default parameter values, as these ones are the same for all the machines

$PSDefaultParameterValues = @{

‘Add-LabMachineDefinition:Network’ = $labName

‘Add-LabMachineDefinition:ToolsPath’= “$labSources\Tools”

‘Add-LabMachineDefinition:DomainName’ = $domainName

‘Add-LabMachineDefinition:OperatingSystem’ = ‘Windows Server 2016 Standard (Desktop Experience)’

}

#the first machine is the root domain controller. Everything in $labSources\Tools gets copied to the machine’s Windows folder

Add-LabMachineDefinition -Name ($LabName + “DC1”) -Memory 1GB -Roles RootDC

# Integrate an iSCSI Target into your machines

$storageRole = Get-LabMachineRoleDefinition -Role FailoverStorage -Properties @{LunDrive = ‘D’ }

Add-LabDiskDefinition -Name LunDisk -DiskSizeInGb 20

Add-LabMachineDefinition -Name ($LabName + “Cls1”) -Roles $storageRole -DiskName LunDisk

#Read all ISOs in the LabSources folder and add the SQL 2017 ISO

Add-LabIsoImageDefinition -Name SQLServer2017 -Path $labSources\ISOs\en_sql_server_2017_developer_x64_dvd.iso

#Define Two cluster roles with static IP

$cluster1 = Get-LabMachineRoleDefinition -Role FailoverNode -Properties @{ ClusterName = ‘Clu1’; ClusterIp = ‘192.168.15.11’ }

$cluster2 = Get-LabMachineRoleDefinition -Role FailoverNode -Properties @{ ClusterName = ‘Clu2’; ClusterIp = ‘192.168.15.21’ }

# Create Cluster and SQL server Node definition

$sql1 = ($LabName + “SQL1”)

Add-LabMachineDefinition -name $sql1 -Roles $cluster1 , SQLServer2017

$sql11 = ($LabName + “SQL11”)

Add-LabMachineDefinition -name $sql11 -Roles $cluster1

$sql2 = ($LabName + “SQL2”)

Add-LabMachineDefinition -name $sql2 -Roles $cluster2 , SQLServer2017

$sql22 = ($LabName + “SQL22”)

Add-LabMachineDefinition -name $sql22 -Roles $cluster2

#Define Network adapter

$netAdapter = @()

$netAdapter += New-LabNetworkAdapterDefinition -VirtualSwitch $labName

$netAdapter += New-LabNetworkAdapterDefinition -VirtualSwitch External  -UseDhcp

Install-Lab

Show-LabDeploymentSummary -Detailed

#Copy DBs to SQL1 VM

Copy-LabFileItem -Path ‘D:\LabSources\SampleDBs\’ -ComputerName $sql1 -DestinationFolderPath C:\

Copy-LabFileItem -Path ‘D:\LabSources\SampleDBs\’ -ComputerName $sql2 -DestinationFolderPath C:\

#Install dbaTools module on SQL1 and SQL2

Invoke-LabCommand -ActivityName ‘Install dbatools’ -ScriptBlock { Install-Module -Name dbatools -ErrorAction SilentlyContinue } -ComputerName ‘$sql1’ -PassThru

Invoke-LabCommand -ActivityName ‘Install dbatools’ -ScriptBlock { Install-Module -Name dbatools -ErrorAction SilentlyContinue } -ComputerName ‘$sql2’ -PassThru

#Restore Database on SQL1

Invoke-LabCommand -ActivityName ‘Restore DBs’ -ScriptBlock { Restore-DbaDatabase -SqlServer $env:COMPUTERNAME -Path ‘C:\SampleDBs’ } -ComputerName ‘$sql1’ -PassThru

#Restore Database on SQL2

Invoke-LabCommand -ActivityName ‘Restore DBs’ -ScriptBlock { Restore-DbaDatabase -SqlServer $env:COMPUTERNAME -Path ‘C:\SampleDBs’ } -ComputerName ‘$sql2’ -PassThru

#Create AG on both nodes

Invoke-LabCommand -ActivityName ‘EnableAG’ -ScriptBlock { Enable-DbaAgHadr -SqlServer $env:COMPUTERNAME  } -ComputerName ‘$sql1’ -PassThru

Invoke-LabCommand -ActivityName ‘EnableAG’ -ScriptBlock { Enable-DbaAgHadr -SqlServer $env:COMPUTERNAME  } -ComputerName ‘$sql2’ -PassThru

PS:

  1. Make sure to use first letter as CAPITAL in the admin password for Domain Admin account
  2. By default lab would be installed in C drive, but if you want to use different drive\folder structure you can use the switch -VMPATH  after below command       New-LabDefinition -Name $labName -DefaultVirtualizationEngine HyperV
  3. The Network Adapter added would be External tied to your Ethernet Adpater on machine. For the above script internet is need on the SQL server nodes to download and install DBATools module. If you are using Ethernet on your machine you should be ok as internet would be working on that. If you are using Wifi you need to add a switch to your sql server nodes to make the last portion of this script work .

References:

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

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