SQLServer的主从服务器配置和历史痕迹清理

我们做web应用的MySQL接触的比较多，大家都知道MySQL的备份或者负载均衡我们会采用主从复制技术。最近工作中接触到了SQLServer,老板要求也搭起类似的环境。从而我做了以下研究：一般情况下，SQLServer不叫主从复制，MS喜欢叫它日志传送功能。其实质是由两台服务器的代理作业完成的，首先基于源库服务器的备份机制，然后将事务日志定期传送到目的服务器，从库服务器接收并完成数据库还原操作名词解释：源库服务器主库服务器目标服务器从库服务器传送/接收一一主从服务器之间的数据传输【通过磁盘共享】还原一一执行日志文件实现机制1 .登入账号由SQLServer2000DTS封装，将主登入账号转移到从服务器，且执行各服务器间登入帐号SID之解析动作，由Agent完成2 .日志传送主从服务器的数据同步，主要依赖于数据库事务日志传送功能。简单的说就是：备份作业一>复制作业一>还原作业一>警报作业我们可以通过在数据创建维护计划中勾选将事务日志传送到其它的SQLServer(日志传送)”选项。那么，主服务器SQLServer代理会生成两个作业：1) .完整备份作业2) .事务日志备份作业从服务器SQLServer代理会生成五个作业：1) .事务日志备份作业2) .日志传送复制作业3) .日志还原作业4) .日志备份警报作业5) .日志恢复警报作业3 .角色变更通过 SQL Server MSDB预置存储过程 sp_change_primary_role对主服务器降级对从服务器升级通知监控服务器角色变更通过SQLServerMSDB预置存储过程sp_change_secondary_role通过SQLServerMSDB预置存储过程sp_change_monitor_role4 .角色互换通过一系列SQLServerMSDB预置存储过程清除之前日志传送信息在新主服务器和从服务器之间创建新的日志传送维护计划，参考日志传送实施步骤a.准备工作，创建配置存储目录完整备份目录，事务日志备份目录，预留从日志复制目录需要设置属性为共享，权限为完全控制b.创建数据库维护计划在创建数据库备份计划中将事务日志传送到其它的SQLServer(日志传送)”，只有选中此项才会启动日志传送作业按照提示下一步，根据上面的存储配置选择对应的目录和网络路径，网络路径主要用来提供日志复制使用c.配置完整备份周期和同步周期d.配置数据清理周期e.手动查看下主从服务器的SQLServer代理是否正常启动，作业是否创建成功5 .角色变更a.主服务器建立登入账号SQLServer2000DTS(package)封装，通过主服务器SQLServer代理工作b.降级主服务器(demote),使用msdb.pdo.sp_change_primary_role预存储过程，将主服务器从读/写模式转换为读备援模式，准备随时接受交易日志的备份资料USEmasterGOEXECmsdb.dbo.sp_change_primary_roledb_name='dbname',backup_log=1,©terminate=1,final_state=3,access_level=1c.升级从服务器（promote）,使用msdb.pdo.sp_change_secondary_role预存储过程，将从服务器升级为复原状态，一旦复原不会再回存同步日志restoredatabasedbnamewithrecoveryUSEmasterGOEXECmsdb.dbo.sp_change_secondary_roledb_name='dbname',do_load=1,force_load=1,final_state=1,access_level=1,©terminate=1,keep_replication=0,stopat=nulld.监控服务器角色变更，使用msdb.pdo.sp_change_monitor_role将监控服务器系统表中的主从配置删除，修改主服务器名称为新主服务器名称（即旧从服务器）USEmasterGOEXECmsdb.dbo.sp_change_monitor_roleprimary_server='hostnameinstance1',secondary_server='hostnameinstance2',database='dbname',new_source='hostnameinstance2'e.从服务器上解析登入账号，账号通过sp_resolve_logins读取，解析各服务器之间的差异USEmasterGOEXECsp_resolve_loginsdest_db='dbname',dest_path='d:',filename='syslogins.dat'f.清除监控服务器主服务器信息execmsdb.dbo.sp_delete_log_shipping_primaryprimary_server_name=N'hostnameslave'primary_database_name=N'dbname'delete_secondaries=1g.连接数据库存取与权限，使用sp_change_users_login修改新主服务器账号权限USEdbnameGOEXECsp_change_users_login'Update_One','UserName','LoginName'6 .角色互换a.在新主服务器上删除日志传送b.删除主从服务器数据库维护计划和相关作业c.维持所有事务日志文件d.在新主服务器配置新的数据库维护计划，配置新的日志传送，参见H志传送”备注如果主服务器宕机，以上角色变更”和角色互换”操作无法自动切换，需要人工干预，同时主服务器需要在启动的情况下才可以切换只有服务器在安装SQLServer2000前配置Microsoft集群服务器（MSCS）,才可以在SQLServer2000中创建故障自动转移集群服务上面说的基本在网上都可以找到，我想说的DBA同学们在搭建环境的时候都会经历测试搭建到正式搭建的过程，那么就会遇到清理历史痕迹的问题，因为日志传送如果不清理是无法重做的。大家可以看下扩展部分，手动日志传送配置清理>>>>>>手动日志传送清理我们知道SQLServer2005可以在属性中勾掉日志传送功能”选项，系统就会自动清理配置痕迹，但是SQLServer2000还没有这么强大，我们需要通过手动清理才可以首先，我们先来了解下主服务器、从服务器、监控服务器都会保留哪些痕迹主服务器：log_shipping_databases做主的实例信息log_shipping_monitor监控器信息其余表都没有记录从服务器：log_shipping_plans计戈U信息log_shipping_plan_databases计戈U中从实例信息log_shipping_plan_history计划历史记录表【略】其余表都没有记录监控服务器：log_shipping_primaries主实例信息log_shipping_secondaries从实例信息其余表都没有记录知道了这些，我们就可以清晰的了解到在每个服务器上执行哪些存储过程是必要的根据下面存储过程和系统表对应关系，我们就可以轻松的完成历史痕迹清理主服务器：sp_delete_log_shipping_database清除主服务器主实例配置select*fromlog_shipping_databases从服务器：sp_delete_log_shipping_plansp_delete_log_shipping_plan_database清除从服务器记录的传送计划select*fromlog_shipping_plansselect*fromlog_shipping_plan_databases存储过程参数，我们需要通过执行SQL语句得到selecta.plan_id,a.plan_name,b.source_databasefromlog_shipping_plansa,log_shipping_plan_databasesbwherea.plan_id=b.plan_id;参数赋值注意：查询结果给出的是xxxx-xxxx-xxxx,赋值存储过程的时候，需要手动改为单引号'xxxx-xxxx-xxxx，否则就会出现"MicrosoftODBCSQLServerDriver语法错误或违反访问规则"问题！监控服务器：sp_delete_log_shipping_primarysp_delete_log_shipping_secondary清除主从监控配置select*fromlog_shipping_primariesselect*fromlog_shipping_secondaries其实个人感觉存储过程不那么好用，有时候清除不掉，我们还得去跟存储过程：sp_helptextsp_delete_log_shipping_plan;使用这个命令可以查询每个存储过程的内容，其实就是对一组表的delete操作我们也可以抽取出其中的delete语句，为where条件制定参数即可【个人感觉比存储过程好用,操作简单易懂，好调试八丁】日志传送相关存储过程和系统表sp_delete_log_shipping_database删除日志传送的数据库信息sp_delete_log_shipping_monitor_info删除监控服务器信息sp_delete_log_shipping_monitor_job删除监控作业信息sp_delete_log_shipping_plan删除日志传送计戈Usp_delete_log_shipping_plan_database删除日志传送计划数据库sp_delete_log_shipping_primary删除日志传送主服务器信息sp_delete_log_shipping_secondary删除日志传送从服务器信息log_shipping_plans维护计戈U表log_shipping_plan_databases数据库表计戈Ulog_shipping_databases数据库信息表log_shipping_plan_history计划历时记录表log_shipping_monitor监控表log_shipping_primaries主服务器信息表log_shipping_secondaries从服务器信息表