Network Design And IP
| Node Name | Management IP | Role |
|---|---|---|
| DC01 | 10.10.101.5 | AD/Dns Server |
| SQL0A | 10.10.101.12 | SQL Server |
| SQL0B | 10.10.101.13 | SQL Server |
| SQLCL0A | 10.10.101.10 | Cluster Name Object (CNO) |
| SQLAGL0A | 10.10.101.11 | SQL Availability Group Listener |
Requirement
- SQL Server requires a minimum of 20 GB of available hard drive space.
- Windows Server 2019 or greater
- Ensure that the system isn’t a domain controller.
- Ensure that each computer is running Windows Server 2019 or later versions with latest patches.
- Ensure that each computer is a node in a WSFC.
- Ensure that the WSFC contains sufficient nodes to support your availability group configurations.
Disk configuration
C: 300GB (OS) -> Formatted with 4k allocation unit.
D: 1TB (DATABASE) -> Formatted with 64k allocation unit.
L: 500GB (LOGS) -> Default with 64k allocation unit.
Server Basic Setting:
AD Preparation.
Login to AD server, and create SQL service account and SQL Admin Group
# Create SQL Admins Group
New-ADGroup -Name "SQLAdmins" -SamAccountName "SQLAdmins" -GroupCategory Security -GroupScope Global -DisplayName "SQL Admins" -Path "CN=Groups,DC=amanulloh,DC=com" -Description "SQL Admins Group"
# Create SQL Service Account
New-ADUser -Name "SQL Service Account" -SamAccountName "sqlsvc" -AccountPassword (ConvertTo-SecureString "StrongPassword" -AsPlainText -Force) -DisplayName "SQL Service Account" -Enabled $True -GivenName "SQL" -Path "CN=Users,DC=home,DC=amanulloh,DC=com" -Surname "Service Account" -UserPrincipalName "[email protected]"
# Add SQL Service into SQL Admins Group
Add-ADGroupMember SQLAdmins -Members "HOME\sqlsvc"
Build the SQL server
# Install the new server using latest **Windows 2022 GUI** version and update it to the latest patches.
# Rename Computer
Rename-Computer -NewName "SQL01" -Force
# Disable IPV6
Disable-NetAdapterBinding –InterfaceAlias “Ethernet” –ComponentID ms_tcpip6
# Change IP Address
New-NetIPAddress -InterfaceAlias "Ethernet" -IPAddress 10.10.101.12 -PrefixLength 24 -DefaultGateway 10.10.101.1
# Set DNS server
Set-DnsClientServerAddress -InterfaceAlias "Ethernet" -ServerAddresses 10.10.101.5,10.10.101.6
# Restart Server
Restart-Computer
# Join the server to the domain
Add-computer –domainName "amanulloh.com" -restart
# Enable RDP
Set-ItemProperty -Path 'HKLM:\System\CurrentControlSet\Control\Terminal Server' -name "fDenyTSConnections" -value 0
# Disable Firewall
Set-NetFirewallProfile -Profile Domain,Public,Private -Enabled False
# Set Timezone
Set-TimeZone -Id "Central Standard Time"
# Add SQL Service Admin account as Local Administrator
Add-LocalGroupMember -Group "Administrators" -Member "AMAN\sqladmins"
Add-LocalGroupMember -Group "Administrators" -Member "AMAN\sqlsvc"
Installation and Configuration
SQL Installation
- Mount the SQL ISO file to the VM, and assign the drive letter as F:\ Drive.
F:\setup.exe /qs /ACTION=Install /FEATURES=SQLEngine,FullText /INSTANCENAME=MSSQL /SQLSVCACCOUNT='AMAN\sqlsvc' /SQLSVCPASSWORD='Stringpassword' /SQLSYSADMINACCOUNTS='AMAN\sqladmins' /AGTSVCACCOUNT='AMAN\sqlsvc' /AGTSVCPASSWORD='Stringpassword' /AGTSVCSTARTUPTYPE=Automatic /TCPENABLED=1 /SQLSVCINSTANTFILEINIT=True /INSTALLSQLDATADIR='C:\Program Files\Microsoft SQL Server' /SQLBACKUPDIR='D:\SQL\Backup' /SQLUSERDBDIR='G:\SQL\INST01\DATA' /SQLUSERDBLOGDIR='L:\SQL\INST01\LOGS' /USESQLRECOMMENDEDMEMORYLIMITS /IACCEPTSQLSERVERLICENSETERMS
Failover Clustering Installation
Install-WindowsFeature Failover-Clustering –IncludeManagementTools
Change the HostRecordTTL
Import-Module FailoverClusters
$nameResource = "SQL Network Name (SQL35)"
Get-ClusterResource $nameResource | Set-ClusterParameter HostRecordTTL 300
Enforce TLS 1.2
This is a best practice, but not a requirement.
# Disable everything except TLS 1.2
$RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Client"
IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null}
New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null
New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null
$RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 2.0\Server"
IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null}
New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null
New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null
$RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Client"
IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null}
New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null
New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null
$RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\SSL 3.0\Server"
IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null}
New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null
New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null
$RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Client"
IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null}
New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null
New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null
$RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.0\Server"
IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null}
New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null
New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null
$RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client"
IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null}
New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null
New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null
$RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Server"
IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null}
New-ItemProperty -Path $RegPath -Name "Enabled" -Value "0" -PropertyType DWORD -Force | Out-Null
New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "1" -PropertyType DWORD -Force | Out-Null
$RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client"
IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null}
New-ItemProperty -Path $RegPath -Name "Enabled" -Value "1" -PropertyType DWORD -Force | Out-Null
New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "0" -PropertyType DWORD -Force | Out-Null
$RegPath = "HKLM:\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Server"
IF (!(Test-Path $RegPath)) {New-Item -Path $RegPath -Force | Out-Null}
New-ItemProperty -Path $RegPath -Name "Enabled" -Value "1" -PropertyType DWORD -Force | Out-Null
New-ItemProperty -Path $RegPath -Name "DisabledByDefault" -Value "0" -PropertyType DWORD -Force | Out-Null
# Reboot computer to apply the changes.
Restart-Computer
Enable SQL Always On.
$ServerInstance = 'SQL01\MSSQL01' #this should be in format SERVERNAME\INSTANCENAME or just use servername for default instance
Enable-SqlAlwaysOn -ServerInstance $ServerInstance -Force
Create a Cluster
New-Cluster –Name SQLCL01 –StaticAddress 10.10.101.10 –Node SQL01,SQL02
Create a file share witness for the cluster
Create a share and a folder for this cluster. Assign SQLCL01 FULL CONTROL rights to the folder:
# Login to the witness server. On this setup, I'll create a shared folder on the AD/DNS server.
mkdir "C:\SQLCL01-Witness"
$FolderName = 'C:\SQLCL01-Witness'
$compid = 'AMAN\SQLCL01$'
New-SmbShare -Path $FolderName -Name 'SQLCL01-Witness' -FullAccess $compid
$Acl = Get-Acl $FolderName
$NewAccessRule = New-Object system.security.accesscontrol.filesystemaccessrule($compid,"full")
$Acl.SetAccessRule($NewAccessRule)
Set-Acl $FolderName $Acl
TO BE CONTINUED..