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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,独享型 2核4GB
日志服务 SLS,月写入数据量 50GB 1个月
简介:

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端口


本文转自 rong341233 51CTO博客,原文链接:http://blog.51cto.com/fengwan/1865070


相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
相关文章
|
1月前
|
SQL IDE Java
Java连接SQL Server数据库的详细操作流程
Java连接SQL Server数据库的详细操作流程
|
1月前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
255 1
|
1天前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
19 13
|
4天前
|
存储 SQL C++
对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型
【7月更文挑战7天】SQL Server 中的 VARCHAR(max) vs VARCHAR(n): - VARCHAR(n) 存储最多 n 个字符(1-8000),适合短文本。 - VARCHAR(max) 可存储约 21 亿个字符,适合大量文本。 - VARCHAR(n) 在处理小数据时性能更好,空间固定。 - VARCHAR(max) 对于大文本更合适,但可能影响性能。 - 选择取决于数据长度预期和业务需求。
|
8天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
36 3
|
14天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
20天前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
26 2
|
25天前
|
SQL 存储 测试技术
|
24天前
|
SQL 机器学习/深度学习 搜索推荐
SQL SERVER 转换失败
【6月更文挑战第25天】
|
3天前
|
SQL 监控 数据库
SQL Server 查询超时问题排查
【7月更文挑战第8天】排查 SQL Server 查询超时涉及五个主要方面:检查复杂查询、评估服务器性能、审视配置参数、更新统计信息和分析执行计划。关注点包括查询的结构(如连接、子查询和索引),服务器资源(CPU、内存、网络延迟),连接和内存设置,以及统计信息的时效性。通过这些步骤可定位并解决性能瓶颈。