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

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

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

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

主體服務器：192.168.164.100

鏡像服務器：192.168.164.101

見證服務器：192.168.164.102

前置概念

主從方案介紹

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

（一）復制模式

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

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

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

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

　　1.快照發(fā)布

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

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

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

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

　　3.合并發(fā)布

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

（二）鏡像傳輸模式

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

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

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

（三）日志傳輸模式

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

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

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

（四）故障轉移集群

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

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

1.1、服務器概念

◆ 主體服務器（Principal Server）

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

◆ 鏡像服務器（Mirror Server）

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

◆ 見證服務器（Witness Server）

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

1.2、模式概念

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

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

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

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

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

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

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

前置配置

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

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

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

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

（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ù)庫。主庫必須是完整恢復模式。且主從庫必須是相同的數(shù)據(jù)庫名。

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

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

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

搭建過程

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

-- 2、分別在主體服務器、鏡像服務器、見證服務器上創(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

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


-- 3、分別在主體服務器、鏡像服務器、見證服務器上創(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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- 見證服務器上執(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ù)庫做完整備份和事務日志備份，然后在鏡像庫上做還原（先還原完整備份，再還原事務日志），使用NORECOVERY模式

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

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
-- 連接見證服務器
use master
go
ALTER DATABASE testdb SET WITNESS = 'TCP://192.168.164.102:5022';
go

完成后測試

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

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

新建表

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');