Microsoft SQL Server Always On in a Workgroup environment


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.


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) mssql01 mssql01.priv mssql02 mssql02.priv wfc_mssql wfc_mssql.priv ha_mssql ha_mssql.priv

Windows Failover Clustering (MSSQL01 and MSSQL02)

Install the Windows Failover Clustering role.

Windows Failover Clustering Install

Install from the graphical interface

Install With PowerShell

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

Windows Failover Clustering Configuration (MSSQL01)

Configure the Windows Failover Clustering role.

Configuration from the GUI

Configuration with PowerShell

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

Enable AlwaysOn Availability (MSSQL01 and MSSQL02)

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

Microsoft SQL Server Management Studio (MSSQL01 and MSSQL02)

Download and Install Microsoft SQL Server Management Studio wherever you want

Configure mssqlservice security (MSSQL01 and MSSQL02)

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

Set up SQL Server AlwaysOn feature


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

Contact :