如何不断交付基于SQL的应用程序？ [英] How to continuously delivery SQL-based app?
问题描述
我想将连续交付概念应用于我们正在构建的Web应用程序，并想知道是否有任何解决方案来保护数据库免受意外错误提交。例如，一个错误，擦除整个表，而不是一个单一的记录。
如何影响这个问题可以限制根据连续交付博士，其中应用程序逐步部署
首先你不能告诉刚才从看什么是一个坏的SQL语句。您可能想删除表的全部内容。因此，在物理上不可能具有检测意图的自动化工具。
因此，为了保护您的数据库，首先确保您处于完全恢复（不是简单）模式，并且每隔15分钟进行完整备份和事务日志备份。现在你不能失去很多信息，无论这个过程如何破坏。你的dbas应该训练成能够恢复到某个时间点。如果你没有任何dbas，我建议你可以做的最好的事情，以保护你的数据是雇用一些。这在任何非平凡的数据库环境中是不可协商的，如果您的数据对业务至关重要，那么没有经过培训的经验丰富的dbas是非常危险的。
接下来，您需要像任何其他代码一样对待SQL，它应该在脚本中的源代码控制中。如果您非常关注意外删除，请编写删除脚本以将所有删除复制到暂存表，并在每周左右删除暂存表的内容。在代码审查中强制实施此约定。或者更好地设置一个通过触发器运行的审计过程。一旦所有记录都被审计，在不必恢复数据库的情况下，更容易获得150个意外删除。我永远不会考虑有任何企业应用程序没有审计。
所有SQL脚本没有异常应该像其他代码一样进行代码审查。所有SQL脚本都应在QA上测试，并在移动到porduction之前传递。这将大大降低错误的可能性。没有开发人员应该有生产的写权限，只有dbas应该有。因此，每个脚本都应该编写，以便可以运行，而不是一次运行一个块，您可能会意外地忘记突出where子句。训练开发人员在脚本中正确使用事务。
I'm looking to apply continuous delivery concepts to web app we are building, and wondering if there any solution to protecting the database from accidental erroneous commit. For example, a bug that erases whole table instead of a single record.
How this issue impact can be limited according to continuous delivery doctorine, where the application deployed gradually over segments of infrastructure?
Any ideas?
Well first you cannot tell just from looking what is a bad SQL statement. You might have wanted to delete the entire contents of the table. Therefore is is not physiucally possible to have an automated tool that detects intent.
So to protect your database, first make sure you are in full recovery (not simple) mode and have full backups nightly and transaction log backups every 15 minutes or so. Now you cannot lose much information no matter how badly the process breaks. Your dbas should be trained to be able to recover to a point in time. If you don't have any dbas, I'd suggest the best thing you can do to protect your data is hire some. This is a non-negotiable in any non-trivial database environment and it is terribly risky not to have trained, experienced dbas if your data is critical to the business.
Next, you need to treat SQL like any other code, it should be in source control in scripts. If you are terribly concerned about accidental deletions, then write the scripts for deletes to copy all deletes to a staging table and delete the content of the staging table once a week or so. Enforce this convention in the code reviews. Or better yet set up an auditing process that runs through triggers. Once all records are audited, it is much easier to get back the 150 accidental deletions without having to restore a database. I would never consider having any enterprise application without auditing.
All SQL scripts without exception should be code-reviewed just like other code. All SQL scripts should be tested on QA and passed before moving to porduction. This will greatly reduce the possiblility for error. No developer should have write rights to production, only dbas should have that. Therefore each script should be written so that is can just be run, not run one chunk at a time where you could accidentally forget to highlight the where clause. Train your developers to use transactions correctly in the scripts as well.
这篇关于如何不断交付基于SQL的应用程序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
