SQL Server HA - 数据库镜像2 （Mirroring）(1418错误解决)

精选转载

chenhao_asd 2016-09-09 14:40:27 博主文章分类：SQL 高可用性

文章标签 服务器 文章分类 数据库

SQL Server2008数据库镜像的配置 (1418错误解决)

准备三台安装SQL的服务器，三台中两台担当惊喜数据库，一天当作监控服务器

在要做镜像的数据库之上右键单击，选择备份
备份类型为完整，注意记录备份路径
重复以上操作，此次备份类型为事务日志
将刚刚备份的文件拷贝到另外一台备份的数据库内
右键数据库，选择恢复数据库
选择源设备
选择完整备份的数据库
单击确定
勾选刚刚恢复的数据库
点击选项，勾选不对数据库执行任何操作
右键选择刚刚恢复的数据库，选择还原任务日志
勾选从磁盘恢复，选择对应的文件
同样选择不对数据库执行任何操作
可看到正在同步和还原
右键刚刚的数据库选择镜像
点击配置安全
勾选使用见证服务器
主服务器
选择镜像服务器
选择见证服务器
设置服务器连接用户名

设置服务器连接用户名(注意：此处必须指定SQLServer服务启动的账号)
这个是引起1418错误的原因，由于没有
GRANT CONNECT on ENDPOINT::Mirroring TO [Domain\_TPSQLAccount];
Mirroring server network address cannotbe reached or does not exist
点击完成结束
点击开始惊喜
镜像结束

[TroubleShooting] The servernetwork address can not be reached or does not exist:

http://blog.csdn.net/wzhiu/article/details/24774987

Backtround:

I'm trying to set upmirroring between two sql 2008 R2 databases on different servers in myinternal network, as a test run before doing the same thing with two liveservers in different locations.

When I actually try and switch the mirroring on the target DB (with
ALTER DATABASE xxxdb SET PARTNER = TCP://myserverAddress:50221') I'm getting anerror telling me that the server network address can not be reached or does notexist. A little research suggests this is a fairly unhelpful message that popsup due to a number of possible causes, some of which are not directly relatedto the server existing or otherwise.

In fact, this is error isnot clear for us.Checking the log, I'mseeing the following errors. "Database mirroringconnection error 5 'Connection handshake failed. The certificate used by thepeer is invalid due to the following reason: Certificate not found. State 89.'for 'TCP://myserverAddress:50221'." in event viewer(eventvwr).

Solution:

You can manual copyingthe certificates around(Principal, Mirror and Witness). and drop login, user.and then create login, user with certificate responsed. Last, Grant connectendpoint.

CREATE LOGIN HOST_pro_login WITH PASSWORD ='Password01!';

CREATE USER HOST_pro_user FOR LOGIN HOST_pro_login;

-- drop CERTIFICATE HOST_pro_cert

CREATE CERTIFICATE HOST_pro_cert AUTHORIZATION HOST_pro_user FROM FILE ='D:\Hot backup\HOST_pro_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_pro_login];

CREATE LOGIN HOST_pro_login WITH PASSWORD ='Password01!';

CREATE USER HOST_pro_user FOR LOGIN HOST_pro_login;

-- drop CERTIFICATE HOST_pro_cert

CREATE CERTIFICATE HOST_pro_cert AUTHORIZATION HOST_pro_user FROM FILE ='D:\Hot backup\HOST_pro_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_pro_login];

OtherSuggestions:

Thesolution to the above problem is very simple and as follows.

Fix/WorkAround/Solution:Tryall the suggestions one by one.

Suggestion1:Make sure that on Mirror Server the database is restored with NO RECOVERYoption (This is the most common problem).

Suggestion2:Make sure that from Principal the latest LOG backup is restored to mirrorserver. (Attempt this one more time even though the full backup has beenrestored recently).

-- Back up 1:

BACKUP DATABASE [dbName] TO DISK = N'D:\Hot backup\testbackup01.bak' WITH NOFORMAT, NOINIT, NAME = N'TestBackup01-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

BACKUP LOG [dbName] TO DISK = N'D:\Hot backup\TestBackup01.trn' WITH NOFORMAT, NOINIT, NAME = N'TestBackup01-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

-- Restore 1:

RESTORE DATABASE dbName

FROM DISK = 'D:\Hot backup\dbName.bak' WITH  FILE = 1, 

MOVE 'dbName' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\dbName.mdf',

MOVE 'dbName_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\dbName_log.ldf',

NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10

-- Restore 2:

RESTORE DATABASE dbName FROM DISK = 'D:\Hot backup\backup_TestBackup01.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD;

RESTORE LOG dbName FROM DISK = 'D:\Hot backup\backup_TestBackup01.trn' WITH NORECOVERY

-- Restore 2:

RESTORE DATABASE dbName FROM DISK = 'D:\Hot backup\backup_TestBackup01.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD;

RESTORE LOG dbName FROM DISK = 'D:\Hot backup\backup_TestBackup01.trn' WITH NORECOVERY

Suggestion3:Check if you can telnet to your ports using command TELNET ServerName Portslike “telnet SQLServerName 50221″.(Please add feature lelnet client|Server inserver 2k8, if it not exsit.)

Suggestion4:Make sure your firewall is turned off.

Suggestion5:Verify that the endpoints are started on the partners by using thestate or state_desc column the ofthe sys.database_mirroring_endpointscatalog view. You can start end point by executing an ALTER ENDPOINT statement.

Suggestion6:Try the following command as one of the last options.

GRANTCONNECT ON ENDPOINT::Mirroring TO ALL

Suggestion7:Delete the end points and recreate them.

Suggestion8: CheckSQL log.

Ifany of above solutions does not fix your problem, do leave comment here. Basedon the comment, I will update this article with additional suggestions.

Pleasenote that some of the above suggestions can be security threat to your system.Please use them responsibly and review your system with security expert in yourcompany.

SQL SERVER 2008 数据库镜像动手实验笔记

----判断数据库是否正在被使用，如果被使用中，那么中止使用数据库的进程

IF EXISTS(SELECT * FROM sys.sysprocesses WHEREdbid=DB_ID(N'SampleData'))

BEGIN

DECLARE @sNVARCHAR(1000)

DECLARE myCurCURSOR FOR

SELECT 'kill '+CAST(spid AS VARCHAR) FROMsys.sysprocesses WHERE dbid=DB_ID(N'SampleData')

OPEN myCur

FETCH NEXT FROMmyCur INTO @s

WHILE@@FETCH_STATUS =0

BEGIN

EXEC(@S)

FETCH NEXT FROMmyCur INTO @s

END

CLOSE myCur

DEALLOCATEmyCur

END

----新建数据库

IF EXISTS(SELECT * FROM sys.sysdatabases WHERE dbid=db_id(N'SampleData'))

DROP DATABASE SampleData

---IF DB_ID(N'SampleData') IS NOT NULL

--- DROP DATABASESampleData

CREATE DATABASE SampleData ON PRIMARY

(NAME=N'SampleData',FILENAME=N'E:\Sample\SampleData.MDF',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%),

FILEGROUP FG1DEFAULT

(NAME=N'SampleData_01',FILENAME=N'E:\Sample\SampleData_01.ndf',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%),

(NAME=N'SampleData_02',FILENAME=N'E:\Sample\SampleData_02.ndf',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)

LOG ON

(NAME=N'SampleData_Log',FILENAME=N'E:\Sample\SampleData_Log.ldf',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)

COLLATEChinese_PRC_CI_AS

----将数据库的恢复模式设定为【FULL】

IF NOT EXISTS(SELECT recovery_model FROM sys.databases WHEREdatabase_id =DB_ID(N'SampleData') AND recovery_model=1)

ALTER DATABASESampleData SET RECOVERY FULL

----在数据库中新建表[TestTable],并添加测试数据

USE SampleData

IF EXISTS(SELECT * FROM sys.objects WHERE object_id=OBJECT_ID(N'dbo.TestTable') AND type IN (N'U'))

DROP TABLE dbo.TestTable

CREATE TABLE dbo.TestTable

(ID INT IDENTITY(1,1),

Column1 VARCHAR(50) NOT NULL,

CONSTRAINTpk_testtableID PRIMARY KEY(ID))

INSERT INTO dbo.TestTable(Column1) VALUES('ROWA')

INSERT INTO dbo.TestTable(Column1) VALUES('ROWB')

INSERT INTO dbo.TestTable(Column1) VALUES('ROWC')

INSERT INTO dbo.TestTable(Column1) VALUES('ROWD')

INSERT INTO dbo.TestTable(Column1) VALUES('ROWE')

----完整备份数据库

BACKUP DATABASE [SampleData] TO DISK = N'E:\SampleBack\SampleDataAllback.bak'WITH NOFORMAT, NOINIT, NAME =N'SampleData-完整数据库备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10

----事务日志备份（截断日志）

BACKUP LOG [SampleData] TO DISK = N'E:\SampleBack\SampleDataLogBack.bak' WITH NOFORMAT,NOINIT, NAME = N'SampleData-事务日志备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10

----在辅助数据库服务器上执行还原

------还原完整备份

--------RESTORE DATABASE [SampleData] FROM DISK = N'E:\SampleBack\SampleDataAllback.bak'WITH FILE = 1, MOVE N'SampleData_01' TON'E:\Sample\SampleData_1.ndf', MOVEN'SampleData_02' TO N'E:\Sample\SampleData_2.ndf', MOVE N'SampleData_Log' TON'E:\Sample\SampleData_3.ldf', NORECOVERY, NOUNLOAD, STATS = 10

--------GO

------还原事务日志备份

--------RESTORE LOG [SampleData] FROM DISK = N'E:\SampleBack\SampleDataLogBack.bak'WITH FILE = 1, NORECOVERY,NOUNLOAD, STATS = 10

----说明:数据库镜像的两种身份验证方式:证书和Windows身份验证

----使用基于证书的身份验证方式设置数据库镜像

-------第一步：配置[出站连接]

---------配置主机服务器的出站连接

------------在master数据库中创建数据库主密钥

USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD='hdf007@163.com'

------------为主机服务器数据库实例制作一个证书

USE master

CREATE CERTIFICATE HOST_A_Cert WITH SUBJECT='HOST_Acertificate'

------------使用该证书为主机服务器数据库实例创建一个镜像端口

CREATE ENDPOINT Endpoint_Mirroring

STATE=STARTED

AS TCP (LISTENER_PORT=7024,LISTENER_IP=ALL)

FOR DATABASE_MIRRORING (AUTHENTICATION=CERTIFICATE HOST_A_Cert,ENCRYPTION=REQUIREDALGORITHM AES,ROLE=ALL)

------------备份HOST_A证书,并将其复制到辅助服务器、见证服务器

BACKUP CERTIFICATE HOST_A_Cert TO FILE='C:\HOST_A_Cert.cer'

---------配置辅助服务器的出站连接

------------在master数据库中创建数据库主密钥

USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD='hdf007@163.com'

------------为辅助服务器数据库实例制作一个证书

USE master

CREATE CERTIFICATE HOST_B_Cert WITH SUBJECT='HOST_Bcertificate'

------------使用该证书为辅助服务器数据库实例创建一个镜像端口

CREATE ENDPOINT Endpoint_Mirroring

STATE=STARTED

AS TCP (LISTENER_PORT=7024,LISTENER_IP=ALL)

FOR DATABASE_MIRRORING (AUTHENTICATION=CERTIFICATEHOST_B_Cert,ENCRYPTION=REQUIRED ALGORITHM AES,ROLE=ALL)

------------备份HOST_B证书,并将其复制到主机服务器、见证服务器

BACKUP CERTIFICATE HOST_B_Cert TO FILE='C:\HOST_B_Cert.cer'

----------配置见证服务器的出站连接

USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD='hdf007@163.com'

---------为见证服务器数据库实例制作一个证书

USE master

CREATE CERTIFICATE HOST_C_Cert WITHSUBJECT='HOST_C_certificate'

---------使用该证书为见证服务器数据库实例创建一个镜像端口

CREATE ENDPOINT Endpoint_Mirroring

STATE=STARTED

AS TCP (LISTENER_PORT=7024,LISTENER_IP=ALL)

FOR DATABASE_MIRRORING (AUTHENTICATION=CERTIFICATEHOST_C_Cert,ENCRYPTION=REQUIRED ALGORITHM AES,ROLE=ALL)

---------备份HOST_C证书，并将其复制到主机服务器、辅助服务器

BACKUP CERTIFICATE HOST_C_Cert TO FILE='C:\HOST_C_Cert.cer'

-------第二步：配置[入站连接]

---------配置主机服务器的入站连接

------------在主机服务器上为镜像服务器创建一个登录名

USE master

CREATE LOGIN HOST_B_Login WITH PASSWORD='hdf007@163.com'

------------创建一个该登录名的用户

CREATE USER HOST_B_User FROM LOGIN HOST_B_Login

------------使用从镜像服务器复制过来的证书与此用户关联

CREATE CERTIFICATE HOST_B_Cert AUTHORIZATION HOST_B_UserFROM FILE='C:\HOST_B_Cert.cer'

------------授予对远程镜像端点的登录名的 CONNECT 权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO[HOST_B_Login]

------------在主机服务器上为见证服务器创建一个登录名

USE master

CREATE LOGIN HOST_C_Login WITH PASSWORD='hdf007@163.com'

------------创建一个该登录名的用户

CREATE USER HOST_C_User FROM LOGIN HOST_C_Login

------------使用从见证服务器复制过来的证书与此用户关联

CREATE CERTIFICATE HOST_C_Cert AUTHORIZATION HOST_C_UserFROM FILE='C:\HOST_C_Cert.cer'

------------授予对远程镜像端点的登录名的CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO[HOST_C_Login]

---------配置辅助服务器的入站连接

------------在辅助服务器上为主机服务器创建一个登录名

USE master

CREATE LOGIN HOST_A_Login WITH PASSWORD='hdf007@163.com'

------------创建一个该登录名的用户

CREATE USER HOST_A_User FROM LOGIN HOST_A_Login

------------使用从主机服务器复制过来的证书与此用户关联

CREATE CERTIFICATE HOST_A_Cert AUTHORIZATION HOST_A_UserFROM FILE='C:\HOST_A_Cert.cer'

------------授予对远程镜像端点的登录名的 CONNECT 权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO[HOST_A_Login]

------------在辅助服务器上为见证服务器创建一个登录名

USE master

CREATE LOGIN HOST_C_Login WITH PASSWORD='hdf007@163.com'

------------创建一个该登录名的用户

CREATE USER HOST_C_User FROM LOGIN HOST_C_Login

------------使用从见证服务器复制过来的证书与此用户关联

CREATE CERTIFICATE HOST_C_Cert AUTHORIZATION HOST_C_UserFROM FILE='C:\HOST_C_Cert.cer'

------------授予对远程镜像端点的登录名的CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO[HOST_C_Login]

---------配置见证服务器的入站连接

------------在见证服务器上为主机服务器创建一个登录名

USE master

CREATE LOGIN HOST_A_Login WITH PASSWORD='hdf007@163.com'

------------创建一个该登录名的用户

CREATE USER HOST_A_User FROM LOGIN HOST_A_Login

------------使用从主机服务器复制过来的证书与此用户关联

CREATE CERTIFICATE HOST_A_Cert AUTHORIZATION HOST_A_UserFROM FILE='C:\HOST_A_Cert.cer'

------------授予对远程镜像端点的登录名的 CONNECT 权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO[HOST_A_Login]

------------在见证服务器上为辅助服务器创建一个登录名

USE master

CREATE LOGIN HOST_B_Login WITH PASSWORD='hdf007@163.com'

------------创建一个该登录名的用户

CREATE USER HOST_B_User FROM LOGIN HOST_B_Login

------------使用从见证服务器复制过来的证书与此用户关联

CREATE CERTIFICATE HOST_B_Cert AUTHORIZATION HOST_B_UserFROM FILE='C:\HOST_B_Cert.cer'

------------授予对远程镜像端点的登录名的CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO[HOST_B_Login]

-------第三步：配置镜像伙伴

---------在镜像服务器上将主机服务器设置为伙伴

ALTER DATABASE SampleData SET PARTNER='TCP://S1:7024'

---------在主机服务器上将镜像服务器设置为伙伴

ALTER DATABASE SampleData SET PARTNER='TCP://S2:7024'

---------在主机服务器上设置见证服务器

ALTER DATABASE SampleData SET WITNESS='TCP://S2:7024'

-----以下代码是在没有见证服务器的情况下实现手动故障转移

---------若要在高性能模式下配置此会话，在主体服务器实例上，将事务安全性设置为 OFF。

ALTER DATABASE SampleData SET PARTNER SAFETY OFF

---------若要在高安全模式下配置些会话，在主体服务器实例上，将事务安全性设置为FULL

ALTER DATABASE SampleData SET PARTNER SAFETY FULL

---------在高性能模式下，使用下列语句在镜像服务器上强制执行实现故障转移，会丢失数据

ALTER DATABASE SampleData SET PARTNERFORCE_SERVICE_ALLOW_DATA_LOSS

---------在高安全模式下，使用下列语句实现手工故障转移

ALTER DATABASE SampleData SET PARTNER FAILOVER

---------原来的主服务器恢复,可以继续工作,需要重新设定镜像

---------在辅助服务器上执行：

USE master

ALTER DATABASE SampleData SET PARTNER RESUME --恢复镜像

ALTER DATABASE SampleData SET PARTNER FAILOVER --切换主备

----使用基于Windows的身份验证方式设置数据库镜像

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

-- 主机服务器 S1 S1\S1User --

-- 辅助服务器 S2 S2\S2User --

-- 见证服务器 S3 S3\S3User --

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

---在主机服务器上创建一个镜像端点

CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP(LISTENER_PORT=7024) FOR DATABASE_MIRRORING (ROLE=ALL)

---在主机服务器上为辅助服务器创建一个登录

CREATE LOGIN [S2\S2User] FROM WINDOWS

---授予对远程镜像端点的登录名的CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [S2\S2User]

---在主机服务器上为见证服务器创建一个登录

CREATE LOGIN [S3\S3User] FROM WINDOWS

---授予对远程镜像端点的登录名的CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [S3\S3User]

---在辅助服务器上创建一个镜像端点

CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP(LISTENER_PORT=7024) FOR DATABASE_MIRRORING (ROLE=ALL)

---在辅助服务器上为主机服务器创建一个登录

CREATE LOGIN [S1\S2User] FROM WINDOWS

---授予对远程镜像端点的登录名的CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [S1\S2User]

---在辅助服务器上为见证服务器创建一个登录

CREATE LOGIN [S3\S3User] FROM WINDOWS

---授予对远程镜像端点的登录名的CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [S3\S3User]

---在见证服务器上创建一个镜像端点

CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP(LISTENER_PORT=7024) FOR DATABASE_MIRRORING (ROLE=ALL)

---在见证服务器上为主机服务器创建一个登录

CREATE LOGIN [S1\S1User] FROM WINDOWS

---授予对远程镜像端点的登录名的CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [S1\S1User]

---在见证服务器上为辅助服务器创建一个登录

CREATE LOGIN [S2\S2User] FROM WINDOWS

---授予对远程镜像端点的登录名的CONNECT权限

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [S2\S2User]

----配置镜像伙伴

---------在镜像服务器上将主机服务器设置为伙伴

ALTER DATABASE SampleData SET PARTNER='TCP://S1:7024'

---------在主机服务器上将镜像服务器设置为伙伴

ALTER DATABASE SampleData SET PARTNER='TCP://S2:7024'

---------在主机服务器上设置见证服务器

ALTER DATABASE SampleData SET WITNESS='TCP://S2:7024'

将客户端连接到数据库镜像会话 (SQL Server)

Failover Partner 属性

除了初始伙伴名称以外，客户端还可以指定应标识当前镜像服务器实例的故障转移伙伴名称。故障转移伙伴是由 failover partner 属性的某个关键字指定的。具体由该属性的哪个关键字指定取决于您所使用的 API。下表列出了这些关键字：

API	failover partner 属性的关键字
OLE DB 访问接口	FailoverPartner
ODBC 驱动程序	Failover_Partner
ActiveX 数据对象 (ADO)	Failover Partner

标识服务器实例的最简单方法是指定其名称 <server_name>[\<SQL_Server_instance_name>]。

连接字符串示例

例如，为了使用 TCP/IP 显式连接到 Partner_A 或 Partner_B 上的 AdventureWorks 数据库，使用 ODBC 驱动程序的客户端应用程序可能会提供以下连接字符串：

"Server=Partner_A; Failover_Partner=Partner_B; Database=AdventureWorks; Network=dbmssocn"

另外，客户端还可以使用 IP 地址和端口号标识初始伙伴 Partner_A；例如，如果 IP 地址为 250.65.43.21，端口号为 4734，则连接字符串将为：

"Server=250.65.43.21,4734; Failover_Partner=Partner_B; Database=AdventureWorks; Network=dbmssocn"

参考：

http://liulike.blog.51cto.com/1355103/339183/

http://hdf007.blog.51cto.com/42396/824297

http://jingyan.baidu.com/article/3c343ff70c10e10d37796389.html

https://technet.microsoft.com/zh-cn/library/ms175484.aspx

上一篇：SQL Server HA - 数据库镜像（Mirroring）

下一篇：SQL Server HA - 数据库复制（Replication）

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯