【已苦思7天】数据库镜像配置之疑惑 本帖最后由 un_name 于 2012-01-06 02:14:38 编辑 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 没有手工同步登錄名及密碼證書--主机执行:USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itdba!@#123';CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' , START_DATE = '10/10/2011'; --备机执行:USE master;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itdba!@#123';CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate', START_DATE = '10/10/2011';端點--主机执行:CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_A_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 HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );證書互聯--主机执行:BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';--备机执行:BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';添加用戶--主机执行:CREATE LOGIN HOST_B_login WITH PASSWORD = 'itdba!@#123';CREATE USER HOST_B_user FOR LOGIN HOST_B_login;CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];--备机执行:CREATE LOGIN HOST_A_login WITH PASSWORD = 'itdba!@#123';CREATE USER HOST_A_user FOR LOGIN HOST_A_login;CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE ='D:\SQLBackup\HOST_A_cert.cer';GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]; 手工同步登錄名及密碼在主数据库中执行如下语句:USE master;select sid,name from syslogins;0x6FF81B32DAC76D43989AA56577C6C2A5備庫執行:USE master;exec sp_addlogin @loginame = 'HOST_B_login', @passwd = 'itdba!@#123', @sid = 0x6FF81B32DAC76D43989AA56577C6C2A5 ;準備備機數據庫承接上文,该节是描述如何同步主备数据库内的数据。還原數據庫及日誌,在还原数据的时候需要使用选上“with non recover”。建立镜像由于是实验,没有为服务器配置双网卡,IP地址与图有点不一样,但是原理一样。--主机执行:ALTER DATABASE SMT_PRD SET PARTNER = 'TCP://192.168.0.1:5022';--如果主体执行不成功,尝试在备机中执行如下语句:ALTER DATABASE SMT_PRD SET PARTNER = 'TCP://192.168.0.2:5022'; 回复peggye,手工同步登錄名及密碼,也已经做了,并且保证了@sid一致。代码和步骤相信是无问题的,已经在其他服务器反复测试多次。 服务器网络地址 "TCP://124.172.242.105:5088" 无法访问或不存在。 还是用 netstat 和 telnet 命令 看看 5088 端口 是否已经开通 not in 2个字段 sql server能否记录每一条执行的tsql语句和发生时间? 我要按时间统计怎么做呢 求SQL语句,跨表、记录横排,能实现否? 合计排序的问题 求教:帮写一个SQL语句,以实现自动筛选辨别的功能,不胜感激! 头疼啊,闹心~ 大家帮帮我吧,一个关于数据库连接的问题 vb如何用adodc调用sql存程过程 关于串口技术,请高手赐教! SQL SERVER :一条SQL列出所有包含指定字符串的记录 求高手解答。分组查询。 关于SQLServer2000 存储过程
--主机执行:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itdba!@#123';
CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,
START_DATE = '10/10/2011';
--备机执行:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itdba!@#123';
CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',
START_DATE = '10/10/2011';端點
--主机执行:
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS
TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )
FOR
DATABASE_MIRRORING
( AUTHENTICATION = CERTIFICATE HOST_A_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 HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );證書互聯
--主机执行:
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\SQLBackup\HOST_A_cert.cer';
--备机执行:
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\SQLBackup\HOST_B_cert.cer';添加用戶
--主机执行:
CREATE LOGIN HOST_B_login WITH PASSWORD = 'itdba!@#123';
CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\SQLBackup\HOST_B_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
--备机执行:
CREATE LOGIN HOST_A_login WITH PASSWORD = 'itdba!@#123';
CREATE USER HOST_A_user FOR LOGIN HOST_A_login;
CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE ='D:\SQLBackup\HOST_A_cert.cer';
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]; 手工同步登錄名及密碼
在主数据库中执行如下语句:
USE master;
select sid,name from syslogins;
0x6FF81B32DAC76D43989AA56577C6C2A5
備庫執行:
USE master;
exec sp_addlogin
@loginame = 'HOST_B_login',
@passwd = 'itdba!@#123',
@sid = 0x6FF81B32DAC76D43989AA56577C6C2A5 ;準備備機數據庫
承接上文,该节是描述如何同步主备数据库内的数据。
還原數據庫及日誌,在还原数据的时候需要使用选上“with non recover”。建立镜像
由于是实验,没有为服务器配置双网卡,IP地址与图有点不一样,但是原理一样。
--主机执行:
ALTER DATABASE SMT_PRD SET PARTNER = 'TCP://192.168.0.1:5022';
--如果主体执行不成功,尝试在备机中执行如下语句:
ALTER DATABASE SMT_PRD SET PARTNER = 'TCP://192.168.0.2:5022';