Configure SQL Server 2016 Always On with Core Version of Windows Server 2016 without Active Directory Domain

This is a step by step demonstration to help database administrator deploy SQL Server 2016 Always On high availability solution on core version of Windows server 2016 without Active Directory domain. The demonstration environment will be created as following,

   * Two servers are Windows Server 2016 Datacenter core version (without Desktop Experience) configured with two network interface (NIC)

   * First server (node 1) configuration:
     host name:  sqln1.lab.dbaplus.ca
     NIC 1:      10.10.25.201
     NIC 2:      192.168.1.201

   * Second server (node 2) configuration:
     host name:  sqln2.lab.dbaplus.ca
     NIC 1:      10.10.25.202
     NIC 2:      192.168.1.202
   
   * Database is SQL Server 2016 Enterprise
   
1. Install Windows Server 2016 Datacenter on both nodes

The installation is pretty straight forward. Please choose "Windows Server 2016 Datacenter" when you are asked to select the operating system you want to install. This option (called core version) does not include a GUI, and the server can be fully managed locally or remotely with Windows PowerShell or other tools.

After finishing the installation process and automatic reboot, you will have an option to create an administrator password. Make sure use same password for administrator on both nodes.
  
2. Remove password complexity and password age policy (optional)

This is optional step and not recommended for production environment. I have many test-purpose hosts and do not want to change passwords periodically and remember many complicated passwords.
  
Export security settings by PowerShell command secedit

PS C:\> secedit /export /cfg c:\secPolicy.cfg /areas securitypolicy

The task has completed successfully.
See log %windir%\security\logs\scesrv.log for detail info.

  
  
Open the exported text file with notepad

PS C:\> notepad secPolicy.cfg

Change values of "PasswordComplexity" and "MaximumPasswordAge" as following, then exit notepad with saving changes

[System Access]
  ... ...
MaximumPasswordAge = -1
  ... ...
PasswordComplexity = 0
  ... ...

Write back the new values to system

PS C:\> secedit /configure /db c:\windows\security\local.sdb /cfg c:\secPolicy.cfg /areas SECURITYPOLICY

The task has completed successfully.
See log %windir%\security\logs\scesrv.log for detail info.

Now, I can use my favorite password for administrator and never need to change it :)

C:\>net user administrator microsoft
The command completed successfully.

3. Configure computer name and the Primary DNS Suffix

By default, Primary DNS Suffix is empty for standalone (not in Active Directory domain) Windows server. To be able to create a Windows cluster without any Active Directory domain, Primary DNS Suffix has to be set to same value on all nodes. Here, lab.dbaplus.ca is used as Primary DNS Suffix.

Find out current computer name of node 1

C:\>hostname
WIN-EN0L6Q2PN7B

C:\>echo %computername%
WIN-EN0L6Q2PN7B

Show current Primary DNS Suffix node 1

C:\>ipconfig /all

Windows IP Configuration

   Host Name . . . . . . . . . . . . : WIN-860APORJIRT
   Primary Dns Suffix  . . . . . . . :
   Node Type . . . . . . . . . . . . : Hybrid
   IP Routing Enabled. . . . . . . . : No
   WINS Proxy Enabled. . . . . . . . : No

   <<Content truncated>>

Change computer name and the primary DNS suffix on node1

C:\>netdom computername %computername% /add:sqln1.lab.dbaplus.ca
Successfully added sqln1.lab.dbaplus.ca
as an alternate name for the computer.

The command completed successfully.

C:\>netdom computername %computername% /makeprimary:sqln1.lab.dbaplus.ca
Successfully made sqln1.lab.dbaplus.ca
the primary name for the computer. The computer must be rebooted for this name
change to take effect. Until then this computer may not be able to authenticate
users and other computers, and may not be authenticated by other computers in
the forest. The specified new name was removed from the list of alternate
computer names. The primary computer name will be set to the specified new
name after the reboot.

The command completed successfully.

Reboot node1

c:\>shutdown /r /t 0

 
Confirm the changes

C:\Users\Administrator>hostname
sqln1

C:\Users\Administrator>ipconfig /all

Windows IP Configuration

   Host Name . . . . . . . . . . . . : sqln1
   Primary Dns Suffix  . . . . . . . : lab.dbaplus.ca
   Node Type . . . . . . . . . . . . : Hybrid
   IP Routing Enabled. . . . . . . . : No
   WINS Proxy Enabled. . . . . . . . : No
   DNS Suffix Search List. . . . . . : lab.dbaplus.ca

   <<content truncated>>
  

 
 
In same way, change computer name & Primary DNS Suffix of node 2 to sqln2 & lab.dbaplus.ca.

4. Configure IP addresses

List available network interface,

PS C:\> get-NetIPInterface -AddressFamily IPv4

ifIndex InterfaceAlias                  AddressFamily NlMtu(Bytes) InterfaceMetric Dhcp     ConnectionState PolicyStore
------- --------------                  ------------- ------------ --------------- ----     --------------- -----------
3       Ethernet 2                      IPv4                  1500              25 Enabled  Connected       ActiveStore
4       Ethernet                        IPv4                  1500              25 Enabled  Connected       ActiveStore
1       Loopback Pseudo-Interface 1     IPv4            4294967295              75 Disabled Connected       ActiveStore

Configure IP addresses on sqln1 (node 1)

PS C:\> new-NetIPaddress  -InterfaceAlias Ethernet -IPAddress 10.10.25.201 -PrefixLength 24 -DefaultGateway 10.10.25.1


IPAddress         : 10.10.25.201
InterfaceIndex    : 2
InterfaceAlias    : Ethernet
AddressFamily     : IPv4
  <<content truncated>>
PolicyStore       : ActiveStore

IPAddress         : 10.10.25.201
InterfaceIndex    : 2
InterfaceAlias    : Ethernet
AddressFamily     : IPv4
  <<content truncated>>
PolicyStore       : PersistentStore

PS C:\> new-NetIPaddress  -InterfaceAlias "Ethernet 2" -IPAddress 192.168.1.201 -PrefixLength 24


IPAddress         : 192.168.1.201
InterfaceIndex    : 4
InterfaceAlias    : Ethernet 2
AddressFamily     : IPv4
  <<content truncated>>
PolicyStore       : ActiveStore

IPAddress         : 192.168.1.201
InterfaceIndex    : 4
InterfaceAlias    : Ethernet 2
AddressFamily     : IPv4
  <<content truncated>>
PolicyStore       : PersistentStore

 

Confirm the changes,

PS C:\> get-NetIPConfiguration


InterfaceAlias       : Ethernet 2
InterfaceIndex       : 3
InterfaceDescription : Intel(R) PRO/1000 MT Desktop Adapter #2
NetProfile.Name      : Unidentified network
IPv4Address          : 192.168.1.201
IPv6DefaultGateway   :
IPv4DefaultGateway   :
DNSServer            : fec0:0:0:ffff::1
                       fec0:0:0:ffff::2
                       fec0:0:0:ffff::3

InterfaceAlias       : Ethernet
InterfaceIndex       : 4
InterfaceDescription : Intel(R) PRO/1000 MT Desktop Adapter
NetProfile.Name      : Network  2
IPv4Address          : 10.10.25.201
IPv6DefaultGateway   :
IPv4DefaultGateway   : 10.10.25.1
DNSServer            : fec0:0:0:ffff::1
                       fec0:0:0:ffff::2
                       fec0:0:0:ffff::3

 


In same way on sqln2 (node 2), configure 10.10.25.202 for network interface "Ethernet" and 192.168.1.202 for "Ethernet 2"

5. Configure host naming

Configure following FQDN of both nodes in DNS server.

  sqln1.lab.dbaplus.ca
  sqln2.lab.dbaplus.ca

If DNS server is not configured in the network, add following lines to file C:\Windows\System32\drivers\etc\hosts on both nodes

  10.10.25.201   sqln1.lab.dbaplus.ca
  10.10.25.202   sqln2.lab.dbaplus.ca

It guarantee both nodes' FQDN can be resolved if DNS is not configured in the network. Otherwise, Windows Cluster cannot be created.


6. Disable firewall  (optional)

Firewall rules could become your headache when building new server or deploying new service on existing server, because you have to figure out how the communication is created between server and client (protocol, data flow direction, port number, etc). Typically, following protocol/port (may be different on your servers) have to be enabled on the firewall,

   TCP 1433    - default port for SQL Server Instance
   TCP 1434    - default port for SQL Dedicated Admin Connection
   TCP 5022    - default port for SQL Always On Availability Group Endpoint
   TCP <port>  - port for SQL Always On Availability Group listener if you listener is created

Making it simple, I am going to disable the firewall.
  
Check current firewall state

C:\>netsh advfirewall show allprofiles state

Domain Profile Settings:
----------------------------------------------------------------------
State                                 ON

Private Profile Settings:
----------------------------------------------------------------------
State                                 ON

Public Profile Settings:
----------------------------------------------------------------------
State                                 ON
Ok.

Turning Off Firewall

C:\>netsh advfirewall set allprofiles state off
Ok.

C:\>netsh advfirewall show allprofiles state

Domain Profile Settings:
----------------------------------------------------------------------
State                                 OFF

Private Profile Settings:
----------------------------------------------------------------------
State                                 OFF

Public Profile Settings:
----------------------------------------------------------------------
State                                 OFF
Ok.

7. Install Windows Server Failover Cluster (WSFC)

One fabulous new feature of Windows Server 2016 is that WSFC can be created without any Active Directory domain. Therefore, SQL Server Always On which uses the functionality of WSFC can be deployed on servers without any Active Directory domain.

Check if WSFC is installed on all nodes

PS C:\> get-WindowsFeature -Name Failover-Clustering

Display Name                                            Name                       Install State
------------                                            ----                       -------------
[ ] Failover Clustering                                 Failover-Clustering            Available

Install WSFC on all nodes on which WSFS is not installed

PS C:\> Install-WindowsFeature -name Failover-Clustering -IncludeManagementTools

Success Restart Needed Exit Code      Feature Result
------- -------------- ---------      --------------
True    No             Success        {Failover Clustering, Remote Server Admini...


PS C:\> get-WindowsFeature -Name Failover-Clustering

Display Name                                            Name                       Install State
------------                                            ----                       -------------
[X] Failover Clustering                                 Failover-Clustering            Installed

8. Create Windows cluster

Note: finish this step only on ONE node.
  
Make sure you have same password for administrators on all nodes before running following command on one of the nodes

PS C:\> new-cluster -name sqlcluster -Node sqln1,sqln2 -StaticAddress 10.10.25.203 -NoStorage -AdministrativeAccessPoint DNS
WARNING: There were issues while creating the clustered role that may prevent it from starting. For more information view the report file below.
WARNING: Report file location: C:\Windows\cluster\Reports\Create Cluster Wizard sqlcluster on 2019.05.01 At 13.05.30.htm

Name
----
sqlcluster

Following warning message may be found in the report file created by new-cluster command,

An appropriate disk was not found for configuring a disk witness. The cluster is not configured with a witness. As a best practice, configure a witness to help achieve the highest availability of the cluster. If this cluster does not have shared storage, configure a File Share Witness or a Cloud Witness.

  
It can be ignored for testing system, but not recommended. As a best practice, it is strongly recommended to install and configure shared storage (i.e. SAN, iSCSI) as disk witness for the cluster.

9. Install SQL Server 2016 on both servers

Install SQL Server 2016 on sqln1 (node 1):

Setup.exe /QS /ACTION=Install /FEATURES=SQL,IS /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT Service\MSSQLSERVER" /SQLSYSADMINACCOUNTS="sqln1\Administrator"  /AGTSVCACCOUNT="NT Service\SQLSERVERAGENT" /IACCEPTSQLSERVERLICENSETERMS /SECURITYMODE=SQL /SAPWD="microsoft″ /TCPENABLED=1

Install SQL Server 2016 on sqln2 (node 2):

Setup.exe /QS /ACTION=Install /FEATURES=SQL,IS /INSTANCENAME=MSSQLSERVER /SQLSVCACCOUNT="NT Service\MSSQLSERVER" /SQLSYSADMINACCOUNTS="sqln2\Administrator" /AGTSVCACCOUNT="NT Service\SQLSERVERAGENT" /IACCEPTSQLSERVERLICENSETERMS /SECURITYMODE=SQL /SAPWD="microsoft″ /TCPENABLED=1

10. Enables Always On feature on the instance of SQL Server

The Enable-SqlAlwaysOn PowerShell command enables Always On on an instance of SQL Server. If the Always On availability groups feature is enabled while the SQL Server service is running, the database engine service must be restarted for the changes to complete. Unless you specify the Force parameter, the command prompts you to restart the service.

To enable SQL Always On, we need to provide SERVERNAME\INSTANCENAME as value of the parameter -ServerInstance.  If the default instance name is used like I did above (MSSQLSERVER), just provide the SERVERNAME. 

Enable SQL Always On on sqln1 (node 1)

PS C:\> Enable-SqlAlwaysOn -ServerInstance sqln1 -Force

Enable SQL Always On on sqln2 (node 2)

PS C:\> Enable-SqlAlwaysOn -ServerInstance sqln2 -Force

11.  Create Database Mirroring Endpoint with certificate-authenticating Database Mirroring Connections

SQL Always On replicates data from primary database to secondary database through Database Mirroring Connections between Endpoint of SQL instances. Therefore, Endpoints must be correctly configured as following on all nodes.

Note: All Transact-SQL statements have to be run in the master database. Since Windows Server is core version and GUI is not available, all Transact-SQL will be run locally with SQL Server command utility sqlcmd, start sqlcmd as SA and connect to master database as following (on all nodes)

C:\>sqlcmd -U sa
userPassword:
1> select db_name();
2> go
                                                                                                                       
-------------------------
master                                                                                                                 

(1 rows affected)

Configure server instances for outbound mirroring connections on sqln1 (node1)

-- Create a database master key
-- Use a unique, strong password, and record it in a safe place. Do not use microsoft like me
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'microsoft'
GO

-- Create a new certificate
-- When EXPIRY_DATE option is used to create certificate, it is recommended to use SQL Server Management
-- Studio to create a Policy-Based Management rule to alert you when your certificates are expiring
CREATE CERTIFICATE sqln1_cert
   WITH SUBJECT = 'sqln1 certificate for database mirroring',
   EXPIRY_DATE = '12/31/2029'
GO

-- View the certificates in the master database
SELECT * FROM sys.certificates;
GO

-- Check if the database mirroring endpoint exist on the server instances
SELECT name, role_desc, state_desc, connection_auth_desc, encryption_algorithm_desc  
   FROM sys.database_mirroring_endpoints; 
GO  

-- Create an Endpoint if it does not exist
-- Note: use ALTER ENDPOINT instead of CREATE ENDPOINT to link endpoint with new certificate created above (sqln1_cert)
CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
     LISTENER_PORT = 5022,
     LISTENER_IP = ALL
     )
   FOR DATABASE_MIRRORING(
     AUTHENTICATION = CERTIFICATE sqln1_cert,
     ROLE = ALL,
     ENCRYPTION = REQUIRED ALGORITHM AES
     );
GO

-- Back up the certificate and the backup file will be copied to the other nodes.
BACKUP CERTIFICATE sqln1_cert TO FILE = 'c:\sqln1_cert.cer'
GO

Configure server instances for outbound mirroring connections on sqln2 (node2)

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'microsoft'
GO

CREATE CERTIFICATE sqln2_cert
   WITH SUBJECT = 'sqln2 certificate for database mirroring',
   EXPIRY_DATE = '12/31/2029'
GO

CREATE ENDPOINT Endpoint_Mirroring
   STATE = STARTED
   AS TCP (
     LISTENER_PORT = 5022,
     LISTENER_IP = ALL
     )
   FOR DATABASE_MIRRORING(
     AUTHENTICATION = CERTIFICATE sqln2_cert,
     ROLE = ALL,
     ENCRYPTION = REQUIRED ALGORITHM AES
     );
GO

BACKUP CERTIFICATE sqln2_cert TO FILE = 'c:\sqln2_cert.cer'
GO

Copy certificate backup to other nodes

-- On sqln1 (node1)
C:\>dir *.cer
C:\>copy \\sqln2\c$\sqln2_cert.cer .\
C:\>dir *.cer

-- On sqln2 (node2)
C:\>dir *.cer
C:\>copy \\sqln1\c$\sqln1_cert.cer .\
C:\>dir *.cer

Configure server instances for inbound mirroring connections on sqln1

-- Create a login for the other node (sqln2)
-- Substitute a password of your own for the sample password
CREATE LOGIN sqln2_login WITH PASSWORD = 'microsoft'; 
GO

-- Check if login is created
SELECT name,create_date  FROM sys.server_principals;
GO

--Create a user for that login
CREATE USER sqln2_user FOR LOGIN sqln2_login; 
GO

-- Check if user is created
select name,createdate from sys.sysusers;
GO

-- Associate the certificate copied from other node (sqln2) with the user created above
CREATE CERTIFICATE sqln2_cert AUTHORIZATION sqln2_user FROM FILE = 'C:\sqln2_cert.cer';
GO 

-- Grant CONNECT permission on the login for the remote mirroring endpoint
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO sqln2_login; 
GO

This completes setting up certificate authentication for sqln2 to log in to sqln1. Now, we need to perform the equivalent inbound steps for sqln1 on sqln2.

Configure server instances for inbound mirroring connections on sqln2

-- Create a login for the other node (sqln1)
CREATE LOGIN sqln1_login WITH PASSWORD = 'microsoft'; 
GO

--Create a user for that login
CREATE USER sqln1_user FOR LOGIN sqln1_login; 
GO

-- Associate the certificate copied from other node (sqln1) with the user created above
CREATE CERTIFICATE sqln1_cert AUTHORIZATION sqln1_user FROM FILE = 'C:\sqln1_cert.cer';
GO 

-- Grant CONNECT permission on the login for the remote mirroring endpoint
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO sqln1_login; 
GO

12. Create a test database on sqln1 for Always On feature testing

-- Create database
create database TESTDB
go

-- Check created databases
select name from sys.databases
go

-- alter database to use the full recovery model
-- Prerequisite for candidate database to join Availability Group
ALTER DATABASE TESTDB SET RECOVERY FULL; 
GO
-- Make a full backup
-- Prerequisite for candidate database to join Availability Group through SQL Server Management Studio
backup database TESTDB to disk = 'c:\testdb.bak';
go

13. Create Availability Group

Run following Transact-SQL in master database on sqln1 where TESTDB is created

CREATE AVAILABILITY GROUP TESTGRP
  WITH
    (
     AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    )
  FOR DATABASE TESTDB
  REPLICA ON
    'sqln1' WITH
       (
        ENDPOINT_URL = 'TCP://sqln1.lab.dbaplus.ca:5022',
        FAILOVER_MODE = AUTOMATIC,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        SECONDARY_ROLE
          (
           ALLOW_CONNECTIONS = ALL
          )
       ),
    'sqln2' WITH
       (
           ENDPOINT_URL = 'TCP://sqln2.lab.dbaplus.ca:5022',
           FAILOVER_MODE = AUTOMATIC,
        SEEDING_MODE = AUTOMATIC,       
           AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
           SECONDARY_ROLE
          (
              ALLOW_CONNECTIONS = ALL
          )
       )
GO

The Availability Group is created with following options,

  * Database backup job should occur on a secondary replica except when the primary replica is the only online replica. In that case, the backup should occur on the primary replica.

  * All connections are allowed to the databases in the secondary replica for read-only access.
 
  * Automatic failover is enabled
 
  * The secondary replica will be initially seeded (automatic seeding) over the network, and does not require to backup and restore a copy of the primary database on the replica
 
 
Run following Transact-SQL in master database on sqln2 to join sqln2 as the secondary replica to the availability group

ALTER AVAILABILITY GROUP TESTGRP JOIN; 
GO 

Grant create database permission on in master database on sqln2 (secondary replica) to availability group

ALTER AVAILABILITY GROUP TESTGRP GRANT CREATE ANY DATABASE
GO

Monitor the replication status by running Transact-SQL in master database on any any nodes

select ag.name as group_name, db.name as database_name, ar.replica_server_name,
       rs.is_primary_replica, rs.synchronization_state_desc, rs.synchronization_health_desc
  from sys.availability_groups ag, sys.dm_hadr_database_replica_states  rs,
       sys.availability_replicas ar, sys.databases db
 where ag.group_id=rs.group_id and ar.replica_id=rs.replica_id and db.group_database_id=rs.group_database_id;

On the primary replica (sqln1), it returns a row for each primary database and an additional row for the corresponding secondary database

group_name    database_name    replica_server_name    is_primary_replica    synchronization_state_desc    synchronization_health_desc
----------  -------------   ------------------- ------------------  --------------------------  ---------------------------
TESTGRP        TESTDB          sqln2               0                   SYNCHRONIZED                HEALTHY
TESTGRP        TESTDB          sqln1               1                   SYNCHRONIZED                HEALTHY

On a secondary replica (sqln2), it returns a row for every secondary database on the server instance

group_name    database_name    replica_server_name    is_primary_replica    synchronization_state_desc    synchronization_health_desc
----------  -------------   ------------------- ------------------  --------------------------  ---------------------------
TESTGRP        TESTDB          sqln2               0                   SYNCHRONIZED                HEALTHY