Building Windows Domain and Installing SQL server and Windows Cluster

This is the first of a multi-part article on how to run a SQL test lab on a laptop. To begin, we must first enable Windows’ hyper-V feature. Hyper-V is included with Windows 10, however it is turned off by default. We can utilize the GUI, PowerShell, or the command line to enable HyperV.

Using GUI:  From the Desktop use   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.

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://atomic-temporary-105871631.wpcomstaging.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

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:

• AutomatedLab Github
• Craig Porteous
• Windows Server 2016 evaluation Edition
• SQL Server 2017 Developer Edition for Windows Server