国产欧美日韩第一页|日本一二三不卡视频|在线精品小视频,亚洲第一免费播放区,metcn人体亚洲一区,亚洲精品午夜视频

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

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

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

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

主體服務(wù)器：192.168.164.100

鏡像服務(wù)器：192.168.164.101

見(jiàn)證服務(wù)器：192.168.164.102

前置概念

主從方案介紹

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

（一）復制模式

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

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

　　復制模式同步，要求數據庫名稱(chēng)和主機名稱(chēng)必須一致，否則查找不到數據庫主機；要求數據庫不能使用端口，必須是可以通過(guò)ip直接訪(fǎng)問(wèn)的；

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

　　1.快照發(fā)布

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

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

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

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

　　3.合并發(fā)布

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

（二）鏡像傳輸模式

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

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

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

（三）日志傳輸模式

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

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

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

（四）故障轉移集群

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

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

1.1、服務(wù)器概念

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

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

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

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

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

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

1.2、模式概念

數據庫鏡像會(huì )話(huà)以同步操作或異步操作運行。

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

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

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

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

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

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

前置配置

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

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

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

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

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

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

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

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

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

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

搭建過(guò)程

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

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

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


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

-- 見(jiàn)證服務(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、備份證書(shū)（主體服務(wù)器、鏡像服務(wù)器、見(jiàn)證服務(wù)器都備份，并互相拷貝過(guò)去，保證每個(gè)服務(wù)器上都有3個(gè)證書(shū)）
-- 主庫
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

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

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

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

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

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

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

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

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

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

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

-- 鏡像庫上執行

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

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

-- 見(jiàn)證服務(wù)器上執行

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

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

-- 7、證書(shū)與用戶(hù)關(guān)聯(lián)
-- 主庫上執行

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

--使見(jiàn)證庫上的證書(shū)與用戶(hù)關(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

-- 鏡像庫上執行

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

--使見(jiàn)證服務(wù)器上的證書(shū)與用戶(hù)關(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

-- 見(jiàn)證服務(wù)器上執行

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

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

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

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

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

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

完成后測試

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

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

新建表

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