1 证书部分；principle 和 secondary 端执行同样操作,更改相应name即可
USE master;
--1.1 Create the database Master Key, if needed.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#2>';
GO
-- 1.2 Make a certificate on HOST_B server instance.
CREATE CERTIFICATE HOST_B_cert
WITH SUBJECT = 'HOST_B certificate for database mirroring',
EXPIRY_DATE = '11/30/2014';
GO
--1.3 Create a mirroring endpoint for the server instance on HOST_B.
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE HOST_B_cert
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO
--1.4 Backup HOST_B certificate.
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'C:HOST_B_cert.cer';
GO
--1.5 Using any secure copy method, copy C:HOST_B_cert.cer to HOST_A.
2 SQL中创建用户；principle 和 secondary 端执行同样操作,更改相应name即可
USE master;
--2.1 On HOST_B, create a login for HOST_A.
CREATE LOGIN HOST_A_login WITH PASSWORD = '1qaz2wsxE';
GO
--2.2 Create a user, HOST_A_user, for that login.
CREATE USER HOST_A_user FOR LOGIN HOST_A_login
GO
--2.3 Obtain HOST_A certificate. Associate this certificate with the user, HOST_A_user.
CREATE CERTIFICATE HOST_A_cert
AUTHORIZATION HOST_A_user
FROM FILE = 'C:HOST_A_cert.cer';
GO
--2.4 Grant CONNECT permission for the server instance on HOST_A.
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO HOST_A_login
GO
3 备份还原数据
在Host-A 机器上
--3.1 backup database
BACKUP DATABASE suzytest
TO DISK = 'C:suzytest.bak'
WITH FORMAT
GO
--3.2 backup log
BACKUP LOG suzytest
TO DISK = 'C:suzytest_log.bak'
GO
在 Host-B机器上
--3.3 restore database
RESTORE DATABASE suzytest
FROM DISK = 'C:suzytest_log.bak'
WITH NORECOVERY
GO
--3.4 restore log
RESTORE log suzytest
FROM DISK = 'C:suzytest_log.bak'
WITH FILE=1,NORECOVERY
GO
4 建立mirroing关系
--4.1 change the endpoint port（此处可省略，用上面的port7024）
Alter ENDPOINT endpoint_mirroring
STATE = STARTED
AS TCP ( LISTENER_PORT = 7022 )
FOR DATABASE_MIRRORING (ROLE=PARTNER);
GO
--4.2 On the mirror server instance(Host-B), set the server instance on Host-A as the partner (making it the initial principal server):
USE master;
GO
ALTER DATABASE suzytest
SET PARTNER =
'TCP://servername.silverstone.org:7022'
GO
--4.3 On the principal server instance, set the server instance on PARTNERHOST5 as the partner (making it the initial mirror server):
USE master;
GO
ALTER DATABASE suzytest
SET PARTNER = 'TCP://servername.silverstone.org:7022'
GO
5 删除mirroring 关系
为任一镜像伙伴连接到数据库引擎。
在标准菜单栏上，单击“新建查询”。
发出以下 Transact-SQL 语句：
ALTER DATABASE database_name SET PARTNER OFF
其中，database_name 是要删除其会话的镜像数据库。