数据库副本的自动种子设定(自增长)

背景

在 SQL Server 2012 和 2014 中,初始化 SQL Server Always On 可用性组中的次要副本的唯一方法是使用备份、复制和还原。

在一个高可用组里面添加一个数据库需要很多手动任务和一些必要条件。需要完成的这些工作中,有一些是有些困难的,比如:

  • 我们需要从主副本中备份数据库,并将这个备份分发到可用组的其他副本中。这是基于手动操作的,因为这需要你从主数据库备份还原数据库到次要副本。
  • 有时候,我们不能通过网络防火墙在副本之间传送数据库备份文件。这种情况下,需要开放SMB协议端口,以便能在节点间传输备份文件。
  • 通常,复制一个备份文件会占用更多空间,这是额外的需求,更多的磁盘空间。
  • 特别是针对第三方备份的方案,会导致备份/还原链的中断。但是这又取决于数据库的备份策略。
  • 分发和还原数据库是耗时并且不透明的过程。

作为一个兼职DBA,我不喜欢处理这些问题。当我创建数据库在主节点上时,我想要数据库被自动创建在所有副本上,幸运的是SQLServer 2016以后这样的工作就容易多了。为了更好的帮助DBA们处理这些问题,微软引入了可用组的自动增长数据库。即SQL Server 2016 引入了用于初始化次要副本的新功能 - 我一般称之为数据库自增长(自动种子设定)

那么这个自动化如何实现的?

当你打算创建一个数据库在主副本上,并且加入数据库到AG里面时,自动填充就会在数据库镜像端间进行通信,并且复制数据库到次要副本中。假设有一个可用组,由N个副本组成,replicas - Replica1, Replica2, ... ReplicaN。当前你的主副本是Replica2 ,然后你创建一个新的数据库在主副本上。下一步就是要尽快把数据库加入到可用组的其他副本上。这个解决方案就如下图的流程所示:

数据库自动种子设定(自增长),没有特殊的必要条件和限制,只是需要数据和日志文件路径在可用组中是完全一致的。这个可用组配置自增长,数据库在可用组中一定是完整还原模式,需要有一个完成备份以及事务日志备份。如果在可用组中用手动同步加入的数据库也需要上述一样的条件。

启动自动种子设定功能

在每一个需要自增长数据库的副本上,需要允许可用组创建数据库。下面的脚本用来允许AG创建数据库,但是确保你可以连接到master数据库并且能够对所有可用组中的副本执行这个脚本:

ALTER AVAILABILITY GROUP [{your_AG_name}]
GRANT CREATE ANY DATABASE;
GO

可用组必须被切换为自动增长模式。该模式可以通过在每个副本中执行下面额T-SQL代码来实现(或者每个副本中启动自增模式):

ALTER AVAILABILITY GROUP [{your_AG_name}]
MODIFY REPLICA ON '{your_replica_name}'
WITH (SEEDING_MODE = AUTOMATIC);
GO

另外,对于Always On 的可用组来说,自增期间能够使用跟踪标记trace flag 9567压缩数据流,从而大大减少传输时间。相关内容可以再找个连接看到: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql

添加一个带有自增长属性的数据库

打开SSMS并且连接可用组主节点。导航到AlwaysOn 高可用(AlwaysOn High Availability)文件,打开“Availability Groups”,然后找到打算添加数据库的可用组。在打开"Availability Databases" 文件夹。右击"Availability Databases",右键菜单"Add databases..", 这个菜单选项将会激活配置向导如下:

选择打算加入到可用组的数据库:

注意,这个数据库一定满足一下必要条件。选择选项“Skip initial data synchronization”:

在次要节点,你会看到数据库已经被还原的信息:

因此,你也可以在msdb.dbo.restorehistory 表中看到每个数据库的增加信息。在主服务器,可以在表msdb.dbo.backupmediafamily 中看到每个数据库信息,数据库可以shiyo8ng下面的T-SQL语句实现:

ALTER AVAILABILITY GROUP {your_AG_name} ADD DATABASE {your_database_name};

这个脚本必须在可用组的每个数据库上执行。

优缺点

我通常使用自动数据库填充为可用组并且发现它很有价值。例如,微软SharePoint 支持组有权去创建新的数据库在主副本上,但是他们不能直接把数据库加入到可用组中,因为这样做影响了数据库的高可用。当我们使用自动数据填充在指定的可用组上时,我们可以自动添加新的Sharepoint 数据库在可用组上,使用一个将所有最近创建的Sharepoint 数据库添加到可用组上的脚本,使用2016这个特性不在需要使用手动操作。

综上所述,使用自动填充的优势有如下几点:

  • 操作过程简单.
  • 节省磁盘空间.
  • 节省备份还原事件,因为SQLServer复制数据库使用网络.
  • 使用追踪标记9567,能够大大减少同步时间。

在可用组中使用自动增长的缺点:

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据和云

经典故障分析 -用好UTL_FILE包其实并不是太容易

作者介绍 ? 崔华 网名 dbsnake Oracle ACE Director,ACOUG 核心专家 UTL_FILE包可以用来读写操作系统上的文本文件...

3369
来自专栏逸鹏说道

SQL SERVER几种数据迁移/导出导入的实践

SQLServer提供了多种数据导出导入的工具和方法,在此,分享我实践的经验(只涉及数据库与Excel、数据库与文本文件、数据库与数据库之间的导出导入)。 (一...

6119
来自专栏C/C++基础

MySQL设置远程访问

在MySQL Server端,执行mysql 命令进入mysql 命令模式。使用grant命令用来建立新用户,指定用户口令并增加用户权限。命令格式如下:

1291
来自专栏python3

解决centos7 /etc/rc.local 不能执行

最近发现centos7 的/etc/rc.local不会开机执行,于是认真看了下/etc/rc.local文件内容的就发现了问题的原因了

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

11g中关于控制文件自动备份的改进(r6笔记第22天)

在之前做一个测试演示的时候,使用的是11gR2的库,在说rman的备份配置的时候有一个功能时控制文件的自动备份, CONFIGURE CONTROLFILE ...

3444
来自专栏散尽浮华

Mysql备份系列(1)--备份方案总结性梳理

mysql数据库备份有多么重要已不需过多赘述了,废话不多说!以下总结了mysql数据库的几种备份方案: 一、binlog二进制日志通常作为备份的重要资源,所以再...

6169
来自专栏醉梦轩

Ubuntu 16.04 部署MySQL服务

MySQL是一款开源的关系型数据库管理系统,大量公司都在使用它,或是在它的基础上做二次开发。有时,我们会需要在Linux系统上部署MySQL服务用于测试。

1792
来自专栏Danny的专栏

SQL Server 2008 附加数据库时出错

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/huyuyang6688/article/...

1.8K3
来自专栏猿天地

solr or es 结合弥补mongodb的全文检索功能

最近将公司的solr集群升级到了最新的6.5版本。 之前用了N年的是3点多的版本,那个时候solr还不支持自带的集群,集群方式都是自己实现的。 公司里用的最多的...

44614
来自专栏Linux运维学习之路

MySQL体系结构及多实例

MySQL客户端和服务器端模型 MySQL是一个典型C/S,服务器端与客户端两部分组成 服务器端程序  mysqld mysql自带的客户端(mysql mys...

35812

扫码关注云+社区