SQLServer 複製和資料庫鏡像 詳細配置部署

標籤：

SQLserver 可以把鏡像和複製同時部署，結合了雙方的高可用性，可以使資料庫得到更好的高可用性和容災的保證。

關於鏡像：資料庫鏡像

關於複製：資料庫鏡像

本章的複製為事務可更新訂閱：事務複製的可更新訂閱

關於複製和資料庫鏡像：複製和資料庫鏡像

理論的東西參考官方文檔吧，這裡主要是部署配置過程。

為本章參考部署的架構圖：

本章類比的伺服器：

kk-ad	192.168.2.1	DC（域控）
kk-db1	192.168.2.10	主機（Replication + Mirror）
kk-db2	192.168.2.11	鏡像（Mirror）
kk-db3	192.168.2.12	見證機（WITNESS）+ 分發
kk-db4	192.168.2.13	訂閱（測試1個）

將複製與資料庫鏡像一起使用時，注意以下要求和注意事項：

1. 主體資料庫和鏡像資料庫必須共用散發者。 建議此處使用遠端散發者，如果發行伺服器有意外容錯移轉，則遠端散發者可以提供較大的容錯能力。

2. 對於合併式複寫，以及對於使用唯讀訂閱伺服器或排隊更新訂閱伺服器的事務複製，複製支援對發行集資料庫進行鏡像。 不支援即時更新對等拓撲中的訂閱伺服器、Oracle 發行伺服器、發行伺服器並重新發布。

 

3. 存在於資料庫外部的中繼資料和對象不複製到鏡像資料庫，包括登入名稱、作業、連結的伺服器等等。 如果要求鏡像資料庫中有中繼資料和對象，則必須手動複製它們。

配置複製和資料庫鏡像主要步驟：

1. 設定資料庫鏡像；（參考 SQLServer 資料庫鏡像（二）域環境中完整鏡像指令碼配置）

2. 配置散發者；（參考 如何配置發布和分發  ， 如何在散發者上啟用遠程發行伺服器）

3. 配置發布和訂閱；（參考 如何建立事務性發布的可更新訂閱  ，SqlServer 使用指令碼建立分發服務及事務複製的可更新訂閱）

說明及注意修改的地方：

本測試使用域帳號作為資料庫登入帳號：[KK\UserReplMirror]

對應資料庫使用者：[UserReplMirror]

測試資料庫：[DemoDB]

確保各伺服器能相互訪問

資料庫 [DemoDB]  復原模式為完整模式（鏡像必須）

資料庫 [DemoDB]  所有者改為 [sa]

帳號 [KK\UserReplMirror] 授予 sysadmin 許可權；或者在資料庫 [DemoDB] 中授予使用者 [UserReplMirror] 資料庫角色為 [db_owner] 

複製的代理作業的所有者都改為[sa]

1. 設定資料庫鏡像：

--主體建立資料庫USE [master]GOCREATE DATABASE [DemoDB]GOALTER DATABASE [DemoDB] SET RECOVERY FULL WITH NO_WAITGO--主體：建立認證 和 備份USE master;  GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[email protected]';  GO  CREATE CERTIFICATE Cert_kk_db1_mssqlserver   WITH SUBJECT = 'Cert_kk_db1_mssqlserver',  START_DATE = '2015-06-01',EXPIRY_DATE = '2018-06-01';  GO  BACKUP CERTIFICATE Cert_kk_db1_mssqlserver   TO FILE = 'C:\Databases\Cert_kk_db1_mssqlserver.cer';  GO  --鏡像：建立認證 和 備份USE master;  GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[email protected]';  GO CREATE CERTIFICATE Cert_kk_db2_mssqlserver WITH SUBJECT = 'Cert_kk_db2_mssqlserver',  START_DATE = '2015-06-01',EXPIRY_DATE = '2018-06-01'; GO BACKUP CERTIFICATE Cert_kk_db2_mssqlserver  TO FILE = 'C:\Databases\Cert_kk_db2_mssqlserver.cer';  GO --見證：建立認證 和 備份USE master;  GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[email protected]';  GO CREATE CERTIFICATE Cert_kk_db3_mssqlserver WITH SUBJECT = 'Cert_kk_db3_mssqlserver',  START_DATE = '2015-06-01',EXPIRY_DATE = '2018-06-01'; GO BACKUP CERTIFICATE Cert_kk_db3_mssqlserver  TO FILE = 'C:\Databases\Cert_kk_db3_mssqlserver.cer';  GO --  交換認證(相互拷貝認證):  /*  主體認證(拷貝到)————>鏡像、見證  鏡像認證(拷貝到)————>主體、見證  見證認證(拷貝到)————>主體、鏡像  */  --  主體(建立使用者、還原認證、建立端點)：  USE master      GO      CREATE LOGIN [KK\UserReplMirror] FROM WINDOWS;      GO      CREATE USER [UserReplMirror] FOR LOGIN [KK\UserReplMirror];      GO       CREATE CERTIFICATE [Cert_kk_db2_mssqlserver]    AUTHORIZATION [UserReplMirror]     FROM FILE = 'C:\Databases\Cert_kk_db2_mssqlserver.cer';    GO     CREATE CERTIFICATE [Cert_kk_db3_mssqlserver]    AUTHORIZATION [UserReplMirror]     FROM FILE = 'C:\Databases\Cert_kk_db3_mssqlserver.cer';    GOCREATE ENDPOINT [Endpoint_For_Mirror]         AUTHORIZATION [KK\UserReplMirror]        STATE=STARTED        AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)        FOR DATA_MIRRORING         (ROLE = PARTNER, AUTHENTICATION = CERTIFICATE Cert_kk_db1_mssqlserver, ENCRYPTION = REQUIRED ALGORITHM AES)    GO    --GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserReplMirror];    --GO--  鏡像(建立使用者、還原認證、建立端點)：  USE master      GO      CREATE LOGIN [KK\UserReplMirror] FROM WINDOWS;      GO      CREATE USER [UserReplMirror] FOR LOGIN [KK\UserReplMirror];      GO       CREATE CERTIFICATE [Cert_kk_db1_mssqlserver]    AUTHORIZATION [UserReplMirror]     FROM FILE = 'C:\Databases\Cert_kk_db1_mssqlserver.cer';    GO     CREATE CERTIFICATE [Cert_kk_db3_mssqlserver]    AUTHORIZATION [UserReplMirror]     FROM FILE = 'C:\Databases\Cert_kk_db3_mssqlserver.cer';    GOCREATE ENDPOINT [Endpoint_For_Mirror]         AUTHORIZATION [KK\UserReplMirror]        STATE=STARTED        AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)        FOR DATA_MIRRORING         (ROLE = PARTNER, AUTHENTICATION = CERTIFICATE Cert_kk_db2_mssqlserver, ENCRYPTION = REQUIRED ALGORITHM AES)    GO    --GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserReplMirror];    --GO--  見證(建立使用者、還原認證、建立端點)：  USE master      GO      CREATE LOGIN [KK\UserReplMirror] FROM WINDOWS;      GO      CREATE USER [UserReplMirror] FOR LOGIN [KK\UserReplMirror];      GO       CREATE CERTIFICATE [Cert_kk_db1_mssqlserver]    AUTHORIZATION [UserReplMirror]     FROM FILE = 'C:\Databases\Cert_kk_db1_mssqlserver.cer';    GO     CREATE CERTIFICATE [Cert_kk_db2_mssqlserver]    AUTHORIZATION [UserReplMirror]     FROM FILE = 'C:\Databases\Cert_kk_db2_mssqlserver.cer';    GOUSE master;    CREATE ENDPOINT [Endpoint_For_Mirror]         AUTHORIZATION [KK\UserReplMirror]        STATE=STARTED        AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)        FOR DATA_MIRRORING         (ROLE = WITNESS, AUTHENTICATION = CERTIFICATE Cert_kk_db3_mssqlserver, ENCRYPTION = REQUIRED ALGORITHM AES)    GO --GRANT CONNECT ON ENDPOINT::Endpoint_For_Mirror TO [KK\UserReplMirror];    --GO--主體：備份資料庫USE master;  BACKUP DATABASE [DemoDB]    TO DISK = 'C:\Databases\DemoDB.BAK' WITH INIT,FORMAT    GO BACKUP LOG [DemoDB]    TO DISK = 'C:\Databases\DemoDB_LOG.BAK' WITH INIT,FORMAT    GO --鏡像：還原資料庫（NORECOVERY）USE master;  RESTORE DATABASE [DemoDB]FROM  DISK = N'C:\Databases\DemoDB.BAK'   WITH  FILE = 1,   MOVE N'DemoDB' TO N'C:\Databases\DemoDB.mdf',    MOVE N'DemoDB_log' TO N'C:\Databases\DemoDB_log.ldf',    NOUNLOAD, NORECOVERY, STATS = 10  GO   RESTORE DATABASE [DemoDB]FROM DISK = N'C:\Databases\DemoDB_LOG.BAK'   WITH NORECOVERYGO --開始鏡像 --在【鏡像】執行,PARTNER為主伺服器  USE [master]  GO  ALTER DATABASE [DemoDB] SET PARTNER = 'TCP://192.168.2.10:5022';  GO    --在【主體】執行,PARTNER為鏡像伺服器  USE [master]  GO  ALTER DATABASE [DemoDB] SET PARTNER = 'TCP://192.168.2.11:5022';  GO  ALTER DATABASE [DemoDB] SET WITNESS = 'TCP://192.168.2.12:5022'; GO--在主體執行:設定為高安全模式  ALTER DATABASE [DemoDB] SET SAFETY FULL  

EXEC [DemoDB].dbo.sp_changedbowner @loginame = N'sa', @map = falseGOEXEC master..sp_addsrvrolemember @loginame = N'KK\UserReplMirror', @rolename = N'sysadmin'GO

2. 配置散發者

登入到該伺服器。

kk-db3

192.168.2.12

見證機（WITNESS）+ 分發

右鍵複製，配置分發。

散發者選擇 “kk-db3” 本機伺服器。添加 “KK-DB1 ” （主機）和  “KK-DB2 ” （鏡像） 為發行伺服器，否則串連不到該散發者。

注意：建立散發者時，要求資料管理密碼。右鍵“複製”—“散發者屬性”—“發行伺服器”  可看到和設定。

配置用於容錯移轉的複製代理

可配置參數 PublisherFailoverPartner 的代理：
1 - 複製快照代理（對於所有發布）
2 - 複製記錄讀取器代理程式（對於所有事務發布）
4 - 複製合并代理（對於合并訂閱）
9 - 複製佇列讀取器代理程式（對於支援排隊更新訂閱的事務發布）

當前為可更新訂閱，只要更改1,2,9 就行（分發庫執行）

[email protected]_value 為鏡像服務exec sp_add_agent_parameter @profile_id = 1, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'kk-db2'exec sp_add_agent_parameter @profile_id = 2, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'kk-db2'exec sp_add_agent_parameter @profile_id = 9, @parameter_name = N'-PublisherFailoverPartner', @parameter_value = N'kk-db2'

接著 重啟SQL Server Agent

3. 配置發布和訂閱

登入到伺服器

kk-db1

192.168.2.10

主機（Replication + Mirror）

建立本地發布，選擇散發者。（只有分發中添加了發行伺服器的資訊，此處才能訪問）

這裡需要輸入在散發者設定的管理密碼。

註：此時在散發者（ 192.168.2.12 ）產生的複製相關作業代理，所有者改為 [sa]

登入到伺服器

kk-db4

192.168.2.13

訂閱

建立訂閱：（發布選擇 “kk-db1”）

建立完成訂閱後，初始化訂閱，登入到伺服器：

kk-db1

192.168.2.10

主機（Replication + Mirror）

登入到伺服器：

kk-db3

192.168.2.12

見證機（WITNESS）+ 分發

右鍵 “複製”—“啟動複製監視器”，右鍵 “添加發行伺服器”，將 發行伺服器 “kk-db1” 添加，即可看到複製監控情況

配置已完成！~

4. 測試

現在把主體（Replication + Mirror）服務停止：

停止後，到原來的鏡像（kk-db2）查看，鏡像變成了主體，本地發布也出現了（原來鏡像是不存在的）

但是，當我從現在的主體（kk-db2）插入資料時，資料並沒有同步到訂閱！~

難道是哪裡配置不對？？！~~

網上尋找後，發現是BUG，原因是：

This problem occurs because Log Reader Agent does not copy the transactions that are marked for replication from the transaction log into the distribution database. Log Reader Agent cannot read past a specific log sequence number (LSN). This specific LSN represents the last LSN that has been hardened to the transaction log of the mirror database.

FIX: Changes in the publisher database are not replicated to the subscribers in a transactional replication if the publisher database runs exposed in a database mirroring session in SQL Server 2005

暫時不修複，用其他方法設定也可以：

（先啟動 kk-db1 的SQLserver 服務）

在主體 和 鏡像中，設定服務啟動參數，添加 -T1448，重啟服務即可。

此時停止主體執行個體，鏡像變成主體，操作資料可正常和訂閱同步了！~

至此，就算完成了，個人測試，也可能有不對的地方。

SQLServer 複製和資料庫鏡像 詳細配置部署