镜像服务器(***.88.55.224)
主体服务器(***.88.55.223)
1.迁移数据库备份(***.88.55.168)
use master
GO
backup DATABASE db_mydb To Disk = 'D:\portalBak\portal0902.bak' WITH FORMAT; --2s
go
2.把备份拷贝到要恢复的机器中
3.服务器数据库恢复
use master
go
RESTORE DATABASE db_mydb
FROM DISK ='d:\portalBak\portal0902.bak' WITH NORECOVERY,
MOVE 'db_mydb' TO 'D:\portalData
\db_mydb.MDF',
MOVE 'db_mydb_log' TO 'D:\portalData\db_mydb_log.LDF'
GO
use master
go
RESTORE DATABASE db_mydb
FROM DISK ='d:\portalBak\portal0902.bak' WITH RECOVERY,
MOVE 'db_mydb' TO 'D:\portalData
\db_mydb.MDF',
MOVE 'db_mydb_log' TO 'D:\portalData\db_mydb_log.LDF'
GO
4.建立数据库端点（只需要创建一次）
CREATE ENDPOINT [Endpoint_db_mydb]
AUTHORIZATION [IT-PORTAL-1-224\meizuadmin]
STATE=STARTED
AS TCP (LISTENER_PORT =
5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED)
CREATE ENDPOINT [Endpoint_db_mydb]
AUTHORIZATION [IT-PORTAL-1-223\meizuadmin]
STATE=STARTED
AS TCP (LISTENER_PORT =
5022, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED)
5.配置镜像命令 (ip为主体ip)
镜像：
use master
go
ALTER DATABASE db_mydb SET PARTNER =N'TCP://***.88.55.223:5022';
go
主体：
use master
go
ALTER DATABASE db_mydb SET PARTNER =N'TCP://***.88.55.224:5022';
go
6.事务安全模式(同步/异步)
主体
ALTER DATABASE db_mydb SET PARTNER SAFETY FULL;
7.同步两服务器的登录用户
主体：
use master;
select sid,name from syslogins where name = 'portalweb_aspnet';
镜像：
USE master;
exec sp_addlogin
@loginame = 'portalweb_aspnet',
@passwd = '2B6kwY7T7Pn7ZuCl',
@sid
=0x0330DEF76AE2484CA75FD1DC660EDFB1;
测试：
1.主备互换
主体:
USE master
go
ALTER DATABASE db_mydb SET PARTNER FAILOVER;
go
镜像：
USE master
go
ALTER DATABASE db_mydb SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;
go
2.原来的主服务器恢复，可以继续工作，需要重新设置镜像
镜像：
USE master;
go
ALTER DATABASE db_mydb SET PARTNER RESUME; --恢复镜像
ALTER DATABASE db_mydb SET PARTNER FAILOVER; --切换主备
go
3.原来的主服务器恢复,可以继续工作
USE master;
go
ALTER DATABASE db_mydb SET PARTNER SAFETY FULL; --事务安全,同步模式
go
<add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Data Source=1...238;Failover Partner=1...239;Initial Catalog=as_mydb;Persist Security Info=True;User ID=aaa;Password=aaa" />
