一、概念簡介

1.1、基本概念

1）讀寫分離概念：是把對數(shù)據(jù)庫的讀操作和寫操作分離開。在一定程度上，讀寫分離可以緩解讀寫操作并發(fā)時(shí)產(chǎn)生鎖的問題。

2）讀寫分離原理：是讓主數(shù)據(jù)庫處理事務(wù)性增、刪、改操作（INSERT、DELETE、UPDATE），而從數(shù)據(jù)庫處理查詢操作（SELECT）。

1.2、技術(shù)簡介

 SQL Server提供了三種技術(shù)來實(shí)現(xiàn)讀寫分離，分別是：日志傳送、事務(wù)復(fù)制、Always On。以下是三種技術(shù)的比較：

事務(wù)復(fù)制沒有Always On的要求那么高，只需要主從服務(wù)器能通過TCP進(jìn)行通訊即可，主從服務(wù)器操作系統(tǒng)和SQL Server版本可以不完全一致（生產(chǎn)環(huán)境建議一致），同時(shí)，主從服務(wù)器也不需要加入域。

注：本文主從同步實(shí)現(xiàn)方式采用事務(wù)復(fù)制方式。

二、實(shí)戰(zhàn)準(zhǔn)備

2.1、網(wǎng)絡(luò)環(huán)境

1）主從服務(wù)器最好在同一個(gè)局域網(wǎng)內(nèi)，而且要互相ping得通，可以是不同網(wǎng)段。

2）以下是本文的測試環(huán)境：

2.2、數(shù)據(jù)庫主機(jī)名

1）SQL Server數(shù)據(jù)庫實(shí)例主機(jī)名需與本地服務(wù)器名稱一致：

--本地服務(wù)器名稱 SELECT @@SERVERNAME --數(shù)據(jù)庫實(shí)例主機(jī)名 SELECT SERVERPROPERTY(’ServerName’)

2）若出現(xiàn)SQL Server數(shù)據(jù)庫實(shí)例主機(jī)名與本地服務(wù)器名稱不一致的情況，可通過以下語句來更改：

IF (SERVERPROPERTY(’SERVERNAME’)<>@@SERVERNAME) BEGIN DECLARE @SERVER SYSNAME SET @SERVER=@@SERVERNAME EXEC SP_DROPSERVER @SERVER=@SERVER SET @SERVER=CAST(SERVERPROPERTY(’SERVERNAME’) AS SYSNAME) EXEC SP_ADDSERVER @SERVER=@SERVER,@LOCAL=’LOCAL’ END

更改完成后請重啟SQL Server服務(wù)：

2.3、同步賬號

主從服務(wù)器都需要建立一個(gè)賬號及密碼都相同的本地管理員用戶如sync：

2.4、SQL Server 代理

主從服務(wù)器都需要啟動(dòng)SQL Server代理，另外登錄賬號都設(shè)為同步賬號如sync：

2.5、同步說明

1）以數(shù)據(jù)庫AdventureWorks為例。

2）在AdventureWorks上執(zhí)行以下SQL語句，否則后續(xù)會(huì)出現(xiàn)【進(jìn)程無法在“IT01”上執(zhí)行“sp_replcmds”】報(bào)錯(cuò)。 

sys.sp_changedbowner ’sa’

3）主從搭建，實(shí)際是發(fā)布->分發(fā)->訂閱的過程。本文發(fā)布與分發(fā)使用的是同一臺(tái)服務(wù)器IT01。

2.6、同步規(guī)則

1）新增的表一定要有主鍵，否則不能進(jìn)行同步。

2）從庫上一定不能有任何的數(shù)據(jù)修改，這個(gè)原則一定要遵守。

三、實(shí)戰(zhàn)操作

3.1、分發(fā)配置

1）在IT01主服務(wù)器上，對著SQL Server的"復(fù)制"右鍵->點(diǎn)擊"配置分發(fā)"。

2）點(diǎn)擊"下一步"。

3）默認(rèn)選擇，點(diǎn)擊"下一步"。

4）快照文件夾應(yīng)使用網(wǎng)絡(luò)路徑，因此要先設(shè)置文件夾共享。

5）打開"D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL"->對著"repldata"文件夾"右鍵"->點(diǎn)擊"屬性"。

6）選擇"共享"頁簽->點(diǎn)擊"共享"。

7）添加"Everyone"用戶。

8）授予"Everyone"用戶"讀取/寫入"權(quán)限->點(diǎn)擊"共享"。

9）共享成功后，在快照文件夾中輸入網(wǎng)絡(luò)路徑"\\IT01\ReplData"，點(diǎn)擊"下一步"。

10）默認(rèn)選擇，點(diǎn)擊"下一步"。

11）默認(rèn)選擇，點(diǎn)擊"下一步"。

12）默認(rèn)選擇，點(diǎn)擊"下一步"。

13）點(diǎn)擊"完成"。

14）完成后，點(diǎn)擊"關(guān)閉"即可。

3.2、發(fā)布配置

1）在IT01主服務(wù)器上，點(diǎn)擊SQL Server的"復(fù)制"->對著"本地發(fā)布"右鍵->點(diǎn)擊"新建發(fā)布"。

2）點(diǎn)擊"下一步"。

3）選擇要發(fā)布的數(shù)據(jù)庫如"AdventureWorks"->點(diǎn)擊"下一步"。

4）選擇"事務(wù)發(fā)布"->點(diǎn)擊"下一步"。

5）選擇要發(fā)布的對象如"表"（也可以選擇某個(gè)具體表）->點(diǎn)擊"下一步"。 

6）默認(rèn)選擇，點(diǎn)擊"下一步"。

7）勾選"立即創(chuàng)建快照并使快照保持可用狀態(tài)，以初始化訂閱"->點(diǎn)擊"下一步"。

8）點(diǎn)擊"安全設(shè)置"。

9）由于本測試環(huán)境為非域環(huán)境，因此只能選擇"在 SQL Server 代理服務(wù)賬號下運(yùn)行"。同時(shí)，錄入SQL Server登錄名及密碼，點(diǎn)擊"確定"。

10）點(diǎn)擊"下一步"。

11）默認(rèn)選擇，點(diǎn)擊"下一步"。

12）起個(gè)發(fā)布名稱，點(diǎn)擊"完成"。

13）執(zhí)行成功后，點(diǎn)擊"關(guān)閉"即可。

14）對著發(fā)布名稱"右鍵"->點(diǎn)擊"屬性"。

15）點(diǎn)擊"快照"->取消勾選"將文件放入默認(rèn)文件夾"，勾選"將文件放入下列文件夾"，并錄入網(wǎng)絡(luò)地址"\\IT01\ReplData"->點(diǎn)擊"確定"。

3.3、訂閱配置

1）在HW01從服務(wù)器上打開運(yùn)行->輸入"\\IT01"。

2）確保能正常訪問主服務(wù)上的共享文件夾"repldata"。

3）對著數(shù)據(jù)庫"右鍵"->選擇"新建數(shù)據(jù)庫"。

4）輸入數(shù)據(jù)庫名如"AdventureWorks"->點(diǎn)擊"確定"。

5）打開"復(fù)制"，對著本地訂閱"右鍵"->點(diǎn)擊"新建訂閱"。

6）默認(rèn)選擇，點(diǎn)擊"下一步"。

7）在下拉框中選擇"查找 SQL Server 發(fā)布服務(wù)器..."。

8）輸入主服務(wù)器名稱及身份驗(yàn)證，同時(shí)勾選"記住密碼"，最后點(diǎn)擊"連接"。

9）默認(rèn)選擇，點(diǎn)擊"下一步"。

10）選擇"在其訂閱服務(wù)器上運(yùn)行每個(gè)代理(請求訂閱)"->點(diǎn)擊"下一步"。

11）選擇訂閱數(shù)據(jù)庫"AdventureWorks"->點(diǎn)擊"下一步"。

12）點(diǎn)擊"..." 。

13）選擇"在 SQL Server 代理服務(wù)賬戶下運(yùn)行"->輸入連接到分發(fā)服務(wù)器的登錄賬號及密碼->點(diǎn)擊"確定"。

14）點(diǎn)擊"下一步"。

15）選擇"連續(xù)運(yùn)行"->點(diǎn)擊"下一步"。

16）初始化時(shí)間選擇"立即"->點(diǎn)擊"下一步"。

17)默認(rèn)選擇，點(diǎn)擊"下一步"。

18)點(diǎn)擊完成。

19）創(chuàng)建成功后，點(diǎn)擊"關(guān)閉"。

20）對著訂閱名稱"右鍵"->點(diǎn)擊"屬性"。

 22）在快照項(xiàng)中，快照位置選擇"備份文件夾"->快照文件夾輸入"\\IT01\repldata"->點(diǎn)擊"確定"。

四、異常檢查

1）對著發(fā)布名稱"右鍵"->點(diǎn)擊"啟動(dòng)復(fù)制監(jiān)視器"。

2）記錄行"右鍵"，可以"停止代理"再"啟動(dòng)代理"，這樣就可以發(fā)現(xiàn)執(zhí)行過程中的報(bào)錯(cuò)。也可以點(diǎn)擊"查看詳細(xì)信息"，查看執(zhí)行的過程日志等。

五、新增項(xiàng)目內(nèi)容

1）假如有新的表或其它新的項(xiàng)目內(nèi)容需要同步，可以對著發(fā)布名稱"右鍵"->點(diǎn)擊"屬性"。

2）選擇"項(xiàng)目"->勾選新增的表等項(xiàng)目內(nèi)容->點(diǎn)擊"確定"。

3）對著發(fā)布名稱"右鍵"->點(diǎn)擊"查看快照代理狀態(tài)"。

4）點(diǎn)擊"啟動(dòng)"。

六、刪除發(fā)布服務(wù)器上的主從復(fù)制

1）先刪除發(fā)布服務(wù)器上的訂閱和發(fā)布。

2）執(zhí)行以下命令，刪除distribution分發(fā)數(shù)據(jù)庫。

USE master GO EXEC sp_dropdistributiondb @database=N’distribution’ GO EXEC sp_dropdistributor @no_checks=1,@ignore_distributor=1 GO