SQLServer 資料庫鏡像+複製方案

標籤：cat   alter   .com   rda   需要   key   訂閱   param   state   

目標：

      主機做了Mirror和Replication，當主機出現問題時，Replication和Mirror實現自動的容錯移轉（Mirror 和Replication都切換到備機，而當主機

       重新啟動後，自動充當備機的角色）。

 

環境：

          五台虛擬機器，配置均為Windows2008 Enterprise + SQLServer2008R2 Enterprise

          08R201：Mirror 見證機(WITNESS)           IP:192.168.56.101

          08R202：主機（Rep+Mirror）                  IP:192.168.56.102

          08R203：Rep分發機                               IP:192.168.56.103

          08R204：Rep訂閱機                               IP:192.168.56.104

          08R205：鏡像機（Mirror）                       IP:192.168.56.105

步驟：

配置有見證伺服器的鏡像

1. 建立認證和Endpoint

     08R202(Master) 上運行下面的指令碼：

--主機執行： USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD =‘123456abc‘; CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = ‘HOST_A certificate‘ , START_DATE = ‘11/08/2010‘,EXPIRY_DATE = ‘10/31/2099‘; --主機執行： 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 = ALL ); --主機執行： BACKUP CERTIFICATE HOST_A_certTO FILE = ‘D:HOST_A_cert.cer‘;

      08R205(Mirror) 上運行下面的指令碼：

--備機執行： USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD =‘123456abc‘; CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = ‘HOST_B certificate‘, START_DATE = ‘11/08/2010‘,EXPIRY_DATE = ‘10/31/2099‘; --備機執行： 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 AES , ROLE = ALL ); --備機執行： BACKUP CERTIFICATE HOST_B_certTO FILE = ‘D:HOST_B_cert.cer‘;

      08R201(WITNESS) 上運行下面的指令碼：

--見證機執行 USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD =‘123456abc‘; CREATE CERTIFICATE HOST_W_cert WITH SUBJECT = ‘HOST_W certificate‘, START_DATE = ‘11/08/2010‘,EXPIRY_DATE = ‘10/31/2099‘; --見證機執行 CREATE ENDPOINT Endpoint_Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE HOST_W_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL ); --見證機執行 BACKUP CERTIFICATE HOST_W_certTO FILE = ‘D:HOST_W_cert.cer‘;

1. 交換認證

     將HOST_B_cert.cer和HOST_W_cert.cer拷貝到 08R202 機器的”D:Cert“目錄；

     將HOST_A_cert.cer和HOST_W_cert.cer拷貝到 08R205 機器的”D:Cert“目錄；

     將HOST_A_cert.cer和HOST_B_cert.cer拷貝到 08R201 機器的”D:Cert“目錄；

 

     08R202(Master) 上運行下面的指令碼：

--Master CREATE LOGIN HOST_B_login WITH PASSWORD = ‘[email protected]#‘; CREATEUSER HOST_B_user FOR LOGIN HOST_B_login; CREATE CERTIFICATE HOST_B_certAUTHORIZATION HOST_B_user FROM FILE = ‘D:CertHOST_B_cert.cer‘; GRANTCONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login]; CREATE LOGIN HOST_W_login WITH PASSWORD = ‘[email protected]#‘; CREATE USER HOST_W_user FORLOGIN HOST_W_login; CREATE CERTIFICATE HOST_W_cert AUTHORIZATIONHOST_W_user FROM FILE = ‘D:CertHOST_W_cert.cer‘; GRANT CONNECT ONENDPOINT::Endpoint_Mirroring TO [HOST_W_login];

     08R205(Mirror) 上運行下面的指令碼：

--Mirror CREATE LOGIN HOST_A_login WITH PASSWORD = ‘[email protected]#‘; CREATEUSER HOST_A_user FOR LOGIN HOST_A_login; CREATE CERTIFICATE HOST_A_certAUTHORIZATION HOST_A_user FROM FILE = ‘D:CertHOST_A_cert.cer‘; GRANTCONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]; CREATE LOGIN HOST_W_login WITH PASSWORD = ‘[email protected]#‘; CREATE USER HOST_W_user FORLOGIN HOST_W_login; CREATE CERTIFICATE HOST_W_cert AUTHORIZATIONHOST_W_user FROM FILE = ‘D:CertHOST_W_cert.cer‘; GRANT CONNECT ONENDPOINT::Endpoint_Mirroring TO [HOST_W_login];

     08R201(WITNESS) 上運行下面的指令碼：

--WITNESS CREATE LOGIN HOST_A_login WITH PASSWORD = ‘[email protected]#‘; CREATEUSER HOST_A_user FOR LOGIN HOST_A_login; CREATE CERTIFICATE HOST_A_certAUTHORIZATION HOST_A_user FROM FILE = ‘D:CertHOST_A_cert.cer‘; GRANTCONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login]; CREATE LOGIN HOST_B_login WITH PASSWORD = ‘[email protected]#‘; CREATE USER HOST_B_user FORLOGIN HOST_B_login; CREATE CERTIFICATE HOST_B_cert AUTHORIZATIONHOST_B_user FROM FILE = ‘D:CertHOST_B_cert.cer‘; GRANT CONNECT ONENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

1. 備份還原資料庫

     08R202(Master) 上備份資料庫：

BACKUP DATABASE RepTest TO DISK=‘D:tempRepTest.bak‘ BACKUP LOG RepTestTO DISK=‘D:tempRepTest.trn‘

     將備份檔案拷貝到 08R205(Mirror) 上做還原（最好以SA帳號登入，使得資料庫擁有者為”SA“）：

--RESTORE RESTORE DATABASE RepTest FROM DISK = N‘D:RepTest.bak‘ WITHFILE = 1, MOVE N‘RepTest‘ TO N‘D:DataRepTest.mdf‘, MOVE N‘RepTest_log‘TO N‘D:DataRepTest_log.ldf‘, NORECOVERY, NOUNLOAD, STATS = 10 GORESTORE LOG RepTest FROM DISK =‘D:RepTest.trn‘ WITH NORECOVERY

1. 建立鏡像

      在08R205(Mirror) 上執行：

--Mirror ALTER DATABASE RepTest SET PARTNER =‘TCP://192.168.56.102:5022‘;

     在 08R202(Master)上執行：

ALTER DATABASE RepTest SET PARTNER = ‘TCP://192.168.56.105:5022‘; ALTERDATABASE RepTest SET WITNESS = ‘TCP://192.168.56.101:5022‘; ALTERDATABASE RepTest SET SAFETY FULL

到此，鏡像已經建立完成。

 

配置Replication（事務型複製）

     Replicaiton不做具體的搭建過程，只做幾點說明：

     1.  08R202和08R205都需要搭建到08R203的Distribution；

      

     2.  08R202和08R205上的發布庫的所有者必須為“SA”，否則切換會出現如下錯誤：The process could not execute ‘sp_replcmds’ on ‘WIN-08R205′.

     3.  08R203分發機上設定檔需要做如下修改（配置容錯移轉Partner）：

--配置複製容錯移轉參數 --查看代理配置，在散發者運行 execsp_help_agent_profile --Agent_Type含義 --1 = Snapshot Agent; 2 = Log Reader Agent; 3 = Distribution Agent; --4 = Merge Agent; 9 = Queue Reader Agent. --對於事務複製，需查看Agent_Type=1,2的Profile_id --對於事務複製，需要配置快照代理(Snapshot Agent)和日誌讀取代理(Log Reader Agent) execsp_add_agent_parameter @profile_id = 1, @parameter_name = N‘-PublisherFailoverPartner‘, @parameter_value = N‘WIN-08R205‘--鏡像伺服器名稱 exec sp_add_agent_parameter @profile_id = 2, @parameter_name = N‘-PublisherFailoverPartner‘, @parameter_value = N‘WIN-08R205‘--鏡像伺服器名稱

修改完成後，記得重啟Agent服務，使之生效。

      4.  從08R202建立到08R204的同步鏈。

 

類比容錯移轉並觀察結果

     1.  暫停08R202的SQLServer服務；

     2.  到08R205上觀察鏡像和同步鏈是否轉移過去了；

      

      3.  如果同步鏈正常，修改某個表中的資料，看是否能同步到08R204上；

      4.  重新啟動08R202的SQLServer服務，看其是否成為了鏡像機；

      

       5.  如果一起都正常，那恭喜啦，我們的測試成功。

 

SQLServer 資料庫鏡像+複製方案