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

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

要求：重啟主庫(kù)數(shù)據(jù)庫(kù)服務(wù)器看鏡像庫(kù)是否變自動(dòng)更為主庫(kù)，原主庫(kù)重啟后是否變更為鏡像庫(kù)，同時(shí)與新主庫(kù)保持同步狀態(tài)，再把從庫(kù)切換成主庫(kù)看是否正常。測(cè)試數(shù)據(jù)隨便在庫(kù)表內(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ù)庫(kù)，用戶遍布世界各地。sql server也有一些比較成熟的主備方案，目前主要有：復(fù)制模式(發(fā)布-訂閱模式)、鏡像傳輸模式、日志傳輸模式、故障轉(zhuǎn)移集群。后面會(huì)一一介紹介紹各自的優(yōu)缺點(diǎn)。

（一）復(fù)制模式

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

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

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

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

　　1.快照發(fā)布

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

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

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

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

　　3.合并發(fā)布

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

（二）鏡像傳輸模式

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

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

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

（三）日志傳輸模式

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

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

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

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

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

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

1.1、服務(wù)器概念

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

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

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

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

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

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

1.2、模式概念

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

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

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

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

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

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

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

前置配置

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

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

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

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

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

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

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

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

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

（10）端點(diǎn)：鏡像兩端的加密算法必須保持一致，否則無法搭建。（因?yàn)椴煌姹灸J(rèn)加密算法不一樣）小版本沒關(guān)系，但前提是從庫(kù)可以還原主庫(kù)。

搭建過程

-- 1、創(chuàng)建主密鑰（主庫(kù)、鏡像庫(kù)、認(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)建證書
-- 主庫(kù)
use master
go
create certificate sqlsrv1_cert with subject='sqlsrv1_cert',expiry_date='2099-1-1'
go

-- 鏡像庫(kù)
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)建端點(diǎn)
-- 主庫(kù)
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

-- 鏡像庫(kù)
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ù)器都備份，并互相拷貝過去，保證每個(gè)服務(wù)器上都有3個(gè)證書）
-- 主庫(kù)
use master
go
backup certificate sqlsrv1_cert to file = 'C:\\cert\\sqlsrv1_cert.cer'
go

-- 從庫(kù)
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)建登錄名（這個(gè)要和證書關(guān)聯(lián)，所以主體服務(wù)器、鏡像服務(wù)器、見證服務(wù)器都要?jiǎng)?chuàng)建除自己以外的另外2個(gè)用戶）
-- 主庫(kù)上執(zhí)行：
-- 創(chuàng)建鏡像庫(kù)的證書關(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

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

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

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

-- 創(chuàng)建鏡像庫(kù)的證書關(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

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

-- 創(chuàng)建主庫(kù)的證書關(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)建主庫(kù)的證書關(guān)聯(lián)用戶
use master
go
create user sqlsrv1_user for login sqlsrv1_login
go

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

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

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

--使見證庫(kù)上的證書與用戶關(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

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

-- 使主庫(kù)上的證書與用戶關(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í)行

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

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

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

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

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

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

-- 主庫(kù)上執(zhí)行

-- 連接鏡像庫(kù)
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

完成后測(cè)試

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

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

新建表

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',' 計(jì)算機(jī)組成原理','103');
INSERT INTO course VALUES('1005',' 操作系統(tǒng)','104');