一、概念簡介
1.1、基本概念
1）讀寫分離概念：是把對資料庫的讀操作和寫操作分離開，在一定程度上，讀寫分離可以緩解讀寫操作并發時產生鎖的問題，
2）讀寫分離原理：是讓主資料庫處理事務性增、刪、改操作（INSERT、DELETE、UPDATE），而從資料庫處理查詢操作（SELECT），
1.2、技術簡介
SQL Server提供了三種技術來實作讀寫分離，分別是：日志傳送、事務復制、Always On，以下是三種技術的比較：
| 日志傳送 | 事務復制 | Always On | |
| 原理 |
通過SQL Server Agent調度作業進行日志 的備份、復制、還原實作同步 |
由復制代理同步發布資料上的 增刪改操作到訂閱服務器 |
主資料庫的事務日志記錄發送并 運用到每個輔助資料庫 |
| 版本功能支持 |
SQL Server 2000 企業版 SQL Server 2005 及以后標準版、企業版 |
標準版、企業版 | SQL Server 2012 企業版 |
| 作業系統要求 | 無限制 | 無限制 |
Windows 企業版 故障轉移群集 |
| 限制 | 要求資料庫必須是完整恢復模式 | 要求表必須有主鍵 | 要求資料庫必須是完整恢復模式 |
| 同步粒度 | 資料庫級 | 表級 | 資料庫級 |
| 資料差異 | 取決于備份、復制、還原的作業設定 | 幾秒 | 幾秒 |
| 副本數量 | 無限制 | 無限制 | 4個 |
| 副本讀取 | 間歇性，在還原時會中斷查詢， | 正常 | 正常 |
| 自動故障轉移 | 不支持 | 不支持 | 支持 |
事務復制沒有Always On的要求那么高，只需要主從服務器能通過TCP進行通訊即可，主從服務器作業系統和SQL Server版本可以不完全一致（生產環境建議一致），同時，主從服務器也不需要加入域，
注：本文主從同步實作方式采用事務復制方式，
二、實戰準備
2.1、網路環境
1）主從服務器需在同一個局域網內，而且要互相ping得通，可以是不同網段，
2）以下是本文的測驗環境：
| 計算機名 | IP地址 | 作業系統 | 資料庫 | |
| 主服務器 | IT01 | 192.168.2.174 | Windows 10 | SQL Server 2016 |
| 從服務器 | HW01 | 192.168.2.242 | Windows Server 2012 | SQL Server 2016 |
2.2、資料庫主機名
1）SQL Server資料庫實體主機名需與本地服務器名稱一致：
--本地服務器名稱 SELECT @@SERVERNAME --資料庫實體主機名 SELECT SERVERPROPERTY('ServerName')
2）若出現SQL Server資料庫實體主機名與本地服務器名稱不一致的情況，可通過以下陳述句來更改：
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服務：
2.3、同步賬號
主從服務器都需要建立一個賬號及密碼都相同的本地管理員用戶如sync：
2.4、SQL Server 代理
主從服務器都需要啟動SQL Server代理，另外登錄賬號都設為同步賬號如sync：
2.5、同步說明
1）以資料庫AdventureWorks為例，
2）在AdventureWorks上執行以下SQL陳述句，否則后續會出現【行程無法在“IT01”上執行“sp_replcmds”】報錯，
sys.sp_changedbowner 'sa'
3）主從搭建，實際是分發->分布->訂閱的程序，本文分發與發布使用的是同一臺服務器IT01，
2.6、同步規則
1）新增的表一定要有主鍵，否則不能進行同步，
2）從庫上一定不能有任何的資料修改，這個原則一定要遵守，
三、實戰操作
3.1、分發配置
1）在IT01主服務器上，對著SQL Server的"復制"右鍵->點擊"配置分發"，
2）點擊"下一步"，
3）默認選擇，點擊"下一步"，
4）快照檔案夾應使用網路路徑，因此要先設定檔案夾共享，
5）打開"D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL"->對著"repldata"檔案夾"右鍵"->點擊"屬性"，
6）選擇"共享"頁簽->點擊"共享"，
7）添加"Everyone"用戶，
8）授予"Everyone"用戶"讀取/寫入"權限->點擊"共享"，
9）共享成功后，在快照檔案夾中輸入網路路徑"\\IT01\ReplData"，點擊"下一步"，
10）默認選擇，點擊"下一步"，
11）默認選擇，點擊"下一步"，
12）默認選擇，點擊"下一步"，
13）點擊"完成"，
14）完成后，點擊"關閉"即可，
3.2、發布配置
1）在IT01主服務器上，點擊SQL Server的"復制"->對著"本地發布"右鍵->點擊"新建發布"，
2）點擊"下一步"，
3）選擇要發布的資料庫如"AdventureWorks"->點擊"下一步"，
4）選擇"事務發布"->點擊"下一步"，
5）選擇要發布的物件如"表"（也可以選擇某個具體表）->點擊"下一步"，
6）默認選擇，點擊"下一步"，
7）勾選"立即創建快照并使快照保持可用狀態，以初始化訂閱"->點擊"下一步"，
8）點擊"安全設定"，
9）由于本測驗環境為非域環境，因此只能選擇"在 SQL Server 代理服務賬號下運行"，同時，錄入SQL Server登錄名及密碼，點擊"確定"，
10）點擊"下一步"，
11）默認選擇，點擊"下一步"，
12）起個發布名稱，點擊"完成"，
13）執行成功后，點擊"關閉"即可，
14）對著發布名稱"右鍵"->點擊"屬性"，
15）點擊"快照"->取消勾選"將檔案放入默認檔案夾"，勾選"將檔案放入下列檔案夾"，并錄入網路地址"\\IT01\ReplData"->點擊"確定"，
3.3、訂閱配置
1）在HW01從服務器上打開運行->輸入"\\IT01"，
2）確保能正常訪問主服務上的共享檔案夾"repldata"，
3）對著資料庫"右鍵"->選擇"新建資料庫"，
4）輸入資料庫名如"AdventureWorks"->點擊"確定"，
5）打開"復制"，對著本地訂閱"右鍵"->點擊"新建訂閱"，
6）默認選擇，點擊"下一步"，
7）在下拉框中選擇"查找 SQL Server 發布服務器..."，
8）輸入主服務器名稱及身份驗證，同時勾選"記住密碼"，最后點擊"連接"，
9）默認選擇，點擊"下一步"，
10）選擇"在其訂閱服務器上運行每個代理(請求訂閱)"->點擊"下一步"，
11）選擇訂閱資料庫"AdventureWorks"->點擊"下一步"，
12）點擊"..." ，
13）選擇"在 SQL Server 代理服務賬戶下運行"->輸入連接到分發服務器的登錄賬號及密碼->點擊"確定"，
14）點擊"下一步"，
15）選擇"連續運行"->點擊"下一步"，
16）初始化時間選擇"立即"->點擊"下一步"，
17)默認選擇，點擊"下一步"，
18)點擊完成，
19）創建成功后，點擊"關閉"，
20）對著訂閱名稱"右鍵"->點擊"屬性"，
22）在快照項中，快照位置選擇"備份檔案夾"->快照檔案夾輸入"\\IT01\repldata"->點擊"確定"，
四、例外檢查
1）對著發布名稱"右鍵"->點擊"啟動復制監視器"，
2）記錄行"右鍵"，可以"停止代理"再"啟動代理"，這樣就可以發現執行程序中的報錯，也可以點擊"查看詳細資訊"，查看執行的程序日志等，
五、新增專案內容
1）假如有新的表或其它新的專案內容需要同步，可以對著發布名稱"右鍵"->點擊"屬性"，
2）選擇"專案"->勾選新增的表等專案內容->點擊"確定"，
3）對著發布名稱"右鍵"->點擊"查看快照代理狀態"，
4）點擊"啟動"，
六、洗掉發布服務器上的主從復制
1）先洗掉發布服務器上的訂閱和發布，
2）執行以下命令，洗掉distribution分發資料庫，
USE master GO EXEC sp_dropdistributiondb @database=N'distribution' GO EXEC sp_dropdistributor @no_checks=1,@ignore_distributor=1 GO
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/325403.html
標籤：其他
下一篇：Sqoop