T-SQL 语句创建Database的SQL mirroring关系

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 是要删除其会话的镜像数据库。