使用證照設定資料庫映象

環境如下：

主資料庫：192.168.1.179 映象資料庫：192.168.1.183 見證伺服器：192.168.1.117 資料庫：Crn

在主資料庫中執行如下操作：

USE [master]

   --建立資料庫主金鑰。
IF EXISTS (SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1)
    --drop master key;
     OPEN MASTER KEY DECRYPTION BY PASSWORD='123456789';
ELSE
     create MASTER KEY ENCRYPTION BY PASSWORD='123456789';
GO

SELECT * FROM sys.key_encryptions; --檢視金鑰

go

--向資料庫中新增證照。
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring')
DROP ENDPOINT Endpoint_Mirroring
GO

IF EXISTS(select * from sys.certificates WHERE name='HOST_A_cert')
DROP CERTIFICATE HOST_A_cert;
GO

CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate',
START_DATE = '2009-01-01';
GO

SELECT * FROM sys.certificates;
GO

--建立資料庫端點
IF EXISTS(SELECT * FROM sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring')
DROP ENDPOINT Endpoint_Mirroring;
GO

CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED
    AS TCP ( LISTENER_PORT=5022 ,
         LISTENER_IP = ALL )
    FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE HOST_A_cert ,
         ENCRYPTION = REQUIRED ALGORITHM AES ,
         ROLE = PARTNER );
GO

SELECT * FROM sys.database_mirroring_endpoints;
GO

--備份證照
BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\MIRROR\HOST_A_cert.cer';
GO

--在伺服器之間手動複製證照,保證每個服務只器都有所有證照

--建立使用者,用於訪問MIRROR
IF EXISTS(select * from sys.certificates WHERE name='HOST_B_cert')
DROP CERTIFICATE HOST_B_cert
GO

IF EXISTS(select * from sys.sql_logins WHERE name='HOST_B_login')
DROP LOGIN HOST_B_login
GO

IF EXISTS(select * from sys.database_principals WHERE name='HOST_B_user')
DROP USER HOST_B_user
GO

--在host_a中為host_b建立登陸名
create LOGIN HOST_B_login WITH PASSWORD = '123456789';
--為host_b登陸名建立一個使用者
create USER HOST_B_user FOR LOGIN HOST_B_login;
--使用這個使用者與host_b的證照相關聯
create CERTIFICATE
HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\MIRROR\HOST_B_cert.cer';
GO
--授予對遠端映象端點的登入名的 CONNECT 許可權。
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO

--建立使用者,用於訪問WITNESS
IF EXISTS(select * from sys.certificates WHERE name='HOST_C_cert')
DROP CERTIFICATE HOST_C_cert
GO

IF EXISTS(select * from sys.sql_logins WHERE name='HOST_C_login')
DROP LOGIN HOST_C_login
GO

IF EXISTS(select * from sys.database_principals WHERE name='HOST_C_user')
DROP USER HOST_C_user
GO

create LOGIN HOST_C_login WITH PASSWORD = '123456789';
create USER HOST_C_user FOR LOGIN HOST_C_login;
create CERTIFICATE
HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:\MIRROR\HOST_C_cert.cer';
GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO

--複製使用者
SELECT [name],[sid] FROM sys.database_principals WHERE [name]='BetterDev'

--備份資料庫

--設定夥伴
ALTER DATABASE northwind SET PARTNER='TCP://192.168.1.183:5022';
GO
--設定見證
ALTER DATABASE NORTHWIND SET WITNESS='TCP://192.168.1.117:5022';
GO
--設定安全選項
ALTER DATABASE NORTHWIND SET SAFETY FULL

在映象伺服器中執行的程式碼：

USE [master]

--建立資料庫主金鑰。
IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1)
    --drop master key;
    OPEN MASTER KEY DECRYPTION BY PASSWORD='123456789';
ELSE
    create MASTER KEY ENCRYPTION BY PASSWORD='123456789';
GO

SELECT * FROM sys.key_encryptions;
go

--向資料庫中新增證照。
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring')
DROP ENDPOINT Endpoint_Mirroring
GO

IF EXISTS(select * from sys.certificates WHERE name='HOST_B_cert')
DROP CERTIFICATE HOST_B_cert;
GO

CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',
START_DATE = '2008-01-01';
GO

SELECT * FROM sys.certificates;
GO

--建立資料庫端點
IF EXISTS(SELECT * FROM sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring')
DROP ENDPOINT Endpoint_Mirroring;
GO

CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED
    AS TCP ( LISTENER_PORT=5022 ,
        LISTENER_IP = ALL )
    FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE HOST_B_cert ,
        ENCRYPTION = REQUIRED ALGORITHM RC4 ,
        ROLE = PARTNER );
GO

SELECT * FROM sys.database_mirroring_endpoints;
GO

--備份證照
BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\MIRROR\HOST_B_cert.cer';
GO

--在伺服器之間手動複製證照,保證每個伺服器都有所有證照

--建立使用者,用於訪問MAIN
IF EXISTS(select * from sys.certificates WHERE name='HOST_A_cert')
DROP CERTIFICATE HOST_A_cert
GO

IF EXISTS(select * from sys.sql_logins WHERE name='HOST_A_login')
DROP LOGIN HOST_A_login
GO

IF EXISTS(select * from sys.database_principals WHERE name='HOST_A_user')
DROP USER HOST_A_user
GO
--在host_b中為host_a建立登陸名
create LOGIN HOST_A_login WITH PASSWORD = '123456789';
--為該登陸名建立使用者
create USER HOST_A_user FOR LOGIN HOST_A_login;
--使該使用者同host_a的證照相關聯
create CERTIFICATE
HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\MIRROR\HOST_A_cert.cer';
GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO

--建立使用者,用於訪問WITNESS
IF EXISTS(select * from sys.certificates WHERE name='HOST_C_cert')
DROP CERTIFICATE HOST_C_cert
GO

IF EXISTS(select * from sys.sql_logins WHERE name='HOST_C_login')
DROP LOGIN HOST_C_login
GO

IF EXISTS(select * from sys.database_principals WHERE name='HOST_C_user')
DROP USER HOST_C_user
GO
--
create LOGIN HOST_C_login WITH PASSWORD = '123456789';
create USER HOST_C_user FOR LOGIN HOST_C_login;
create CERTIFICATE
HOST_C_cert AUTHORIZATION HOST_C_user FROM FILE = 'D:\MIRROR\HOST_C_cert.cer';
GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_login];
GO

--新增使用者
EXEC sp_addlogin
    @loginname=''
    @passwd=''
    @sid='';
GO

--手動還原資料庫

--設定夥伴
ALTER DATABASE northwind SET PARTNER='TCP://192.168.1.179:5022';
GO

在見證伺服器中執行的程式碼：

USE [master]

--建立資料庫主金鑰。
IF EXISTS(SELECT * FROM sys.databases WHERE name='master' and is_master_key_encrypted_by_server=1)
    --drop master key;
    OPEN MASTER KEY DECRYPTION BY PASSWORD='123456789';
ELSE
    create MASTER KEY ENCRYPTION BY PASSWORD='123456789';
GO

SELECT * FROM sys.key_encryptions;
go

--向資料庫中新增證照。
IF EXISTS(select * from sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring')
DROP ENDPOINT Endpoint_Mirroring
GO

IF EXISTS(select * from sys.certificates WHERE name='HOST_C_cert')
DROP CERTIFICATE HOST_C_cert;
GO

CREATE CERTIFICATE HOST_C_cert WITH SUBJECT = 'HOST_C certificate',
START_DATE = '2008-01-01';
GO

SELECT * FROM sys.certificates;
GO

--建立資料庫端點
IF EXISTS(SELECT * FROM sys.database_mirroring_endpoints WHERE name='Endpoint_Mirroring')
DROP ENDPOINT Endpoint_Mirroring;
GO

CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED
    AS TCP ( LISTENER_PORT=5022 ,
        LISTENER_IP = ALL )
    FOR DATABASE_MIRRORING( AUTHENTICATION = CERTIFICATE HOST_C_cert ,
        ENCRYPTION = REQUIRED ALGORITHM RC4 ,
        ROLE = WITNESS );
GO

SELECT * FROM sys.database_mirroring_endpoints;
GO

--備份證照
BACKUP CERTIFICATE HOST_C_cert TO FILE = 'E:\MIRROR\HOST_C_cert.cer';
GO

--在伺服器之間手動複製證照,保證每個伺服器都有所有證照

--建立使用者,用於訪問MAIN
IF EXISTS(select * from sys.certificates WHERE name='HOST_A_cert')
DROP CERTIFICATE HOST_A_cert
GO

IF EXISTS(select * from sys.sql_logins WHERE name='HOST_A_login')
DROP LOGIN HOST_A_login
GO

IF EXISTS(select * from sys.database_principals WHERE name='HOST_A_user')
DROP USER HOST_A_user
GO

create LOGIN HOST_A_login WITH PASSWORD = '123456789';
create USER HOST_A_user FOR LOGIN HOST_A_login;
create CERTIFICATE
HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'E:\MIRROR\HOST_A_cert.cer';
GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];
GO

--建立使用者,用於訪問映象
IF EXISTS(select * from sys.certificates WHERE name='HOST_B_cert')
DROP CERTIFICATE HOST_B_cert
GO

IF EXISTS(select * from sys.sql_logins WHERE name='HOST_B_login')
DROP LOGIN HOST_B_login
GO

IF EXISTS(select * from sys.database_principals WHERE name='HOST_B_user')
DROP USER HOST_B_user
GO

create LOGIN HOST_B_login WITH PASSWORD = '123456789';
create USER HOST_B_user FOR LOGIN HOST_B_login;
create CERTIFICATE
HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'E:\MIRROR\HOST_B_cert.cer';
GO

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];
GO

come from：http://hi.baidu.com/ownswords/blog/item/3a5305ddfc1e1ec88c102984.html

SQlServer高可用性之資料庫映象篇(2)--安裝

相關文章