sqlserver關於mirror映象的總結

官方文件

mirror映象的基本原理：主伺服器上的主體資料庫執行的增、刪、改、查操作以日誌的方法在映象伺服器的映象資料庫中重做。主體資料庫建立映象後，會啟動一個單獨的事務日誌傳送執行緒，維護一個虛擬的傳送佇列，然後讀取事務日誌，將其進行壓縮，然後傳送給 mirror 節點，mirror 節點接收到以後，會將其寫入本地在磁碟上的一個重做佇列檔案中，然後再透過另外的一個執行緒非同步的方式，從重做佇列中獲取事務日誌，然後分發給應用執行緒(process unit)進行回放。

資料映象的兩種模式

同步映象操作：在事務傳送中，主伺服器必須等待映象伺服器返回成功接收日誌的訊息後，主伺服器才繼續下一事務日誌到磁碟的寫入與提交到映象伺服器。這種映象不會造成資料丟失，但是存在映象操作的事務延遲。

非同步映象操作：在事務傳送中，主伺服器不等待映象伺服器返回日誌的接收情況，繼續寫下一事務日誌到物理磁碟並提交給映象伺服器，這種映象操作效能較高，但是主伺服器當機後可能造成映象伺服器資料丟失。

1、搭建mirror，必須先對主庫進行全備和日誌備份，並且要以norecovery方式把全備和日誌恢復到從庫，之後再在主庫右鍵資料庫-->properties-->mirror，參考圖形介面一步步來搭建，如下是主庫搭建mirror的報錯,因為從庫恢復的時候只恢復了資料庫沒有恢復日誌導致

The mirror database, "XX", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database.

2、圖形介面搭建映象過程中，跳出的端點名稱會自動建立好，不需要手工先去建立，跳出的端點名稱和埠可以自己定義，預設的是Mirroring和5022

3、搭建好後，監控mirror的工具，右鍵資料庫-->Tasks-->Launch Database Mirroring Monitor,這個工具主庫從庫都有，效果一樣，這點和log shipping的Transaction Log Shipping Status主庫只負責主庫，從庫只負責從庫不一樣。

4、搭建好後，主庫後面狀態顯示(Principal,Synchronized)，從庫後面狀態顯示(Mirror,Synchronized/Restoring..)

5、搭建好後，主庫和從庫都有一個job名字是“Database Mirroring Monitor Job”，就算拆掉資料庫的mirror，主從上該job還在，新增一個資料庫的mirror，主備還是該job，沒新增job，該job刪除後，就算還有資料庫的mirror，該job也不會自動重建，但是下次新增資料庫的mirror時，該job會重建

6、因為從庫的映象資料庫無法讀，所以可以在從庫建立快照資料庫來讀，來確定mirror是否真正的同步

7、如果想實現主從自動切換，即自動故障轉移功能，必須要有見證伺服器

8、如果主庫故障了，從庫狀態變成(Mirror,Disconnected/In Recovery)，執行如下語句，恢復從庫的讀寫狀態(必須先執行第二條語句刪除從庫的快照，否則第三條語句無法執行)

ALTER DATABASE testdb SET PARTNER OFF;

drop database testdb_snapshot;

RESTORE DATABASE testdb WITH RECOVERY;

9、如果主庫執行了移除remove映象操作後需要刪除從庫再重新搭建主庫從庫的映象，但是從庫仍然顯示(Mirror,Disconnected/In Recovery)，導致從庫無法刪除，且從庫執行ALTER DATABASE testdb SET PARTNER OFF後狀態仍是(Mirror,Disconnected/In Recovery)，則需要先在主庫配置一下映象，然後會報錯映象搭建不成功，這個時候從庫狀態顯示(In Recovery),從庫這時候可以直接刪除。如果從庫還是無法刪除，就先在主庫配置一下映象，然後會報錯映象搭建不成功，再重啟從庫例項，從庫狀態一般會顯示為(suspect),這時從庫也可以直接刪除了。

10、手動故障轉移需要將事務安全設定為 FULL，且當夥伴連線在一起並且資料庫已同步時即資料庫處於 SYNCHRONIZED 狀態時，才支援手動故障轉移，登入主庫執行如下語句，上面8非手動故障轉移，因為資料庫不是同步狀態而是Disconnected/In Recovery

USE master;

ALTER DATABASE testdb SET PARTNER FAILOVER;

11、如果從庫使用主庫的全備和日誌備份進行restore norecovery後，開始搭建mirror，但是這個過程中，mirror還沒有搭建好，主庫又備份了日誌，mirror無法成功搭建，會有如下報錯，只能把主庫備份的日誌再restore norecovery到從庫，才可以正常搭建mirror

The remote copy of database <DatabaseName> has not been rolled forward to a point in time that is encompassed in the local copy of the database log

12、從庫無法直接delete刪除，這點和logshipping不一樣

13、映象故障查詢，可以從主庫和從庫的日誌中找相關資訊

14、mirror資料庫升級後，無法修改資料庫版本COMPATIBILITY_LEVEL，因為mirror是隻讀的，暫停mirror也無法修改，因為資料庫是restoring狀態

15、mirror相關資訊，可以參考檢視sys.database_mirroring

16、mirror的從庫，不能執行backup

17、主庫升級後可以修改level，從庫升級後無法修改level，如果主庫修改了level一旦主庫的日誌同步到從庫後，從庫對應的資料庫的level會和主庫一樣

18、主庫新增datafile後，從庫也會新增datafile，並且路徑和主庫的路徑一樣

19、mirror主庫增加了檔案，但是從庫沒有相應的目錄，則同步會suspend掛起，就算從庫有預設的datafile和logfile路徑。在從庫的資料庫日誌裡面可以看到報錯資訊:CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'E:\XX\YY.ndf'.

20、FILETABLE的資料庫無法搭建mirror，會報錯A database cannot be enabled for both Database Mirroring and FILESTREAM or for both Database Mirroring and MEMORY_OPTIMIZED_DATA storage

21、搭建mirror時遇到一個怪異報錯Database 'mirror_1' cannot be opened. It is in the middle of a restore。最後發現是因為資料庫例項的版本是2016，而搭建mirror時使用的SSMS版本是2017，把SSMS換成2016就沒有再報這個錯誤了，這個算是sqlserver的bug了

22 、在從庫建立映象資料庫mirror1的快照資料庫mirror_snapshot， NAME必須等於等於主庫裡面的資料檔案相同的邏輯名稱， filename自己隨意定義，指快照資料庫的filename

create database mirror_snapshot on

(NAME=mirror1,filename='G:\DEFAULT.DATA\mirror_snapshot')

as snapshot of mirror1;

23、監控mirror資料庫Db1最近的同步情況，可以參考如下語句（ 2表示最後4小時的行，如果把2改成1表示最後2小時的行）

USE msdb;  
EXEC sp_dbmmonitorresults Db1,2, 0;

24、主庫的狀態一直是(Principal,Suspend),右鍵主庫--屬性--Mirroring--Resume後，主庫的狀態是(Principal,Synchronizing),過一會主庫狀態還是(Principal,Suspend),檢視從庫例項的日誌有如下內容

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

    解決方法：右鍵主庫--屬性--Mirroring--Remove Mirroring，從庫狀態變成(Mirror,Disconnected/In Recovery),再在主庫建立Mirror，這時會報錯，從庫狀態變成(restoring),這個時候，把主庫備份的日誌拿到從庫去手工restore，所有日誌都restore完了後，再在主庫建立Mirror就正常了。

25、8核CPU，資料庫最大記憶體16GB環境，發現Database1在15分鐘內產生的日誌達到500MB以上時，mirror很容易出現suspend的狀態，檢視日誌發現資訊如下，就算你重啟資料庫，還是解決不了這個問題，只能移除mirror，在從庫上再手工restore這些日誌，再搭建mirror

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

Database mirroring will be suspended. Server instance 'Instance1' encountered error 1204, state 4, severity 19 when it was acting as a mirroring partner for database 'Database1'. The database mirroring partners might try to recover automatically from the error and resume the mirroring session. For more information, view the error log for additional error messages.

26、關於mirror和logshipping的選擇，遇到資料庫在短時間內產生的日誌很大，比如15分鐘內產生了500MB，那麼mirror不如logshipping，因為mirror需要消耗更多的記憶體，mirror很容易出現suspend的狀態

27、搭建mirror時遇到錯誤：伺服器網路地址 “TCP://dbalias:5022″ 無法訪問或不存在。請檢查網路地址名稱，並檢查本地和遠端端點的埠是否正常執行。 (Microsoft SQL Server，錯誤: 1418)。

解決思路

27.1、telnet dbalias:5022 是否通

27.2、檢查主備機器的sqlserver啟動賬號是否有許可權訪問對方例項

27.3、以上兩點都正常的情況下，重啟備機

27.4、以上步驟3也做過還是不行的話，重啟主機和備機的mirror endpoint端點，ALTER ENDPOINT endpoint_name STATE = STOPPED；ALTER ENDPOINT endpoint_name STATE = STARTED

27.5、以上步驟4也做過還是不行的話，重啟主機(自己就遇到一個這樣的問題，直到這第五步做完才能正常搭建mirror)

28、監控mirror同步更新狀態可以結合使用儲存過程msdb.sys.sp_dbmmonitorupdate和系統表msdb.dbo.dbm_monitor_data

29、在一臺伺服器上的例項搭建mirror報錯：The Database Mirroring endpoint cannot listen on port 5022 because it is in use by another process

解決思路

29.1、檢視發現該主機和備機伺服器上都有兩個例項1、2，透過以下語句查詢到主備機伺服器的例項1已經使用了5022，主機伺服器例項2使用了5023，備機伺服器例項2還是使用5022，所以報錯

select * from sys.endpoints where type_desc='DATABASE_MIRRORING'
select name,port from sys.tcp_endpoints  where type_desc='DATABASE_MIRRORING'

29.2、對備機伺服器例項2修改endpoint端點的埠5022為5023，透過以下語句修改，sys.tcp_endpoints.name表示查詢sys.tcp_endpoints得出的name結果

ALTER ENDPOINT sys.tcp_endpoints.name AS TCP (LISTENER_PORT=5023) FOR DATABASE_MIRRORING()

30、mirror主備透過遇到問題而發生中斷supsend，執行resume後還是不行，可以重啟端點再resume

30.1主備順序執行以下語句試試

ALTER ENDPOINT sys.tcp_endpoints.name STATE = STOPPED
ALTER ENDPOINT sys.tcp_endpoints.name STATE = STARTED

30.2主節點執行以下語句

ALTER DATABASE DBNAME  SET PARTNER RESUME

31、mirror主從環境，遇到過主庫sql service伺服器重啟，重啟是因為遇到大事務在回滾導致這個資料庫一些表被鎖死，引發一堆的堵塞，想透過重啟來加快回滾，重啟後主庫的某個資料庫一直處於recovery狀態，mirror對應的從庫也是recovery not sysncring狀態，重啟後10幾個小時都還是這種狀態的情況，嘗試把該庫的mirror移除，主庫馬上就恢復到了open狀態。所以個人得出結論：mirror情況下，如果主庫在recovery，那麼這些recovery在主庫完成了並完全同步到了從庫，這個時候主庫才會從recovery狀態恢復到open狀態，所以遇到主庫資源足，從庫資源不足，假設主庫本身recovery需要1小時，這些recovery的更改完全同步到從庫需要2小時，那麼mirror情況下主庫recovery的完成就需要1+2=3小時。

32、查詢mirror映象資訊包含主從伺服器名稱，資料庫的同步模式和同步狀態
select mirroring_partner_name,mirroring_partner_instance,mirroring_witness_name,db_name(database_id) db_name,mirroring_role_desc,mirroring_safety_level_desc,mirroring_state_desc from sys.database_mirroring