标签：dbm certificate 非域 -- Always 证书 Server key 节点

　　SQLServer 2019开始支持“read-scale for an Always On availability group”，中文翻译的很别扭，是"读取缩放"，繁体版翻译为“读取级别”，其特点不依赖于windows的cluster集群以及域，简化了搭建操作步骤和前置条件，与传统的availability groups类似，缺点是无法实现自动故障转移，本质上是一个不带自动故障转移的availability groups。

另外跑一下题：很多人说微软的文档好，据了解，SQLserver的文档大部分都是机器翻译的（点击这里有搞笑的翻译案例）正如上面截图中提示的“本主题有部分内容为机器翻译”，真的很容易理解吗？

本文尝试一分钟搭建非域&非windows集群模式的AlwaysOn读扩展(read-scale for an Always On availability group)，来体验这种模式搭建AG的快捷性。另外本文的最后会引出几个关于AG节点同步模式的问题。
开始之前：
1，本文windows Server 2019 & SQL Server 2019，主机名分别叫SQL1，SQL2，SQL3
2，本文没有可以严格遵循使用一些SQLServer中的专有词汇，比如主副本/辅助副本，可能会使用主节点/从节点代替，
3，本文不涉及侦听器以及只读路由相关的配置
4，本文测试环境为英文环境的虚拟机，可能会在远程（英文环境）/ 本地（中文环境）SSMS连接相关数据库，所以截图中会有中英文混合出现的情况

step_00 SQLServer 2019 特性

相关特性请参考 read-scale for an Always On availability group

step_01，启用AlwaysOn_health事件会话

所有节点上启用AlwaysOn_health事件会话

step_02 创建基于证书认证的endpoint

SQL1上执行：

SQL2上执行：

SQL3上执行：

step_03 复制证书与私钥到伙伴节点

三台机器最终有所有其他两个节点的证书和私钥

step_04 创建镜像用户并关联证书

SQL1 上执行

SQL2上执行：

SQL3上执行：

完成之后每个节点都会创建其他两个节点的证书做互信访问。

step_05创建可用性组

执行如下脚本创建availability group

注意这里的failover_mode只能为manual，后面会解释。

如下是上文中三个节点上执行的全部脚本。

--  主+所有从节点上分别启用 AlwaysOn_health 事件会话
use master
GO
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
    ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO




--创建基于证书认证的endpoint

/*01，创建主密钥master key */
create master key encryption by password = 'master_key_password_123';

/*02，创建证书，*/
--主题相当于备注，这个证书时做什么用的，这里备注为for always on
create certificate dbm_server1_certificate with subject = 'dbm_for_always_on', EXPIRY_DATE = '2099-12-30'; --默认有效期是1年，这里显示定义为一个日期

/*--03，使用证书为服务器创建一个镜像端点*/
IF NOT EXISTS ( SELECT  1  FROM  sys.database_mirroring_endpoints ) 
BEGIN
    CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        --指定上一步创建的证书名称
        AUTHENTICATION = CERTIFICATE dbm_server1_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
        );
END

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
    ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END


/*--04，备份证书*/
--从节点上还原证书的时候，密码必须跟在主节点上创建证书时一样，注意目标文件夹里不能有同名的文件，如果有需要提前删掉
backup certificate dbm_server1_certificate
to file = 'C:\DBCertificate\dbm_server1_certificate.cer'
with private key (
        file = 'C:\DBCertificate\dbm_server1_certificate.pvk',
        encryption by password = 'private_key_password_123' );




/*05，创建用户，用用镜像库登录 */

/*****************************************************SQL1节点上执行*****************************************************/
--创建数据库镜像endpoint身份验证，主节点上创建，
USE master
GO

CREATE LOGIN dbm_server_login WITH PASSWORD = 'server_1_password';
CREATE USER dbm_server_user FOR LOGIN dbm_server_login;



/*06，用户关联镜像库的证书，使用证书授权用户:在主节点上还原从节点证书 */
--这里的名字保持主节点的名字，做好主从节点证书的区分，密码必须跟主节点上备份证书的密码一致，否则会提示The private key password is invalid，还原失败
create certificate dbm_server2_certificate
--注意这里是SQL2主节点copy过来的证书和私钥
authorization dbm_server_user
from file = 'C:\DBCertificate\dbm_server2_certificate.cer'
with private key (
                    file = 'c:\DBCertificate\dbm_server2_certificate.pvk',
                    decryption by password = 'private_key_password_123'
                );


--这里的名字保持主节点的名字，做好主从节点证书的区分，密码必须跟主节点上备份证书的密码一致，否则会提示The private key password is invalid，还原失败
create certificate dbm_server3_certificate
--注意这里是SQL2主节点copy过来的证书和私钥
authorization dbm_server_user
from file = 'C:\DBCertificate\dbm_server3_certificate.cer'
with private key (
                    file = 'c:\DBCertificate\dbm_server3_certificate.pvk',
                    decryption by password = 'private_key_password_123'
                );

--授权数据库用户对endpoint的权限
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO dbm_server_login;







--创建可用性组
CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
    --注意这里将SQL1和SQL2修改为对应的服务器的主机名，工作组模式下需要完整的主机名
        N'SQL1' WITH (
            ENDPOINT_URL = N'tcp://SQL1:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
            ),
        N'SQL2' WITH (
            ENDPOINT_URL = N'tcp://SQL2:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
            ),
       N'SQL3' WITH (
            ENDPOINT_URL = N'tcp://SQL3:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL,
            SEEDING_MODE = AUTOMATIC,
            SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
            );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;


--此步骤开始手动执行，将数据库加入可用组，这里建议手动添加，可选数据库的同步模式

SQL1上执行的完整脚本

--创建基于证书认证的endpoint

/*01，创建主密钥master key */
create master key encryption by password = 'master_key_password_123';

/*02，创建证书，*/
--主题相当于备注，这个证书时做什么用的，这里备注为for always on
create certificate dbm_server2_certificate with subject = 'dbm_for_always_on', EXPIRY_DATE = '2099-12-30'; --默认有效期是1年，这里显示定义为一个日期

/*--03，使用证书为服务器创建一个镜像端点*/
IF NOT EXISTS ( SELECT  1  FROM  sys.database_mirroring_endpoints ) 
BEGIN
    CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        --指定上一步创建的证书名称
        AUTHENTICATION = CERTIFICATE dbm_server2_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
        );
END

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
    ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END


/*--04，备份证书*/
--从节点上还原证书的时候，密码必须跟在主节点上创建证书时一样，注意目标文件夹里不能有同名的文件，如果有需要提前删掉
backup certificate dbm_server2_certificate
to file = 'C:\DBCertificate\dbm_server2_certificate.cer'
with private key (
        file = 'C:\DBCertificate\dbm_server2_certificate.pvk',
        encryption by password = 'private_key_password_123' );




/*05，创建用户，用用镜像库登录 */

/*****************************************************SQL2主节点上执行*****************************************************/
--创建数据库镜像endpoint身份验证，主节点上创建，
USE master
GO

CREATE LOGIN dbm_server_login WITH PASSWORD = 'server_2_password';
CREATE USER dbm_server_user FOR LOGIN dbm_server_login;



/*06，用户关联镜像库的证书，使用证书授权用户:在主节点上还原从节点证书 */
--这里的名字保持主节点的名字，做好主从节点证书的区分，密码必须跟主节点上备份证书的密码一致，否则会提示The private key password is invalid，还原失败
create certificate dbm_server1_certificate
--注意这里是SQL2主节点copy过来的证书和私钥
authorization dbm_server_user
from file = 'C:\DBCertificate\dbm_server1_certificate.cer'
with private key (
                    file = 'c:\DBCertificate\dbm_server1_certificate.pvk',
                    decryption by password = 'private_key_password_123'
                );


--这里的名字保持主节点的名字，做好主从节点证书的区分，密码必须跟主节点上备份证书的密码一致，否则会提示The private key password is invalid，还原失败
create certificate dbm_server3_certificate
--注意这里是SQL2主节点copy过来的证书和私钥
authorization dbm_server_user
from file = 'C:\DBCertificate\dbm_server3_certificate.cer'
with private key (
                    file = 'c:\DBCertificate\dbm_server3_certificate.pvk',
                    decryption by password = 'private_key_password_123'
                );

--授权数据库用户对endpoint的权限
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO dbm_server_login;

SQL2上执行的完整脚本

--创建基于证书认证的endpoint

/*01，创建主密钥master key */
create master key encryption by password = 'master_key_password_123';

/*02，创建证书，*/
--主题相当于备注，这个证书时做什么用的，这里备注为for always on
create certificate dbm_server3_certificate with subject = 'dbm_for_always_on', EXPIRY_DATE = '2099-12-30'; --默认有效期是1年，这里显示定义为一个日期

/*--03，使用证书为服务器创建一个镜像端点*/
IF NOT EXISTS ( SELECT  1  FROM  sys.database_mirroring_endpoints ) 
BEGIN
    CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = 5022)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        --指定上一步创建的证书名称
        AUTHENTICATION = CERTIFICATE dbm_server3_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
        );
END

IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
    ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END


/*--04，备份证书*/
--从节点上还原证书的时候，密码必须跟在主节点上创建证书时一样，注意目标文件夹里不能有同名的文件，如果有需要提前删掉
backup certificate dbm_server3_certificate
to file = 'C:\DBCertificate\dbm_server3_certificate.cer'
with private key (
        file = 'C:\DBCertificate\dbm_server3_certificate.pvk',
        encryption by password = 'private_key_password_123' );





/*05，创建用户，用用镜像库登录 */

/*****************************************************SQL3主节点上执行*****************************************************/
--创建数据库镜像endpoint身份验证，主节点上创建，
USE master
GO

CREATE LOGIN dbm_server_login WITH PASSWORD = 'server_3_password';
CREATE USER dbm_server_user FOR LOGIN dbm_server_login;



/*06，用户关联镜像库的证书，使用证书授权用户:在主节点上还原从节点证书 */
--这里的名字保持主节点的名字，做好主从节点证书的区分，密码必须跟主节点上备份证书的密码一致，否则会提示The private key password is invalid，还原失败
create certificate dbm_server1_certificate
--注意这里是SQL2主节点copy过来的证书和私钥
authorization dbm_server_user
from file = 'C:\DBCertificate\dbm_server1_certificate.cer'
with private key (
                    file = 'c:\DBCertificate\dbm_server1_certificate.pvk',
                    decryption by password = 'private_key_password_123'
                );


--这里的名字保持主节点的名字，做好主从节点证书的区分，密码必须跟主节点上备份证书的密码一致，否则会提示The private key password is invalid，还原失败
create certificate dbm_server2_certificate
--注意这里是SQL2主节点copy过来的证书和私钥
authorization dbm_server_user
from file = 'C:\DBCertificate\dbm_server2_certificate.cer'
with private key (
                    file = 'c:\DBCertificate\dbm_server2_certificate.pvk',
                    decryption by password = 'private_key_password_123'
                );

--授权数据库用户对endpoint的权限
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO dbm_server_login;

SQL3上执行的完整脚本

step_06添加数据库到可用性组

按照向导，一步一即可

所选的数据库必须是完整恢复模式，且经过完整的数据备份。

连接至两个副本节点

自动设置种子意思是自动在SQL2和SQL3两个节点上初始化添加到AG的数据库

AG节点状态

用脚本查看各个节点的状态

Step_07 手动故障转移

在Availablity Group的属性里可以看到，因为上面属于非windows cluster模式的AG，因此这里的cluster type为None，同时数据库的failover mode也只有Manual手动模式可选

如果是基于windows cluster的AlwaysOn，那么这的failover mode是可选为Manual或者Automatic的，

参考下图，是基于window cluster 集群模式的AG的failover mode的可选项，个人认为这也是非windows集群模式的与windows集群模式下的AlwaysOn的唯一的区别。

将SQL1和SQL2修改为同步模式，为故障转移做准备

原本已经将SQL1和SQL2设置为同步提交模式，可能是刚设置完，这里没有刷新出来，所以会给出一个数据未完全同步的一个警告

故障转移后需要执行恢复数据移动，然后将数据看联机至AG组。

ALTER DATABASE [DB01] SET HADR RESUME;
GO

成功将主节点转移到SQL2节点。

Step_07 AlwaysOn节点的同步模式（ASYNCHRONOUS_COMMIT）真的可靠吗？

搭建好AG的环境只是万里长征走完第一步，更重要是是对细节的理解。

正如上文所提到的，主副本和辅助副本之间有同步模式(ASYNCHRONOUS_COMMIT)和异步模式(ASYNCHRONOUS_COMMIT)两种可选模式，同步模式和异步模式。类似于镜像的同步模式和异步模式，同步模式以为这主节点上的写操作一直要等到同步模式的从节点接收到主节点的事物日志之后，主节点才能反馈给客户端提交成功的消息，异步模式则没有该限制。

参考这里：https://learn.microsoft.com/zh-cn/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-ver15&redirectedfrom=MSDN

同步和异步只两个节点之间的数据等待模式，这里每个节点都有一个选项，那么这个同步模式(ASYNCHRONOUS_COMMIT)和异步模式(ASYNCHRONOUS_COMMIT)又该如何理解呢？

直接翻译成人话：

1，主节点异步提交模式：如果主要副本配置为“异步提交模式” ，则从节点不管是同步或者异步，主节点提交事务都无须等待从节点（永远是异步模式）。 2，主节点同步提交模式：需要分两种情况　　2.1 如果从节点是同步模式，则主节点与从节点同步提交（同步模式）　　2.2 如果从节点是异步模式，则主节点提交事务都无须等待从节点（异步模式） 3，上面截图备注中的备注：　　当一辅助副本超过了主副本的会话超时期限，则主副本将暂时切换到该辅助副本的异步提交模式。在该辅助副本重新与主副本连接后，它们将恢复同步提交模式。　　上述第2.1中的情况，也就是同步提交模式，主结点提交事务等从提交同步提交，如果对应的从超时或者从节点自身宕机，同步模式将会自动退化为异步模式。　　此时同步提交模式退化为异步提交(这一点跟MySQL的半同步复制如出一辙，都存在主从同步模式退化的问题)。那么这里先引出几个问题： 1，即便是同步模式，在某些条件下会自动“退化”为异步模式，此时主节点其实是在裸奔，最严重的是从节点直接宕机，主节点还可以正常读写，在某些高安全模式下是不允许单点运行的，这是可以被接受的吗，又如何破解？ 2，上面截图中的Session Timeout是什么作用，可以理解成一个普通的连接超时时间吗？ 3，截图中的参数REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT，与节点之间的同步和异步模式有什么关系？该参数该如何理解？

标签：dbm,certificate,非域,--,Always,证书,Server,key,节点
From： https://www.cnblogs.com/wy123/p/17905118.html

SQL Server 2019 非域&非集群环境创建Always On “只读扩展”