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

新建操作员

配置作业监控,主库

备库

关于日志传送监控视图(摘自官方文档)

监视历史记录表包含监视服务器上存储的元数据。与给定的主服务器或辅助服务器相关的信息副本也存储在本地。

可以查询这些表，以监视日志传送会话的状态。例如，了解日志传送的状态，查看备份作业、复制作业和还原作业的状态和历史记录。通过查询下列监视表，可以查看特定的日志传送历史记录和错误详细信息。

表	说明
log_shipping_monitor_alert	存储警报作业 ID。
log_shipping_monitor_error_detail	存储日志传送作业的错误详细信息。可以查询此表来查看某个代理会话的错误。还可以按每个错误的记录日期和时间对错误进行排序。每个错误都记录为一个异常序列，多个错误（序列）可以形成一个代理会话。
log_shipping_monitor_history_detail	存储日志传送代理的历史记录详细信息。可以查询此表来查看某个代理会话的历史记录详细信息。
log_shipping_monitor_primary	在每个日志传送配置中对主数据库存储一条监视记录，包括有关对监视有用的最新备份文件和最新还原文件的信息。
log_shipping_monitor_secondary	对每个辅助数据库存储一条监视记录，包括有关对监视有用的最新备份文件和最新还原文件的信息。

监视日志传送的存储过程

监视和历史记录信息存储在 msdb 的表中，可以通过日志传送存储过程来访问它。请在下表中指定的服务器上运行下列存储过程。

存储过程	说明	运行存储过程的服务器
sp_help_log_shipping_monitor_primary	从 log_shipping_monitor_primary 表中返回指定的主数据库的监视记录。	监视服务器或主服务器
sp_help_log_shipping_monitor_secondary	从 log_shipping_monitor_secondary 表中返回指定的辅助数据库的监视记录。	监视服务器或辅助服务器
sp_help_log_shipping_alert_job	返回警报作业的作业 ID。	监视服务器或主/辅助服务器（如果未定义监视服务器）
sp_help_log_shipping_primary_database	检索主数据库设置并显示 log_shipping_primary_databases 和log_shipping_monitor_primary 表中的值。	主服务器
sp_help_log_shipping_primary_secondary	检索主数据库的辅助数据库名称。	主服务器
sp_help_log_shipping_secondary_database	从 log_shipping_secondary、log_shipping_secondary_databases 和log_shipping_monitor_secondary 表中检索辅助数据库设置。	辅助服务器
sp_help_log_shipping_secondary_primary (Transact-SQL)	此存储过程将在辅助服务器上检索给定的主数据库的设置。	辅助服务器

表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. 将所有未应用的事务日志备份按顺序应用到每个辅助数据库中。有关详细信息，请参阅应用事务日志备份 (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日志传送如何配置”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！