SQL Server 配置镜像 填坑

2017-08-29 22:02:30来源:CSDN作者:guoneng_wei人点击

分享

公司想做数据库热备份,选择了SqlServer的镜像,但公司没有专门的DBA,这块工作被分到我头上了,没办法,只好客串了一回业余DBA。看文档,查资料,测试,排查解决问题,前前后后搞了2天! 现在将遇到的一些坑给记录下来,算是填坑备忘录吧,也给需要的兄弟一个参考。废话少说,上重点:


坑1:如果SqlServer的启动账户不是域账户,那要搞镜像就麻烦了,得搞证书验证,要写一些TSQL代码,啰嗦得黑纹。文章结尾会附上相关TSQL代码。


坑2:服务器网络地址“TCP://xxxxx”无法访问或不存在。请检查网络地址名称,并检查本地和远程端点的端口是否正常运行。(Microsoft SQL Server,错误:1418) 。这个错误也真是莫名其妙,ping也ping通了,telnet也tel通了,奶奶的,还是报错,按照百度出来的一堆方法,都没用。只得慢慢看SqlServer的日志,还有Windows事件日志。看到日志里面提到,登录端点失败,证书操作失败。猜想可能是权限不够,于是进行“SQL Server 配置管理器”》“SQL Server 服务”》“SQL Server (实例名)”》右击属性》选择“登录”面签》点内置账户》选择local system。 然后,不报错那个错了,却报了另一个错,提示 “无法设置镜像”。再百度,一些兄弟说,要设置两个服务器SqlServer的启动账号一模一样,登录名一样,密码一样。于是,就两边服务器都新建了一模一样的Windows账户,试了一下,真的行了,奶奶的。


坑3:设置两个服务器SqlServer的启动账号一模一样后,要重新从头操作配置镜像。


坑4: 创建镜像时,要从主数据库的备份还原,还原的时候,要选择 WITH NORECOVERY 选项,这还不行!还要备份主数据库事务日志,然后在镜像还原日志,还原日志时,也要选择WITH NORECOVERY。


坑5:配完镜像,不是得查看效果嘛,镜像是不能直接查看的,要做快照。这个快照得用TSQL CREATE DATABASE命令来搞,  里面的 name=XXX 不能随便写, 必须指定和源数据库里面的数据文件相同的逻辑名称。


下面给出主体服务器和镜像服务器的TSQL代码。


主体服务器开始============================================

--创建证书和end-points(主实例服务器):

use master;
GO

-- drop master KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456';
GO
-- DROP CERTIFICATE host_1_cert
CREATE CERTIFICATE host_1_cert WITH SUBJECT = '镜像出站证书 Principal Server Certificate', START_DATE = '2017/01/01', EXPIRY_DATE = '2025/01/01';
GO

-- DROP ENDPOINT 镜像出站
CREATE ENDPOINT 镜像出站 STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE host_1_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL);
GO

BACKUP CERTIFICATE host_1_cert TO FILE = 'D:/Certs/host_1_cert.cer'
GO

-- 创建用户和证书(主实例):

-- 交换证书

USE MASTER
GO
-- DROP LOGIN host_2_login
CREATE LOGIN host_2_login WITH PASSWORD = '123456';
GO
-- DROP USER host_2_user
CREATE USER host_2_user FOR LOGIN host_2_login;
GO
-- DROP CERTIFICATE host_2_cert
CREATE CERTIFICATE host_2_cert
    AUTHORIZATION host_2_user
    FROM FILE = 'D:/Certs/host_2_cert.cer';
GO

GRANT CONNECT ON ENDPOINT::镜像出站 TO host_2_login;
GO

--主数据库实例执行此。

--ALTER DATABASE DB_PayCard_Test
--    SET PARTNER = 'TCP://192.168.30.152:5022'
--GO

主体服务器 结束============================================


镜像服务器开始============================================

--创建证书和end-points(在镜像实例服务器):

USE master
GO
-- drop master KEY

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456';
GO


-- DROP CERTIFICATE host_2_cert

CREATE CERTIFICATE host_2_cert WITH SUBJECT = '镜像入站证书 Mirror Server Certificate', START_DATE = '2017/01/01', EXPIRY_DATE = '2025/01/01';
GO

-- DROP ENDPOINT 镜像入站

CREATE ENDPOINT 镜像入站 STATE = STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE host_2_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL );
GO

BACKUP CERTIFICATE host_2_cert
    TO FILE = 'D:/Certs/host_2_cert.cer';
GO

-- 创建用户和证书:

-- 交换证书

USE MASTER
GO
-- DROP LOGIN host_1_login
CREATE LOGIN host_1_login WITH PASSWORD = '123456';
go
-- DROP USER host_1_user
CREATE USER host_1_user FROM LOGIN host_1_login;
go

-- DROP  CERTIFICATE host_1_cert
CREATE CERTIFICATE host_1_cert
    AUTHORIZATION host_1_user
    FROM FILE = 'D:/Certs/host_1_cert.cer';
Go

GRANT CONNECT ON Endpoint::镜像入站 TO host_1_login;
go


--在镜像数据库实例上执行。

--ALTER DATABASE DB_PayCard_Test
--    SET PARTNER = 'TCP://172.17.20.28:5022'
--GO

镜像服务器结束=============================================


以下是参考的一些文档

http://www.mssqlmct.cn/?post=6

http://www.mssqlmct.cn/dba/?post=65

https://www.howtoing.com/setup-database-mirroring-sql-server/

https://docs.microsoft.com/zh-cn/sql/database-engine/configure-windows/scm-services-change-the-service-startup-account

https://msdn.microsoft.com/zh-cn/library/ms191477(v=sql.90).aspx

https://msdn.microsoft.com/zh-cn/library/ms186384(v=sql.90).aspx

https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/create-a-database-snapshot-transact-sql#Creating_on_AW

https://social.microsoft.com/Forums/zh-CN/fb765092-daea-478b-b05b-560b8565dac8?forum=sqlserverzhchs



最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台