sql server基于鏡像服務器的主從搭建+認證服務器

題目：sql server基于鏡像服務器的主從搭建+認證服務器

要求：重啟主庫數據庫服務器看鏡像庫是否變自動更為主庫，原主庫重啟后是否變更為鏡像庫，同時與新主庫保持同步狀態，再把從庫切換成主庫看是否正常。測試數據隨便在庫表內建立任意數據。

環境說明：windows2016、sql server 2014

主體服務器：192.168.164.100

鏡像服務器：192.168.164.101

見證服務器：192.168.164.102

前置概念

主從方案介紹

sql server 作為目前主流的數據庫，用戶遍布世界各地。sql server也有一些比較成熟的主備方案，目前主要有：復制模式(發布-訂閱模式)、鏡像傳輸模式、日志傳輸模式、故障轉移集群。后面會一一介紹介紹各自的優缺點。

（一）復制模式

　　復制模式也被稱為發布-訂閱模式，是由主服務器進行發布消息，備份服務器進行訂閱，當主服務器數據發生變更時，就會發布消息，備份服務器讀取消息進行同步更新，中間過程延遲比較短。

　　復制方式是以前很常見的一種主備，速度快，延遲小，可以支持部分同步等優點，但是也有一個很明顯的缺點，因為是部分同步，如果是表修改，可以主動同步，但是如果是新增表、視圖等操作，必須在發布屬性中，將新加的表或者視圖添加到同步配置中，否則對這個表做的任何操作都不會同步。

　　復制模式同步，要求數據庫名稱和主機名稱必須一致，否則查找不到數據庫主機；要求數據庫不能使用端口，必須是可以通過ip直接訪問的；

　　主要分為以下4種發布方式：

　　1.快照發布

　　　　快照發布，就是將所有要發布的內容，做成一個鏡像文件，然后一次性復制到訂閱服務器，兩次快照之間的更新不會實時同步。這種方式占用帶寬較多，因此比較適用內容不是很大，或者更新不需要很頻繁的場景

　　2.事務發布/具有可更新訂閱的事務發布

　　　　事務發布，是在第一次設置好事務復制之后，所有發布的內容都會進行鏡像快照，訂閱服務器收到已發布數據的初始快照后，發布服務器將事務流式傳輸到訂閱服務器。當主服務器數據發生變更時，會通過日志傳遞同步給訂閱服務器，數據近似于同步更新。

　　　　此方式會對主服務器性能造成很大影響（實時同步每次變更，而不是最終變更），適用于對數據及時性要求比較嚴格主備方案，但是目前已被微軟提供的集群Always On所取代。

　　3.合并發布

　　　　合并發布是相當于兩臺都是主服務器，都可以對數據進行更新修改等操作，然后定時將發布服務器上的內容與訂閱服務器上的內容進行合并，并根據配置保留相應內容，此種很少用。

（二）鏡像傳輸模式

　　數據庫鏡像傳輸，嚴格來說不是主從架構，而是主備架構，將兩臺數據庫服務器通過一臺中間監控服務器關聯起來，兩臺服務器通過鏡像文件，實時同步數據（有延遲，延遲很短）。當主服務器宕機之后，監控服務器自動切換到備份服務器上。

　　此方案優點是可以快速的切換主備方案，相比較Always on集群，可以不用共享磁盤即可實現，避免了數據庫集群存儲單點故障，導致整個集群崩潰。

　　缺點也很明顯，無論是主備服務器，要實現同步操作，都是依賴于性能低的那一端，因此兩臺服務器都要是高性能的才可以保證同步的及時性；同時備份服務器只是備份和故障轉移，不能提供從服務器的只讀訪問，因此才說是主備服務器，而且是一對一，只能有一臺備份服務器。

（三）日志傳輸模式

　　與鏡像傳輸模式類似，是將主數據庫日志備份，發送到從服務器上，然后從服務器還原日志，更新數據。

　　此方式優點在于從服務器可以有多臺從服務器，而且當主服務器腳本操作異常后，只需要在日志同步之前，及時攔截日志傳輸，即可保留從服務器數據，減少災難損失；此方式相較于“復制發布”模式，還有一個有點就是無論是新增表、視圖等等，都會通過日志同步給從服務器，而復制模式不行

　　而相應的缺點就是通過日志備份傳輸，在還原，會有較大的時間延遲。而且無法自動轉移故障，只能手動轉移。

（四）故障轉移集群

　　集群技術是微軟提供的，可用性最高的主備方案。它是將多臺服務器通過一個共享的外部存儲區域（SAN），連接成一個資源共享的服務器群體，數據庫文件和實例，都存放并運行在該共享區域節點上，每臺服務器相當于一個節點，共同訪問共享的節點實例。服務器只有一個節點處于活動狀態，當活動節點出現故障，會有其他節點主動啟動，取代當前故障點，整個過程只需要幾秒鐘，用戶無法感知。

　　集群有很多優點，是目前最高效的高可用技術，但是他也有很明顯的缺點，所有的節點，都依賴于共享節點實例，如果共享節點出現故障，將會導致整個集群失去作用，且很難恢復。

1.1、服務器概念

◆ 主體服務器（Principal Server）

　　其中一個實例為客戶端提供服務，這個實例稱為"主體服務器"。該服務器"扮演"主體角色"，其數據庫副本為當前的"主體數據庫"。

◆ 鏡像服務器（Mirror Server）

　　另一個實例則充當備用服務器，這個實例稱為"鏡像服務器"（Mirror Server）。該服務器扮演"鏡像角色"，其數據庫副本為當前的"鏡像數據庫"。鏡像數據庫不能供客戶端訪問，但是可以為鏡像數據庫創建一個快照，讓客戶端訪問這個快照。

◆ 見證服務器（Witness Server）

　　見證服務器并不能用于數據庫，只是用來支持自動故障轉移。見證服務器驗證主體服務器是否保持運行，當見證服務器與主體服務器斷開連接之后，如果此時鏡像服務器和見證服務器保持相互連接，則鏡像服務器啟動自動故障轉移，成為新的主體服務器。

1.2、模式概念

數據庫鏡像會話以同步操作或異步操作運行。

　　在同步操作下，事務將在伙伴雙方處提交。由于主體數據庫需要等待鏡像數據庫將日志寫入磁盤后返回的確認消息，因此會延長事務滯后時間。在異步操作下，事務不需要等待鏡像服務器將日志寫入磁盤便可提交，這樣可最大程度地提高性能。

在SQL SERVER 2008之后，主庫和鏡像庫之間的日志流傳送會默認使用壓縮，壓縮一方面降低了網絡壓力，另一方面增大了鏡像兩端的CPU壓力。 可以打開 TF 1462 來關閉日志流壓縮，SQL SERVER 2005 上日志傳送沒有使用壓縮。

根據是否同步操作以及是否支持自動故障轉移功能，數據庫鏡像有以下三種運行模式。

（1） 高安全性模式：主庫把事務日志數據信息發給從庫，從庫返回事務日志持久化確認信息，確認同步后，事務將在主從庫一起提交。

（2） 高性能模式：主庫把事務日志數據信息發給從庫，發完后無需等待從庫返回確認信息。

（3） 自動故障轉移模式（高可用模式）：在高安全模式運行時，可以添加見證服務器，從而實現自動故障轉移。

前置配置

（1）實例版本：必須是2005 SP1及以上（且兼容級別也要在這個版本及以上），且主從版本一致。

（2）數據庫版本：必須是標準版及更高的開發/評估/企業，且只有企業版/開發版才能實現高性能模式。且主從一致。

（3）通信：確認網絡能ping通，確定端點端口（默認一般是5022）與實例端口（默認1433）可以telnet 通實現訪問。

（4）磁盤：足夠的磁盤空間（全備復制+事務備復制+還原空間+預留空間）。最好主從是相同的目錄（不同會造成無法加文件）。

（5）限制：不支持 FILESTREAM。不能在主體上創建它。不能為包含 FILESTREAM 文件組的數據庫配置數據庫鏡像。

（6）系統：32位系統下，單實例最多支持10個數據庫做鏡像。

（7）主數據庫：鏡像的數據庫對象不能是系統數據庫。主庫必須是完整恢復模式。且主從庫必須是相同的數據庫名。

（8）從數據庫：利用主數據庫的相關備份進行還原，必須以norecovery模式（RESTORING 狀態）

（9）權限：登錄名具有實施步驟權限，最好sysadmin。可能還需要實例賬戶擁有一定程度的windows權限，最好是admin組；

（10）端點：鏡像兩端的加密算法必須保持一致，否則無法搭建。（因為不同版本默認加密算法不一樣）小版本沒關系，但前提是從庫可以還原主庫。

搭建過程

-- 1、創建主密鑰（主庫、鏡像庫、認證服務器上都執行）
use master
go
create master key encryption by password='landui@123'
go
-- 查看主密鑰 select * from sys.key_encryptions

-- 2、分別在主體服務器、鏡像服務器、見證服務器上創建證書
-- 主庫
use master
go
create certificate sqlsrv1_cert with subject='sqlsrv1_cert',expiry_date='2099-1-1'
go

-- 鏡像庫
use master
go
create certificate sqlsrv2_cert with subject='sqlsrv2_cert',expiry_date='2099-1-1'
go

-- 見證服務器
use master
go
create certificate sqlsrv_witness_cert with subject='sqlsrv_witness_cert',expiry_date='2099-1-1'
go


-- 3、分別在主體服務器、鏡像服務器、見證服務器上創建端點
-- 主庫
Use master;
go
CREATE ENDPOINT Endpoint_Mirroring
  STATE = STARTED AS TCP (
    LISTENER_PORT=5022
     , LISTENER_IP = ALL
  )

FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE sqlsrv1_cert
     , ENCRYPTION = REQUIRED ALGORITHM AES
     , ROLE =    ALL
  );
GO

-- 鏡像庫
Use master;
go
CREATE ENDPOINT Endpoint_Mirroring
  STATE = STARTED AS TCP (
    LISTENER_PORT=5022
     , LISTENER_IP = ALL
  )

FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE sqlsrv2_cert
     , ENCRYPTION = REQUIRED ALGORITHM AES
     , ROLE =    ALL
  );
GO

-- 見證服務器
Use master;
go
CREATE ENDPOINT Endpoint_Mirroring
  STATE = STARTED AS TCP (
    LISTENER_PORT=5022
     , LISTENER_IP = ALL
  )

FOR DATABASE_MIRRORING (
    AUTHENTICATION = CERTIFICATE sqlsrv_witness_cert
     , ENCRYPTION = REQUIRED ALGORITHM AES
     , ROLE =    ALL
  );
GO

-- 4、備份證書（主體服務器、鏡像服務器、見證服務器都備份，并互相拷貝過去，保證每個服務器上都有3個證書）
-- 主庫
use master
go
backup certificate sqlsrv1_cert to file = 'C:\\cert\\sqlsrv1_cert.cer'
go

-- 從庫
use master
go
backup certificate sqlsrv2_cert to file = 'C:\\cert\\sqlsrv2_cert.cer'
go

-- 見證服務器
use master
go
backup certificate sqlsrv_witness_cert to file = 'C:\\cert\\sqlsrv_witness_cert.cer'
go

-- 5、創建登錄名（這個要和證書關聯，所以主體服務器、鏡像服務器、見證服務器都要創建除自己以外的另外2個用戶）
-- 主庫上執行：
-- 創建鏡像庫的證書關聯用戶
use master
go
create login sqlsrv2_login with password='landui@123'
go

-- 創建見證服務器的證書關聯用戶
use master
go
create login sqlsrv_witness_login with password='landui@123'
go

-- 從庫上執行：
-- 創建主庫的證書關聯用戶
use master
go
create login sqlsrv1_login with password='landui@123'
go

-- 創建見證服務器的證書關聯用戶
use master
go
create login sqlsrv_witness_login with password='landui@123'
go

-- 見證服務器上執行：
-- 創建主庫的證書關聯用戶
use master
go
create login sqlsrv1_login with password='landui@123'
go

-- 創建鏡像庫的證書關聯用戶
use master
go
create login sqlsrv2_login with password='landui@123'
go

-- 6、創建使用該登錄名的用戶（主體服務器、鏡像服務器、見證服務器都要創建）
-- 主庫上執行

-- 創建鏡像庫的證書關聯用戶
use master
go
create user sqlsrv2_user for login sqlsrv2_login
go

-- 創建見證服務器的證書關聯用戶
use master
go
create user sqlsrv_witness_user for login sqlsrv_witness_login
go

-- 鏡像庫上執行

-- 創建主庫的證書關聯用戶
use master
go
create user sqlsrv1_user for login sqlsrv1_login
go

-- 創建見證服務器的證書關聯用戶
use master
go
create user sqlsrv_witness_user for login sqlsrv_witness_login
go

-- 見證服務器上執行

-- 創建主庫的證書關聯用戶
use master
go
create user sqlsrv1_user for login sqlsrv1_login
go

-- 創建鏡像庫的證書關聯用戶
use master
go
create user sqlsrv2_user for login sqlsrv2_login
go

-- 7、證書與用戶關聯
-- 主庫上執行

-- 使鏡像庫上的證書與用戶關聯
use master
go
create certificate sqlsrv2_cert
authorization sqlsrv2_user
from file='C:\\cert\\sqlsrv2_cert.cer'
go

--使見證庫上的證書與用戶關聯
use master
go
create certificate sqlsrv_witness_cert
authorization sqlsrv_witness_user
from file='C:\\cert\\sqlsrv_witness_cert.cer'
go

-- 鏡像庫上執行

-- 使主庫上的證書與用戶關聯
use master
go
create certificate sqlsrv1_cert
authorization sqlsrv1_user
from file='C:\\cert\\sqlsrv1_cert.cer'
go

--使見證服務器上的證書與用戶關聯
use master
go
create certificate sqlsrv_witness_cert
authorization sqlsrv_witness_user
from file='C:\\cert\\sqlsrv_witness_cert.cer'
go

-- 見證服務器上執行

-- 使主庫上的證書與用戶關聯
use master
go
create certificate sqlsrv1_cert
authorization sqlsrv1_user
from file='C:\\cert\\sqlsrv1_cert.cer'
go

--使鏡像庫上的證書與用戶關聯
use master
go
create certificate sqlsrv2_cert
authorization sqlsrv2_user
from file='C:\\cert\\sqlsrv2_cert.cer'
go

-- 8、授予對遠程數據庫端點的登錄名的CONNECT權限(每個上面2個用戶都要有端點的權限)
-- 主庫上執行
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlsrv2_login];
go

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlsrv_witness_login];
go

-- 從庫上執行
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlsrv1_login];
go

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlsrv_witness_login];
go

-- 見證服務器上執行
use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlsrv1_login];
go

use master
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [sqlsrv2_login];
go

-- 9、對需要做鏡像的數據庫做完整備份和事務日志備份，然后在鏡像庫上做還原（先還原完整備份，再還原事務日志），使用NORECOVERY模式

-- 10.連接鏡像(先在鏡像庫上操作,然后在主庫上操作，不需要在見證數據庫上操作)
-- 鏡像庫上執行 (做完這個操作后,數據庫的狀態:正在恢復)

use master
go
ALTER DATABASE testdb SET PARTNER = 'TCP://192.168.164.100:5022';
go

-- 主庫上執行

-- 連接鏡像庫
use master
go
ALTER DATABASE testdb SET PARTNER = 'TCP://192.168.164.101:5022';
go
-- 連接見證服務器
use master
go
ALTER DATABASE testdb SET WITNESS = 'TCP://192.168.164.102:5022';
go

完成后測試

主庫和鏡像庫的狀態

主從同步和見證服務器的自動故障轉移測試

新建表

CREATE  TABLE　course
( course_id   char(5)  PRIMARY  KEY,
course_name  char(20) ,
period  int,
UNIQUE(course_name)
)

INSERT INTO course VALUES('1001',' C++','100');
INSERT INTO course VALUES('1002',' java','101');
INSERT INTO course VALUES('1003',' python','102');

INSERT INTO course VALUES('1004',' 計算機組成原理','103');
INSERT INTO course VALUES('1005',' 操作系統','104');