共計(jì) 5711 個(gè)字符，預(yù)計(jì)需要花費(fèi) 15 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 SQL Server 日志傳送如何配置，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
Date:2016-04-15
Author:Netdata
一. 安裝環(huán)境:
1. 系統(tǒng)環(huán)境
OS:Windows Server 2012 DataCenter
DB:SQL Server 2012 R2 SP3
主:
IP: 172.25.10.186
主機(jī)名: DBCW01-10-186
數(shù)據(jù)庫(kù)名:test
備:
IP:172.25.10.188
主機(jī)名: DBCW02-10-188
數(shù)據(jù)庫(kù)名:test
2. 日志傳送配置簡(jiǎn)介
下圖顯示了具有主服務(wù)器實(shí)例、三個(gè)輔助服務(wù)器實(shí)例和一個(gè)監(jiān)視服務(wù)器實(shí)例的日志傳送配置。此圖闡釋了備份作業(yè)、復(fù)制作業(yè)以及還原作業(yè)所執(zhí)行步驟，如下所示：
1. 主服務(wù)器實(shí)例執(zhí)行備份作業(yè)以在主數(shù)據(jù)庫(kù)上備份事務(wù)日志。然后，該服務(wù)器實(shí)例將日志備份放入主日志備份文件（此文件將被發(fā)送到備份文件夾中）。在此圖中，備份文件夾位于共享目錄（“備份共享”）下。
2. 全部三個(gè)輔助服務(wù)器實(shí)例都執(zhí)行其各自的復(fù)制作業(yè)，以將主日志備份文件復(fù)制到它本地的目標(biāo)文件夾中。
3. 每個(gè)輔助服務(wù)器實(shí)例都執(zhí)行其還原作業(yè)，以將日志備份從本地目標(biāo)文件夾還原到本地輔助數(shù)據(jù)庫(kù)中。
主服務(wù)器實(shí)例和輔助服務(wù)器實(shí)例將它們自己的歷史記錄和狀態(tài)發(fā)送到監(jiān)視服務(wù)器實(shí)例。
二. 安裝數(shù)據(jù)庫(kù)
1. 分別在兩臺(tái)數(shù)據(jù)庫(kù)服務(wù)器上安裝 SQL Server 2012 R2
2. 分別在兩臺(tái)數(shù)據(jù)庫(kù)服務(wù)器上打上 SP3 補(bǔ)丁
三. 配置日志傳送
1. 主庫(kù)上創(chuàng)建應(yīng)用數(shù)據(jù)庫(kù)
創(chuàng)建示例表
–create table
create table test_log
(id int identity(1,1),name varchar(50),dates datetime default getdate());
–general data
declare @i int
set @i=1
while @i 100001
begin
insert into test_log(name)
values(newid())
set @i=@i+1
end ;
2. 創(chuàng)建用戶(hù), 并授權(quán),
注意密碼策略, 可不選, 默認(rèn)數(shù)據(jù)庫(kù)選擇 test
用戶(hù)映射
3. 配置日志傳送
在主備各建一個(gè)帳戶(hù)用于啟動(dòng) sql server 及 agent 帳戶(hù) (sql_cw)
配置共享目錄用于存儲(chǔ), 包含全備及日志備份,
這里在備庫(kù)里面共享一個(gè)目錄 D:\slave_recovery, 并給予 sql_cw 讀寫(xiě)權(quán)限
主庫(kù)上用 UNC 訪(fǎng)問(wèn)共享測(cè)試正常
主庫(kù)上操作
設(shè)置數(shù)據(jù)庫(kù)恢復(fù)模式
數(shù)據(jù)庫(kù)恢復(fù)模式必須為完整恢復(fù)模式
配置傳送事務(wù)日志
注: 默認(rèn)事務(wù)日志備份是每 15 分鐘一次
主庫(kù)備份
BACKUP DATABASE test TO DISK = N \\172.25.10.188\slave_recovery\test.bak WITH NOFORMAT, INIT,
NAME = N test-full Database Backup , SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
備庫(kù)上恢復(fù)
RESTORE DATABASE test
FROM DISK = N d:\ slave_recovery \test.bak WITH FILE = 1,
STANDBY = N d:\Standby\ROLLBACK_UNDO_TEST.BAK , NOUNLOAD, STATS = 10
GO
不過(guò)做了此操作后在選擇的時(shí)候, 選擇備庫(kù)已經(jīng)初始化
4. 配置完成后測(cè)試
在主庫(kù)插入數(shù)據(jù)
declare @i int
set @i=1
while @i 100001
begin
insert into test_log(name)
values(newid())
set @i=@i+1
end ;
輔庫(kù)查看
5. 日志傳送監(jiān)控
日志傳送主要是以作業(yè)形式
配置 SQL 郵件 (主備都需要操作)
監(jiān)視歷史記錄表包含監(jiān)視服務(wù)器上存儲(chǔ)的元數(shù)據(jù)。與給定的主服務(wù)器或輔助服務(wù)器相關(guān)的信息副本也存儲(chǔ)在本地。
可以查詢(xún)這些表，以監(jiān)視日志傳送會(huì)話(huà)的狀態(tài)。例如，了解日志傳送的狀態(tài)，查看備份作業(yè)、復(fù)制作業(yè)和還原作業(yè)的狀態(tài)和歷史記錄。通過(guò)查詢(xún)下列監(jiān)視表，可以查看特定的日志傳送歷史記錄和錯(cuò)誤詳細(xì)信息。
表
說(shuō)明
log_shipping_monitor_alert
存儲(chǔ)警報(bào)作業(yè) ID。
log_shipping_monitor_error_detail
存儲(chǔ)日志傳送作業(yè)的錯(cuò)誤詳細(xì)信息?？梢圆樵?xún)此表來(lái)查看某個(gè)代理會(huì)話(huà)的錯(cuò)誤。還可以按每個(gè)錯(cuò)誤的記錄日期和時(shí)間對(duì)錯(cuò)誤進(jìn)行排序。每個(gè)錯(cuò)誤都記錄為一個(gè)異常序列，多個(gè)錯(cuò)誤（序列）可以形成一個(gè)代理會(huì)話(huà)。
log_shipping_monitor_history_detail
存儲(chǔ)日志傳送代理的歷史記錄詳細(xì)信息?？梢圆樵?xún)此表來(lái)查看某個(gè)代理會(huì)話(huà)的歷史記錄詳細(xì)信息。
log_shipping_monitor_primary
在每個(gè)日志傳送配置中對(duì)主數(shù)據(jù)庫(kù)存儲(chǔ)一條監(jiān)視記錄，包括有關(guān)對(duì)監(jiān)視有用的最新備份文件和最新還原文件的信息。
log_shipping_monitor_secondary
對(duì)每個(gè)輔助數(shù)據(jù)庫(kù)存儲(chǔ)一條監(jiān)視記錄，包括有關(guān)對(duì)監(jiān)視有用的最新備份文件和最新還原文件的信息。
監(jiān)視日志傳送的存儲(chǔ)過(guò)程
監(jiān)視和歷史記錄信息存儲(chǔ)在 msdb 的表中，可以通過(guò)日志傳送存儲(chǔ)過(guò)程來(lái)訪(fǎng)問(wèn)它。請(qǐng)?jiān)谙卤碇兄付ǖ姆?wù)器上運(yùn)行下列存儲(chǔ)過(guò)程。
存儲(chǔ)過(guò)程
說(shuō)明
運(yùn)行存儲(chǔ)過(guò)程的服務(wù)器
sp_help_log_shipping_monitor_primary
從 log_shipping_monitor_primary 表中返回指定的主數(shù)據(jù)庫(kù)的監(jiān)視記錄。
監(jiān)視服務(wù)器或主服務(wù)器
sp_help_log_shipping_monitor_secondary
從 log_shipping_monitor_secondary 表中返回指定的輔助數(shù)據(jù)庫(kù)的監(jiān)視記錄。
監(jiān)視服務(wù)器或輔助服務(wù)器
sp_help_log_shipping_alert_job
返回警報(bào)作業(yè)的作業(yè) ID。
監(jiān)視服務(wù)器或主 / 輔助服務(wù)器（如果未定義監(jiān)視服務(wù)器）
sp_help_log_shipping_primary_database
檢索主數(shù)據(jù)庫(kù)設(shè)置并顯示 log_shipping_primary_databases 和 log_shipping_monitor_primary 表中的值。
主服務(wù)器
sp_help_log_shipping_primary_secondary
檢索主數(shù)據(jù)庫(kù)的輔助數(shù)據(jù)庫(kù)名稱(chēng)。
主服務(wù)器
sp_help_log_shipping_secondary_database
從 log_shipping_secondary、log_shipping_secondary_databases 和 log_shipping_monitor_secondary 表中檢索輔助數(shù)據(jù)庫(kù)設(shè)置。
輔助服務(wù)器
sp_help_log_shipping_secondary_primary (Transact-SQL)
此存儲(chǔ)過(guò)程將在輔助服務(wù)器上檢索給定的主數(shù)據(jù)庫(kù)的設(shè)置。
輔助服務(wù)器
表 t_log_status 腳本如下
create table t_log_status
(status int,
is_primary int,
server varchar(50),
data_name varchar(50),
time_since_last_backup datetime,
last_backup_file varchar(50),
backup_threshold int,
is_backup_alert_enabled int,
time_since_last_copy int,
last_copied_file varchar(500),
time_since_last_restore int,
last_restored_file varchar(500),
last_restored_latency int,
restore_threshold int,
is_restore_alert_enabled int)
監(jiān)控作業(yè)腳本
delete from t_log_status;
insert t_log_status exec sp_help_log_shipping_monitor;
DECLARE @tableHTML NVARCHAR(MAX) ;
declare @str_subject nvarchar(max);
declare @i_result nvarchar(max);
— 獲取當(dāng)前系統(tǒng)時(shí)間, 和數(shù)據(jù)統(tǒng)計(jì)的時(shí)間
— 如果有數(shù)據(jù)則發(fā)送
if exists (select top 1 * from t_log_status)
begin
set @str_subject= 日志傳輸狀態(tài) +convert(varchar(10),getdate(),120);
SET @tableHTML = N
輔庫(kù)狀態(tài)
+
N
+
CAST ((select status as td , ,is_primary as td , ,server as td , ,data_name as td , ,time_since_last_copy as td , ,last_copied_file as td , ,last_restored_file as td
from t_log_status t
FOR XML PATH(tr), ELEMENTS– TYPE
) AS NVARCHAR(MAX) ) + N
狀態(tài) (0 運(yùn)行正常, 無(wú)代理失敗)
是否是主庫(kù) (1 主數(shù)據(jù)庫(kù),0 輔助數(shù)據(jù)庫(kù))
服務(wù)器名稱(chēng)
數(shù)據(jù)庫(kù)
上次復(fù)制日志備份
上次復(fù)制日志文件名
上次恢復(fù)日志文件名
— 發(fā)送郵件
exec @i_result = msdb.dbo.sp_send_dbmail
@profile_name = sqlmail ,
@recipients = huangxianglong@eetop.com ,
@subject = @str_subject,
@body = @tableHTML,
@body_format = HTML
End
四. 故障轉(zhuǎn)移
1. 將所有未復(fù)制的備份文件從備份共享復(fù)制到每臺(tái)輔助服務(wù)器的復(fù)制目標(biāo)文件夾中。
2. 將所有未應(yīng)用的事務(wù)日志備份按順序應(yīng)用到每個(gè)輔助數(shù)據(jù)庫(kù)中。
將所有未應(yīng)用的事務(wù)日志備份按順序應(yīng)用到每個(gè)輔助數(shù)據(jù)庫(kù)中。有關(guān)詳細(xì)信息，請(qǐng)參閱應(yīng)用事務(wù)日志備份 (SQL Server)。
如果可以訪(fǎng)問(wèn)主數(shù)據(jù)庫(kù)，則請(qǐng)備份活動(dòng)的事務(wù)日志，并將日志備份應(yīng)用到輔助數(shù)據(jù)庫(kù)。如果原始主服務(wù)器實(shí)例沒(méi)有損壞，則請(qǐng)使用 WITH NORECOVERY 備份主數(shù)據(jù)庫(kù)的事務(wù)日志尾部。這將使數(shù)據(jù)庫(kù)處于還原狀態(tài)，因此用戶(hù)無(wú)法使用。最終，您將能夠通過(guò)應(yīng)用替換主數(shù)據(jù)庫(kù)中的事務(wù)日志備份前滾此數(shù)據(jù)庫(kù)。
同步輔助服務(wù)器之后，可以根據(jù)您的首選，通過(guò)恢復(fù)任一輔助數(shù)據(jù)庫(kù)并將客戶(hù)端重定向到該服務(wù)器實(shí)例來(lái)故障轉(zhuǎn)移該輔助服務(wù)器?；謴?fù)操作將使數(shù)據(jù)庫(kù)處于一致的狀態(tài)并使其聯(lián)機(jī)。
注意做日志恢復(fù)的時(shí)候中間日志一定要是連續(xù)的
清理掉之前 job
Use master; go sp_delete_log_shipping_secondary_database test;
USE master; GO sp_delete_log_shipping_alert_job;
五. 主備交換角色
當(dāng)初次將故障轉(zhuǎn)移到輔助數(shù)據(jù)庫(kù)并將其用作新的主數(shù)據(jù)庫(kù)時(shí)，必須執(zhí)行一系列步驟。按照這些初始步驟操作后，就可以輕松地交換主數(shù)據(jù)庫(kù)和輔助數(shù)據(jù)庫(kù)的角色。
手動(dòng)從主數(shù)據(jù)庫(kù)故障轉(zhuǎn)移到輔助數(shù)據(jù)庫(kù)。請(qǐng)確保用 NORECOVERY 備份主服務(wù)器上的活動(dòng)事務(wù)日志。有關(guān)詳細(xì)信息，請(qǐng)參閱 故障轉(zhuǎn)移到日志傳送輔助服務(wù)器 (SQL Server)。
禁用原始主服務(wù)器上的日志傳送備份作業(yè)以及原始輔助服務(wù)器上的復(fù)制和還原作業(yè)。
使用 SQL Server Management Studio 在輔助數(shù)據(jù)庫(kù)（要用作新的主數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)）上配置日志傳送。有關(guān)詳細(xì)信息，請(qǐng)參閱 配置日志傳送 (SQL Server)。包括下列步驟：
使用同一個(gè)共享來(lái)創(chuàng)建為原來(lái)的主服務(wù)器所創(chuàng)建的備份。
添加輔助數(shù)據(jù)庫(kù)時(shí)，在“輔助數(shù)據(jù)庫(kù)設(shè)置”對(duì)話(huà)框的“輔助數(shù)據(jù)庫(kù)”框中輸入原來(lái)的主數(shù)據(jù)庫(kù)的名稱(chēng)。
在“輔助數(shù)據(jù)庫(kù)設(shè)置”對(duì)話(huà)框中，選中“否，輔助數(shù)據(jù)庫(kù)已初始化”。
如果對(duì)于您之前的日志傳送配置啟用了日志傳送監(jiān)視，則重新配置日志傳送監(jiān)視以便監(jiān)視新的日志傳送配置。執(zhí)行以下命令，將 database_name 你數(shù)據(jù)庫(kù)的名稱(chēng)：
在新的主服務(wù)器上
執(zhí)行以下 Transact-SQL 語(yǔ)句
-- Statement to execute on the new primary server USE msdb GO EXEC master.dbo.sp_change_log_shipping_secondary_database @secondary_database = N database_name , @threshold_alert_enabled = 0; GO在新的輔助服務(wù)器上
執(zhí)行以下 Transact-SQL 語(yǔ)句：
-- Statement to execute on the new secondary server USE msdb GO EXEC master.dbo.sp_change_log_shipping_primary_database @database=N database_name , @threshold_alert_enabled = 0; GO完成以上步驟執(zhí)行初始角色交換后，就可以按照本節(jié)的下列步驟交換主數(shù)據(jù)庫(kù)和輔助數(shù)據(jù)庫(kù)的角色。若要執(zhí)行角色交換，請(qǐng)執(zhí)行下列常規(guī)步驟：
1. 使輔助數(shù)據(jù)庫(kù)聯(lián)機(jī)，用 NORECOVERY 備份主服務(wù)器上的事務(wù)日志。
2. 禁用原始主服務(wù)器上的日志傳送備份作業(yè)以及原始輔助服務(wù)器上的復(fù)制和還原作業(yè)。
3. 在輔助服務(wù)器（新的主服務(wù)器）上啟用日志傳送備份作業(yè)，在主服務(wù)器（新的輔助服務(wù)器）上啟用復(fù)制和還原作業(yè)
以上是“SQL Server 日志傳送如何配置”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！
