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服務

新建操作員

配置作業監控,主庫

備庫

關於日誌傳送監控檢視(摘自官方文件)

監視歷史記錄表包含監視伺服器上儲存的後設資料。與給定的主伺服器或輔助伺服器相關的資訊副本也儲存在本地。

可以查詢這些表，以監視日誌傳送會話的狀態。例如，瞭解日誌傳送的狀態，檢視備份作業、複製作業和還原作業的狀態和歷史記錄。透過查詢下列監視表，可以檢視特定的日誌傳送歷史記錄和錯誤詳細資訊。

表	說明
	儲存警報作業 ID。
	儲存日誌傳送作業的錯誤詳細資訊。可以查詢此表來檢視某個代理會話的錯誤。還可以按每個錯誤的記錄日期和時間對錯誤進行排序。每個錯誤都記錄為一個異常序列，多個錯誤（序列）可以形成一個代理會話。
	儲存日誌傳送代理的歷史記錄詳細資訊。可以查詢此表來檢視某個代理會話的歷史記錄詳細資訊。
	在每個日誌傳送配置中對主資料庫儲存一條監視記錄，包括有關對監視有用的最新備份檔案和最新還原檔案的資訊。
	對每個輔助資料庫儲存一條監視記錄，包括有關對監視有用的最新備份檔案和最新還原檔案的資訊。

監視日誌傳送的儲存過程

監視和歷史記錄資訊儲存在 msdb 的表中，可以透過日誌傳送儲存過程來訪問它。請在下表中指定的伺服器上執行下列儲存過程。

儲存過程	說明	執行儲存過程的伺服器
	從 log_shipping_monitor_primary 表中返回指定的主資料庫的監視記錄。	監視伺服器或主伺服器
	從 log_shipping_monitor_secondary 表中返回指定的輔助資料庫的監視記錄。	監視伺服器或輔助伺服器
	返回警報作業的作業 ID。	監視伺服器或主/輔助伺服器（如果未定義監視伺服器）
	檢索主資料庫設定並顯示 log_shipping_primary_databases 和log_shipping_monitor_primary 表中的值。	主伺服器
	檢索主資料庫的輔助資料庫名稱。	主伺服器
	從 log_shipping_secondary、log_shipping_secondary_databases 和log_shipping_monitor_secondary 表中檢索輔助資料庫設定。	輔助伺服器
	此儲存過程將在輔助伺服器上檢索給定的主資料庫的設定。	輔助伺服器

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

狀態(0執行正常,無代理失敗)	是否是主庫(1主資料庫,0輔助資料庫)	伺服器名稱	資料庫	上次複製日誌備份	上次複製日誌檔名	上次恢復日誌檔名

';

-- 傳送郵件

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. 將所有未應用的事務日誌備份按順序應用到每個輔助資料庫中。有關詳細資訊，請參閱。
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 備份主伺服器上的活動事務日誌。 有關詳細資訊，請參閱 。
2. 禁用原始主伺服器上的日誌傳送備份作業以及原始輔助伺服器上的複製和還原作業。
3. 使用 SQL Server Management Studio 在輔助資料庫（要用作新的主資料庫的資料庫）上配置日誌傳送。 有關詳細資訊，請參閱 。 包括下列步驟：
   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 日誌傳輸優點是:

為單個主資料庫以及一個或多個輔助資料庫

支援對輔助資料庫的受限的只讀訪問許可權（在還原作業之間的間隔期間）

允許使用者將延遲時間定義為：從主伺服器備份主資料庫日誌到輔助伺服器必須還原（應用）日誌備份之間的時間。例如，如果主資料庫上的資料被意外更改，則較長的延遲會很有用。如果很快發現意外更改，則透過延遲，您可以在輔助資料庫反映此更改之前從其中檢索仍未更改的資料。

缺點:

恢復有一定延時(至少分鐘級)