Configure Database Mirroring

 

使用证书配置的镜像基本安装微软次序做就可以了

http://msdn.microsoft.com/zh-cn/library/ms191140.aspx

 

备份还原首先要转换成完全备份模式没什么好多说的

1 USE master;
2 GO
3 ALTER DATABASE 
4 SET RECOVERY FULL;
5 GO

直接备份数据库不能直接写C盘,目录自己建

1 BACKUP DATABASE [AdventureWorks2012]  
2     TO DISK = 'C:\HOSTA\AdventureWorks2012.bak' 
3     WITH FORMAT
4 GO

同样还原也需要指定,注意一定要使用WITH NORECOVERY

1 USE [master]
2 RESTORE DATABASE [AdventureWorks2012] 
3 FROM  DISK = N'C:\HOSTB\AdventureWorks2012.bak' 
4 WITH NORECOVERY,   
5 MOVE N'AdventureWorks2012_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2\MSSQL\DATA\AdventureWorks2012_Data.mdf',  
6 MOVE N'AdventureWorks2012_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2\MSSQL\DATA\AdventureWorks2012_log.ldf'
7 GO

证书创建和对倒部分其实就一步步做就可以了,注意端口号,路径用刚才的路径就成。密码偷懒也可以不改,原则上机器用的密码不要设置过期日期

HOSTA上

 1 USE master;
 2 CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
 3 GO
 4 USE master;
 5 CREATE CERTIFICATE HOST_A_cert 
 6    WITH SUBJECT = 'HOST_A certificate';
 7 GO
 8 CREATE ENDPOINT Endpoint_Mirroring
 9    STATE = STARTED
10    AS TCP (
11       LISTENER_PORT=7024
12       , LISTENER_IP = ALL
13    ) 
14    FOR DATABASE_MIRRORING ( 
15       AUTHENTICATION = CERTIFICATE HOST_A_cert
16       , ENCRYPTION = REQUIRED ALGORITHM AES
17       , ROLE = ALL
18    );
19 GO
20 BACKUP CERTIFICATE HOST_A_cert TO FILE = 'C:\HOSTA\HOST_A_cert.cer';
21 GO

HOSTB上

 1 USE master;
 2 CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#2>';
 3 GO
 4 CREATE CERTIFICATE HOST_B_cert 
 5    WITH SUBJECT = 'HOST_B certificate for database mirroring';
 6 GO
 7 CREATE ENDPOINT Endpoint_Mirroring
 8    STATE = STARTED
 9    AS TCP (
10       LISTENER_PORT=7024
11       , LISTENER_IP = ALL
12    ) 
13    FOR DATABASE_MIRRORING ( 
14       AUTHENTICATION = CERTIFICATE HOST_B_cert
15       , ENCRYPTION = REQUIRED ALGORITHM AES
16       , ROLE = ALL
17    );
18 GO
19 BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:\HOSTB\HOST_B_cert.cer';
20 GO 

HOSTA上的证书copy到HOSTB上,HOSTB上的证书copy到HOSTA上,随后创建用户导入证书

HOSTA

 1 USE master;
 2 CREATE LOGIN HOST_B_login WITH PASSWORD = '1Sample_Strong_Password!@#';
 3 GO
 4 CREATE USER HOST_B_user FOR LOGIN HOST_B_login;
 5 GO
 6 CREATE CERTIFICATE HOST_B_cert
 7    AUTHORIZATION HOST_B_user
 8    FROM FILE = 'C:\HOSTA\HOST_B_cert.cer'
 9 GO
10 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
11 GO

HOSTB

 1 USE master;
 2 CREATE LOGIN HOST_A_login WITH PASSWORD = '=Sample#2_Strong_Password2';
 3 GO
 4 CREATE USER 
 5  FOR LOGIN HOST_A_login;
 6 GO
 7 CREATE CERTIFICATE HOST_A_cert
 8    AUTHORIZATION HOST_A_user
 9    FROM FILE = 'C:\HOSTB\HOST_A_cert.cer'
10 GO
11 GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
12 GO

在HOSTB上首先运行, 可以使用IP地址或者主机名

1 --At HOST_B, set server instance on HOST_A as partner (principal server):
2 ALTER DATABASE [AdventureWorks2012]
3     SET PARTNER = 'TCP://192.168.100.101:7024';
4 GO

随后在HOSTA上运行

1 --At HOST_A, set server instance on HOST_B as partner (mirror server).
2 ALTER DATABASE [AdventureWorks2012]
3     SET PARTNER = 'TCP://192.168.100.102:7024';
4 GO

主库上应该显示为,主体已同步,备库上显示为正在恢复。

强制关闭HOSTA,在HOSTB使用以下语句进行切换,需要等待几秒。数据库显示为主体已断开连接

1 USE master;
2 alter database [AdventureWorks2012] set partner FORCE_SERVICE_ALLOW_DATA_LOSS;

http://msdn.microsoft.com/zh-cn/library/bb522476.aspx

此时恢复HOSTA,数据库状态为镜像,挂起/正在还原。。。在HOSTB中操作

1 USE master;
2 alter database [AdventureWorks2012] set partner resume; 

这样HOSTB 就成为了主体,假如要将HOSTA在设置为主体,在HOSTB上的运行一下语句,切换为HOSTA为主题

1 USE master;
2 alter database [AdventureWorks2012] set partner resume; 

 Reference

http://msdn.microsoft.com/zh-cn/library/ms189852(v=sql.110).aspx

http://msdn.microsoft.com/zh-cn/library/ms189053.aspx

http://msdn.microsoft.com/zh-cn/library/ms189921.aspx

http://www.mssqltips.com/sqlservertip/2464/configure-sql-server-database-mirroring-using-ssms/

posted @ 2014-10-23 08:44  Anderson.Ling  阅读(479)  评论(0编辑  收藏  举报