手动更新生产数据库的最佳做法 [英] Best Practice for Updating a Production Database manually

问题描述

只是想知道在手动运行脚本时更新生产数据库时，人们的想法是什么，比如当早上2点需要紧急支持更改时）。

Just wondering what peoples thoughts are on updating production databases when manually running scripts, say when emergency support changes are required 2AM in the morning :)

开发人员使用在脚本结尾处具有事务回滚的脚本，并且更改的行和计数显示在屏幕上。一旦他们满意，脚本hasnt更新数据库中的每个记录，他们他们运行事务，没有回滚。

I have seen some developers use scripts that have a rollback of the transaction at the end of their scripts and the changed rows and counts are displayed to the screen. Once they are then satisfied the the script hasnt updated every record in the database they they run the transaction without the rollback.

有关最佳实践的任何提示？从DBA的任何其他策略？

Any tips about best practices here? Any other strategies from DBA's?

感谢

推荐答案

我们的救生器是一套健康检查脚本 - 将确定数据库是否处于一致状态的SQL查询。在我们的系统中，我们主要处理时间卡，发票，付款等等 - 所以我们有脚本，以确保时间表只由当前的员工提交，发票反映了客户在发出时的总债务，所有员工的可收费率大于零但小于一百万等。

Our life saver was a suite of "sanity check scripts" - SQL queries that would determine if the database was in a consistent state. In our system, we dealt mostly with time cards, invoices, payments etc. - so we had scripts to make sure timecards were submitted only by current employees, that invoices reflected the total debt for the customer at the time they were issued, that all employees had a billable rate greater than zero but less than a million, etc.

我们每次有人做模式更改时都运行健全性检查脚本，每次任何人想要运行手动数据库更新 - 在更新之前（以确保一切都一致）和更新后。

We'd run the sanity check scripts every time anyone made a schema change, and every time anyone wanted to run a manual database update - both before the update (to make sure everything was consistent), and after the update.

如果我们发现一个数据库一致性错误没有被正确的脚本拾取，我们将添加一个检查该特定问题;这些年来，脚本增长到几百个查询。

If we ever found a database consistency bug that wasn't picked up by the sanity scripts, we'd add a check for that particular issue; the scripts grew to several hundred queries over the years.

我们的一般程序是：

• 备份资料库
• 
  
• 运行健全性检查脚本


• 每次更改
  
  
  
  • 开始事务
  
  
  • SQL查询以确定应受影响的行数
  
  
  • SQL更新/插入/删除
  
  
  • 受到影响
  
  
  • 如果实际受到影响则提交事务==预期受影响
  
  
  
  
  
  • back up database(s)
  • run sanity check scripts
  • for each change
    • begin transaction
    • SQL query to establish how many rows should be affected
    • SQL query to update/insert/delete
    • SQL query to establish how many rows were affected
    • commit transaction if actual affected == expected affected
    • roll back transaction if actual affected <> expected affected

    这篇关于手动更新生产数据库的最佳做法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！