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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

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

0938b5708911296d73a0e046ce1c0e0e8474ef4c

在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的变化。

准备测试环境的服务器

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

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

f946b5302cc57a106a657196cc787b7614d95fff

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

操作系统:CentOS7

$ cat /etc/centos-release

CentOS Linux release 7.4.1708 (Core)

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

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

安装SQL Server

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

Public preview of the next release of SQL Server on Linux

启用AlwaysOn AG功能

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

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

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

sudo systemctl restart mssql-server

启用 AlwaysOn_health 事件

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

这一步不是必须的。

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

创建数据库复制的用户

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

CREATE LOGIN dbm_login WITH PASSWORD = ‘YourPassword’;

CREATE USER dbm_user FOR LOGIN dbm_login;

创建认证

执行范围:在 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'

);

创建数据库复制的 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];

创建 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;

将数据库加入 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];

允许 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 )

);

测试

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

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.

监控 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;


原文发布时间为:2018-05-9

本文作者:张乐奕

本文来自云栖社区合作伙伴“数据和云”,了解相关信息可以关注“数据和云”。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
3天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库:第二章 关系数据库
【SQL server】玩转SQL server数据库:第二章 关系数据库
38 10
|
1月前
|
SQL 数据库 数据安全/隐私保护
Sql Server数据库Sa密码如何修改
Sql Server数据库Sa密码如何修改
|
13天前
|
SQL
启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
15 0
|
3天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
38 6
|
3天前
|
SQL 存储 数据挖掘
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
服务器数据恢复环境: 一台安装windows server操作系统的服务器。一组由8块硬盘组建的RAID5,划分LUN供这台服务器使用。 在windows服务器内装有SqlServer数据库。存储空间LUN划分了两个逻辑分区。 服务器故障&初检: 由于未知原因,Sql Server数据库文件丢失,丢失数据涉及到3个库,表的数量有3000左右。数据库文件丢失原因还没有查清楚,也不能确定数据存储位置。 数据库文件丢失后服务器仍处于开机状态,所幸没有大量数据写入。 将raid5中所有磁盘编号后取出,经过硬件工程师检测,没有发现明显的硬件故障。以只读方式将所有磁盘进行扇区级的全盘镜像,镜像完成后将所
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
|
7天前
|
SQL 安全 Java
SQL server 2017安装教程
SQL server 2017安装教程
13 1
|
20天前
|
SQL 存储 Python
Microsoft SQL Server 编写汉字转拼音函数
Microsoft SQL Server 编写汉字转拼音函数
|
1月前
|
SQL 存储 数据库
数据安全无忧,SQL Server 2014数据库定时备份解密
数据安全无忧,SQL Server 2014数据库定时备份解密
|
1月前
|
SQL 网络协议 Windows
破解SQL Server迷局,彻底解决“管道的另一端无任何进程错误233”
破解SQL Server迷局,彻底解决“管道的另一端无任何进程错误233”
|
1月前
|
SQL 数据库 数据安全/隐私保护
SQL Server的安装步骤_kaic
SQL Server的安装步骤_kaic