sql server 2005镜像数据库事务日志文件维护
好的，对于标准的非镜像数据库，只需将数据库置于简单模式或通过定期备份即可保持对事务日志的检查。我们保持简单，因为我们进行了 SAN 快照备份，并且不需要 SQL 备份。
我们现在要进行镜像。我显然不再有简单模式的选择，必须使用完整模式。这显然会导致日志文件很大并且需要日志备份。没关系，我可以处理这个问题；进行日志备份并丢弃任何以前的日志的维护计划。我意识到，如果没有其前身，此备份基本上毫无用处，但 SAN 快照正在执行备份。
我的问题是...
a) 有没有办法在不创建备份的情况下截断所有已处理行的日志文件？ （因为我无论如何都无法使用它们...）
b) 维护计划位于服务器本地，并且不会跨镜像对进行复制。在镜像设置上应该如何完成？这样当数据库故障转移时，计划开始在新主体上运行，但当它是镜像时不会感到不安？
谢谢
Ok so for standard, non-mirrored databases, the transaction log is kept in check either simply by having the database in simple mode or by doing regular backups. We keep ours in simple as we have SAN snapshot backups taking place and there is no need for SQL backups.
We're now going to mirroring. I obviously no longer have the choice of simple mode and must use full. this obviously leads to large log files and the need for log backups. That's fine I can deal with that; a maintenance plan that takes a log backup and discards any previous ones. I realise that this backup is essentially useless without its predecessors but the SAN snapshots are doing the backups.
My question is...
a) Is there a way to truncate the log file of all processed rows without creating a backup? (as I can't use them anyway...)
b) A maintenance plan is local to a server and is not replicated across a mirrored pair. How should it be done on a mirrored setup? such that when the database fails over, the plan starts running on the new principal, but doesn't get upset when its a mirror?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
答：如果您的服务器足够重要，可以对其进行镜像，那么为什么它不够重要，无法进行事务日志备份？ SAN 快照只是一个时间点的时间点映像，但它们无法让您在整个过程中的不同时间点停止。当您的开发人员截断表时，您希望重播该语句之前的所有日志，并在那里停止。这就是事务日志备份的用处。
B. 制定维护计划（或者更好的是，T-SQL 脚本，例如 Ola Hallengren 的 http://ola.hallengren.com ）备份所有数据库，但选中复选框仅备份在线数据库。 （我突然想到，不确定这是否是 2005 年的一个选项 - 可能仅是 2008 年。）这样，您将始终能够获得发生故障转移的任何情况。
当然，请记住，您需要小心清理脚本和复制这些备份文件之类的事情。如果您的一半 t-log 备份在一个共享上，另一半在另一个共享上，则恢复起来会更加困难。
A. If your server is important enough to mirror it, why isn't it important enough to take transaction log backups? SAN snapshots are point-in-time images of just one point in time, but they don't give you the ability to stop at different points of time along the way. When your developers truncate a table, you want to replay all of the logs right up until that statement, and stop there. That's what transaction log backups are good for.
B. Set up a maintenance plan (or even better, T-SQL scripts like Ola Hallengren's at http://ola.hallengren.com) to back up all of the databases, but check the boxes to only back up the online ones. (Off the top of my head, not sure if that's an option in 2005 - might be 2008 only.) That way, you'll always get whatever ones happen to fail over.
Of course, keep in mind that you need to be careful with things like cleanup scripts and copying those backup files. If you have half of your t-log backups on one share and half on the other, it's tougher to restore.
a) 不，您不能截断属于镜像数据库一部分的日志。备份日志是您最好的选择。我有几个数据库只是根据 HA 需求设置了镜像，但由于各种原因不需要 DR。看来你的情况是这样的？我真的仍然建议保留日志备份一段时间。没有理由取消由 HA 策略添加的完美恢复计划。 :)
b) 我自己的解决方案是有一个辅助代理作业，根据镜像的状态进行监控。如果发现镜像发生更改，则启用镜像实例上的辅助作业，如果可能，则禁用旧主体。如果主体宕机后又恢复，则该作业仍处于禁用状态。作业本身切换回来的唯一方法是再次发生强制故障转移。
a) no, you cannot truncate a log that is part of a mirrored database. backing the logs up is your best option. I have several databases that are setup with mirroring simply based on teh HA needs but DR is not required for various reasons. That seems to be your situation? I would really still recommend keeping the log backups for a period of time. No reason to kill a perfectly good recovery plan that is added by your HA strategy. :)
b) My own solutions for this are to have a secondary agent job that monitors based on the status of the mirror. If the mirror is found to change, the secondary job on teh mirror instance is enabled and if possible, the old principal is disabled. if the principal was down and it comes back up, the job is still disabled. the only way the jobs themselves would be switched back is the event of again, another forced failover.