Ellie's blog

---------------------------------------
-- SQL 7 / Primary
---------------------------------------
--在 master 資料庫中，建立資料庫主要金鑰。
select * from sys.symmetric_keys 
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'

--在 master 資料庫中，建立憑證。
USE master;  
CREATE CERTIFICATE HOST_SQL7_cert   
  WITH SUBJECT = 'HOST_SQL7 certificate for database mirroring'--,   
  --EXPIRY_DATE = '11/30/2013';  

USE master;  
SELECT * FROM sys.certificates;  

--使用憑證來建立其端點
CREATE ENDPOINT Endpoint_Mirroring  
  STATE = STARTED  
  AS TCP (  
     LISTENER_PORT=5022  
     , LISTENER_IP = ALL  
  )   
  FOR DATABASE_MIRRORING (   
     AUTHENTICATION = CERTIFICATE HOST_SQL7_cert  
     , ENCRYPTION = REQUIRED ALGORITHM AES  
     , ROLE = ALL  
  ); 
  
SELECT name, role_desc, state_desc, connection_auth_desc, encryption_algorithm_desc   
  FROM sys.database_mirroring_endpoints;  

--備份憑證，複製到Secondary /witness 使用
BACKUP CERTIFICATE HOST_SQL7_cert TO FILE = 'C:\temp\HOST_SQL7_cert.cer';  --Secondary需使用此certificate

--為Secondary建立login & user
CREATE LOGIN Host_SQL8_Login WITH PASSWORD = 'Pa$$w0rd';
CREATE USER Host_SQL8_User For Login Host_SQL8_Login;

--用Secondary端點的憑證授權login
CREATE CERTIFICATE Host_SQL8_Cert  
AUTHORIZATION Host_SQL8_User  
FROM FILE = 'C:\temp\HOST_SQL8_cert.cer';  

--將 CONNECT 權限授與登入，以連接鏡像端點。
GRANT CONNECT ON ENDPOINT::[Endpoint_Mirroring] TO [Host_SQL8_Login];

 

---------------------------------------
-- SQL 8 / Secondary
---------------------------------------
select * from sys.symmetric_keys 
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd'

CREATE CERTIFICATE HOST_SQL8_cert   
  WITH SUBJECT = 'HOST_SQL8 certificate for database mirroring'--,   
  --EXPIRY_DATE = '11/30/2013';  
GO

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

BACKUP CERTIFICATE HOST_SQL8_cert TO FILE = 'C:\temp\HOST_SQL8_cert.cer';  

--為Primary建立login & user
CREATE LOGIN Host_SQL7_Login WITH PASSWORD = 'Pa$$w0rd';
CREATE USER Host_SQL7_User For Login Host_SQL7_Login;

--用Primary端點的憑證授權login
CREATE CERTIFICATE Host_SQL7_Cert  
AUTHORIZATION Host_SQL7_User  
FROM FILE = 'C:\temp\HOST_SQL7_cert.cer';  

----將 CONNECT 權限授與登入，以連接鏡像端點。
GRANT CONNECT ON ENDPOINT::[Endpoint_Mirroring] TO [Host_SQL7_Login];

SELECT EP.name, SP.STATE,   
  CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))   
     AS GRANTOR,   
  SP.TYPE AS PERMISSION,  
  CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))   
     AS GRANTEE   
  FROM sys.server_permissions SP , sys.endpoints EP  
  WHERE SP.major_id = EP.endpoint_id  
  ORDER BY Permission,grantor, grantee   
GO  

 

-------------------------------------------------------------------------------------
-- Config Mirroring
-------------------------------------------------------------------------------------
-- 在Primary 上備份DB
backup database TestDB to disk = 'C:\Temp\Testdb.bak' with init
backup log  TestDB to disk = 'C:\Temp\Testdb.trn' with init

-- 在Secondary上還原
restore database TestDB from disk = 'C:\Temp\TestDB.bak' with 
move 'TestDB' to  'C:\Temp\TestDB.mdf',
move 'TestDB_log' to  'C:\Temp\TestDB_log.ldf',norecovery
GO

restore log TestDB from disk = 'C:\Temp\TestDB.trn' with norecovery

-- 在Secondary上執行alter database
USE master;  
GO  
ALTER DATABASE TestDB   
   SET PARTNER = 'TCP://SQL7:5022'  
GO

-- 在Primary上執行alterdatabase
USE master;  
GO  
ALTER DATABASE TestDB   
   SET PARTNER =   'TCP://SQL8:5022'  
GO