采用自动种子的方式搭建sqlserver alwaysOn，和备份恢复模块

什么是自动种子，他的优点和缺点是什么

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

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

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

为了更好的帮助DBA们处理这些问题，微软引入了可用组的自动增长数据库。即SQL Server 2016 引入了用于初始化次要副本的新功能 - 我一般称之为数据库自增长（自动种子设定）

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

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

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

• 在同步过程中需要巨大的网络流。
• 处理需要一些手动处理步骤

为什么要使用自动种子

SQL Server 2016 推出了可用性组的自动种子设定。 自动种子设定使用日志流传输，使用 VDI 备份并将备份传输到使用endpoints配置的次要副本的每个数据库【若要加入 Always On 可用性组 或数据库镜像，服务器实例上必须创建自己专用的“数据库镜像端点” （database mirroring endpoint）。 此端点用途特殊，专门用于接收来自其他实例的连接。数据库镜像端点使用TCP协议在参与数据库镜像会话或承载可用性副本的实例之间发送和接收消息。 数据库镜像端点在唯一的 TCP 端口号上进行侦听。但注意客户端与主服务器或主副本的连接不使用数据库镜像端点。】。 创建具有自动种子设定的可用性组时，SQL Server 将自动为该组中每个数据库创建次要副本。 你不再需要手动备份和还原次要副本。
使用自动种子可以简化sqlserver AlwaysOn的搭建过程，而且如果要对数据库做备份恢复的话，自动种子也可以起到自动恢复的作用。
在某些情况下，自动种子设定可能不是初始化次要副本的最优选择。 自动种子设定过程中，SQL Server 通过网络执行备份以进行初始化。 如果数据库非常大或者次要副本是远程副本，此过程会很缓慢。 在备份过程中，无法截断这些数据库的事务日志，因此，如果繁忙的数据库初始化过程耗时冗长，则可能导致事务日志大幅增加。 在将数据库添加到具有自动种子设定的可用性组之前，请先评估数据库大小、负载和副本之间的站点距离。
自动种子设定是单线程进程，最多可处理五个数据库。 单线程可能会影响性能，尤其是在可用性组具有多个数据库的情况下。
但是总体来看，自动种子的使用已经是一种趋势。

配置思路

我们采用的时windows无域配置方式，因此选用了一台linux服务器作为见证节点。可以采用samba的方式配置linux和window之间的通信。
两台window机器的sqlserver互通采用证书认证的方式，自动种子采用window认证的方式。【我这边的环境貌似只有这样可以行得通，一般都采用windows认证方式就可以搭建成功】

配置流程

服务器操作

1- 配置主机域名映射：

Linux - /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4 mssql-witness linuxName
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6 mssql-witness

Windows- C:\Windows\System32\drivers\etc\

192.168.1.01 node1Name
192.168.1.02 node2Name
192.168.1.03 cluster
192.168.1.04 listener

2- 创建群集

new-itemproperty -path HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System -Name LocalAccountTokenFilterPolicy -Value 1
New-Cluster -Name FCCluster -node name1,name2 -AdministrativeAccessPoint Dns -StaticAddress 192.168.1.03 （cluster的ip）

3- 添加共享文件

Set-ClusterQuorum -FileShareWitness \\192.168.1.05(linux IP)\wfsc-cluster -AccountName wfsc-cluster -AccessKey unicloud

以下是关于 SQL Server 的相关操作

1- 在sqlserver服务中勾选可用性组选项，并重启服务。

2-使用sa登录sqlserver，密码在guest_Info.conf中获取

3- start to build

-- Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!';
-- Change the Certificate Name and Subject to meet your environment
CREATE CERTIFICATE [SQL01Cert] WITH SUBJECT = 'SQL01Cert';
-- Create Login for SQL02 Cluster Node
CREATE LOGIN login_AvailabilityGroup  WITH PASSWORD = 'password!';
-- Create the User Account for the newly created Login for SQL02
CREATE USER login_AvailabilityGroup FOR LOGIN login_AvailabilityGroup;
-- Backup the Certificate to copy across to SQL02
-- (\\192.168.1.05\共享文件目录)
BACKUP CERTIFICATE [SQL01Cert] To FILE = '\\192.168.1.05\SQL01Cert.cer';
（这一步要等从节点备份完Cert之后执行）
CREATE CERTIFICATE [SQL02Cert] AUTHORIZATION login_AvailabilityGroup FROM FILE = '\\192.168.1.05\SQL02Cert.cer';

-- used to create endpoint and build connection to AG
CREATE ENDPOINT AG_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [SQL01Cert] , ROLE = ALL);
Go
GRANT CONNECT ON ENDPOINT::AG_Endpoint TO login_AvailabilityGroup;

-- 首先进行主数据库的备份
Backup Database db
　　To disk='\\192.168.1.05\db.bak'
Backup Log db
	To disk='\\192.168.1.05\dbLog.bak'


-- 创建可用性组。 下面的语句将创建可用性组(不使用自动种子)
-- node1:这里如果配置了dns后缀，需要加上
Use Master
CREATE AVAILABILITY GROUP [clusterA]
	for 
	REPLICA ON 'node1'
    WITH ( ENDPOINT_URL = N'TCP://node1:5022', 
        FAILOVER_MODE = AUTOMATIC, 
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
        BACKUP_PRIORITY = 50, 
        SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL),
        SEEDING_MODE = AUTOMATIC
        ),
    N'node2' WITH (ENDPOINT_URL = N'TCP://node2:5022', 
        FAILOVER_MODE = AUTOMATIC, 
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, 
        BACKUP_PRIORITY = 50, 
        SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL),
        SEEDING_MODE = AUTOMATIC
        )
-- 向可用性组授予创建数据库的权限。
ALTER AVAILABILITY GROUP [cluster] GRANT CREATE ANY DATABASE
GO
-- 将数据库加入到可用性组的命令[这步要等到从节点加进来再执行]
ALTER AVAILABILITY GROUP cluster ADD DATABASE db

-- Create Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password!';
-- Create Certificate to be used across on SQL01
CREATE CERTIFICATE [FCCLUSTERSQL02Cert] WITH SUBJECT = 'SQL02Cert';
-- Create Login for SQL01 Cluster Node
CREATE LOGIN login_AvailabilityGroup  WITH PASSWORD = 'password!';
-- Create the User Account for the newly created Login for SQL01
CREATE USER login_AvailabilityGroup FOR LOGIN login_AvailabilityGroup;
-- Backup the Certificate to copy across to SQL01
BACKUP CERTIFICATE [SQL02Cert] To FILE = '\\192.168.1.05\SQL02Cert.cer';
(这一步要等主节点完成Cert备份之后执行)
CREATE CERTIFICATE [SQL01Cert] AUTHORIZATION login_AvailabilityGroup FROM FILE = '\\192.168.1.05\SQL01Cert.cer';

-- used to create endpoint
CREATE ENDPOINT AG_Endpoint
STATE = STARTED
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) 
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE [SQL02Cert] , ROLE = ALL);
Go
GRANT CONNECT ON ENDPOINT::AG_Endpoint TO login_AvailabilityGroup;

AlwaysOn 测试

创建侦听器：
Use Master
ALTER AVAILABILITY GROUP cluster   
      ADD LISTENER 'listener' ( WITH IP ( ('192.168.1.06','255.255.255.0') ) , PORT = 1433 );   
GO  
停掉主副本后，可通过可用性组面板观察角色发生切换了。

备份恢复

按时间恢复

• master数据库是SQL Server中最重要bai的数据库，记录了SQL Server系统中所有的du系统信息，包括登入账户、zhi系统配置和设置、服务器中数据库的名称、相关信息和这些数据库文件的位置，以及SQL Server初始化信息等。在这里提供了master是否恢复的选项，建议选择false；
• 针对HA，首先要将数据库从AG中脱离，在进行恢复；恢复结束后将数据库重新加入到AG，由于我们使用的是自动种子，从节点会自动同步主节点恢复好的数据库；
• 首先进行全量恢复，选择距离时间点最近的，在时间点之前的一次全量备份的文件，进行全量恢复；
• 异步恢复数据库，首先判断数据库的状态，只有online才能够进行恢复；然后判断恢复的数据库名字是否和之前的一样，如果一样，则设置数据库为offline，然后进行文件恢复，然后再设置online；如果恢复数据库名字和之前的不一样，则执行RestoreFileToNewDB【伪命令】的命令；
• 之后进行log恢复，选择全量恢复时间点到恢复时间点之间的所有备份log日志文件，按照先后顺序逐个进行恢复；

按文件恢复

基本和按时间恢复的步骤相同，不需要后面log恢复的部分。