SQLServer 2008R2主從部署實(shí)戰(zhàn)

說(shuō)明：

由于臨時(shí)接到做OA的主從環(huán)境，基于Windows 200R2 SQLServer2008 R2，由于搜索的資料都不完整，好多重要之處有遺漏，親自動(dòng)手做了好幾次，填完了坑，整理以備忘記與分享，如有錯(cuò)誤遺漏之處歡迎指正交流～

一、配置環(huán)境

主庫(kù): 10.8.11.214 數(shù)據(jù)庫(kù)版本：SQL Server2008 R2 服務(wù)器名稱(chēng)：WIN-D4GRPQKED93
從庫(kù): 10.8.11.79 數(shù)據(jù)庫(kù)版本: QL Server2008 R2 服務(wù)器名稱(chēng)：WIN-3ME2DJ8L9KT

注意點(diǎn)：
服務(wù)器名稱(chēng)(主機(jī)名)最好在確定好后再安裝SQL Server 2008R2數(shù)據(jù)庫(kù)這樣能確保主機(jī)名，實(shí)例名保持一致
如果是在安裝好sqlserver2008后修改了主機(jī)名，通常會(huì)導(dǎo)致主機(jī)名和實(shí)例名不一致問(wèn)題;
可通過(guò)下面的命令進(jìn)行檢測(cè)和修復(fù)

查看服務(wù)名稱(chēng)（默認(rèn)安裝時(shí)的實(shí)例名也是和主機(jī)名一致）和主機(jī)名是否一致
    select @@servername
    select serverproperty('servername')

如果不一致請(qǐng)進(jìn)行修改:
    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

以下是我的運(yùn)行結(jié)果：

二、搭建主從準(zhǔn)備

1、主從兩臺(tái)分別創(chuàng)建sqladmin用戶加入administrators組刪除默認(rèn)的users組，并且設(shè)置"密碼永不過(guò)期"和"用戶不能更改密碼" 為sqladmin創(chuàng)建一個(gè)密碼
2、主從兩臺(tái)設(shè)置網(wǎng)絡(luò)共享
打開(kāi)控制面板-->網(wǎng)絡(luò)和 Internet-->網(wǎng)絡(luò)和共享中心-->高級(jí)共享設(shè)置 如圖:

3、在主從庫(kù)上分別創(chuàng)建用于存放主從備份日志文件的共享文件夾DB_Backpup(sqladmin用戶能讀寫(xiě)administrators擁有者)
步聚如下:

右擊 ”DB_Bakcup“ -->共享-->特定用戶 -->添加sqladmin用戶讀寫(xiě)權(quán)限
如圖:

在"高級(jí)共享"窗口中，勾選"共享此文件夾"選項(xiàng)，然后單機(jī)"權(quán)限"按鈕對(duì)該共享文件夾的權(quán)限進(jìn)行設(shè)置。需要讓sqladmin用戶具有完全控制該文件夾的權(quán)限，先將默認(rèn)的“erverone”用戶刪除，然后添加sqladmin用戶，并在“sqladmin”的權(quán)限中勾選“完全控制”，“更改”和“讀取”項(xiàng)，然后單擊兩次“確定”按鈕保存共享設(shè)置。 如下圖:

在NTFS文件系統(tǒng)中，還需要設(shè)置用戶對(duì)該目錄的安全權(quán)限，如果安全權(quán)限不夠，系統(tǒng)在寫(xiě)入備份文件的時(shí)候會(huì)報(bào)沒(méi)有權(quán)限的錯(cuò)誤。
可以在“安全”選項(xiàng)卡，單機(jī)“編輯”按鈕，在“DB_Backup的權(quán)限”界面，單擊“按鈕”，添加sqladmin用戶，然后在“sqladmin的權(quán)限”中選擇“完全控制”權(quán)限，單機(jī)“確定”按鈕保存權(quán)限信息。
如下圖:

4、分別從主數(shù)據(jù)庫(kù)服務(wù)器上和從數(shù)據(jù)庫(kù)服務(wù)器上打開(kāi)"SQLServer配置管理器"，將SQLServer服務(wù)和SQLServer代理服務(wù)的“登錄身為”sqladmin用戶且啟動(dòng)模式為：自動(dòng)
如圖:

至此重啟主庫(kù)服務(wù)器！

三、配置SQLServer日志傳送

主庫(kù)重啟后以sqladmin用戶登錄系統(tǒng)并能sqladmin登錄數(shù)據(jù)庫(kù)
在主數(shù)據(jù)庫(kù)上配置
在主數(shù)據(jù)庫(kù)服務(wù)器上打開(kāi)SQL Server Management Studio管理控制臺(tái)，連接到本地的主數(shù)據(jù)庫(kù)服務(wù)器上，此時(shí)應(yīng)確保下面的“用戶名”中的用戶具有控制該SQL Server服務(wù)器的權(quán)限。單擊“連接”按鈕連接到本地SQL Server數(shù)據(jù)庫(kù)示例中。
如圖:

右擊數(shù)據(jù)庫(kù)服務(wù)器實(shí)例，選擇“屬性”選項(xiàng)，在彈出的“服務(wù)器屬性 - WIN-D4GRPQKED93”界面中，單機(jī)左側(cè)的“安全性”，然后在右側(cè)窗口中的“服務(wù)器身份驗(yàn)證”中選擇“SQLServer和Windows身份驗(yàn)證模式”，并勾選“服務(wù)器代理賬戶”中的“啟用服務(wù)器代理賬戶”選項(xiàng)。輸入正確的“代理賬號(hào)”和“密碼”，單擊“確定”按鈕保存配置信息。
如圖:

在主數(shù)據(jù)庫(kù)服務(wù)器WIN-D4GRPQKED93中配置要同步的數(shù)據(jù)庫(kù)UFSystem屬性
如圖:

設(shè)置完之后選擇“事務(wù)日志傳送”，勾選“將此數(shù)據(jù)庫(kù)啟用未日志傳送配置中的主數(shù)據(jù)庫(kù)”選項(xiàng)，單擊“事務(wù)日志備份”中的“備份設(shè)置按鈕”，打開(kāi)“事務(wù)日志備份設(shè)置”界面。

如圖:

主庫(kù)配置先暫停，到從庫(kù)上測(cè)試是否能訪問(wèn)主庫(kù)的網(wǎng)絡(luò)共享文件夾DB_Backup文件夾
備庫(kù)上進(jìn)行操作：
win+R，輸入網(wǎng)絡(luò)路徑看能否訪問(wèn)\10.8.11.214\DB_Backup
如圖:

右擊-UFSystem--屬性 --事務(wù)日志傳送--備份設(shè)置--在“作業(yè)計(jì)劃屬性”界面，確認(rèn)“計(jì)劃類(lèi)型”為重復(fù)執(zhí)行，為測(cè)試效果明顯，設(shè)置為15秒執(zhí)行一次作業(yè)計(jì)劃。最后確認(rèn)“持續(xù)時(shí)間”，根據(jù)自己4需要設(shè)置，如果一直備份的話，可以設(shè)置為“無(wú)結(jié)束日期”
如圖:


設(shè)置完成，保存之后再次打開(kāi)“事務(wù)日志備份設(shè)置”界面，則備份作業(yè)的作業(yè)名稱(chēng)后面變成“編輯作業(yè)”按鈕，單擊進(jìn)去，將“所有者”修改為sqladmin。
如圖:

單擊數(shù)據(jù)庫(kù)屬性界面的“輔助數(shù)據(jù)庫(kù)”中的“添加”按鈕，打開(kāi)“輔助數(shù)據(jù)庫(kù)設(shè)置”窗口。


如果瀏覽不到從庫(kù)，需要從庫(kù)啟動(dòng)SQL Server Browser服務(wù)

在第一次連接時(shí)可能無(wú)法連接到輔助數(shù)據(jù)庫(kù)，會(huì)報(bào)錯(cuò)：
a、用戶XXX登陸失敗
解決方法：
在從庫(kù)上使用sa登錄，master上新建查詢，輸入
create LOGIN [WIN-3ME2DJ8L9KT\sqladmin] FROM WINDOWS
b、用戶XXX不在sysadmin固定的服務(wù)器角色中
在從庫(kù)上執(zhí)行
EXEC sp_addsrvrolemember 'WIN-3ME2DJ8L9KT\sqladmin','sysadmin'
連接成功后對(duì)輔助數(shù)據(jù)庫(kù)進(jìn)行設(shè)置
如圖:


查看輔助數(shù)據(jù)庫(kù)的數(shù)據(jù)目錄
如圖:





設(shè)置完之后點(diǎn)擊確定按鈕，在數(shù)據(jù)庫(kù)屬性配置界面將配置好的腳本保存到本地，最后點(diǎn)擊確定如圖:


到此數(shù)據(jù)庫(kù)主從配置完成，可以在主庫(kù)進(jìn)行操作，在從庫(kù)上查看對(duì)應(yīng)的數(shù)據(jù)是否同步，如果沒(méi)有同步成功，可以在從庫(kù)上查看同步任務(wù)狀態(tài)，查看失敗原因。
以下是同步成功的截圖，同時(shí)從庫(kù)上已經(jīng)同步了主庫(kù)上的UFSystem數(shù)據(jù)庫(kù)
如圖:

四、驗(yàn)證主從同步
在主庫(kù)UFSystem中創(chuàng)建一張表

稍等15s以后到從庫(kù)上查看

此時(shí)可以發(fā)現(xiàn)已經(jīng)完成了主從同步

參考文檔：
https://yq.aliyun.com/articles/601067
https://www.cnblogs.com/tatsuya/p/5025583.html
https://www.cnblogs.com/brucewang/p/7825128.html