SQL Server2014高可用配置（镜像+复制）

谁践踏了优雅 2024-03-27 10:07 119阅读 0赞

数据库服务器在安装数据库系统后更改计算机名称，将会导致复制订阅出现问题，所以需要将名称更改为一致，以下SQL语句用于处理此问题：

1)先执行如下脚本，看下名称是否一致

use master 
    go 
    select @@servername
    select serverproperty('servername')

2)如果一致就不需要修改，不一致时执行如下

脚本：

EXEC sp_dropserver 'old_server_name' 
    go
    EXEC sp_addserver 'current_computer_name','local'

*  注意：old\_server\_name为上面查询出的第一行的值，current\_computer\_name为第二行的值
 *  注意：需要重启服务之后，再次运行第1个脚本，如下：
 *  观察发现已经保持一致。再次新建发布订阅，观察一切正常。

也可以通过批处理的方式处理此问题

if serverproperty('servername') <> @@servername
    begin
    declare @server sysname
    set @server = @@servername
    exec sp_dropserver @server = @server
    set @server = cast(serverproperty('servername') as sysname)
    exec sp_addserver @server = @server , @local = 'LOCAL'
    end

数据库服务器在安装数据库系统后更改计算机名称，通过执行下述SQL，会发现数据库的所有者是更名前的计算机名，将其更改为“sa”：

select name, suser_sname(owner_sid) from sys.databases -- where name = 'AH_AnalyticalData'
    USE AH_AnalyticalData
    GO
    sp_changedbowner 'sa'

### **SQL Server2008R2-镜像数据库实施手册(双机)** ###

--------------------

文档下载：[https://www.winjay.cn/upload/2021/04/SQL%20Server2014%E9%AB%98%E5%8F%AF%E7%94%A8%E9%85%8D%E7%BD%AE%EF%BC%88%E9%95%9C%E5%83%8F+%E5%A4%8D%E5%88%B6%EF%BC%89-35b75eea74984e66920b813a31b3ba5c.docx][https_www.winjay.cn_upload_2021_04_SQL_20Server2014_E9_AB_98_E5_8F_AF_E7_94_A8_E9_85_8D_E7_BD_AE_EF_BC_88_E9_95_9C_E5_83_8F_E5_A4_8D_E5_88_B6_EF_BC_89-35b75eea74984e66920b813a31b3ba5c.docx]

#### **一、配置主备机** ####

主机名称为：masterdb01，IP地址为：192.168.1.158

备机名称为：mirrordb01， IP地址为：192.168.1.254

#### **二、主备实例互通** ####

##### **1.创建证书(主备可并行执行)** #####

###### **--主机执行：** ######

\--如果有endpoint，master key先删除

--SELECT * FROM master.sys.database_mirroring_endpoints
      --DROP ENDPOINT Endpoint_Mirroring
      --DROP master key;

USE master;   
    ALTER SERVICE MASTER KEY FORCE REGENERATE
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'adh21xdl_Zy'; 
    CREATE CERTIFICATE Matser_cert WITH SUBJECT = 'HOST_A certificate' , 
    START_DATE = '2019-09-24', 
    EXPIRY_DATE = '2099-09-24';

\----数据库中已存在主密钥。执行此语句前，请先删除该主密钥

--DROP CERTIFICATE Matser_cert

\----删除数据库主密钥

--DROP MASTER KEY

###### **--备机执行：** ######

\--如果有endpoint，master key先删除

--select * from master.sys.database_mirroring_endpoints
    --DROP ENDPOINT Endpoint_Mirroring
    --drop master key;

USE master; 
    ALTER SERVICE MASTER KEY FORCE REGENERATE
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'adh21xdl_Zy'; 
    CREATE CERTIFICATE Slave_cert WITH SUBJECT = 'HOST_B certificate', 
    START_DATE = '2019-09-24', 
    EXPIRY_DATE = '2099-09-24';

\----数据库中已存在主密钥。执行此语句前，请先删除该主密钥

--DROP CERTIFICATE Slave_cert

\----删除数据库主密钥

--DROP MASTER KEY

##### **2.创建连接的端点(主备可并行执行)** #####

###### **--主机执行：** ######

CREATE ENDPOINT Endpoint_Mirroring 
    STATE = STARTED 
    AS
    TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
    FOR
    DATABASE_MIRRORING 
    ( AUTHENTICATION = CERTIFICATE Matser_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

###### **--备机执行：** ######

CREATE ENDPOINT Endpoint_Mirroring 
    STATE = STARTED 
    AS
    TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) 
    FOR
    DATABASE_MIRRORING 
    ( AUTHENTICATION = CERTIFICATE Slave_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

##### **3.备份证书以备建立互联** #####

\--------------主备可并行执行

###### **--主机执行：** ######

BACKUP CERTIFICATE Matser_cert TO FILE = 'C:\SQLBackup\Matser_cert.cer';

###### **--备机执行：** ######

BACKUP CERTIFICATE Slave_cert TO FILE = 'C:\SQLBackup\Slave_cert.cer';

##### **4.** **互换证书** #####

\--将备份到C:\\SQLBackup\\的证书进行互换，即Matser\_cert.cer复制到备机的C:\\SQLBackup\\。Slave\_cert.cer复制到主机的C:\\SQLBackup\\。

##### **5.** **添加登陆名、用户** #####

\--------------主备可并行执行

\*\*--\*\***以下操作只能通过命令行运行，通过图形界面无法完成。**

###### **--主机执行：** ######

CREATE LOGIN Slave_01_login WITH PASSWORD = 'ayhrrfl_19brsyj'; 
    CREATE USER Slave_01_user FOR LOGIN Slave_01_login; 
    CREATE CERTIFICATE Slave_cert AUTHORIZATION Slave_01_user FROM FILE = 'C:\SQLBackup\Slave_cert.cer'; 
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Slave_01_login];

###### **--备机执行：** ######

CREATE LOGIN Matser_01_login WITH PASSWORD = 'ayhrrfl_19brsyj'; 
    CREATE USER Matser_01_user FOR LOGIN Matser_01_login; 
    CREATE CERTIFICATE Matser_cert AUTHORIZATION Matser_01_user FROM FILE = 'C:\SQLBackup\Matser_cert.cer'; 
    GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [Matser_01_login];

#### **三、** **建立镜像关系** ####

**以下步骤是针对每个数据库进行的，例如：现有主机中有7个数据库以下过程就要执行7次。**

##### **手工同步登录名和密码** #####

数据库镜像的缺点之一是无法维护登录名，所以需要我们手工维护登录。  
通常来说数据库都将会有若干个用户作为访问数据库的用户，并且数据库会有相应的登录名，但是在备机中缺少与之相对应的登录名，例如业务系统使用’myuser’作为登录名访问数据库，但是在备机中没有’myuser’这个登录名，因此一旦主备切换，业务系统就无法登录数据库了，这种情况称为'孤立用户'。在主机和备机数据库上建立相同用户名及密码即可。

##### 准备备机数据库(主机备份及镜像还原) #####

###### 在主机上备份数据库，先做完整备份，再做日志事务备份。 ######

*  主数据必须设置成完整模式进行备份，如下图：
    
    ![image-20210412152248801][]  
    上图中将“恢复模式”选成“完整模式”。
    
     *  备份数据库，如下图:

![image-20210412152616852][image-20210412152248801]

备份时将“备份类型”选成“完整”。

*  备份事务日志，如下图：
    
    ![image-20210412153607540][image-20210412152248801]

将“备份类型”选成“事务日志”且备份目录与备份数据库的目录一致。

##### 在备机上使用主机的备份文件进行还原(将主机的备份文件拷贝到备机上，)。 #####

**A.** **还原要点**

**数据库还原时，在“还原”菜单选项中，完整备份选择“文件和文件组”进行还原，日志事务备份还原选择** **“事务日志”。**

**B.** **还原数据库**

![https://images2017.cnblogs.com/blog/58846/201801/58846-20180124165229444-1953242796.png][image-20210412152248801]

**在还原数据库的时候需要使用选上**\*\*“**WITH NORECOVERY**”\*\*\*\*。如图所示：\*\*

![https://images2015.cnblogs.com/blog/58846/201607/58846-20160705151526858-1940661671.jpg][image-20210412152248801]

**上二个截图对应的中文界面如下：**

![https://img-blog.csdnimg.cn/20190228144023604.png?x-oss-process=image/watermark,type\_ZmFuZ3poZW5naGVpdGk,shadow\_10,text\_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VsaWVfeWFuZw==,size\_16,color\_FFFFFF,t\_70][image-20210412152248801]

![https://img-blog.csdnimg.cn/20190228145156186.png?x-oss-process=image/watermark,type\_ZmFuZ3poZW5naGVpdGk,shadow\_10,text\_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VsaWVfeWFuZw==,size\_16,color\_FFFFFF,t\_70][image-20210412152248801]

**还原的路径建议设置成和主体服务器一致的路径。**

**如果数据库还原成功，将会变成如下所示的样子：**

![https://images2015.cnblogs.com/blog/58846/201607/58846-20160705151540749-915727141.jpg][image-20210412152248801]

**C.** **还原日志备份**

**在还原日志备份的时候需要使用选上**\*\*“**WITH NORECOVERY**”\*\* **(重要，否则后面执行镜像会报错)\*\*\*\*。如图所示：**

![https://img-blog.csdnimg.cn/20190228150400589.png?x-oss-process=image/watermark,type\_ZmFuZ3poZW5naGVpdGk,shadow\_10,text\_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VsaWVfeWFuZw==,size\_16,color\_FFFFFF,t\_70][image-20210412152248801]

##### **3**\*\*、启动镜像操作\*\*\*\*(先备机后主机)\*\* #####

\--备机执行：

ALTER DATABASE AH\_AnalyticalData SET PARTNER = 'TCP://192.168.1.158:5022';

\--说明：AH\_AnalyticalData为数据库名。192.168.1.158为主机IP地址。

\--主机执行：

ALTER DATABASE AH\_AnalyticalData SET PARTNER = 'TCP://192.168.1.254:5022';

\--说明：AH\_AnalyticalData为数据库名。192.168.1.254为备机IP地址。

**到此，SQL镜像热备配置完成。以下为镜像配置界面：** ![https://img-blog.csdnimg.cn/20190301093539396.png?x-oss-process=image/watermark,type\_ZmFuZ3poZW5naGVpdGk,shadow\_10,text\_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VsaWVfeWFuZw==,size\_16,color\_FFFFFF,t\_70][image-20210412152248801]

**说明：目前为止，还没有配置见证服务器，所以上图为空，另外运行模式只有“高性能(异步)”和“高安全(同步)”可选择。**

![img][image-20210412152248801]

**目前主体数据库和镜像数据库的状态显示界面如下：**

![img][image-20210412152248801]

#### **四、** **数据库复制** ####

参考：[https://www.cnblogs.com/datazhang/p/6142349.html][https_www.cnblogs.com_datazhang_p_6142349.html]

![img][image-20210412152248801]

**创建完成之后还有关键的一步，就是在主体和镜像服务器上执行 DBCC TRACEON(1448，-1)，如果可以重启最好加入到实例的启动参数中去。如果不设置该参数，会报复制的事务正等待下一次日志备份或等待镜像伙伴更新**

#### **五、** **测试** ####

**测试一：**

**在主体服务器中，点击下图中的“故障转移按钮”：然后观察主体和镜像数据库角色的切换。**

![https://img-blog.csdnimg.cn/20190228153425511.png?x-oss-process=image/watermark,type\_ZmFuZ3poZW5naGVpdGk,shadow\_10,text\_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VsaWVfeWFuZw==,size\_16,color\_FFFFFF,t\_70][image-20210412152248801]

**关闭主体服务器的SQL服务，然后观察各数据库状态，见下图：**

![https://img-blog.csdnimg.cn/20190301090000850.png?x-oss-process=image/watermark,type\_ZmFuZ3poZW5naGVpdGk,shadow\_10,text\_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VsaWVfeWFuZw==,size\_16,color\_FFFFFF,t\_70][image-20210412152248801]

**测试二：**

连接发布库，向测试表中插入数据并查询

USE mydb

INSERT INTO Admin( user\_name) VALUES('master' )

SELECT \* FROM admin WHERE user\_name='master'

![https://images2015.cnblogs.com/blog/797406/201612/797406-20161207185128179-190289702.png][image-20210412152248801]

连接订阅库，查询插入数据

USE mydb

SELECT \* FROM admin WHERE user\_name='master'

![https://images2015.cnblogs.com/blog/797406/201612/797406-20161207185157054-159743473.png][image-20210412152248801]

故障切换：

停止主体数据库服务，过一会在镜像库执行强制接收

use master ;

alter database mydb set partner FORCE\_SERVICE\_ALLOW\_DATA\_LOSS; --强制接收

切换成功后，新的主体数据库显示‘主体，已断开连接’

在新的主体服务器执行：

USE mydb

INSERT INTO Admin( user\_name) VALUES('mirror' )

SELECT \* FROM admin WHERE user\_name='mirror'

![https://images2015.cnblogs.com/blog/797406/201612/797406-20161207185243991-1316868120.png][image-20210412152248801]

在订阅服务器查看：

USE mydb

SELECT \* FROM admin WHERE user\_name='mirror'

测试成功(测试成功的图居然忘记截了。。。)

当宕机的原主体数据库连接上来后，现在的主体数据库状态由“主体，已断开”变成“主体，挂起”，此时需要在现主体数据库上执行恢复操作，主体数据库状态变为“主体，已同步”

use master ;

alter database mydb set partner resume;

#### **六、** **常见命令** ####

\--切换主备(在主机principle数据库所在HOST上执行)

use master;

ALTER DATABASE testdb set partner failover;

\-- 备机强制切换(在备机上数据库状态非同步状态下可执行)

use master;

ALTER DATABASE testdb set partner force\_service\_allow\_data\_loss;

\--恢复镜像

use master;

ALTER DATABASE testdb set partner resume;

\--取消见证服务器

ALTER DATABASE testdb SET WITNESS OFF ;

\--取消镜像(在主机principle数据库所在HOST上执行)

ALTER DATABASE testdb SET PARTNER OFF;

\--设置镜像数据库还原为正常

RESTORE DATABASE testdb WITH RECOVERY;

**备份主数据库出现：Backup a database on a HDD with a different sector size，可以执行以下语句备份：**

**BACKUP DATABASE MyDB TO DISK = N'D:\\MyDB.bak' WITH INIT , NOUNLOAD , NAME = N'MyDB backup', STATS = 10, FORMAT**

#### **七、** **总结** ####

**要进行以上sql server的镜像设置一定要使用sql server 的配置管理器开启TCP/IP协议，如下图：**

![https://images2015.cnblogs.com/blog/58846/201607/58846-20160705151629155-1359476577.jpg][image-20210412152248801]

**如果没有启用TCP/IP协议则只能在同一个网段内的机器配置镜像，前面的配置步骤里面所用到的IP地址要换成对应的实例名。同一个网段配置并使用镜像的时实性、传输速率更高，适用于大数据量的同步，跨网段或者跨公网的sql server 镜像一般适用于数据量小，时实性要求不高的数据同步，而且数据库在公网上同步也不安全。**

[https_www.winjay.cn_upload_2021_04_SQL_20Server2014_E9_AB_98_E5_8F_AF_E7_94_A8_E9_85_8D_E7_BD_AE_EF_BC_88_E9_95_9C_E5_83_8F_E5_A4_8D_E5_88_B6_EF_BC_89-35b75eea74984e66920b813a31b3ba5c.docx]: https://www.winjay.cn/upload/2021/04/SQL%20Server2014%E9%AB%98%E5%8F%AF%E7%94%A8%E9%85%8D%E7%BD%AE%EF%BC%88%E9%95%9C%E5%83%8F+%E5%A4%8D%E5%88%B6%EF%BC%89-35b75eea74984e66920b813a31b3ba5c.docx
[image-20210412152248801]: 
[https_www.cnblogs.com_datazhang_p_6142349.html]: https://www.cnblogs.com/datazhang/p/6142349.html