Microsoft SQL Server Always On in a Workgroup environment

Intro

I had to set a high availability Microsoft SQL Server in a WORKGROUP environment. Since Microsoft SQL Server 2017 the Always On feature is available (for only one database with a MSSQL Standard licencing). And since Windows 2016 Server Standard High Availability cluster is possible in a WORKGROUP environment.

Configuration

Network diagram

Create Microsoft SQL service account (MSSQL01 and MSSQL02)

net user mssqlservice MyPassw0rdMSSQL! /add /expires:never
net localgroup administrators mssqlservice /add

Microsoft SQL Server Installation (MSSQL01 and MSSQL02)

Network and DNS configuration (MSSQL01 and MSSQL02)

192.168.1.196 mssql01
192.168.1.196 mssql01.priv
192.168.1.197 mssql02
192.168.1.197 mssql02.priv
192.168.1.198 wfc_mssql
192.168.1.198 wfc_mssql.priv
192.168.1.199 ha_mssql
192.168.1.199 ha_mssql.priv

Windows Failover Clustering Installation (MSSQL01 and MSSQL02)


Install the Windows Failover Clustering role.




Install-WindowsFeature -Name Failover-Clustering –IncludeManagementTools -Restart

Windows Failover Clustering Configuration (MSSQL01)


Configure the Windows Failover Clustering role.






New-Cluster -Name WFC_MSSQL -Node mssql01.priv, mssql02.priv -AdministrativeAccessPoint DNS -StaticAddress 192.168.1.198

Enable AlwaysOn Availability (MSSQL01 and MSSQL02)


In Microsoft SQL Server we need to enable the AlwaysOn Availability feature.

Microsoft SQL Server Management Studio

Download and Install Microsoft SQL Server Management Studio wherever you want

Configure mssqlservice security

Note : open TCP 1433 and 5022 ports on each server or disable Firewall



Set up SQL Server AlwaysOn feature





References

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.

Contact :