SQLServer Alwayson Without Domain and Samba As Witness Node

Manual Doc

1. 准备阶段

1- 首先，开出两台 Windows 虚机及1台 Linux 虚机; 确认在同一个VPC下，相互之间网通。

2- 分别关闭防火墙；如有黑白名单，注意修改

3- 分别修改群集节点主机名称，Windows 如需重启，请重启使之生效

```
sudo hostnamectl set-hostname <host-name>
```

4- 配置主机域名映射：

Linux - /etc/hosts
Windows- C:\Windows\System32\drivers\etc\host

10.0.44.44      fc-cluster-1.xxx.com
10.0.44.43      fc-cluster-2.xxx.com
10.0.44.41      fc-cluster-witness.xxx.com

10.0.44.44      fc-cluster-1
10.0.44.43      fc-cluster-2
10.0.44.41      fc-cluster-witness

10.0.44.45      wfsc-cluster-45

5- 创建群集

（请在Windows 两个节点中执行）
new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1
（在主节点执行即可）
New-Cluster -Name FCCluster -node i-khtaKKE93Q,i-MvUIjVMVxf -AdministrativeAccessPoint Dns -StaticAddress 172.16.16.112 （cluster的ip）

2. Linux Centos 安装Samba

yum install samba samba-client
cp  /etc/samba/smb.conf /etc/samba/smb.conf.bak
vi  /etc/samba/smb.conf
mkdir -p /samba/wfsc-cluster-45
sudo chmod  -R 0755 /samba/wfsc-cluster-45
sudo groupadd sambashare
sudo useradd -M -d /samba/wfsc-cluster-45 -s /usr/sbin/nologin -G sambashare wfsc-cluster-45
sudo chown wfsc-cluster-45:sambashare /samba/wfsc-cluster-45
sudo smbpasswd -a wfsc-cluster-45
sudo smbpasswd -e wfsc-cluster-45
systemctl restart smb.service
systemctl restart nmb.service

verify: smbclient //localhost/wfsc-cluster-45 -U wfsc-cluster-45

sudo chcon -t samba_share_t /samba/wfsc-cluster-45
systemctl stop firewalld
chmod -R 777 wfsc-cluster-45

Result:
[root@fc-cluster-witness samba]# cd /samba/wfsc-cluster-45/
[root@fc-cluster-witness wfsc-cluster-45]# ls -lhtr
total 0
drwxrwsrwx. 2 nobody nobody 59 Jun 28 15:46 13af1f6a-6265-4cca-914e-0d5ad677ee88
[root@fc-cluster-witness wfsc-cluster-45]# cd 13af1f6a-6265-4cca-914e-0d5ad677ee88/
[root@fc-cluster-witness 13af1f6a-6265-4cca-914e-0d5ad677ee88]# ls -lhtr
total 4.0K
-rwxrw-r--. 1 nobody nobody 512 Jun 28 15:45 Witness.log
-rwxrw-r--. 1 nobody nobody   0 Jun 28 15:49 VerifyShareWriteAccess.txt

注意: 做成服务，随机启动，关掉防火墙；在/etc/hosts中配置本机的id，修改smb.conf中的workgroup=WORKGROUP
创建成功后需要在windows的powershell中执行：（\192.168.1.70\wfsc-cluster：共享文件夹位置）
Set-ClusterQuorum -FileShareWitness \\192.168.1.70\wfsc-cluster -AccountName wfsc-cluster -AccessKey xxx

3. 以下是关于 SQL Server 的相关操作

1- 请各自安装SQLSever 软件，并检查运行正常。版本可以为2016/2019 等常见版本；

2- open SQL Server Configuration Manager | Enable AlwaysOn Availability Groups | Restart the Instance for this feature to take effect

3- 2个Windows节点分别各自创建主密钥，证书，Login，User 确保相互之间可以通过证书认证登录

Use Master
Go

-- Create Master Key
-- 将托管主要副本的每个实例都需要数据库主密钥。 如果主密钥不存在，运行以下命令：
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Zggyy2020!';
Go

-- Create Certificate to be used across on FC-CLUSTER-SQL02
-- Change the Certificate Name and Subject to meet your environment
CREATE CERTIFICATE [FC-CLUSTER-SQL01-Cert] WITH SUBJECT = 'FC-CLUSTER-SQL01-Cert';
Go

-- Create Login for FC-CLUSTER-SQL02 Cluster Node
-- Change the Login Name and Password for your environment
-- 在主要副本上，必须为可用性组的每个次要副本创建登录名。 此登录名将被授予权限，以连接到域独立可用性组使用的终结点。
CREATE LOGIN [FC-CLUSTER-SQL02-Login]  WITH PASSWORD = 'Zggyy2020!';
Go

-- Create the User Account for the newly created Login for FC-CLUSTER-SQL02
-- Change the User Account and Login to match above and your environment
CREATE USER [FC-CLUSTER-SQL02-User] FOR LOGIN [FC-CLUSTER-SQL02-Login];
Go

-- Backup the Certificate to copy across to FC-CLUSTER-SQL02
-- Change the location to meet your environment
-- (\\192.168.1.70\wfsc-cluster共享文件目录)
BACKUP CERTIFICATE [FC-CLUSTER-SQL01-Cert] To FILE = '\\192.168.1.70\wfsc-cluster\FC-CLUSTER-SQL01-Cert.cer';
Go

（这一步要等从节点备份完Cert之后执行）
CREATE CERTIFICATE [FC-CLUSTER-SQL02-Cert] AUTHORIZATION [FC-CLUSTER-SQL02-User] FROM FILE = '\\192.168.1.70\wfsc-cluster\FC-CLUSTER-SQL02-Cert.cer';
Go

-- used to check or drop invalid cert
EXEC sp_dropuser 'FC-CLUSTER-SQL01-User' ;
EXEC sp_droplogin 'FC-CLUSTER-SQL01-Login';
DROP MASTER KEY;  
select * from sys.certificates;
DROP CERTIFICATE [FC-CLUSTER-SQL01-Cert];

-- used to create endpoint

CREATE ENDPOINT WGAG_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [FC-CLUSTER-SQL01-Cert] , ROLE = ALL);
Go
GRANT CONNECT ON ENDPOINT::WGAG_Endpoint TO [FC-CLUSTER-SQL02-Login];

Use Master
Go

-- Create Master Key
-- Use your own Password and not this demo password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Zggyy2020!';
Go
-- Create Certificate to be used across on FC-CLUSTER-SQL01
-- Change the Certificate Name and Subject to meet your environment
CREATE CERTIFICATE [FC-CLUSTER-SQL02-Cert] WITH SUBJECT = 'FC-CLUSTER-SQL02-Cert';
Go
-- Create Login for FC-CLUSTER-SQL01 Cluster Node
-- Change the Login Name and Password for your environment
CREATE LOGIN [FC-CLUSTER-SQL01-Login]  WITH PASSWORD = 'Zggyy2020!';
Go
-- Create the User Account for the newly created Login for FC-CLUSTER-SQL01
-- Change the User Account and Login to match above and your environment
CREATE USER [FC-CLUSTER-SQL01-User] FOR LOGIN [FC-CLUSTER-SQL01-Login];
Go
-- Backup the Certificate to copy across to FC-CLUSTER-SQL01
-- Change the location to meet your environment
BACKUP CERTIFICATE [FC-CLUSTER-SQL02-Cert] To FILE = '\\192.168.1.70\wfsc-cluster\FC-CLUSTER-SQL02-Cert.cer';
Go

(这一步要等主节点完成Cert备份之后执行)
CREATE CERTIFICATE [FC-CLUSTER-SQL01-Cert] AUTHORIZATION [FC-CLUSTER-SQL01-User] FROM FILE = '\\192.168.1.70\wfsc-cluster\FC-CLUSTER-SQL01-Cert.cer';
Go

EXEC sp_dropuser 'FC-CLUSTER-SQL02-User' ;
EXEC sp_droplogin 'FC-CLUSTER-SQL02-Login';
select * from sys.certificates;
DROP CERTIFICATE [FC-CLUSTER-SQL01-Cert];

-- used to create endpoint
CREATE ENDPOINT WGAG_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [FC-CLUSTER-SQL02-Cert] , ROLE = ALL);
Go
GRANT CONNECT ON ENDPOINT::WGAG_Endpoint TO [FC-CLUSTER-SQL01-Login];

4- 创建可用性组

• 创建可用性组。 下面的脚本将创建可用性组。

-- 首先进行主数据库的备份
Backup Database dbNew
　　To disk='C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\dbNew.bak'

-- clusterA:创建的故障转移群集名字   sqlnode1:这里如果配置了dns后缀，需要加上
CREATE AVAILABILITY GROUP [clusterB]
    FOR DATABASE db2
    REPLICA ON'sqlnode1'
    WITH (ENDPOINT_URL = N'TCP://sqlnode1:5022', 
        FAILOVER_MODE = AUTOMATIC, 
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
        BACKUP_PRIORITY = 50, 
        SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 
        SEEDING_MODE = AUTOMATIC),
    N'sqlnode2' WITH (ENDPOINT_URL = N'TCP://sqlnode2:5022', 
        FAILOVER_MODE = AUTOMATIC, 
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
        BACKUP_PRIORITY = 50, 
        SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL), 
        SEEDING_MODE = AUTOMATIC);

-- 将辅助服务器实例联接到可用性组，并向可用性组授予创建数据库的权限。 
-- 更新以下脚本，为环境替换尖括号 <> 中的值，并在 SQL Server 的次要副本实例上运行它：
ALTER AVAILABILITY GROUP [clusterB] JOIN
GO  
ALTER AVAILABILITY GROUP [clusterB] GRANT CREATE ANY DATABASE
GO

-- SQL Server 将在辅助服务器上自动创建数据库副本。 如果数据库较大，则可能需要一些时间才能完成数据库同步。 如果数据库在为自动种子设定配置的可用性组中，你可以查询 sys.dm_hadr_automatic_seeding 系统视图来监视种子设定进度。 对于处于为自动种子设定配置的可用性组中的每个数据库，以下查询都返回一行。
SELECT start_time,
    ag.name,
    db.database_name,
    current_state,
    performed_seeding,
    failure_state,
    failure_state_desc
FROM sys.dm_hadr_automatic_seeding autos 
    JOIN sys.availability_databases_cluster db 
        ON autos.ag_db_id = db.group_database_id
    JOIN sys.availability_groups ag 
        ON autos.ag_id = ag.group_id
GO

5- AlwaysOn 测试

创建侦听器：
Use Master
ALTER AVAILABILITY GROUP clusterA   
      ADD LISTENER 'wfsc-cluster-45' ( WITH IP ( ('192.168.1.88','255.255.255.0') ) , PORT = 1433 );   
GO  
配置/etc/hosts
停掉主副本后，可通过可用性组面板观察角色发生切换了。