mssql alwayson[2]

前一篇文章
https://imbs.top/index.php/archives/313/

启用AG并重启mssql-server
在所有节点的SQL Server上启用AlwaysOn AG，然后重启mssql-server服务：
```sql
sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server
```
启用AlwaysOn_health扩展事件会话
```sql
ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO
```
创建数据库镜像端点访问使用的用户
```sql
CREATE LOGIN dbm_login WITH PASSWORD = '**<Your Password>**';
CREATE USER dbm_user FOR LOGIN dbm_login;
```
创建证书【主节点】
```sql
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
TO FILE = '/tmp/dbm_certificate.cer'
WITH PRIVATE KEY (
FILE = '/tmp/dbm_certificate.pvk',
ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
);
```
将证书和私钥拷贝到所有可用副本的服务器上的相同位置。
在每个目标服务器上，授予mssql用户访问这些文件的权限。
```sql
cd /tmp
chown mssql:mssql dbm_certificate.*
```
在辅助服务器上创建证书
```sql
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate
FROM FILE = '/tmp/dbm_certificate.cer'
WITH PRIVATE KEY (
FILE = '/tmp/dbm_certificate.pvk',
DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
);
```
在所有节点上创建数据库镜像端点
可以包含 IP 地址 LISTENER_IP = (0.0.0.0)。 侦听器 IP 地址必须是 IPv4 地址。 还可以使用 0.0.0.0。
如果配置的节点是仅配置副本，唯一有效的值为ROLE = WITNESS。
对于 SQL Server 2017 版本中，支持数据库镜像终结点的唯一身份验证方法是CERTIFICATE。
```sql
CREATE ENDPOINT [Hadr_endpoint]
AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = **<5022>**)
FOR DATA_MIRRORING (
ROLE = ALL,
AUTHENTICATION = CERTIFICATE dbm_certificate,
ENCRYPTION = REQUIRED ALGORITHM AES
);
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
```
在主节点上创建AG
- 三个同步副本
```sql
CREATE AVAILABILITY GROUP [ag1]
WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL)
FOR REPLICA ON
N'<node1>'
WITH (
ENDPOINT_URL = N'tcp://<node1>:<5022>',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = EXTERNAL,
SEEDING_MODE = AUTOMATIC
),
N'<node2>'
WITH (
ENDPOINT_URL = N'tcp://<node2>:<5022>',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = EXTERNAL,
SEEDING_MODE = AUTOMATIC
),
N'<node3>'
WITH(
ENDPOINT_URL = N'tcp://<node3>:<5022>',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = EXTERNAL,
SEEDING_MODE = AUTOMATIC
);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
```
- 两个同步副本
```sql
CREATE AVAILABILITY GROUP [ag1]
WITH (CLUSTER_TYPE = EXTERNAL)
FOR REPLICA ON
N'node1' WITH (
ENDPOINT_URL = N'tcp://node1:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = EXTERNAL,
SEEDING_MODE = AUTOMATIC
),
N'node2' WITH (
ENDPOINT_URL = N'tcp://node2:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = EXTERNAL,
SEEDING_MODE = AUTOMATIC
);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
```
- 两个同步副本和仅配置副本
```sql
CREATE AVAILABILITY GROUP [ag1]
WITH (CLUSTER_TYPE = EXTERNAL)
FOR REPLICA ON
N'<node1>' WITH (
ENDPOINT_URL = N'tcp://<node1>:<5022>',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = EXTERNAL,
SEEDING_MODE = AUTOMATIC
),
N'<node2>' WITH (
ENDPOINT_URL = N'tcp://<node2>:<5022>',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = EXTERNAL,
SEEDING_MODE = AUTOMATIC
),
N'<node3>' WITH (
ENDPOINT_URL = N'tcp://<node3>:<5022>',
AVAILABILITY_MODE = CONFIGURATION_ONLY
);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
```
将辅助副本加入到AG
在所有辅助副本上执行：
```sql
ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL);
ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;
```
将数据库添加到可用性组
确保添加到可用性组的数据库处于完全恢复模式，并具有有效的日志备份。
```sql
CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
TO DISK = N'/var/opt/mssql/bak/db1.bak';
ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];
```
验证是否已在辅助服务器上创建了数据库
在每个辅助服务器上执行：
```sql
SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;
```

添加新评论

关于博主

文章分类

最受欢迎的文章

最近回复

其它