MSSQL镜像—无见证服务器

镜像服务器(***.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" />