SQLServer日志傳送如何配置

小編給大家分享一下SQL Server日志傳送如何配置，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

成都創(chuàng)新互聯(lián)公司基于分布式IDC數(shù)據(jù)中心構(gòu)建的平臺為眾多戶提供德陽機(jī)房服務(wù)器托管 四川大帶寬租用 成都機(jī)柜租用 成都服務(wù)器租用。

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ù)庫名:test

備:

IP:172.25.10.188

主機(jī)名: DBCW02-10-188

數(shù)據(jù)庫名:test

2.日志傳送配置簡介

下圖顯示了具有主服務(wù)器實(shí)例、三個輔助服務(wù)器實(shí)例和一個監(jiān)視服務(wù)器實(shí)例的日志傳送配置。此圖闡釋了備份作業(yè)、復(fù)制作業(yè)以及還原作業(yè)所執(zhí)行步驟，如下所示：

1. 主服務(wù)器實(shí)例執(zhí)行備份作業(yè)以在主數(shù)據(jù)庫上備份事務(wù)日志。然后，該服務(wù)器實(shí)例將日志備份放入主日志備份文件（此文件將被發(fā)送到備份文件夾中）。在此圖中，備份文件夾位于共享目錄（“備份共享”）下。

2. 全部三個輔助服務(wù)器實(shí)例都執(zhí)行其各自的復(fù)制作業(yè)，以將主日志備份文件復(fù)制到它本地的目標(biāo)文件夾中。

3. 每個輔助服務(wù)器實(shí)例都執(zhí)行其還原作業(yè)，以將日志備份從本地目標(biāo)文件夾還原到本地輔助數(shù)據(jù)庫中。

主服務(wù)器實(shí)例和輔助服務(wù)器實(shí)例將它們自己的歷史記錄和狀態(tài)發(fā)送到監(jiān)視服務(wù)器實(shí)例。

二.安裝數(shù)據(jù)庫

1.分別在兩臺數(shù)據(jù)庫服務(wù)器上安裝SQL Server 2012 R2

2.分別在兩臺數(shù)據(jù)庫服務(wù)器上打上SP3補(bǔ)丁

三.配置日志傳送

1.主庫上創(chuàng)建應(yīng)用數(shù)據(jù)庫

創(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)建用戶,并授權(quán),

注意密碼策略,可不選,默認(rèn)數(shù)據(jù)庫選擇test

用戶映射

3.配置日志傳送

在主備各建一個帳戶用于啟動sql server及agent帳戶(sql_cw)

配置共享目錄用于存儲,包含全備及日志備份,

這里在備庫里面共享一個目錄D:\slave_recovery,并給予sql_cw讀寫權(quán)限

主庫上用UNC訪問共享測試正常

主庫上操作

設(shè)置數(shù)據(jù)庫恢復(fù)模式

數(shù)據(jù)庫恢復(fù)模式必須為完整恢復(fù)模式

配置傳送事務(wù)日志

注:默認(rèn)事務(wù)日志備份是每15分鐘一次

備庫狀態(tài)

注:以上操作也用腳本實(shí)現(xiàn)

主庫備份

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

備庫上恢復(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

不過做了此操作后在選擇的時候,選擇備庫已經(jīng)初始化

4.配置完成后測試

在主庫插入數(shù)據(jù)

declare @i int

set @i=1

while @i<100001

begin

insert into test_log(name)

values(newid())

set @i=@i+1

end ;

輔庫查看

5.日志傳送監(jiān)控

日志傳送主要是以作業(yè)形式

配置SQL郵件(主備都需要操作)

注意配置完要啟用一下,并重啟一下sql agent服務(wù)

新建操作員

配置作業(yè)監(jiān)控,主庫

備庫

關(guān)于日志傳送監(jiān)控視圖(摘自官方文檔)

監(jiān)視歷史記錄表包含監(jiān)視服務(wù)器上存儲的元數(shù)據(jù)。與給定的主服務(wù)器或輔助服務(wù)器相關(guān)的信息副本也存儲在本地。

可以查詢這些表，以監(jiān)視日志傳送會話的狀態(tài)。例如，了解日志傳送的狀態(tài)，查看備份作業(yè)、復(fù)制作業(yè)和還原作業(yè)的狀態(tài)和歷史記錄。通過查詢下列監(jiān)視表，可以查看特定的日志傳送歷史記錄和錯誤詳細(xì)信息。

表	說明
log_shipping_monitor_alert	存儲警報作業(yè) ID。
log_shipping_monitor_error_detail	存儲日志傳送作業(yè)的錯誤詳細(xì)信息。可以查詢此表來查看某個代理會話的錯誤。還可以按每個錯誤的記錄日期和時間對錯誤進(jìn)行排序。每個錯誤都記錄為一個異常序列，多個錯誤（序列）可以形成一個代理會話。
log_shipping_monitor_history_detail	存儲日志傳送代理的歷史記錄詳細(xì)信息?？梢圆樵兇吮韥聿榭茨硞€代理會話的歷史記錄詳細(xì)信息。
log_shipping_monitor_primary	在每個日志傳送配置中對主數(shù)據(jù)庫存儲一條監(jiān)視記錄，包括有關(guān)對監(jiān)視有用的最新備份文件和最新還原文件的信息。
log_shipping_monitor_secondary	對每個輔助數(shù)據(jù)庫存儲一條監(jiān)視記錄，包括有關(guān)對監(jiān)視有用的最新備份文件和最新還原文件的信息。

監(jiān)視日志傳送的存儲過程

監(jiān)視和歷史記錄信息存儲在 msdb的表中，可以通過日志傳送存儲過程來訪問它。請在下表中指定的服務(wù)器上運(yùn)行下列存儲過程。

存儲過程	說明	運(yùn)行存儲過程的服務(wù)器
sp_help_log_shipping_monitor_primary	從 log_shipping_monitor_primary表中返回指定的主數(shù)據(jù)庫的監(jiān)視記錄。	監(jiān)視服務(wù)器或主服務(wù)器
sp_help_log_shipping_monitor_secondary	從 log_shipping_monitor_secondary表中返回指定的輔助數(shù)據(jù)庫的監(jiān)視記錄。	監(jiān)視服務(wù)器或輔助服務(wù)器
sp_help_log_shipping_alert_job	返回警報作業(yè)的作業(yè) ID。	監(jiān)視服務(wù)器或主/輔助服務(wù)器（如果未定義監(jiān)視服務(wù)器）
sp_help_log_shipping_primary_database	檢索主數(shù)據(jù)庫設(shè)置并顯示 log_shipping_primary_databases和log_shipping_monitor_primary表中的值。	主服務(wù)器
sp_help_log_shipping_primary_secondary	檢索主數(shù)據(jù)庫的輔助數(shù)據(jù)庫名稱。	主服務(wù)器
sp_help_log_shipping_secondary_database	從 log_shipping_secondary、log_shipping_secondary_databases和log_shipping_monitor_secondary表中檢索輔助數(shù)據(jù)庫設(shè)置。	輔助服務(wù)器
sp_help_log_shipping_secondary_primary (Transact-SQL)	此存儲過程將在輔助服務(wù)器上檢索給定的主數(shù)據(jù)庫的設(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ù)據(jù)統(tǒng)計的時間

-- 如果有數(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'

輔庫狀態(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)行正常,無代理失敗)	是否是主庫(1主數(shù)據(jù)庫,0輔助數(shù)據(jù)庫)	服務(wù)器名稱	數(shù)據(jù)庫	上次復(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ù)制到每臺輔助服務(wù)器的復(fù)制目標(biāo)文件夾中。

2. 將所有未應(yīng)用的事務(wù)日志備份按順序應(yīng)用到每個輔助數(shù)據(jù)庫中。

1. 將所有未應(yīng)用的事務(wù)日志備份按順序應(yīng)用到每個輔助數(shù)據(jù)庫中。有關(guān)詳細(xì)信息，請參閱應(yīng)用事務(wù)日志備份 (SQL Server)。

2. 如果可以訪問主數(shù)據(jù)庫，則請備份活動的事務(wù)日志，并將日志備份應(yīng)用到輔助數(shù)據(jù)庫。如果原始主服務(wù)器實(shí)例沒有損壞，則請使用 WITH NORECOVERY 備份主數(shù)據(jù)庫的事務(wù)日志尾部。這將使數(shù)據(jù)庫處于還原狀態(tài)，因此用戶無法使用。最終，您將能夠通過應(yīng)用替換主數(shù)據(jù)庫中的事務(wù)日志備份前滾此數(shù)據(jù)庫。

3. 同步輔助服務(wù)器之后，可以根據(jù)您的首選，通過恢復(fù)任一輔助數(shù)據(jù)庫并將客戶端重定向到該服務(wù)器實(shí)例來故障轉(zhuǎn)移該輔助服務(wù)器?；謴?fù)操作將使數(shù)據(jù)庫處于一致的狀態(tài)并使其聯(lián)機(jī)。

注意做日志恢復(fù)的時候中間日志一定要是連續(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ù)庫并將其用作新的主數(shù)據(jù)庫時，必須執(zhí)行一系列步驟。 按照這些初始步驟操作后，就可以輕松地交換主數(shù)據(jù)庫和輔助數(shù)據(jù)庫的角色。

1. 手動從主數(shù)據(jù)庫故障轉(zhuǎn)移到輔助數(shù)據(jù)庫。 請確保用 NORECOVERY 備份主服務(wù)器上的活動事務(wù)日志。 有關(guān)詳細(xì)信息，請參閱 故障轉(zhuǎn)移到日志傳送輔助服務(wù)器 (SQL Server)。

2. 禁用原始主服務(wù)器上的日志傳送備份作業(yè)以及原始輔助服務(wù)器上的復(fù)制和還原作業(yè)。

3. 使用 SQL Server Management Studio 在輔助數(shù)據(jù)庫（要用作新的主數(shù)據(jù)庫的數(shù)據(jù)庫）上配置日志傳送。 有關(guān)詳細(xì)信息，請參閱 配置日志傳送 (SQL Server)。 包括下列步驟：

1. 使用同一個共享來創(chuàng)建為原來的主服務(wù)器所創(chuàng)建的備份。

2. 添加輔助數(shù)據(jù)庫時，在“輔助數(shù)據(jù)庫設(shè)置”對話框的“輔助數(shù)據(jù)庫”框中輸入原來的主數(shù)據(jù)庫的名稱。

3. 在“輔助數(shù)據(jù)庫設(shè)置”對話框中，選中“否，輔助數(shù)據(jù)庫已初始化”。

4. 如果對于您之前的日志傳送配置啟用了日志傳送監(jiān)視，則重新配置日志傳送監(jiān)視以便監(jiān)視新的日志傳送配置。 執(zhí)行以下命令，將database_name 你數(shù)據(jù)庫的名稱：

1. 在新的主服務(wù)器上

執(zhí)行以下 Transact-SQL 語句

-- 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 語句：

-- 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ù)庫和輔助數(shù)據(jù)庫的角色。 若要執(zhí)行角色交換，請執(zhí)行下列常規(guī)步驟：

1. 使輔助數(shù)據(jù)庫聯(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)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

文章名稱：SQLServer日志傳送如何配置
轉(zhuǎn)載來于：http://bm7419.com/article22/ipoejc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站改版、小程序開發(fā)、響應(yīng)式網(wǎng)站、商城網(wǎng)站、標(biāo)簽優(yōu)化、網(wǎng)站設(shè)計

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)