sql server基于鏡像服務(wù)器的主從搭建+認(rèn)證服務(wù)器

題目：sql server基于鏡像服務(wù)器的主從搭建+認(rèn)證服務(wù)器

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

環(huán)境說明：windows2016、sql server 2014

主體服務(wù)器：192.168.164.100

鏡像服務(wù)器：192.168.164.101

見證服務(wù)器：192.168.164.102

前置概念

主從方案介紹

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

（一）復(fù)制模式

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

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

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

　　主要分為以下4種發(fā)布方式：

　　1.快照發(fā)布

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

　　2.事務(wù)發(fā)布/具有可更新訂閱的事務(wù)發(fā)布

　　　　事務(wù)發(fā)布，是在第一次設(shè)置好事務(wù)復(fù)制之后，所有發(fā)布的內(nèi)容都會進(jìn)行鏡像快照，訂閱服務(wù)器收到已發(fā)布數(shù)據(jù)的初始快照后，發(fā)布服務(wù)器將事務(wù)流式傳輸?shù)接嗛喎?wù)器。當(dāng)主服務(wù)器數(shù)據(jù)發(fā)生變更時，會通過日志傳遞同步給訂閱服務(wù)器，數(shù)據(jù)近似于同步更新。

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

　　3.合并發(fā)布

　　　　合并發(fā)布是相當(dāng)于兩臺都是主服務(wù)器，都可以對數(shù)據(jù)進(jìn)行更新修改等操作，然后定時將發(fā)布服務(wù)器上的內(nèi)容與訂閱服務(wù)器上的內(nèi)容進(jìn)行合并，并根據(jù)配置保留相應(yīng)內(nèi)容，此種很少用。

（二）鏡像傳輸模式

　　數(shù)據(jù)庫鏡像傳輸，嚴(yán)格來說不是主從架構(gòu)，而是主備架構(gòu)，將兩臺數(shù)據(jù)庫服務(wù)器通過一臺中間監(jiān)控服務(wù)器關(guān)聯(lián)起來，兩臺服務(wù)器通過鏡像文件，實時同步數(shù)據(jù)（有延遲，延遲很短）。當(dāng)主服務(wù)器宕機之后，監(jiān)控服務(wù)器自動切換到備份服務(wù)器上。

　　此方案優(yōu)點是可以快速的切換主備方案，相比較Always on集群，可以不用共享磁盤即可實現(xiàn)，避免了數(shù)據(jù)庫集群存儲單點故障，導(dǎo)致整個集群崩潰。

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

（三）日志傳輸模式

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

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

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

（四）故障轉(zhuǎn)移集群

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

　　集群有很多優(yōu)點，是目前最高效的高可用技術(shù)，但是他也有很明顯的缺點，所有的節(jié)點，都依賴于共享節(jié)點實例，如果共享節(jié)點出現(xiàn)故障，將會導(dǎo)致整個集群失去作用，且很難恢復(fù)。

1.1、服務(wù)器概念

◆ 主體服務(wù)器（Principal Server）

　　其中一個實例為客戶端提供服務(wù)，這個實例稱為"主體服務(wù)器"。該服務(wù)器"扮演"主體角色"，其數(shù)據(jù)庫副本為當(dāng)前的"主體數(shù)據(jù)庫"。

◆ 鏡像服務(wù)器（Mirror Server）

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

◆ 見證服務(wù)器（Witness Server）

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

1.2、模式概念

數(shù)據(jù)庫鏡像會話以同步操作或異步操作運行。

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

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

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

（1） 高安全性模式：主庫把事務(wù)日志數(shù)據(jù)信息發(fā)給從庫，從庫返回事務(wù)日志持久化確認(rèn)信息，確認(rèn)同步后，事務(wù)將在主從庫一起提交。

（2） 高性能模式：主庫把事務(wù)日志數(shù)據(jù)信息發(fā)給從庫，發(fā)完后無需等待從庫返回確認(rèn)信息。

（3） 自動故障轉(zhuǎn)移模式（高可用模式）：在高安全模式運行時，可以添加見證服務(wù)器，從而實現(xiàn)自動故障轉(zhuǎn)移。

前置配置

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

（2）數(shù)據(jù)庫版本：必須是標(biāo)準(zhǔn)版及更高的開發(fā)/評估/企業(yè)，且只有企業(yè)版/開發(fā)版才能實現(xiàn)高性能模式。且主從一致。

（3）通信：確認(rèn)網(wǎng)絡(luò)能ping通，確定端點端口（默認(rèn)一般是5022）與實例端口（默認(rèn)1433）可以telnet 通實現(xiàn)訪問。

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

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

（6）系統(tǒng)：32位系統(tǒng)下，單實例最多支持10個數(shù)據(jù)庫做鏡像。

（7）主數(shù)據(jù)庫：鏡像的數(shù)據(jù)庫對象不能是系統(tǒng)數(shù)據(jù)庫。主庫必須是完整恢復(fù)模式。且主從庫必須是相同的數(shù)據(jù)庫名。

（8）從數(shù)據(jù)庫：利用主數(shù)據(jù)庫的相關(guān)備份進(jìn)行還原，必須以norecovery模式（RESTORING 狀態(tài)）

（9）權(quán)限：登錄名具有實施步驟權(quán)限，最好sysadmin?？赡苓€需要實例賬戶擁有一定程度的windows權(quán)限，最好是admin組；

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

搭建過程

-- 1、創(chuàng)建主密鑰（主庫、鏡像庫、認(rèn)證服務(wù)器上都執(zhí)行）
use master
go
create master key encryption by password='landui@123'
go
-- 查看主密鑰 select * from sys.key_encryptions

-- 2、分別在主體服務(wù)器、鏡像服務(wù)器、見證服務(wù)器上創(chuàng)建證書
-- 主庫
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

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


-- 3、分別在主體服務(wù)器、鏡像服務(wù)器、見證服務(wù)器上創(chuàng)建端點
-- 主庫
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

-- 見證服務(wù)器
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、備份證書（主體服務(wù)器、鏡像服務(wù)器、見證服務(wù)器都備份，并互相拷貝過去，保證每個服務(wù)器上都有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

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

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

-- 創(chuàng)建見證服務(wù)器的證書關(guān)聯(lián)用戶
use master
go
create login sqlsrv_witness_login with password='landui@123'
go

-- 從庫上執(zhí)行：
-- 創(chuàng)建主庫的證書關(guān)聯(lián)用戶
use master
go
create login sqlsrv1_login with password='landui@123'
go

-- 創(chuàng)建見證服務(wù)器的證書關(guān)聯(lián)用戶
use master
go
create login sqlsrv_witness_login with password='landui@123'
go

-- 見證服務(wù)器上執(zhí)行：
-- 創(chuàng)建主庫的證書關(guān)聯(lián)用戶
use master
go
create login sqlsrv1_login with password='landui@123'
go

-- 創(chuàng)建鏡像庫的證書關(guān)聯(lián)用戶
use master
go
create login sqlsrv2_login with password='landui@123'
go

-- 6、創(chuàng)建使用該登錄名的用戶（主體服務(wù)器、鏡像服務(wù)器、見證服務(wù)器都要創(chuàng)建）
-- 主庫上執(zhí)行

-- 創(chuàng)建鏡像庫的證書關(guān)聯(lián)用戶
use master
go
create user sqlsrv2_user for login sqlsrv2_login
go

-- 創(chuàng)建見證服務(wù)器的證書關(guān)聯(lián)用戶
use master
go
create user sqlsrv_witness_user for login sqlsrv_witness_login
go

-- 鏡像庫上執(zhí)行

-- 創(chuàng)建主庫的證書關(guān)聯(lián)用戶
use master
go
create user sqlsrv1_user for login sqlsrv1_login
go

-- 創(chuàng)建見證服務(wù)器的證書關(guān)聯(lián)用戶
use master
go
create user sqlsrv_witness_user for login sqlsrv_witness_login
go

-- 見證服務(wù)器上執(zhí)行

-- 創(chuàng)建主庫的證書關(guān)聯(lián)用戶
use master
go
create user sqlsrv1_user for login sqlsrv1_login
go

-- 創(chuàng)建鏡像庫的證書關(guān)聯(lián)用戶
use master
go
create user sqlsrv2_user for login sqlsrv2_login
go

-- 7、證書與用戶關(guān)聯(lián)
-- 主庫上執(zhí)行

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

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

-- 鏡像庫上執(zhí)行

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

--使見證服務(wù)器上的證書與用戶關(guān)聯(lián)
use master
go
create certificate sqlsrv_witness_cert
authorization sqlsrv_witness_user
from file='C:\\cert\\sqlsrv_witness_cert.cer'
go

-- 見證服務(wù)器上執(zhí)行

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

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

-- 8、授予對遠(yuǎn)程數(shù)據(jù)庫端點的登錄名的CONNECT權(quán)限(每個上面2個用戶都要有端點的權(quán)限)
-- 主庫上執(zhí)行
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

-- 從庫上執(zhí)行
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

-- 見證服務(wù)器上執(zhí)行
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、對需要做鏡像的數(shù)據(jù)庫做完整備份和事務(wù)日志備份，然后在鏡像庫上做還原（先還原完整備份，再還原事務(wù)日志），使用NORECOVERY模式

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

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

-- 主庫上執(zhí)行

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

完成后測試

主庫和鏡像庫的狀態(tài)

主從同步和見證服務(wù)器的自動故障轉(zhuǎn)移測試

新建表

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',' 操作系統(tǒng)','104');