利用日志传送实现高度可用性

最新推荐文章于 2023-10-19 08:00:00 发布

weixin_33895695

最新推荐文章于 2023-10-19 08:00:00 发布

阅读量125

点赞数

文章标签：数据库操作系统

原文链接：http://blog.51cto.com/yejunsheng/160974

版权

日志传送概述: 日志传送由三项操作组成: 在主服务器实例中备份事务日志将事务日志文件复制到辅助服务器实例在辅助服务器实例中还原日志备份主服务器和数据库辅助服务器和数据库监视服务器日志传送操作-->备份作业复制作业还原作业警报作业

配置日志传送: 要求权限步骤-->选择作为主服务器、辅助服务器和可选的监视服务器的服务器最好在不属于日志传送配置的容错服务器上,为事务日志备份创建文件共享选择主数据库的备份计划为每个辅助服务器创建一个文件夹,事务日志备份文件将会复制到其中配置一个或多个辅助数据库 (可选)配置一个监视服务器

监视日志传送: (一)存储过程-->sp_help_log_shipping_monitor_primary sp_help_log_shipping_monitor_secondary sp_help_log_shipping_primary_database sp_help_log_shipping_primary_secondary sp_help_log_shipping_secondary_database sp_help_log_shipping_secondary_primary (二)日志传送报告

如何配置日志传送呢?

我已经提前在C盘里面创建几个文件了第一个共享文件夹叫做PrimaryData,在里面创建了一个叫做Logs的文件夹这个目录是干什么用的呢? 是用来给主服务器进行备份用的就是说我去对主服务器上的生产的数据库进行日志备份我的日志备份将会存储在主服务器上的PrimaryData这个文件夹里面第二个共享文件夹叫做SecondaryData,在里面也创建了一个叫做Logs的文件夹这个目录是用来存储从主服务器上拷贝备份拷日志文件到的目录然后辅助服务器自动从这个文件夹把日志文件还原到辅助服务器对应的数据库当中里面第三个共享文件夹叫做DatabaseBackup 这个目录是用来创建主服务器上生产数据库初始化的一个备份这个备份我们可以用来辅助服务器上去初始化出来一个主服务器上生产数据库的一个副本换句话说辅助服务器上对应的生产数据库的一个副本它可以通过一个备份去还原出来这个备份可以是主服务器上已有的备份就是说已经存在的一个数据库备份这个数据库在辅助的服务器上进行一个还原这样的话还原就是主服务器上生产数据库对应的一个副本数据库出来然后继续地进行日志传送就是说从主服务器上的数据库到辅助服务器上的备用数据库去进行日志传送但是辅助服务器上的备用数据库是可以通过主服务器上已经存在的生产数据库的备份来进行地还原的我已经在这台SQL Server 2005服务器上安装了3个实例了第一个实例(SQL2005)-->默认实例去用来作为主服务器第二个实例(SQL2005\INSTANCE1)作为辅助服务器第三个实例(SQL2005\INSTANCE2)作为监视服务器

如果说用一台其他的服务器来存放日志文件的备份的话是有前提条件的假设SQL Server Agent(MSSQLSERVER)这个实例(默认实例)就是主服务器那你要确定这个实例的SQL Server Agent(MSSQLSERVER)这个服务是用一个Windows账户去启动的不应该去用一个本地系统账户去启动的因为如果这个服务是用本地系统账户去启动的话呢这个账户本身的权利是不具有在网络上进行访问权利的一旦SQL Server Agent这个服务设置成是使用本地系统账户来去启用这个服务的话那你只能用你的主服务器上的文件夹做一个备份的目录换句话说在另一台文件夹服务器上创建文件夹并且共享你去指定说我要通过主服务器把日志文件备份到那台服务器上的共享文件夹里面是不行的因为SQL Server Agent这个服务会对配置成本地系统账户来启用的如果按照我现在的配置是使用一个Windows的域用户(YEJUNSHENG\Administrator)来去启动SQL Server Agent(MSSQLSERVER)这个服务的话是没有问题的完全可以把主服务器生产数据库的日志文件备份到其他的服务器上面并一定非得在主服务器上创建备份的目录

展开SQL2005(主服务器)--数据库--对着AdventureWorks右键--选择属性--按选项--在恢复模式里面选择完整--按确定注意:如果想对AdventureWorks这个数据库配置日志传送的话你事先一定要把它的恢复模式设置为完全或者是大容量日志这样它才支持我们去配置日志传送因为在简单的记录下面它是不会去记录事务的详细过程因此我们就没法去进行事务日志的备份如果你不去修改恢复模式的话直接配置日志传送会报错的默认情况下它的恢复模式是简单

现在AdventureWorks这个数据库的恢复模式是简单展开数据库--对着AdventureWorks右键--按任务--选择传送事务日志把将此数据库启用为日志传送配置中的主数据库沟上--可以看到一个报错提示-->此数据库在日志传送配置中不能是主数据库,因为它使用了简单恢复模式。你必须使用完整恢复模式或大容量日志恢复模式,然后才能生成事务日志。

我现在把AdventureWorks这个数据库的恢复模式修改成完整按新建查询--在里面输入alter database adventureworks set recovery full go这条语句--按执行输入sp_helpdb adventureworks go这条语句--按执行之后可以看到Recovery=FULL表明已经成功把AdventureWorks这个数据库的恢复模式修改成完整了

我为了待会能够去使用主服务器上生产数据库的一个备份来去对辅助服务器上的备用数据库做一个初始化我在主服务器上对adventureworks这个数据库做一个完整的备份--输入backup database adventureworks to disk='c:\DatabaseBackup\adventureworks-001.bak' go这条语句--按执行然后我来创建一张叫做dbo.table1的表--输入use adventureworks go按执行--输入create table dbo.table1(id int,name char(10)) go按执行--输入insert dbo.table1 values(1,'john') go按执行展开AdventureWorks--表--可以看到已经有dbo.table1这张表了现在我去对adventureworks做一下日志备份为什么要做日志备份呢? 因为有可能我们做了一个完整的数据库备份之后你可能又做了一些其他的操作此时你是必须对日志做一个备份的换句话说我待会需要在辅助服务器上面对备用数据库还原做一个初始化时你可能就需要还原它的一个数据库的备份同时还需要还原你现在所做的日志备份输入backup log adventureworks to disk='c:\DatabaseBackup\adventureworks-001.bak' go按执行之后可以看到在消息里面显示已经成功对日志做一个备份了

大家如果想确认是否已经成功备份adventureworks的话就展开服务器对象--对着备份设备右键--选择新建备份设备--设备名称就叫做database吧--按浏览键--展开C:--DatabaseBackup--按adventureworks-001.bak--按确定

对着database右键--选择属性--按媒体内容--可以看到在备份设备里面确实是有同一个数据库的两个备份一个是完整的备份一个是事务日志的备份按确定

备份完成之后就开始去做日志传送的配置了展开数据库--对着AdventureWorks右键--按任务--选择传送事务日志把将此数据库启用为日志传送配置中的主数据库沟上--按备份设置--在备份文件夹的网络路径(示例:\\primaryserver\backup)里面输入 \\sql2005\PrimaryData\Logs --在如果备份文件夹位于主服务器上,则键入该文件夹的本地路径(示例:c:\backup)里面输入c:\PrimaryData\Logs 我把它设置成每隔2分钟就做一次日志备份--每隔3分钟没有执行备份时就报警--每隔10分钟没有执行备份时就删除文件按确定

接下来我们就需要去配置辅助服务器是谁就是说作为主服务器生产数据库对应的一个备用数据库所在的服务器是谁一旦出现问题我就手动切换到辅助服务器来实现一个手动的故障转移在辅助数据库里面按添加--在辅助服务器实例里面按连接--按照我前面的描述,辅助服务器是SQL2005\INSTANCE1--在服务器名称里面选择SQL2005\INSTANCE1 按连接

在初始化辅助数据库里面选择是,将主数据库的现有备份还原到辅助数据库中(如果辅助数据库不存在,则创建它)--在备份文件里面输入C:\DatabaseBackup\adventureworks-001.bak 就是说我要通过这个备份去对辅助服务器上的辅助数据库来做一个应用让辅助服务器能够做一个初始化

在辅助数据库设置里面按复制文件选项--在复制文件的目标文件夹(此文件夹通常位于辅助服务器上。)里面输入 \\sql2005\SecondaryData\Logs 我把它设置成每隔2分钟就执行一次备份--每隔12分钟之后就把复制过来的文件删除掉就是说日志文件一旦从主服务器那边复制过来以后超过12分钟之后就删除掉但是有一个前提是要确定在12分钟到的时候删除掉的日志文件是否已经能够去还原到辅助服务器上的辅助数据库里面了

在辅助数据库设置里面按还原事务日志--在还原备份时的数据库状态里面选择备用模式--把在还原备份时断开数据库中用户的连接沟上如果是选择无恢复模式的话辅助服务器上的辅助数据库是不能够访问的换句话说这个数据库它只能够通过日志传送自动去做一个Log的还原但是想直接对辅助服务器上设为无恢复模式的辅助数据库进行查询的话是做不了的因为辅助数据库一旦设置为无恢复模式之后就不能被访问了如果你希望辅助数据库继续能够还原Log同时还能够让用户通过辅助数据库来去做查询的话就是说把一些查询的负载从主服务器上分担到辅助服务器上的话此时就必须选择备用模式了备用模式里面有一个沟选项叫做在还原备份时断开数据库中用户的连接如果你不沟上这一项的话那么事务日志的还原它不会按照你设置好的时间进行执行的比如说我把它设置成每隔2分钟就执行一次还原它到了2分钟的时候是不会做还原的它会一直累计到最后一个用户断开到辅助数据库的连接它才会把所有累计下来还没有还原的日志文件一次性地全部还原如果你沟选上这个选项的话它每次去把拷贝过来的事务日志备份还原到辅助服务器的时候都会自动地断开所有到辅助数据库上用户的连接我把它设置成每隔2分钟就执行一次还原设置延迟还原备份操作至少3分钟设置6分钟之后没有执行还原时就报警延迟还原备份有什么用呢? 比如说你在主服务器做了一个误操作这个误操作是删除了500条记录或者说你写错了一个语句一旦执行之后这个时候可能给你的日志备份记录下来然后传递到辅助服务器上面那么此时可以设置一个延迟时间如果你不设置延迟时间的话拷贝过来的文件2分钟之后就直接还原到辅助数据库里面了这个误操作也同时写到辅助数据库里面所以说我们一般设置一个延迟时间就是说让它在执行还原的时候再还原3分钟这样一旦你发现之后你还要以马上终止

最后来设置一下谁作为监视服务器在监视服务器实例里面按设置

在监视服务器实例里面按连接按照我前面的描述,监视服务器是SQL2005\INSTANCE2 在服务器名称里面选择SQL2005\INSTANCE2 按连接

默认情况下删除历史记录的时间是96小时我把它设置为1小时后就删除历史记录吧同时它也会创建一个以LSAlert_SQL2005\INSTANCE2这样的名字命名的一个警报作业用来去监视从主服务器到辅助服务器的整个日志传送的过程按确定

至此为止日志传送的配置就结束了在数据库属性里面按确定就ok了这个时候它就开始把我们之前在主服务器上做的备份的C:\DatabaseBackup这个文件夹里面的adventureworks-001.bak这个数据库进行一个还原对辅助数据库做一个初始化接下来会保存刚才我们在日志传送整个属性的日志配置界面中所做的一些设置

展开SQL2005\INSTANCE1(辅助服务器)--数据库--可以看到辅助数据库(AdventureWorks)已经出来了它是备用/只读模式就是说辅助服务器上的辅助数据库里面的数据只能够被访问的只能针对这些表来做一个查询是不能够修改的在主体服务器上存储日志文件的文件夹会产生一些日志文件的 C:\PrimaryData\Logs这个路径下可以看到已经产生4个日志文件了同样在辅助服务器上存储日志文件的日志文件夹也会产生4个日志文件

如何监视日志传送呢?

按新建查询--在里面输入use master go sp_help_log_shipping_primary_database 'adventureworks' go 按执行之后可以看到一些详细信息可以看到Primary_database(主数据库)是AdventureWorks backup_directory(备份到的目录)是c:\PrimaryData\Logs这个目录它的网络路径是 \\sql2005\PrimaryData\Logs 这个目录备份10分钟之后就会被删除掉了对应备份作业的ID号是多少 SQL2005\INSTANCE2作为监视服务器备份的话超过3分钟会预警目前备份到c:\PrimaryData\Logs\AdventureWorks_20081221103234.trn了可以看到上次备份的时间是2008-12-21 18:32

在查询语句里面输入sp_help_log_shipping_primary_secondary 'adventureworks'这条语句按执行之后可以看到辅助服务器是SQL2005\INSTANCE1

在Microsoft SQL Server Management Studio里面按连接--选择数据库引擎--在服务器名称里面选择SQL2005\INSTANCE2(监视服务器)--按连接

按新建查询--在里面输入use master go sp_help_log_shipping_monitor_primary 'sql2005','adventureworks' go sp_help_log_shipping_monitor_secondary 'sql2005\instance1','adventureworks' go 按执行之后可以看到SQL2005的主服务器是SQL2005 备份预警的时间是3分钟备份文件的路径是什么什么时候备份的并且也可以看到辅助服务器的一些详细信息

在对象资源管理器里面按连接--选择数据库引擎--在服务器名称里面选择SQL2005\INSTANCE1--按连接来连接到辅助服务器

按新建查询--在里面输入use master go sp_help_log_shipping_secondary_database 'adventureworks' go sp_help_log_shipping_secondary_primary 'sql2005','adventureworks' go 按执行之后也可以看到主服务器的一些详细信息除了用了这些存储过程以外我们还可以用图形化界面的工具展开SQL2005(主服务器)--SQL Server 代理--作业--可以看到在主服务器上多出一个叫做LSBackup_AdventureWorks的备份作业展开SQL2005\INSTANCE1(辅助服务器)--SQL Server 代理--作业--可以看到在辅助服务器上多出二个作业一个是复制作业一个是还原作业

展开SQL2005\INSTANCE2(监视服务器)--SQL Server 代理--作业--可以看到在监视服务器上多出一个叫做LSAlert_SQL2005\INSTANCE2的警报作业展开警报可以看到同时还创建二个警报这二个警报如果监视到错误默认情况下是不会通知你的因为没有经过配置--所以我们一般建议在监视服务器上设置一下操作员--对着操作员右键--选择新建操作员

名称就叫做sqladmin吧在Net send地址里面输入本地连接的IP地址(192.168.0.1)让它来通过Net send命令发送一个消息但是你要保证对应的服务要能够启动按确定

此时我们可以把警报设置一下--对着日志传送辅助服务器警报右键--选择属性在"日志传送辅助服务器警报属性里面按响应--把通知操作员沟上--在操作员列表里面把sqladmin的Net Send沟上按确定如果这个警报被触发了就要去给sqladmin这个操作发送一个消息

同样在日志传送主服务器上也应该给操作员发送一个消息对着日志传送主服务器警报右键--选择属性--在"日志传送主服务器警报的属性里面按响应--把通知操作员沟上在操作员列表里面把sqladmin的Net Send沟上按确定

对着SQL2005\INSTANCE2(监视服务器)右键--按报表--选择事务日志传送状态这个时候它就会帮你产生一个报表来告诉你谁是主数据库谁是辅助数据库什么时候备份的什么时候还原的警报的值是多少字体是红色的代表是警报的字体是黑色的代表是正常的可以看到现在辅助数据库有问题辅助数据库一共做二件事情一个是复制一个还原如果不是复制有问题那么就是还原有问题了

还原是由辅助服务器上的用于进行还原的作业做的我们可以手动来进行一下还原展开SQL2005\INSTANCE1(辅助服务器)--SQL Server 代理--作业--对着LSRestore_SQL2005_AdventureWorks右键--选择作业开始步骤--可以看到执行二个作业都是成功的

展开SQL2005\INSTANCE1(辅助服务器)--SQL Server 代理--错误日志--对着当前这条日志右键--选择查看代理日志可以看到一些警报的详细信息可以看到其中有一条消息:The database 'AdventureWorks' is marked RESTORING and is in a state that does not allow recovery to be run-->表示的意思是AdventureWorks这个数据库是设置成正在还原中,因此这个正在还原的状态是不允许进行恢复的换句话说因为这个数据库正在处于还原的过程当中导致日志文件没法还原回去但是我们可以看到辅助数据库显示的状态不是正在还原而是备用/只读我现在知道是怎么回事了我们做了一个初始化的还原时是用了adventureworks-001.bak文件进行还原的但是在这个bak文件里面还包含了一个日志备份也就是说这个唯一的日志备份目前还没有还原到辅助数据库里面所以这个文件中的日志备份还没有还原进去之后导致了这些日志文件都没有办法进行还原也就是说这些日志文件要想进行还原的话我们需要先把AdventureWorks这个数据库文件当中的日志备份首先还原到辅助数据库然后它才能够自动地通过那个还原作业把这些日志文件去进行还原

现在我把adventureworks-001.bak文件里面的一个日志备份还原到辅助数据库里面选中SQL2005\INSTANCE1(辅助服务器)--按新建查询--在里面输入restore log adventureworks from disk='C:\DatabaseBackup\adventureworks-001.bak' with file=2,norecovery按执行之后日志文件就还原回去了也就是说这个时候adventureworks-001.bak这个数据库备份文件当中的第二条的备份日志文件已经被还原回去了这个文件被还原回去之后我相信C:\SecondaryData\Logs这个路径里面的日志文件都会按照我计划好的每隔6分钟还原一次把这些日志文件都还原到数据库里面

如何手动故障转移到辅助数据库呢?

我假设现在主体服务器已经损坏了我来模拟一下主体服务器损坏了通过开始--程序--Microsoft SQL Server 2005--配置工具--选择SQL Server Configuration Manager来打开它--按SQL Server 2005服务--把SQL Server(MSSQLSERVER)和SQL Server Agent(MSSQLSERVER)这二个服务停止服务停止之后可以看到主体服务器的图标是红色的在C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data这个路径下把AdventureWorks_Data.mdf这个数据库文件删除掉

通过开始--程序--Microsoft SQL Server 2005--配置工具--选择SQL Server Configuration Manager来打开它--按SQL Server 2005服务--把SQL Server(MSSQLSERVER)和SQL Server Agent(MSSQLSERVER)这二个服务都启动起来这个时候主体服务器上的主数据库肯定是不能够访问的因为它的数据文件都丢失了

展开SQL2005(主体服务器)--数据库--可以看到现在AdventureWorks这个数据库不能访问了首先第一步我们要做的是什么呢? 因为一旦出问题的话你可能会发现有一些所备份的日志文件可能还没有完全拷贝过来如果你发现还有日志文件没有拷贝过来的话你要马上把它拷贝过来要保证两边有同步性也就是说要保证C:\PrimaryData\Logs这个路径的日志文件和C:\SecondaryData\Logs的日志文件一模一样的如果在损坏时还没有来得及到那个日志文件备份的时候这个候我建议大家马上对损坏的主体服务器的数据库进行一个norecovery参数做一次备份在对象资源管理器里面按连接--选择数据库引擎--在服务器名称里面选择SQL2005(主体服务器)--按连接

这个时候我对损坏的主体服务器上的主体数据库用no_truncate参数做一下事务日志备份这个参数备份能够在事务日志损坏的时候依然可以对数据库做一次备份按新建查询--在里面输入backup log adventureworks to disk='C:\PrimaryData\Logs\last.trn' with no_truncate go这条语句--按执行之后可以看到C:\PrimaryData\Logs这个路径和C:\SecondaryData\Logs这个路径都出现last.trn这个日志文件了

因为现在两边的服务器都同步了我可以把SQL2005(主体服务器)里面的作业和SQL2005\INSTANCE1(辅助服务器)里面的作业都禁用掉因为这些都不需要去运行了这个时候我可以把辅助服务器上的数据库让它去联机按SQL2005\INSTANCE1(辅助服务器)按新建查询--输入restore database adventureworks with recovery这条语句按执行之后再按F5键来刷新一下数据库展开SQL2005\INSTANCE1(辅助服务器)--数据库--可以看到(SQL2005)主体服务器上的AdventureWorks这个数据库已经被转移到SQL2005\INSTANCE1(辅助服务器)里面了-->表明现在已经成功完成一个手动的故障转移数据库了

如何交换主数据库和辅助数据库角色呢?

为什么需要交换角色呢? 比如说我现已经把SQL2005(主体服务器)修复好了修复好了之后我想再把它切换回去此时该如何去做呢?展开SQL2005\INSTANCE1(辅助服务器)--数据库--对着AdventureWorks右键--按任务--选择传送事务日志--把将此数据库启用为日志传送配置中的主数据库沟上--按备份设置--在备份文件夹的网络路径(示例:\\primaryserver\backup)里面输入 \\sql2005\PrimaryData\Logs --在如果备份文件夹位于主服务器上,则键入该文件夹的本地路径(示例:c:\backup)里面输入C:\PrimaryData\Logs 我把它设置成保留时间超过10分钟之后就删除文件每隔2分钟就执行一次备份每隔3分钟没有执行备份时就报警按确定

我现在添加一个辅助服务器--在辅助服务器实例和数据库里面按添加--在辅助服务器实例里面按连接--在服务器名称里面选择SQL2005(主体服务器)按连接注意: 现在的辅助服务器就是原来的辅助服务器

在初始化辅助数据库里面选择是,生成主数据库的完整备份,并将它还原到辅助数据库中(如果辅助数据库不存在,则创建它) 因为现在那个损坏的SQL2005(主体服务器)连数据库文件都没有了所以我让它对现在的主服务器(SQL2005\INSTANCE1-->原来的辅助服务器)的AdventureWorks马上做一个备份然后去还原到原来的辅助服务器(现在的主体服务器)里面

在复制文件选项的复制文件的目标文件夹(此文件夹通常位于辅助服务器上。)里面输入 \\sql2005\SecondaryData\Logs 我把它的时间设置成16分钟之后就删除复制的文件每隔2分钟就执行一次复制

如果大家还需要设置监视的话就在监视服务器实例里面按设置

在监视服务器实例里面按连接--在服务器名称里面选择SQL2005\INSTANCE2(监视服务器)--按连接

我把历史记录保持期的时间设置成16分钟之后就删除历史记录按确定至此为止就配置完成了

在数据库属性里面按确定这个时候它就开始重新把原来的辅助服务器设置成主服务器把原来的主体服务器设置成辅助服务器了

在进行备份的时候可以看到又产生了一个叫做AdventureWorks.bak的文件它是现在的主体服务器(原来辅助服务器)上的一个数据库备份文件然后拿这个备份再还原到原来的主体服务器上的已经损坏的AdventureWorks这个数据库里面做一个初始化最后一步就是把原来的主体服务器上的AdventureWorks这个数据库上所设置的日志传送删除掉展开SQL2005(原来的主体服务器,现在的辅助服务器)--数据库--对着AdventureWorks右键--按任务--选择传送事务日志注意: AdventureWorks这个数据库必须是只读/备用状态才能打开传送事务日志如果是正在还原的状态是不能打开传送事务日志的我刚才在SQL2005\INSTANCE1(现在的主体服务器,原来的辅助服务器)上设置AdventureWorks的时候是把还原事务日志选项的还原备份时的数据库状态设置成无恢复模式也就是说必须把还原备份时的数据库状态设置成备用模式才行修改成无恢复模式之后在SQL2005(原来的主体服务器,现在的辅助服务器)和在SQL2005\INSTANCE1(原来的辅助服务器,现在的主体服务器)上就会多出几份作业然后执行这几份作业之后再刷新一下数据库就可以看到AdventureWorks的状态就变成(备用/只读)状态了此时就可以在SQL2005(原来的主体服务器,现在的辅助服务器)上打开AdventureWorks这个数据库的传送事务日志了