当SQL Server爱上Linux:配置 SQL Server 2017 上的可用性组初体验

作者 | 张乐奕:Oracle ACE 总监,ACOUG (中国 Oracle 用户组)联合发起人。Oracle 数据库高可用解决方案与 Exadata 一体机专家。长于数据库故障诊断,性能调优。作为多家知名论坛版主,热衷社区技术分享,同时也是 Exadata 用户组的发起人,组织策划并作为技术分享者的活动已超过百场。

当微软认真起来,我们发现一切皆有可能。

虽然在关系型数据库领域Oracle是当之无愧的王者,但是SQL Server却也一直排在前三,下图是5月DB-Engines上的数据库流行度排行。

在2017年,微软宣布SQL Server将可以运行在Linux上,拥抱开源让微软赢得了很多赞誉,SQL Server 也成为了 2016年 DB-Engines 的年度数据库,我们也发表过一些文章,回顾参考:SQL Server for Linux 下一版本的公共预览

在之前的预览版中,Public preview of the next release of SQL Server on Linux,Always On Availability Groups 还是不支持的功能,但是在最新的 SQL Server 2017 on Linux 中,该功能已经引入。 让我们一起了解SQL Server的变化。

1

准备测试环境的服务器

在 Always On AG 中如果需要自动 Failover 至少需要集群中有 3 台服务器,但是我只是测试功能,因此只使用了两台服务器。并且本文不涉及任何 Pacemaker 的设置,完全是数据库层面的 AG 配置。

我使用的是 Google Compute Engine 的2台 VM,最低配的 1vCPU,3.75GB 内存。

如果要通过远程客户端配置 SQL Server,则需要在 VPC network 的 Firewall rules 中将 1433 端口开放,如果是在虚拟机本地的 sqlcmd 中操作,则无需配置。

2

操作系统:CentOS7

$ cat /etc/centos-release CentOS Linux release 7.4.1708 (Core)

在 /etc/hosts 中配置双方服务器的名称和IP地址的解析,以保证两台机器可以通过服务器名称互相访问。

重要!服务器主机的 hostname 必须少于等于 15 个字符,否则在配置过程会出现各种莫名其妙的权限报错。

3

安装SQL Server

SQL Server for Linux 的安装非常简单,可以参考我之前的这篇文章。

Public preview of the next release of SQL Server on Linux

4

启用AlwaysOn AG功能

执行范围:在所有机器上执行

安装完的 SQL Server,默认是没有启用 AlwaysOn AG 功能的,需要手工开启,开启的方法很简单。开启该功能需要重启数据库实例。

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1 sudo systemctl restart mssql-server

5

启用 AlwaysOn_health 事件

执行范围:在所有机器上执行

这一步不是必须的。

ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);

6

创建数据库复制的用户

执行范围:在所有机器上执行

CREATE LOGIN dbm_login WITH PASSWORD = ‘YourPassword’; CREATE USER dbm_user FOR LOGIN dbm_login;

7

创建认证

执行范围:在 Primary Replica 机器上执行

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourPassword'; CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm'; BACKUP CERTIFICATE dbm_certificate TO FILE = '/var/opt/mssql/data/dbm_certificate.cer' WITH PRIVATE KEY ( FILE = '/var/opt/mssql/data/dbm_certificate.pvk', ENCRYPTION BY PASSWORD = 'YourPassword' );

将生成的 dbm_certificate.cer 和 dbm_certificate.pvk 文件 scp 到另外一台服务器的相同位置并修改属主,这台服务器就是 Secondary Replica。

cd /var/opt/mssql/data chown mssql:mssql dbm_certificate.*

然后在这台服务器上导入认证。

执行范围:在 Secondary Replica 机器上执行

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourPassword'; CREATE CERTIFICATE dbm_certificate AUTHORIZATION dbm_user FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer' WITH PRIVATE KEY ( FILE = '/var/opt/mssql/data/dbm_certificate.pvk', DECRYPTION BY PASSWORD = 'YourPassword' );

8

创建数据库复制的 Endpoint

执行范围:在所有机器上执行

CREATE ENDPOINT [Hadr_endpoint] AS TCP (LISTENER_PORT = 5022) FOR DATA_MIRRORING ( ROLE = ALL, AUTHENTICATION = CERTIFICATE dbm_certificate, ENCRYPTION = REQUIRED ALGORITHM AES ); ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED; use master GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];

9

创建 Availability Groups

创建 Availability Groups

执行范围:在 Primary Replica 机器上执行

CREATE AVAILABILITY GROUP [ag1] WITH (CLUSTER_TYPE = EXTERNAL) FOR REPLICA ON N'centos1' WITH ( ENDPOINT_URL = N'tcp://centos1:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC ), N'centos2' WITH ( ENDPOINT_URL = N'tcp://centos2:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC ); ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

在主库上创建了 AG 之后,备库需要加入 AG。

执行范围:在 Secondary Replica 机器上执行

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL); ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

10

将数据库加入 AG

这里新建一个数据库 db1,将它加入到 ag1 中。由于上面设置的 SEEDING_MODE 参数为 AUTOMATIC,因此这个 db1 数据库将会在备库实例中自动创建,后续对于该库进行的任何操作也会自动复制到备库中。

执行范围:在 Primary Replica 机器上执行

CREATE DATABASE [db1]; ALTER DATABASE [db1] SET RECOVERY FULL; BACKUP DATABASE [db1] TO DISK = N'/var/opt/mssql/data/db1.bak'; ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

11

允许 Secondary Replica 可以被只读访问

在以上的创建过程中创建出来的 AG 中的备库是不允许被访问的,如果要访问将会遇到以下错误。

The target database, ‘db1’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

执行范围:在 Primary Replica 机器上执行,立刻生效。

use master ALTER AVAILABILITY GROUP ag1 MODIFY REPLICA ON N'centos2' WITH ( SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL ) );

12

测试

在主库中随便创建一张新表,再插入几条记录。

1> use db1 2> select * into t_test from sys.databases; 3> insert into t_test select * from t_test; 4> GO Changed database context to 'db1'. (5 rows affected) (5 rows affected)

在备库中查询,这张表已经复制成功。

1> use db1 2> select count(*) from t_test; 3> GO Changed database context to 'db1'. ------- 10 (1 rows affected)

如果在备库中尝试更新数据,将会遇到以下错误。

1> delete from t_test; 2> GO Msg 3906, Level 16, State 2, Server centos2, Line 1 Failed to update database "db1" because the database is read-only.

13

监控 AG 状态

通过以下这些视图可以监控 AG 中各个部分的状态。

group的监控

select * from sys.availability_groups; select * from sys.availability_groups_cluster; select * from sys.dm_hadr_availability_group_states;

replica 的监控

select * from sys.availability_replicas; select * from sys.dm_hadr_availability_replica_states; select * from sys.dm_hadr_availability_replica_cluster_nodes; select * from sys.dm_hadr_availability_replica_cluster_states;

在 AG 中的 database 的监控

select * from sys.availability_databases_cluster; select * from sys.dm_hadr_database_replica_states; select * from sys.dm_hadr_database_replica_cluster_states; select name,database_id,replica_id,group_database_id from sys.databases;

参考文档

本文配置步骤的参考文档为:

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-configure-ha?view=sql-server-linux-2017

注:详情请戳原文链接

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-05-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

system表空间不足的问题分析(二) (r8笔记第5天)

今天收到一条不太起眼的报警邮件,大体内容是某个表空间的空间有些紧张了。大体内容如下: Tablesapce: CMBI_SNZG_DATA: 92.2% [W...

3427
来自专栏杨建荣的学习笔记

undo retention的思考(一)

最近有个网友咨询我一个问题,是关于undo_retention的,对于这个参数没有过多关注,只是知道需要设置undo_retention搭配使用undotabl...

4525
来自专栏数据和云

专家出诊:SQL Server 高CPU系列之索引诊断

作者题记:CPU高使用率往往会导致SQL Server服务响应缓慢,查询超时,甚至服务挂起僵死,可以说CPU高使用率是数据库这种后台进程服务的第一大杀手。引发C...

4224
来自专栏杨建荣的学习笔记

迁移式升级的测试(三)(r10笔记第36天)

还是继续昨天的任务。 前面的内容可以参见:迁移式升级的一点思考 (r10笔记第27天)、迁移式升级的新方案测试 (r10笔记第30天)、迁移式升级的测试(二)(...

3525
来自专栏乐沙弥的世界

启用 Oracle 10046 调试事件

    Oracle 10046是一个Oracle内部事件。最常用的是在Session级别设置sql_trace(alter session set sql_t...

472
来自专栏数据和云

深入内核:DUMP Block的数据读取与脏数据写入影响

张乐奕 云和恩墨副总经理 Oracle ACE 总监 ITPUB Oracle数据库管理版版主、Oracle高可用版版主、ACOUG联合创始人 如果我们执行al...

2896
来自专栏idba

insert 语句加锁机制

之前的文章里面总结了很多死锁案例,其实里面有几篇文章对于insert加锁流程表述的不准确,而且微信公众号又无法修改,所以通过本文重新梳理insert...

1243
来自专栏数据之美

MySQL 死锁与日志二三事

最近线上 MySQL 接连发生了几起数据异常,都是在凌晨爆发,由于业务场景属于典型的数据仓库型应用,白天压力较小无法复现。甚至有些异常还比较诡异,最后 root...

3846
来自专栏数据之美

详解 MySQL 5.7 新的权限与安全问题

1、新版 MySQL 权限问题:  问题:SQL Error (1130): Host '192.168.1.100' is not allowed to co...

2K10
来自专栏杨建荣的学习笔记

浅谈exp/imp(下) (r5笔记第84天)

相关链接:浅谈exp/imp(上) (r5笔记第81天) 你可能 不了解的dump文件 在工作中,dump文件对于dba而言是再平常不过的文件了。不过因为dum...

3259

扫码关注云+社区

领取腾讯云代金券