SQL Server日志傳送如何配置

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

Date:2016-04-15

Author:Netdata

一. 安裝環境:

1. 系統環境

OS:Windows Server 2012 DataCenter

DB:SQL Server 2012 R2 SP3

主:

IP: 172.25.10.186

主機名: DBCW01-10-186

數據庫名:test

備:

IP:172.25.10.188

主機名: DBCW02-10-188

數據庫名:test

2.日志傳送配置簡介

下圖顯示了具有主服務器實例、三個輔助服務器實例和一個監視服務器實例的日志傳送配置。此圖闡釋了備份作業、復制作業以及還原作業所執行步驟，如下所示：

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

2. 全部三個輔助服務器實例都執行其各自的復制作業，以將主日志備份文件復制到它本地的目標文件夾中。

3. 每個輔助服務器實例都執行其還原作業，以將日志備份從本地目標文件夾還原到本地輔助數據庫中。

主服務器實例和輔助服務器實例將它們自己的歷史記錄和狀態發送到監視服務器實例。

二.安裝數據庫

1.分別在兩臺數據庫服務器上安裝SQL Server 2012 R2

2.分別在兩臺數據庫服務器上打上SP3補丁

三.配置日志傳送

1.主庫上創建應用數據庫

創建示例表

--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.創建用戶,并授權,

注意密碼策略,可不選,默認數據庫選擇test

用戶映射

3.配置日志傳送

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

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

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

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

主庫上操作

設置數據庫恢復模式

數據庫恢復模式必須為完整恢復模式

配置傳送事務日志

注:默認事務日志備份是每15分鐘一次

備庫狀態

注:以上操作也用腳本實現

主庫備份

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

備庫上恢復

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

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

4.配置完成后測試

在主庫插入數據

declare @i int

set @i=1

while @i<100001

begin

insert into test_log(name)

values(newid())

set @i=@i+1

end ;

輔庫查看

5.日志傳送監控

日志傳送主要是以作業形式

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

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

新建操作員

配置作業監控,主庫

備庫

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

監視歷史記錄表包含監視服務器上存儲的元數據。與給定的主服務器或輔助服務器相關的信息副本也存儲在本地。

可以查詢這些表，以監視日志傳送會話的狀態。例如，了解日志傳送的狀態，查看備份作業、復制作業和還原作業的狀態和歷史記錄。通過查詢下列監視表，可以查看特定的日志傳送歷史記錄和錯誤詳細信息。

監視日志傳送的存儲過程

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

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

監控作業腳本

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);

-- 獲取當前系統時間,和數據統計的時間

-- 如果有數據則發送

if exists (select top 1 * from t_log_status )

begin

set @str_subject='日志傳輸狀態'+convert(varchar(10),getdate(),120);

SET @tableHTML = N'

輔庫狀態

' +

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'

';

-- 發送郵件

exec @i_result = msdb.dbo.sp_send_dbmail

@profile_name = 'sqlmail',

@recipients = 'huangxianglong@eetop.com',

@subject = @str_subject,

@body = @tableHTML,

@body_format = 'HTML';

End

四.故障轉移

1.將所有未復制的備份文件從備份共享復制到每臺輔助服務器的復制目標文件夾中。

2. 將所有未應用的事務日志備份按順序應用到每個輔助數據庫中。

1. 將所有未應用的事務日志備份按順序應用到每個輔助數據庫中。有關詳細信息，請參閱應用事務日志備份 (SQL Server)。

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

3. 同步輔助服務器之后，可以根據您的首選，通過恢復任一輔助數據庫并將客戶端重定向到該服務器實例來故障轉移該輔助服務器。恢復操作將使數據庫處于一致的狀態并使其聯機。

注意做日志恢復的時候中間日志一定要是連續的

清理掉之前job

Use master; go sp_delete_log_shipping_secondary_database test;

USE master; GO sp_delete_log_shipping_alert_job;

五.主備交換角色

當初次將故障轉移到輔助數據庫并將其用作新的主數據庫時，必須執行一系列步驟。 按照這些初始步驟操作后，就可以輕松地交換主數據庫和輔助數據庫的角色。

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

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

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

1. 使用同一個共享來創建為原來的主服務器所創建的備份。

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

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

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

1. 在新的主服務器上

執行以下 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

在新的輔助服務器上

執行以下 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

完成以上步驟執行初始角色交換后，就可以按照本節的下列步驟交換主數據庫和輔助數據庫的角色。 若要執行角色交換，請執行下列常規步驟：

1. 使輔助數據庫聯機，用 NORECOVERY 備份主服務器上的事務日志。

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

3. 在輔助服務器（新的主服務器）上啟用日志傳送備份作業，在主服務器（新的輔助服務器）上啟用復制和還原作業

以上是“SQL Server日志傳送如何配置”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！