react sql格式化_为SQL Server数据库损坏做准备；初步React与分析

Z时代
2024-01-10
分类：综合

react

react sql格式化

Corruption is a looming thought through every administrator’s mind, from sysadmins to database administrators there is always a monster hiding in the shadows. SQL Server Database corruption happens all the time around the world and while most of us have been lucky enough to avoid it, we should still be prepared. While there is no way to prevent the corruption from happening, we must work hard to implement practices that minimize damage caused by the corruption. This means good backups and of course running DBCC CHECKDB.

从系统管理员到数据库管理员，腐败是每位管理员心目中的迫在眉睫的想法，总会有一个庞然大物藏在阴影中。 SQL Server数据库损坏在世界各地无时无刻不在发生，尽管我们大多数人都有幸避免了这种情况，但我们仍应做好准备。尽管没有办法防止腐败的发生，但我们必须努力实施将腐败造成的损害降至最低的做法。这意味着备份良好，并且当然可以运行DBCC CHECKDB。

However, this is not enough to keep you out of the dog house. Many administrators do not notice they have corruption until it is to late. SQL Server does checking automatically but its only on pages that have been read into memory, if you have a large Database or do not hit those pages until year end reports it is possible your backups will not go far enough back to allow you to quickly recover that corrupted page (talked about in How to perform a page level restore in SQL Server article) or table. In short, if you notice you have corruption sooner it increases you odds of recovery with minimal to no data loss along with decreasing the amount of downtime required.

但是，这还不足以将您拒之门外。许多管理员直到很晚才注意到他们有损坏。 SQL Server会自动检查，但仅在已读入内存的页面上进行检查，如果您的数据库很大，或者直到年终报告之前才访问这些页面，则备份可能无法进行足够远的备份，无法让您快速恢复损坏的页面（在SQL Server中的如何执行页面级还原中讨论）或表。简而言之，如果您发现损坏较早，则会增加恢复的几率，而数据丢失最少甚至不会丢失，同时减少所需的停机时间。

In this series, we will discuss what to do when you encounter corruption, how to know you have a corrupted database, some basic recovery techniques and some other pointers.

在本系列中，我们将讨论遇到损坏时的处理方法，如何知道数据库已损坏，一些基本的恢复技术以及其他一些指针。

不要恐慌 (Do not panic)

When people first find out they have corruption they instinctively start to freak out. This can cause you to make some chaotic decisions that put your company in a worse position. Such as running a restore or repair with data loss as the first option, these can cause massive data and monetary losses for the company and should be only done in an absolute necessity. I invite you to read the section on running repair allow data loss on the Microsoft docs, their warning is a good enough reason to make sure you are properly monitoring your database. Describing, “no full ACID guarantee” to logical consistency and business logic needs to be validated manually after running. Additionally, according to this infographic from Small business trends, 60% of small businesses that lose data will go out of business within 6 months.

当人们第一次发现自己有贪污腐败时，他们本能地开始疯狂。这可能会导致您做出一些混乱的决定，使您的公司处境更糟。例如，以恢复数据为第一选择进行恢复或修复，这些操作可能会给公司造成大量数据和金钱损失，因此仅在绝对必要时才进行。我邀请您阅读Microsoft文档上有关运行修复允许数据丢失的部分，它们的警告是一个很好的理由，可以确保您正确地监视数据库。描述为逻辑一致性和业务逻辑的“没有完整的ACID保证”需要在运行后手动验证。此外，根据来自小型企业趋势的此信息图表，丢失数据的小型企业中有60％将在6个月内破产。

^[1]^[1]

When first receiving that corruption notification people will try to do a slew of items that will not help them.

初次收到该腐败通知时，人们会尝试做很多对他们没有帮助的项目。

Restarting SQL Server
- This just delays the problem and causes the system to run through crash recovery on the databases. Not to mention, in most systems, you will not be able to do this right away and will delay the problem further
重新启动SQL Server
- 这只会延迟问题并导致系统在数据库上通过崩溃恢复运行。更不用说，在大多数系统中，您将无法立即执行此操作，并且将进一步延迟问题的发生。

Clearing the procedure cache
清除过程缓存

Detaching and moving the SQL server to a new server
- This one will cause you a world of hurt, because SQL Server will fail to attach on the second server and on your primary. You will need to “hackattach” SQL server at this point and even then, that can be a painful experience
- This dba.stackexhange.com post discusses this method along with the limitations. Though, I am not sure why the LDF was deleted
分离SQL服务器并将其移动到新服务器
- 这将给您造成很大的伤害，因为SQL Server将无法连接到第二台服务器和主服务器上。此时，您将需要“ hackattach” SQL Server，即使那样，这仍然是一个痛苦的经历。
- 这篇dba.stackexhange.com帖子讨论了此方法及其局限性。不过，我不确定为什么删除LDF

Knowing what will or will not help in this or any situation requires that you are prepared and have been through it before. This means you will need to create corrupt databases and try to recover the data with minimal to no data loss. Later we will discuss how this can be done and what can be done to recover. However, if you are impatient, I recommend the database corruption challenge by Steve Stedman. Be warned, he dumps you into it right away with the first error being:

要知道在这种情况或任何情况下将有什么帮助或无帮助，需要您已经做好准备并且已经过了。这意味着您将需要创建损坏的数据库并尝试以最小的数据丢失甚至没有数据丢失的方式恢复数据。稍后，我们将讨论如何完成此操作以及如何进行恢复。但是，如果您不耐烦，建议使用Steve Stedman的数据库损坏挑战。受到警告，他立即将您丢弃到其中，第一个错误是：

I will stop here for a moment though, because while training and practice can go a long way, you MUST know when you might make things worse. A bit of knowledge can be a dangerous thing, while you can understand what the data errors are and what to do next, it is a better idea to get someone involved that is more knowledgeable to correct the problem if you are unsure of a solution or have hesitations. In this scenario you will give up the potential for the high praise you will get from management and team, however, it is a better idea to open a $500 ticket with Microsoft to get some senior assistance.

不过，我会在这里停留片刻，因为尽管培训和练习可以走很长一段路，但您必须知道什么时候可能会使情况变得更糟。有点知识可能是一件危险的事情，虽然您可以了解什么是数据错误以及下一步要做什么，但是最好是让一个涉及更多知识的人来解决问题，如果您不确定解决方案或解决方案。犹豫。在这种情况下，您将失去获得管理层和团队好评的潜力，但是，最好是与Microsoft共同开张$ 500的罚单以获得高级帮助。

In fact, even when knowing what the problem is, I always suggest opening a ticket with Microsoft because they will not only give an extra set of eyes on the issue but also their expertise on the subject. Additionally, Microsoft can and will assist you with next steps to help find the root cause of the problem and where the corruption originated from.

实际上，即使知道问题出在哪里，我也总是建议与Microsoft联手，因为他们不仅会更多地关注此问题，而且会针对此问题提供专业知识。此外，Microsoft可以并且将协助您进行下一步，以帮助找到问题的根本原因以及损坏的来源。

根本原因分析 (Root cause analysis)

Root cause analysis is a critical part of this process and must not be overlooked no matter how you recover from the database. This is an important step in preventing the problem from occurring again and potentially sooner than you think. In my experience, when corruption happens, it is bound to happen again if no actions are taken to remediate the problem. Additionally, this is likely to be worse the second time.

根本原因分析是此过程的关键部分，无论您如何从数据库中恢复，都不应忽视。这是防止问题再次发生并可能比您想象的更快发生的重要步骤。以我的经验，当腐败发生时，如果不采取措施纠正问题，势必会再次发生。此外，第二次情况可能更糟。

Now, I would suggest, that even if you think you know the cause of the corruption (E.G. power failure with no UPS) investigate the following sources anyways. Maybe the outage was just a facilitator and there were warning signs occurring. To begin, I always suggest these places to look.

现在，我建议即使您认为知道损坏的原因（没有UPS的EG电源故障），也应调查以下来源。中断可能只是一个促进因素，并且出现了警告信号。首先，我总是建议您寻找这些地方。

Memory and disk diagnostics to make sure there are no problems with the existing hardware
内存和磁盘诊断程序，以确保现有硬件没有问题

SQL Server error logs
SQL Server错误日志

Windows event viewer
Windows事件查看器

While rare, check with your vendors to see if they have had problems with the firmware you are using
很少见，请与您的供应商联系，以查看他们使用的固件是否有问题

Software bugs, believe it or not, Microsoft has been known to cause corruption. See KB2969896. This is where opening tickets with Microsoft are also beneficial
不管您相信与否，都发现软件错误会导致Microsoft损坏。参见KB2969896 。这是与Microsoft一起开票的地方

The event viewer and SQL server error logs can be viewed together.

事件查看器和SQL Server错误日志可以一起查看。

But, I suggest splitting these out to the system administrators as they typically have more man power on their team to review these.

但是，我建议将这些内容拆分给系统管理员，因为他们通常会在团队中拥有更多的人力来审查这些内容。

By the time you finish this series, the goal will be to that when you find out you have corruption, it is coming from your alerts, not an end user, and you will have an action plan to let your managers know where you sit and what the next steps are. This will not only help your company remain calm but also allow you to work without having someone breathing down your neck constantly.

在完成本系列文章时，目标是当您发现自己有腐败行为时，它来自警报而不是最终用户，并且您将有一个行动计划，让您的经理知道您的位置和下一步是什么。这不仅可以帮助您的公司保持冷静，还可以使您的工作而不会有人持续呼吸。

^[1] Image source: https://smallbiztrends.com/2017/04/not-prepared-for-data-loss.html

^[1]图片来源： https : //smallbiztrends.com/2017/04/not-prepared-for-data-loss.html