Configure Database Mirroring

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

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

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

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

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

1 BACKUP DATABASE [AdventureWorks2012] 2 TO DISK = 'C:\HOSTA\AdventureWorks2012.bak' 3 WITH FORMAT4 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 = STARTED10 AS TCP (11 LISTENER_PORT=702412 , LISTENER_ip = ALL13 ) 14 FOR DATABASE_MIRRORING ( 15 AUTHENTICATION = CERTIFICATE HOST_A_cert16 , ENCRYPTION = REQUIRED ALGORITHM AES17 , ROLE = ALL18 );19 GO20 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=702411 , LISTENER_IP = ALL12 ) 13 FOR DATABASE_MIRRORING ( 14 AUTHENTICATION = CERTIFICATE HOST_B_cert15 , ENCRYPTION = REQUIRED ALGORITHM AES16 , ROLE = ALL17 );18 GO19 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 GO10 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 GO11 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/