SQL Server 2008 R2高可用解决方案--镜像(证书方式)

原文地址： https://blog.51cto.com/fengwan/1865070 　　

 

SQL Server高可用方案--镜像搭建(证书)

环境:

角色

IP

操作系统

数据库版本

数据库

主

192.168.10.8

Windows Server  2008 R2

SQL Server 2008 R2

fdb

备

192.168.10.9

Windows Server  2008 R2

SQL Server 2008 R2

fdb

 

一.环境检查

1.网络是否互通

2.防火墙端口是否开放(需要开放1433、5022端口)

3.数据库软件版本是否有差异(软件版本为SQL Server 2008 R2)

4.SQL Server数据库的恢复模式确认，需要为“完整模式”

wKioL1gNxxbirI7xAABPAMv7rSE443.png

如果不是完整模式可以使用

USE [master]  

GO  

ALTER DATABASE [fdb]SET RECOVERY FULL WITH NO_WAIT  

GO

进行更改，当然也可以通过上面的图形界面进行更改

 

二.使用证书配置镜像，并备份还原数据库

主服务器(192.168.10.8)

备服务器(192.168.10.9)

1.创建数据库主密钥

USE master  

GO  

CREATE MASTER KEY  ENCRYPTION BY PASSWORD = 'pa$$w0rd';

 

 

/* 删除主密钥

USE master;

DROP MASTER KEY

*/

 

2.创建证书并使用主密钥加密

USE master  

GO  

CREATE CERTIFICATE  Host_8_Cert   

WITH Subject =  'Host_8 Certificate',  

Expiry_Date =  '2055-1-1'; --过期日期

 

 

/*删除证书

USE master;

DROP CERTIFICATE  Host_8_Cert

*/

 

3.创建端点:

创建端点之前，先查找下看是否已经存在端点:

select * from  sys.database_mirroring_endpoints

如果需要删除端点

drop endpoint 端点名

 

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_8_Cert, ENCRYPTION =  REQUIRED Algorithm AES, ROLE =  

            ALL );  

    END

 

 

4.备份证书

在D盘创建Cert目录

BACKUP CERTIFICATE  Host_8_Cert  

TO FILE = 'D:\Cert\Host_8_Cert.cer';

1.创建数据库主密钥

USE master  

GO  

CREATE MASTER KEY  ENCRYPTION BY PASSWORD = 'pa$$w0rd';

 

 

/* 删除主密钥

USE master;

DROP MASTER KEY

*/

 

2.创建证书并使用主密钥加密

USE master  

GO  

CREATE CERTIFICATE  Host_9_Cert   

WITH Subject =  'Host_9 Certificate',  

Expiry_Date =  '2055-1-1'; --过期日期

 

 

/*删除证书

USE master;

DROP CERTIFICATE  Host_9_Cert

*/

 

3.创建端点:

创建端点之前，先查找下看是否已经存在端点:

select * from  sys.database_mirroring_endpoints

如果需要删除端点

drop endpoint 端点名

 

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_9_Cert,  ENCRYPTION = REQUIRED Algorithm AES, ROLE =    

             ALL );  

    END

 

 

4.备份证书

在D盘创建Cert目录

BACKUP CERTIFICATE  Host_9_Cert  

TO FILE = 'D:\Cert\Host_9_Cert.cer';

 

通过以上我们分别在10.8创建以Host_8_Cert证书认证的端点，以及在10.9创建了以Host_9_Cert证书认证的端点。

将D盘Cert目录下的Host_8_Cert证书复制到10.9服务器的对应的位置，同时将10.9上的Host_9_Cert证书复制到10.8服务器上

 

192.168.10.8

192.168.10.9

5.创建一个登录给镜像服务器10.9使用

CREATE LOGIN Host_9_Login  WITH PASSWORD = 'pa$$w0rd';     

 

6.创建一个用户以映射到上面创建的登录中

CREATE USER Host_9_User  For Login Host_9_Login;

 

7.使用证书进行授权

CREATE CERTIFICATE  Host_9_Cert  

AUTHORIZATION  Host_9_User  

FROM FILE = 'D:\Cert\Host_9_Cert.cer'; 

 

8.对登录进行连接端点的授权

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

5.创建一个登录给镜像服务器10.8使用

CREATE LOGIN Host_8_Login  WITH PASSWORD = 'pa$$w0rd';     

 

6.创建一个用户以映射到上面创建的登录中

CREATE USER Host_8_User  For Login Host_8_Login;

 

7.使用证书进行授权

CREATE CERTIFICATE  Host_8_Cert  

AUTHORIZATION  Host_8_User  

FROM FILE = 'D:\Cert\Host_8_Cert.cer'; 

 

8.对登录进行连接端点的授权

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

 

9.在主服务器对数据库进行备份，以及日志进行备份(192.168.10.8)

查看数据库“恢复模式”，是否为“完整”。否则执行如下语句修改为“完整”恢复模式

 ALTER DATABASE fdb SET RECOVERY FULL;

 

数据库备份

wKiom1gNxzeCzLJ6AACT_YulTvo519.png

 

日志备份

wKiom1gNx0bgR2_OAACCEor95K8607.png

 

 

数据库和日志备份是可以备份到一个文件。

 

10.在备机上对数据库进行还原(192.168.10.9)

wKiom1gNx1fCC13qAACNLyIlHyo130.png

 

 

a.选中源设备，并选中备份集中的“完整数据库备份”，并选择目录数据库为fdb

b.点击“选项”进行不回滚事务的恢复

wKioL1gNx2TBOKM_AACtgCLG6pM973.png

 

使用同样的方法对事务日志也进行“不回滚提交事务的恢复”

恢复完之后可以看到数据库，提示“正在还原”

wKioL1gNx3ax3qdGAAAh_G11PN0380.png

 

三.配置镜像

在192.168.10.9中执行

ALTER DATABASE fdb  

    SET PARTNER = 'TCP://192.168.10.8:5022'; 

GO 

 

然后再在192.168.10.8中执行

ALTER DATABASE fdb  

    SET PARTNER = 'TCP://192.168.10.9:5022'; 

GO 

 

到这里数据库的镜像就配置成功了

wKiom1gNx4LRVdFHAADVhzYfLN4293.png

 

wKioL1gNx7iSJ4k6AABjz2yr6qY370.png

 

 

 

四.主备切换

1.在高安全模式下：

在主机执行:

use master;

alter database fdb set partner failover;

即完成主备切换

 

2.在高性能模式下，需要先切换到高安全模式下再执行切换

use master;

alter database fdb set partner safety full;

alter database fdb set partner failover;

 

3.在主机（10.8）宕机的情况下在备机（10.9）进行强制切换：

use master;

alter database fdb set partner FORCE_SERVICE_ALLOW_DATA_LOSS;

当主机(10.8)重新开机后，在10.9机器上执行

use master;

alter database fdb set partner resume;

此时10.8成为了备机，而10.9成为了主机。

再到10.9机器上执行

alter database fdb set partner failover;

就成了10.8成为主机，10.9成为备机

 

4.切换镜像在高性能模式下(慎用，可能会丢失数据)

 

use master;

alter database fdb set partner safety off;

 

5.关闭数据库镜像

ALTER DATABASE fdb SET PARTNER OFF

 

 

 五.账号

SQL Server默认是不同步master数据库，因此需要在备库新建账号，以期在主备切换后出现无法登录的情形。

USE fdb;

EXEC sp_change_users_login 'Auto_Fix', 'user', NULL, 'password';

 

在此步骤如果依然无法登录，则可能是SQL Server的身份验证只允许“Windows身份验证模式”。

右击“数据库服务器”---“属性”---“安全性”---“服务器身份验证”---“SQL Server和Windows身份验证模式”

wKiom1jSP3Lj96GrAACd3ka_dxo757.png

 

 

问题处理：

在执行完镜像操作后，镜像为挂起状态。这个时候就需要检查日志

Database mirroring will be suspended. Server instance 'SH01TEST144' encountered error 948, state 2, severity 20 when it was acting as a mirroring partner for database 'fdb'.

The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

 

这个时候需要查看服务器--“应用程序日志”，检查发现为主库使用的是SQL Server 2008 R2 sp3，而备库使用的是SQL Server 2008 R2.导致镜像出错，将备机打上sp3补丁。

 

同时检查下主备库的防火墙情况，是否打开1433，5022端口

 

posted @ 2021-01-05 19:50  enif  阅读(655)  评论(0)  编辑  收藏  举报
豫ICP备2021034901号