SQL Server 主从同步(镜像) 收缩日志备份与还原

一、日志处理

报错Messages:
* 错误 0xc0202009: 数据流任务 1: SSIS 错误代码 DTS_E_OLEDBERROR。出现 OLE DB 错误。错误代码: 0x80004005。
已获得 OLE DB 记录。源:“Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 说明:“数据库“express”的事务日志已满，原因为“LOG_BACKUP”。 ”。

DUMP TRANSACTION 数据库名 WITH NO_LOG (此方法2012之后版本已经取消)

方法一：截断日志(收缩日志)
- 1.收缩日志的方法
  企业管理器--右键你要压缩的数据库--任务-收缩（因为要操作的是日志文件），找到收缩操作，有个将日志收缩到，里会给出一个允许收缩到的最小M数，在输入框中直接输入这个数,确定就可以了.

--收缩日志有效的几种情况:
1.选择数据库右键属性->选项->恢复模式(选择'简单'),收缩日志后再切换会'完整'.

2.备份日志("只有在备份完日志后才有效果")
通过任务->备份->备份类型(选事务日志),然后再执行收缩日志.

3.日志文件中有大量的未使用空间.

方法二：限制数据库日志自增长
选择数据库右键点击“属性”以后，弹出1个框，点击左侧的“文件”，然后在右边找到“ginfo_Log”的日志文件这一行，然后点击右边的文件夹选项按钮，点击以后，又弹出1个框框“更改ginfo_Log的自动增长设置”，把“文件增长”的类型改为“按MB”，并且在右边的空格里填写“50”，代表每50MB的大小幅度增长。
方法三：删除过大数据库日志文件

1. 迁移日志

2. 删除日志

1: 删除LOG
分离数据库企业管理器－＞服务器－＞数据库－＞右键－＞分离数据库
2: 删除LOG文件
附加数据库企业管理器－＞服务器－＞数据库－＞右键－＞附加数据库

具体步骤:
1.直接删除log文件

分离数据库。分离数据库之前一定要做好数据库的全备份，选择数据库——右键——任务——分离，如下图

将日志文件和数据文件复制粘贴到另外一个文件夹中以防万一。删除链接，如下图

直接删除日志文件，然后再附加数据库，如下图

附加的时候会自动将ldf文件和mdf文件都附加上，但是会提示找不到ldf文件，没关系，选中ldf文件这一行，点击下面的删除按钮，点击确定就可以了。如下图

如此成功之后生成的新的ldf文件只有504K。

3. 收缩日志

网站的一个数据库的日志文件已经到150个G的地步，数据文件才几十M，通过常规的操作去收缩日志：
- 数据库右键 → 任务 → 收缩 → 文件，在弹出的窗口中，文件类型选择“日志”，收缩选项选择第二个，填一个小的数字

执行很快完成，但是日志文件大小并没有改变。

# 选项说明:
'数据库'
显示所选数据库的名称。

'文件类型'
选择文件的文件类型。 可用的选项包括 “数据” 和 “日志” 文件。 默认选项为 “数据”。 选择不同的文件组类型，其他字段中的选项会相应地发生更改。

'文件组'
在与以上所选的 “文件类型” 相关联的文件组列表中选择文件组。 选择不同的文件组，其他字段中的选项会相应地发生更改。

'文件名'
从所选文件组和文件类型的可用文件列表中选择文件。

'位置'
显示当前所选文件的完整路径。 此路径无法编辑，但是可以复制到剪贴板。

'当前分配的空间'
对于数据文件，会显示当前分配的空间。 对于日志文件，会显示根据 DBCC SQLPERF (LOGSPACE) 的输出计算出的当前分配的空间。

'可用空间'
对于数据文件，会显示根据 SHOWFILESTATS (fileid) 的输出计算出的当前可用空间。 对于日志文件，会显示根据 DBCC SQLPERF (LOGSPACE) 的输出计算出的当前可用空间。

'释放未使用的空间'
将任何文件中未使用的空间释放给操作系统，并将文件收缩到最后分配的区，因此无需移动任何数据即可减小文件尺寸。 不会将行重新定位到未分配的页。

'在释放未使用的空间前重新组织页'
等效于执行用于指定目标文件大小的 DBCC SHRINKFILE。 选中此选项时，用户必须在 “将文件收缩到” 框中指定目标文件的大小。

'将文件收缩到'
为收缩操作指定目标文件的大小。 此大小值不得小于当前分配的空间或大于为文件分配的全部区的大小。 如果输入的值超出最小值或最大值，那么一旦焦点改变或单击工具栏上的按钮时，数值将恢复到最小值或最大值。

'通过将数据迁移到同一文件组中的其他文件来清空文件'
从指定文件迁移所有数据。 此选项允许使用 ALTER DATABASE 语句删除文件。 此选项等效于执行带有 EMPTYFILE 选项的 DBCC SHRINKFILE。

完成收缩有几种方式：

直接收缩日志(除非VLF虚拟日志本身有大量头部剩余空间,否则没有收缩效果,不会变小很多)
分离数据库，删除日志文件，然后再附加数据库(缺点:丢日志)
修改数据库的恢复模式为简单,然后再重复最初的收缩数据库操作(因为事务并未截断，所以完整模式下收缩的大小有限)，完成后再吧恢复模式改为完整（否则数据库就不支持时间点备份）优点: 收缩效果很好; 缺点: 对镜像同步有影响.

点击确定后：
- 1.很快会完成收缩，效果很好。
- 2.可能会弹出一个错误：

因为是有做了镜像服务器，不能进行此操作，只能先取消镜像(暂停镜像也不行)，再进行第一步的操作。

取消镜像后，进行第一步的收缩数据库操作，日志大小成功变为2M左右，收缩成功。把恢复模式改为完整

但此时由于主机的数据库日志和备机的数据库日志不同意，无法重新开始镜像。

这时的操作是：
- 在主机完整备份数据库
- 拷贝备份文件到备机，先取消备机的镜像，然后还原数据库，在选项中，要选择 不对数据库进行任何操作...

可以直接指定新的数据库名,不能自己新建库然后导入.

之后在主机执行

ALTER DATABASE shishan SET PARTNER = 'TCP://192.168.1.2:5022';

如果执行失败，则在备机执行

ALTER DATABASE shishan SET PARTNER = 'TCP://192.168.1.1:5022';

执行成功后会如图所示：

二、备份与还原

sqlserver提供四种数据库备份方式：

完整备份：备份整个数据库的所有内容包括书屋和日志
差异备份：只备份上次完整备份后更高的数据部分
事务日志备份：只备份事务日志里的内容
文件或文件组备份：只备份文件或文件组中的某些文件

在数据库完整备份期间，sqlserver做以下工作：

备份数据以及数据库中的所有表的结构和相应的文件结构
备份在备份期间发生的所有活动
备份在事务日志中未确认的事务

--注意点：
1.SQLServer备份与增量备份文件以及log日志文件在备份时默认会自动备份在一个bak文件中。(也可以设置分离)
2.恢复的时候需要选择全备与所有增备一次性还原（不要分段还原）。

1. 完整备份

数据情况:

右击要备份的数据库--->任务--->备份

--会出现报错的情况:
对于保存备份文件的位置没有权限.(通过授权即可解决.)

成功后可以把指定位置的磁盘备份文件拷贝到指定服务器进行还原.

报错原因：未找到默认的创建文件路径。
解决方案：按下图点击指定路径和文件名即可。

2. 全备还原

此时需要执行: restore database XXX with recovery
即可正常使用.

3. 增量还原

1)差异备份(增备)

--注意: 多次差异备份与全备是在一个物理文件中.

2)事物日志(日志备份)

--注意: 事物日志与多次差异备份与全备是在一个物理文件中.

3) 增备还原

4) 日志还原

与之前的还原不同,需选择"数据库"选项

--在这里可以选择任意时间点恢复.包括返回以前的时间节点. 前提是数据库必须在正在还原状态.

-- 补充: 查看数据库日志的LSN和时间戳 --
做了一系列操作之后需要先
checkpoint  (手动把操作的事物日志信息写入磁盘.)

--查看所有的事物日志信息(量大的时候注意截取)
select * from fn_dblog(null,null)

通过此命令可以查看具体的日志操作时间戳节点.

4. 分离附加还原

1) 物理还原

在原数据库服务器将需要备份的数据库进行分离。
然后将分离后的.mdf和.ldf文件复制到新服务器。
重启新服务器，将.mdf和.ldf文件附加到新数据库.(即可还原数据库)

--附加数据库时，出现如下异常报错信息：
无法打开物理文件 "XXX.mdf"。操作系统错误 5:"5(拒绝访问。)"。 (Microsoft SQL Server，错误: 5120)  

--解决方案：
找到xxx.MDF与xxx_log.LDF文件，右键-属性-安全-在组或用户名处添加Authenticated Users-更改该组权限为完全权限控制,确定，再次附加成功。

--使用存储过程分离与附加数据库：
分离数据库：
　　EXEC sp_detach_db @dbname=E_Market
　　GO
附加数据库：　
　　EXEC sp_attach_db @dbname=E_Market,
　　@filename1='D:\program files\sqlserver2017\sqlserver workspace\E_Market_data.mdf',
　　@filename2='D:\program files\sqlserver2017\sqlserver workspace\E_Market_log.ldf',
　　@filename3='D:\program files\sqlserver2017\sqlserver workspace\E_Market_log1.ldf',
　　@filename4='D:\program files\sqlserver2017\sqlserver workspace\FG_E_Market_data.ndf'
　　GO
　　有几个文件就写几个@filename

2) 脱机与分离

不同点：
　　脱机：与数据库服务断开连接，但在数据库节点上还存在数据库名称
　　分离：将数据库从服务器上分离出去，数据库不存在，只存在相应的数据文件和日志文件。
相同点：都可以对数据库文件进行复制等操作。
建议:先使用脱机离线,再使用分离

2) 报错恢复

附加后出现日志或数据库损坏,mdf和ldf不匹配等问题.
附件后的数据库出现"可疑"状态(一般是日志不同步,或者有未完成的事务.)
当数据库发生这种操作故障时，可以按如下操作步骤可解决此方法，打开数据库里的Sql 查询编辑器窗口，运行以下的命令:

-- 方式一:
　　1、修改数据库为紧急模式
　　   ALTER DATABASE Zhangxing SET EMERGENCY
　　2、使数据库变为单用户模式
　　   ALTER DATABASE Zhangxing SET SINGLE_USER
　　3、修复数据库日志重新生成，此命令检查的分配，结构，逻辑完整性和所有数据库中的对象错误。当您指定“REPAIR_ALLOW_DATA_LOSS”作为DBCC CHECKDB命令参数，该程序将检查和修复报告的错误。但是，这些修复可能会导致一些数据丢失。
　　   DBCC CheckDB (Zhangxing, REPAIR_ALLOW_DATA_LOSS)
　　4、使数据库变回为多用户模式
　　   ALTER DATABASE Zhangxing SET MULTI_USER

-- 方式二:
   1、打开更新配置选项：
      SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE
   2、设置为紧急状态：
      ALTER DATABASE [数据库名称] SET EMERGENCY
   3、打开单用户模式：
      sp_dboption '数据库名称', 'single user', 'true' 
   4、修复发现的错误：
      DBCC CHECKDB('数据库名称','REPAIR_ALLOW_DATA_LOSS')
   5、 设置为联机、在线：
      ALTER DATABASE [数据库名称] SET ONLINE
   6、关闭更新配置选项
      SP_CONFIGURE 'ALLOW UPDATES',1 RECONFIGURE WITH OVERRIDE
   7、关闭单用户模式：
      sp_dboption '数据库名称', 'single user', 'true' 

-- 方式三
　 1、重新建立一个，一样的数据库，路径名称，文件都一样。
　 2、关掉SQL Server服务;
　 3、把源文件COPY过来;
　 4、开启SQL Server服务，这样问题同样就解决了。

三、基于镜像的主从同步

# SQl Server 高可用的选择:

　　SQL自带的高可用或读写分离技术主要有：故障转移群集、发布订阅、镜像、日志传送、AlwaysON可用组（具体可以在进阶问题的资料中详细查看）
　　一般选用读写分离需要根据不同的场景和要求，比如同步的实时性，读写分离功能的需要情况.

# 主要列出几个优缺点：
　　故障转移群集：主备模式，单活（辅助机不可读），硬件资源浪费，主要场景是数据库的高可用。

　　发布订阅：读写分离常用方式，配置灵活，副本节点可以多个，可以发布订阅部分数据（即可以对数据筛选），并提供多种发布订阅模式，缺点：维护比较麻烦，一般不能用作高可用。

　　镜像：主备模式，单活（辅助机不可读），硬件资源浪费，主要场景是数据库的高可用。相对于故障转移群集镜像是数据库级别的高可用。在镜像中可以使用快照的方式实现读写分离。

　　日志传送：主要用于灾备，在备用机上可读，但缺点是日志还原时不能读，读时不能还原。

　　AlwaysON可用组：综合性方案，满足高可用、读写分离等需要，要求：SQL Server2012 以上版本

　　第三方产品：moebius负载均衡集群，实现双活，读负载均衡、读写分离等。缺点实时同步不适合类似采集系统的大规模写入系统。

1. 基础架构

◆ 主体服务器（Principal Server）
其中一个实例为客户端提供服务，这个实例称为"主体服务器"。该服务器"扮演"主体角色"，其数据库副本为当前的"主体数据库"。

◆ 镜像服务器（Mirror Server）
另一个实例则充当备用服务器，这个实例称为"镜像服务器"（Mirror Server）。该服务器扮演"镜像角色"，其数据库副本为当前的"镜像数据库"。镜像数据库不能供客户端访问，但是可以为镜像数据库创建一个快照，让客户端访问这个快照。

◆ 见证服务器（Witness Server）
见证服务器并不能用于数据库，只是用来支持自动故障转移。见证服务器验证主体服务器是否保持运行，当见证服务器与主体服务器断开连接之后，如果此时镜像服务器和见证服务器保持相互连接，则镜像服务器启动自动故障转移，成为新的主体服务器。

2. 运行模式

根据是否同步操作以及是否支持自动故障转移功能，数据库镜像有以下三种运行模式。

（1）高安全性模式：主库把事务日志数据信息发给从库，从库返回事务日志持久化确认信息，确认同步后，事务将在主从库一起提交。（会延长事务滞后时间）

（2）高性能模式：主库把事务日志数据信息发给从库，发完后无需等待从库返回确认信息。（最大程度地提高性能）

（3）自动故障转移模式（高可用模式）：在高安全模式运行时，可以添加见证服务器，从而实现自动故障转移。

3. 优势与不足

数据库镜像技术有以下优势：

（1）消除存储方面的单一故障点：不用共享磁盘
（2）提高数据库可用性：快速自动/手动主从切换
（3）增强的数据保护：提供完整的数据冗余
（4）自动页修复

镜像数据库技术有以下不足：

（1）版本限制
对于标准版的 SQL Server 实例，只可以使用"高安全模式"，即主体数据库与镜像数据库必须实现同步操作。在这种运行模式时，如果任何一个伙伴遇到性能问题，都将使同步操作带来较大的延时。通常标准版的 SQL Server 受到一些技术限制导致性能不能提升，从而使同步操作的延时更加明显。

（2）镜像数据库的访问限制
镜像数据库甚至不可以提供只读访问，只有通过创建快照才能访问，因此镜像数据库的利用率不高。

由于数据库镜像技术存在上述不足，SQL Server 后续产品将删除此项功能，建议改用 AlwaysOn 可用性组。SQL Server 2012 中已经引入了"AlwaysOn 基本可用性组"，用来替代数据库镜像技术。

4. 搭建步骤

服务器host_A	从服务器host_B
【1】在master中，创建数据库秘钥	【1】创建master数据库秘钥
【2】基于【1】中秘钥，创建服务器实例加密证书的出站证书	【2】基于【1】中秘钥，创建服务器实例加密证书的出站证书
【3】使用服务器实例的证书为该服务器实例创建端点。	【3】使用服务器实例的证书为该服务器实例创建端点。
【4】将证书备份到文件，并将其安全地复制到从服务器。	【4】将证书备份到文件，并将其安全地复制到主服务器。
【5】为从服务器创建登录名。	【5】为主服务器创建登录名。
【6】创建一个使用该登录名（【5】中的）的用户	【6】创建一个使用该登录名（【5】中的）的用户
【7】使用从服务器的证书对【6】中用户授权	【7】使用主服务的证书对【6】中用户授权
【8】对【5】中登录名进行连接端点的授权	【8】对【5】中登录名进行连接端点的授权
【9】主数据库设置为完全恢复模式，并以全备传输到从服务器	【9】以norecovery模式还原需要镜像的库
【10】（后做）设置镜像伙伴为从库	【10】（先做）设置镜像伙伴为主库
【11】核验，基于SSMS与T-SQL	【11】核验，基于SSMS与T-SQL

5. 实际操作

1) 环境检查

1.）网络是否能联通，并且端口可用。
2.）SQL Server版本、补丁是否满足镜像要求。
3.）SQL Server数据库的恢复模式、兼容级别。
4.）SQL Server上是否有常规的备份作业，特别是日志备份。
5.）主体服务器和镜像服务器的SQL Server能否互通。

2) 证书配置镜像

在非域环境下，必须使用证书来搭建镜像，所以把搭建证书放在第一步。当磁盘IO、网络性能不佳的时候，备份、传输、还原都会浪费大量的时间（个人操作过2个小时），并且期间服务器几乎不能操作。这种时候，选择先搭建好，再还原，然后马上进行同步，减少主从差异，需要同步更多的数据。

【如果服务器使用Local System作为SQL Server服务账号，就需要使用证书授权。】

Step 1：创建数据库主密钥

主密钥的用处在这里是用于加密证书，当然主密钥不仅仅只有这个作用。对数据库主密钥的密码及存储保护要小心，这是实例级别的对象，影响面非常广。可以使用下面语句来创建：
USE master   
GO   
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pa!!w1rd';

--使用相同方式在镜像服务器创建数据库主密钥。

通过系统表查看，确认:
select top 100 is_master_key_encrypted_by_server,* from sys.databases

Step 2：创建证书，并用主密钥加密

--创建证书时，默认在创建日期开始一年后过期，所以针对证书的创建，要注意其过期时间。下面是在“主体服务器”上创建HOST_A_cert证书的创建
USE master   
GO   
CREATE CERTIFICATE Host_A_Cert    
WITH Subject = 'Host_A Certificate',   
Expiry_Date = '2050-1-1'; --过期日期 

--使用相同的方法在镜像服务器上实现对HOST_B_cert证书的创建。
USE master   
GO   
CREATE CERTIFICATE Host_B_Cert    
WITH Subject = 'Host_B Certificate',   
Expiry_Date = '2050-1-1'; --过期日期

Step 3：创建端点

可以使用下面的代码在主体服务器中创建端点，并且指定使用5022,端口，端口在镜像配置过程中不强制使用特定端口（被占用或者特定端口如1433除外）。

--使用Host_A_Cert证书创建端点   
IF NOT EXISTS ( SELECT  1   
                FROM    sys.database_mirroring_endpoints )   
    BEGIN   
        CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,   
            LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =   
            CERTIFICATE Host_A_Cert, ENCRYPTION = REQUIRED Algorithm AES, ROLE =   
            ALL );   
    END  

--在镜像服务器对证书名稍作修改，创建镜像服务器的端点。
IF NOT EXISTS ( SELECT  1   
                FROM    sys.database_mirroring_endpoints )   
    BEGIN   
        CREATE ENDPOINT [DatabaseMirroring] STATE = STARTED AS TCP ( LISTENER_PORT = 5022,   
            LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION =   
            CERTIFICATE Host_B_Cert, ENCRYPTION = REQUIRED Algorithm AES, ROLE =   
            ALL );   
    END

Step 4：备份证书

--备份证书的目的是发送到别的服务器并导入证书，以便别的服务器能通过证书访问这台服务器（主体服务器）。
BACKUP CERTIFICATE Host_A_Cert   
TO FILE = 'D:\Host_A_Cert.cer';  

"注意文件路径,如果文件夹不存在会报错"

--在镜像服务器上重复一次，备份之后可以在目标文件夹上看到有一个cer文件：
BACKUP CERTIFICATE Host_B_Cert   
TO FILE = 'D:\Host_B_Cert.cer';  

互相Copy至对方文件中。

Step 5：创建登录账号

--针对每个服务器单独创建一个服务器登录账号，这里在主服务器只需要创建一个登录给镜像服务器即可：
CREATE LOGIN Host_B_Login WITH PASSWORD = 'Pa!!w1rd';

--同理，在镜像服务器上创建Host_A_Login给主体服务器。
CREATE LOGIN Host_A_Login WITH PASSWORD = 'Pa!!w1rd';

Step 6：创建用户，并映射到Step 5中创建的登录账号中

--在主体服务器上运行：
CREATE USER Host_B_User For Login Host_B_Login;

--同理在镜像服务器也创建
CREATE USER Host_A_User For Login Host_A_Login;

Step 7：使用证书授权用户

--创建一个新的证书，并使用从伙伴服务器中复制过来的证书导入，然后映射step 6中的账号到这个新证书上。
CREATE CERTIFICATE Host_B_Cert   
AUTHORIZATION Host_B_User   
FROM FILE = 'D:\Host_B_Cert.cer';  

--注意镜像服务器上也同样。
CREATE CERTIFICATE Host_A_Cert   
AUTHORIZATION Host_A_User   
FROM FILE = 'D:\Host_A_Cert.cer';

Step 8：把Step 5中的登录账号授权访问端口

GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_B_Login];  

--镜像服务器也一样
GRANT CONNECT ON ENDPOINT::[DatabaseMirroring] TO [Host_A_Login];

3) 备份还原数据库

一般大型数据库的执行流程(由于过大,需要考虑备份传输的时间) :
- 1.任务->备份(完整)->传输到需还原的服务器->还原-文件和文件组
- 2.任务->备份(差异)(不追加,使用新的介质.)->传输到需还原的服务器->还原-文件和文件组
- 注意点: 备份与备份日志之间不能收缩日志.(否则会提示缺少完整备份.日志与备份无法关联.)
- 3.任务->备份(事务日志)(不追加,使用新的介质.)->传输到需还原的服务器->还原-事务日志

--1.注意点：可以没有差异备份，但是必须先有全备，再有事物日志（两者不可缺少，不可颠倒顺序）
--2.注意点：可以不停机，在数据不完全同步下就做镜像，系统最终会自动同步。

使用界面配置。本次还原是为Mirror做准备，所以，点击 【选项】 按钮,
需要选择【不对数据库执行任何操作，不回滚未提交的事务】

4) 启动镜像

打开windows防火墙->高级设置->入站规则->新建规则,配置TCP的5022端口:

然后随意取个名字即可.

依次分别在镜像Server和主Server上运行以下命令:

在镜像Server上线运行

ALTER DATABASE [需镜像数据库名] 
    SET PARTNER = 'TCP://192.168.XXX.XXX:5022';
GO

--注意最好源数据库名和镜像数据库名字要一致.

在主Server上运行

ALTER DATABASE [需镜像数据库名] 
    SET PARTNER = 'TCP://192.168.XXX.XXX:5022';
GO

配置成功，此时显示如下：

5) 相关命令

1.查看终端点 select * from sys.endpoints
2.删除某终端点(终端点不带引号)   drop endpoint <endpoint_name>
3.删除证书  DROP CERTIFICATE 证书名
4.删除用户  drop user <user_name>
5.然后可以删除登录名  drop login <login_name>
6.修改master key  :   alter master key drop encryption by service master key
7.删除master key  : drop master key
8.删除镜像的命令: alter database <dbname> set partner off
9.查看当前服务器的镜像信息(ip) SELECT * FROM SYS.DM_EXEC_CONNECTIONS  where local_tcp_port = 5022

6) 相关报错

报错1

或者

请检查网络地址名称，并检查本地和远程端点的端口是否正常运行。 (Microsoft SQL Server，错误: 1418) Database mirroring connection error 4 'An error occurred while receiving data: '10054(远程主机强迫关闭了一个现有的连接。)'.' for 'TCP://xxx:5022′.

--注意5022的报错可能有多种情况,具体可以查看sqlserver错误日志中报错详情如下:

日志中的错误描述1:
connection handshake failed . there is no compatible authentication protocol. state 21. encryption algorithm.
--解决方法: 在防火墙中打开tcp连接的5022端口即可.


日志中的错误描述2:
Database Mirroring login attempt failed with error: 'Connection handshake failed. The certificate used by the peer is invalid due to the following reason: The database principal has no mapping to a server principal. State 89.'. 
--握手密钥文件错误,需要打开权限.cert密钥文件(右键->属性->安全->权限->完全控制)
"不同版本会有不同的设置路径,注意查找比对."

报错2

--注意文件夹的位置和权限.一般是文件或文件夹不存在.或者无权限(授权即可).

7) 注意点

1.数据库属性选项中的恢复模式必须使用:"完整" 
2.同步镜像后就无法切换恢复模式了,必须暂停并取消镜像.
3.取消镜像后只要没有动过日志(包括恢复模式.收缩日志等所有和日志相关的操作.),即使主库数据变更,只需要从库和主库重新执行:
ALTER DATABASE [test1] SET PARTNER = 'TCP://192.168.18.151:5022'; 
命令即可重新恢复镜像,镜像会通过日志自动同步补齐差异数据.

四、基于镜像的快照

1. 作用和特点.

可以利用为了实现可用性目标而维护的镜像数据库来减轻报表的负载。
与镜像数据库本身不同，客户端可以访问数据库快照。
由于数据库快照是静态的，是没有数据更新的(也不会更新和删除)。
为了让用户能够使用相对较新的数据，必须定期创建新的数据库快照，并通过应用程序将传入客户端连接定向到最新的快照。
若要在镜像数据库中创建数据库快照，数据库必须处于同步镜像状态。

2. 注意点

新的数据库快照几乎是空的，但是它会随着越来越多的数据页的首次更新而增长。由于数据库中的每个快照都以这种方式增长，因此，每个数据库快照与常规数据库使用同样多的资源。
一般情况下，在创建替换快照之后，应重新将传入查询定向到新的快照，并在完成所有当前的查询之后删除较早的快照。

3. 创建和删除

-- 创建快照语法:
CREATE DATABASE 快照名
ON (NAME = '逻辑库文件名', FILENAME = 'F:\快照名.SNP')
AS SNAPSHOT OF 源库名

例如: 
给test1库创建第一个快照
CREATE DATABASE t1_shot1
ON (NAME = 'test1', FILENAME = 'F:\t1_shot1.SNP')
AS SNAPSHOT OF test1

给test1创建第二个快照
CREATE DATABASE t1_shot2
ON (NAME = 'test1', FILENAME = 'F:\t1_shot2.SNP')
AS SNAPSHOT OF test1

--查看数据库快照:
1.在对象资源管理器中，连接到 SQL Server 数据库引擎实例，然后展开该实例。
2.展开**“数据库”**。
3.展开**“数据库快照”**，然后选择要查看的快照。
4.若要列出 SQL Server 实例的数据库快照，对于非 NULL 值请查询 sys.databases 目录视图的 source_database_id 列。

--删除数据库快照:
DROP DATABASE 快照名

五、主备切换

切换前需要额外注意事项：
（1）系统库的存储过程　　（2）孤立用户　　（3）作业）

1. 高安全模式下：

-- 在主机执行即完成主备切换
use master;
alter database 数据库名 set partner failover;

2. 高性能模式下

-- 需要先切换到高安全模式下再执行切换
use master;
alter database 数据库名 set partner safety full;
alter database 数据库名 set partner failover;

3. 主机宕机切换

-- 在主机（SQLSVR1）宕机的情况下在备机（SQLSVR2）进行强制切换：
use master;
alter database 数据库名 set partner FORCE_SERVICE_ALLOW_DATA_LOSS;

当主机(SQLSVR1)重新开机后，在SQLSVR2机器上执行
use master;
alter database Demo1 set partner resume;
此时SQLSVR1成为了备机，而SQLSVR2成为了主机。

再到SQLSVR2机器上执行
alter database Demo1 set partner failover;
就成了SQLSVR1成为主机，SQLSVR2成为备机

4. 其他命令

1.切换镜像在高性能模式下(慎用，可能会丢失数据)
use master;
alter database Demo1 set partner safety off;


2.关闭数据库镜像
ALTER DATABASE Demo1 SET PARTNER OFF

3.暂停与恢复数据库镜像
在主体镜像服务器上，若是不小心日志过大，可以进行暂停来设置日志上限
（1）暂停：ALTER DATABASE AdventureWorks2012 SET PARTNER SUSPEND;
（2）恢复：ALTER DATABASE AdventureWorks2012 SET PARTNER RESUME;

4.移除见证服务器
USE [master]
ALTER DATABASE Demo1 SET WITNESS OFF

使用DMV查看镜像信息：

sys.database_mirroring
--此视图显示一个服务器实例中每个镜像数据库的数据库镜像元数据。 
有关详细信息，请参阅 sys.database_mirroring (Transact-SQL)。

sys.database_mirroring_endpoints
--目录视图显示有关服务器实例的数据库镜像端点的信息。 
有关详细信息，请参阅 sys.database_mirroring_endpoints (Transact-SQL)。

sys.database_mirroring_witnesses
--此视图显示服务器实例为见证服务器的每个会话的数据库镜像元数据。 
有关详细信息，请参阅 sys.database_mirroring_witnesses (Transact-SQL)。

sys.dm_db_mirroring_connections
--此动态管理视图为每个数据库镜像网络连接返回一行。
有关详细信息，请参阅 sys.dm_db_mirroring_connections (Transact-SQL)。

5. 孤立用户重新映射

方式一

将数据库服务器上的数据库文件拷贝到另外一台机器上，附加后，新建登录名时无法将原数据库用户映射到此登录名。这样用新建的登录名访问数据库时，必须要以“架构名.对象名”的形式才可以。

通过以下方式可以解决该问题：

步骤1：

exec sp_change_users_login 'REPORT'

列出当前数据库的孤立用户

步骤2：

在安全性－登录名下新建用户名，无论成功与否都不要紧！

步骤3：

exec sp_change_users_login 'AUTO_FIX','用户名'

可以自动将用户名所对应的同名登录添加到syslogins中

步骤4：

exec sp_change_users_login 'UPDATE_ONE','用户名','登录名'

将用户名映射为指定的登录名。

方式二

在数据库中找到孤立用户删除

删除后重建映射

6.用户报错

有时候会出现无法映射该用户的对应数据库/用户组或角色在当前数据库已存在的问题

因为SQL Server会报出“错误15023:当前数据库中已存在用户或角色”.
介绍一下sql server中“登录”与“用户”的区别，“登录”用于用户身份验证，而数据库“用户”帐户用于数据库访问和权限验证。登录通过安全识别符 (SID) 与用户关联。将数据库恢复到其他服务器时，数据库中包含一组用户和权限，但可能没有相应的登录或者登录所关联的用户可能不是相同的用户。这种情况被称为存在“孤立用户”。此时是不能通过新建登录或者是对同名登录授予对应数据库的“用户”权限来解决登录问题，
为了解决这个问题，需要调用系统存储过程sp_change_users_login，具体用法如下：

Use aaa
go
sp_change_users_login 'update_one', 'test', 'fwg'

其中aaa为存在孤立用户的数据库，update_one是存储过程的参数，表示只处理一个用户，前一个test是“用户”，后一个fwg是“登录”，以上这个SQL表示将服务器登录“fwg”与 Northwind 数据库用户“fwg”重新连接起来。这样就可以正常使用数据库了。

六、定期备份和定期清理

1.维护计划

使用Sql Server 维护计划

填写信息,设置定时工作时间

选择需要执行的任务

选择需要备份的库和存储位置,压缩等信息.

按需设置定期清理维护任务

然后一直下一步直到完成.

2.脚本处理

--开启文件夹权限d:\123\(如已设置可不用)
GO
SP_CONFIGURE 'SHOW ADVANCED OPTIONS',1
RECONFIGURE
GO
SP_CONFIGURE 'XP_CMDSHELL',1
RECONFIGURE
GO
EXEC xp_cmdshell 'forfiles /p d:\123\ /m * -d -30 /c "cmd /c rd /s /q @path"'

自动批量备份

------批量备份所有非系统库到 d:\123\*.bak
DECLARE
      @FileName VARCHAR(200),
      @CurrentTime VARCHAR(50),
      @DBName VARCHAR(100),
      @SQL VARCHAR(1000),
      @FilePath VARCHAR(100)

SET @CurrentTime = CONVERT(CHAR(8),GETDATE(),112)
SET @FilePath = 'd:\123\'    ---更改备份目录

DECLARE CurDBName1 CURSOR FOR
    SELECT NAME FROM Master..SysDatabases where dbid>4

OPEN CurDBName1
FETCH NEXT FROM CurDBName1 INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
    --Execute Backup
    SET @FileName = @FilePath + @DBName + '_' + @CurrentTime
    SET @SQL = 'BACKUP DATABASE ['+ @DBName +'] TO DISK = ''' + @FileName + '.bak' +
     ''' WITH NOINIT, NOUNLOAD, NAME = N''' + @DBName + '_backup'', NOSKIP, STATS = 10, COMPRESSION, NOFORMAT'
    EXEC(@SQL)

    --Get Next DataBase
    FETCH NEXT FROM CurDBName1 INTO @DBName
END

CLOSE CurDBName1
DEALLOCATE CurDBName1

清理备份

------清理1天前备份d:\123\*.bak
DECLARE 
@olddate datetime ,
@path varchar(1000),
@extension_name varchar(1000),
@database varchar(50) = DB_NAME()

set @path = 'd:\123\'         ---更改备份目录
set @extension_name = 'bak'
set @olddate = DATEADD(D, -1, GETDATE())   ---更改清理时间
select '清理截止时间：',@olddate,'清理目录  ' + @path + '*.bak'

EXECUTE master.dbo.xp_delete_file 0,@path,@extension_name,@olddate

3.重要参数

备份命令Backup说明：
删除命令master.dbo.xp_delete_file说明：
1、此处设置的是每执行一次此函数，就删除七天前的以bak结尾的文件（即数据库备份文件）
2、对sqlserver DATEADD()函数的说明：参数说明链接
3、master.dbo.xp_delete_file：用于删除文件，该存储过程有5个参数

参数排序参数说明有效值
第一个文件类型有效值是0和1，0是指备份文件，1是指报表文件
第二个目录路径目录中的文件会被删除，目录路径必须以“\”结尾
第三个文件扩展名常用的扩展名是’BAK’ 或’TRN’
第四个 Date 早于该日期创建的文件将会被删除
第五个子目录 bool类型，0是指忽略子目录，1是指将会删除子目录中的文件