SQL Server日志傳送如何配置

小編給大家分享一下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ù)狀態(tài)

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

主庫(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郵件(主備都需要操作)

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

新建操作員

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

備庫(kù)

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

監(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ì)信息。

監(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ò)程。

表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'

';

-- 發(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ù)中。

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

2. 如果可以訪(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ù)。

3. 同步輔助服務(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ù)的角色。

1. 手動(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)。

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

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

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

2. 添加輔助數(shù)據(jù)庫(kù)時(shí)，在“輔助數(shù)據(jù)庫(kù)設(shè)置”對(duì)話(huà)框的“輔助數(shù)據(jù)庫(kù)”框中輸入原來(lái)的主數(shù)據(jù)庫(kù)的名稱(chēng)。

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

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

1. 在新的主服務(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)注億速云行業(yè)資訊頻道！