We want to share a story that happened on one of our projects on New Year's Eve. The essence of the project is that it automates the work of doctors in medical institutions. During the patient's visit, the doctor records information on a dictaphone, then the audio recording is transcribed. After the transcription process - i.e. making audio recordings into text - a medical document is compiled according to relevant standards and sent back to the clinic, from which the audio recording came, where the sending doctor receives it, checks and confirms. After passing the mandatory checks, the document is sent to the final patients.
 3r33832.
 3r33832. All medical institutions using the product can be divided into two large groups: 3r3809.  3r33832.
 3r33832. 3r33811.  3r33832.
Hosting in the data center of our client, who is fully responsible for the performance of the application, both for the software and for the hardware. For example, if disk space runs out, or there is not enough server performance on the CPU;
 3r33832.
Self-hosted: they place all the equipment directly at themselves and are responsible for its performance. Our client provides them with the application and its support.
 3r33832. 3r33825.
 3r33832. This is how our team interacts with end servers hosted directly in our client’s cloud.
 3r33832.
 3r33832.  3r33832.
 3r33832. We have access to these servers to carry out all the planned work and maintenance that is required.
 3r33832.
 3r33832. The second group is self-hosted clients - for them the client cloud acts as a gateway through which we connect to these servers. We have limited rights in this case, and often we cannot perform any operations due to security settings. We connect to the servers via RDP - Remote Desktop Protocol on Windows OS. Naturally, this all works through VPN.
 3r33832.
 3r33832. Here we must bear in mind that each server represented in the diagram is in fact a bundle of an application server and a database server. On the database server, MS SQL Server DBMS and SSRS reporting service are installed respectively. Moreover, the MSSQL Server version is different in all clinics: 200? 201? 2014. In addition to the versions themselves, various Service Packs and patches are installed everywhere. In general, a complete zoo.
 3r33832.
 3r33832. On the application server we have installed a web server IIS and ElasticSearch. ElasticSearch is a search engine, which also implements full-text search.
 3r33832. The main entity in terms of our product is “job” (job). Work is an abstract entity that binds together all the information related to a particular patient's reception. This information includes:
 3r33832.
 3r33832. 3r33811.  3r33832.
data about the doctor;
 3r33832.
patient information;
 3r33832.
information about the visit;
 3r33832.
audio file (doctor's speech);
 3r33832.
documents (several versions);
 3r33832.
work processing history;
 3r33832.
separation data, etc.
 3r33832. 3r33825.
 3r33832. This diagram shows a simplified DB schema from which you can see the relationships between the main tables. This is only the base part, in fact, there are more than 200 tables in the database.
 3r33832.
 3r33832.  3r33832.
 3r33832. A little about the clinic where the incident happened:
 3r33832.
 3r33832. 3r33811.  3r33832.
1500-2000 works per day;
 3r33832.
1000+ active users (doctors + secretaries);
 3r33832.
Self-hosted.
 3r33832. 3r33825.
 3r33832. DB: 3r3809.  3r33832.
 3r33832. 3r33811.  3r33832.
Size: 800+ Gb (750K + works, 2M + documents);
 3r33832.
DBMS: MS SQL Server 2008 R2;
 3r33832.
Recovery Model: Simple.
 3r33832. 3r33825.
 3r33832. Here I want to make a little explanation. There are 3 recovery models in SQL Server: simple, bulk-logged, and full. I will not speak about the third now, I will explain about the first and second. The main difference is that in the simple model, we do not keep a transaction history in the log — once the transaction has been committed, the record from the transaction log will be deleted. When using the full recovery mode, the entire history of data changes is stored in the transaction log. What does this give us? In case of some unforeseen situation, when we need to roll back the database from the backup, we can return not only to some specific backup, but we can return to any point in time, up to a certain transaction, i.e. In backups, not only is a certain state of the database at the time of backup, but also the whole is a history of data changes.
 3r33832.
 3r33832. I think it is not necessary to explain that the simple mode is used only in development, on test servers and its use in production is unacceptable. Not at all.
 3r33832.
 3r33832. But the clinic, apparently, had his own thoughts on this subject;)
 3r33832.
 3r33832.
Start 3r3808.
 3r33832. A few days later, the New Year, everyone is preparing for the holiday, buying presents, decorating Christmas trees, conducting corporate events and waiting for a long weekend.
 3r33832.
 3r33832. 3r33737. December 22 (Friday) 1 day
 3r33832.
14:31
The client reported that he had not received the regular daily report. The report arrives at the post office twice a day according to a schedule. It is necessary to control the sending of data to an external integration system, which is not too critical.
 3r33832.
 3r33832. There could be several reasons:
 3r33832.
 3r33832.
 3r33832.
Problems with SMTP, letters simply were not delivered (changed the password, for example, and did not tell anyone);
 3r33832.
Problems on the server side of the report;
 3r33832.
Something is wrong with the database.
 3r33832. 3r3168.
 3r33832.
16:03
The clinic sometimes changes the password to SMTP, without warning anyone about it, so after finishing the current tasks, we calmly check the report manually by launching via the web interface - we get an error that indicates problems in the database.
 3r33832.
 3r33832. An example of an error that we received when running the report.
 3r33832.
 3r33832. 3r3181. 3r3182. SQL Server detected a logical consistency-based error /error: incorrect checksum (expected: 0x9876641f; actual: 0xa3255fbf). It occurred during a read of page (1: 876) in database ID 7 at offset 0x000000006d8000 in file 'D: Program Files Microsoft SQL Server MSSQL 10_50.MSSQLSERVERMSSQLDATAServerLive.mdf'. 3r3183. 3r3184.
 3r33832. This indicates that there are damaged pages in the database. We had a slight feeling of anxiety.
 3r33832.
 3r33832.
20:53
In order to assess the extent of damage, we run a database check using the special command
DBCC CHECKDB
3r3802. . Depending on the size of the damage, the verification team can take quite a long time, so we launch the command in the night. Here we are lucky that this happened on Friday afternoon, that is, we had at least the whole weekend to solve this problem.
 3r33832.
 3r33832. At that moment, the situation was as follows:
 3r33832.
 3r33832.  3r33832.
 3r33832. 3r33737. December 23 (Saturday) 2nd day
 3r33832.
10:02
In the morning, we find out that checking the database with the help of CHECKDB has stopped - this was due to the lack of free disk space, since the tempdb temporary database is actively used in the verification process, and at some point the free disk space just ran out.
 3r33832.
 3r33832. Therefore, instead of checking the entire database, we decide to immediately launch a public check. To do this, use the command
DBCC CHECKTABLE
3r3802. .
 3r33832.
 3r33832.
10:46 3r3802. We decide to start with the JobHistory table, which is probably corrupted, because it was used when generating the report. This table, as can be understood from the title, keeps the history of all works, i.e., transitions of work between stages.
 3r33832.
 3r33832. We start
DBCC CHECKTABLE ('dbo.JobHistory')
3r3802. .
 3r33832.
 3r33832. Checking this table reveals damaged tables in the database, which in principle was expected.
 3r33832.
 3r33832.
12:00 pm 3r3802. At the moment, if the database used the full recovery model, we could restore the damaged pages from the backup, and that would be the end of it, but our database was in simple mode. Therefore, the only option for repairing damage is to launch the same command with a special parameter 3r3-3011.
REPAIR_ALLOW_DATA_LOSS
3r3802. . In this case, data loss is possible.
 3r33832.
 3r33832. We start. The check again fails - we get an error that the recovery of this table is impossible until the linked tables are restored. The history table refers to the work table (Jobs) by the foreign key, therefore, we conclude that there is also damage in the main work table (Jobs).
 3r33832.
 3r33832.
1:30 p.m. The next step is to check the Jobs table, while hoping that the damage is in the index, not in the data. In this case, it will be enough for us to simply rebuild the index for data recovery.
 3r33832.
 3r33832.
17:33
After some time, we discover that our server is not accessible via RDP. It was probably turned off, the check was not completed, the work was suspended. We inform the clinic that the server is unavailable, please raise it.
 3r33832.
 3r33832. Light anxiety takes very specific forms.
 3r33832.
 3r33832. 3r33737. December 24 (Sunday) 3rd day
 3r33832.
14:31
Closer to the dinner server raise, re-start checking the table Jobs.
 3r33832.
DBCC CHECKTABLE ('dbo.Jobs')
3r3802.
 3r33832.
 3r33832.
16:05 3r3802. The check is not completed, the server is unavailable. Again.
 3r33832.
 3r33832. After some time, the server is again unavailable, before we could complete the check of the table. At this moment, the IT clinic service performs a series of server checks. We are waiting for the completion of work.
 3r33832.
 3r33832. Due to holidays, communication between us and the client was slow - we were waiting for answers to questions for several hours.
 3r33832.
 3r33832. 3r33737. December 25 (Monday - Christmas) 4th day
 3r33832.
4:00 p.m. The next day, the server was raised, the client has Christmas, and we again start checking the table, but this time we exclude the indexes from the check, and leave only the data check. And after a while the server is again unavailable.
 3r33832.
 3r33832. What is going on?
 3r33832.
 3r33832. At that moment, thoughts begin to creep in that this is all not just a coincidence and there is a suspicion that it could be damage at the level of iron (a hard disk fell down). We assume that there are bad sectors on the disk and when the check tries to read data from these sectors, the system crashes. We inform on the assumption to the client.
 3r33832.
 3r33832. The client runs a disk check on the host machine.
 3r33832.
 3r33832.
17:19
IT clinic service reported that the virtual machine file is damaged - this is bad! (
 3r33832. We still can not work, and wait for the signal when they fix the problem and we can continue our work.
 3r33832.
 3r33832. 3r33737. December 26 (Tuesday) 5th day
 3r33832.
14:05 3r3802. IT clinic service starts another disk recovery process. We were told that we can run CHECKTABLE in parallel to check the table. We start the scan again - the virtual is crashing again, we inform the client that the virtual machine file is still damaged.
 3r33832.
 3r33832. These days all communications with the customer are very slow with a huge time lag due to holidays.
 3r33832.
 3r33832. 3r33737. December 27 (Wednesday) 6th day
 3r33832.
2pm 3r3802. We start disk check by means of Windows -
checkdisk
inside the virtual machine - no problems identified.
 3r33832. The database is in the Simple mode, so the chances of correcting the current database by means of the DBMS tend to zero, since we are unable to repair individual damaged pages.
 3r33832. We begin to consider the option of rolling back and restoring the database from backup.
 3r33832. We check the backups of the database and find that the backups were not made by the DBMS, the last backup was in 201? i.e. There are no backups. Why not done - a separate question, it is the responsibility of the clinic to ensure the health and safety of the database.
 3r33832.
 3r33832. There is a high probability that it will not be possible to restore the current database, we begin to consider other options for rollback.
 3r33832.
 3r33832. Let's take a closer look at the situation with backups in the clinic.
 3r33832.
 3r33832. The situation with backups:
 3r33832.
 3r33832. 3r33811.  3r33832.
There is no backup database (!!!) 3r3823.  3r33832.
There is no snapshot of a virtual machine either (!?) 3r3823.  3r33832.
But there are disk backups (full + inc)
 3r33832. 3r33825.
 3r33832. The database is on disk D, respectively, they made weekly full backups and daily incremental backups.
 3r33832.
 3r33832. 3r33811.  3r33832.
every Friday at 20:00 full backup
 3r33832.
every day incremental backup
 3r33832.
There is a full backup from the 15th and the 22nd of
 3r33832.
There are daily backups up to the 21st of
 3r33832. 3r33825.
 3r33832. Those. in principle, we can roll back to the state before the problem occurs.
 3r33832. We are waiting for the update from the clinic to start rolling back the database from the backup.
 3r33832.
 3r33832. At the same time, the clinic sent a request to an iron supplier (HP) marked “urgent”.
 3r33832.
 3r33832. 3r33737. December 28 (Thursday) 7th day
 3r33832.
13:13
IT clinic service begins setting up a new virtual machine, because Fix the damage in the file of the old virtual machine does not work.
 3r33832.
 3r33832.
19:09 3r3802. A new virtual machine is available with SQL Server installed.
 3r33832. The next step is to restore the database from the disk backup. To begin, we decide to roll back to the 22nd, if the problem is still present, then we roll back to the 21st, 20th, and so on, until we reach a working state.
 3r33832.
 3r33832. It was the 28th in the courtyard, we are at a corporate party, and here we are informed that the clinic has problems with restoring the backup, because the backups are EMPTY!
 3r33832.
 3r33832. Here is the news!
 3r33832.
 3r33832. During recovery of the backup of disk D from the 21st, it turns out that it is empty, like all the others. Directly backups Schrödinger obtained - they seem to be as it is, but at the same time, they are not. It is not completely clear how this happened at all, but, as far as we were able to understand, the problem is not enough space on the disk to store disk backups. They allocated 500 Gb backups for storage, but at the time of the incident the database already weighed 800 Gb, therefore, in principle, the backup could not be passed successfully. Those. backups were regularly done on a schedule, but due to lack of space, they ended up with an error and were therefore empty, and the IT-service of the clinic did not even bother to check that everything was fine with them. Do not do it this way.
 3r33832.
 3r33832. 3r33737. December 29 (Friday) 8th day
 3r33832.
13:11
Discussion of further action. Possible options:
 3r33832.
 3r33832. 3r33811.  3r33832.
Trying to copy database files (.mdf + .two files) - the chances of success are very low;
 3r33832.
Trying to make a backup database - again, the chances are very small;
 3r33832.
Set up replication - may work.
 3r33832. 3r33825.
 3r33832. On the new server, 1 Tb disk was allocated, which is obviously not enough if we try to backup and restore from it, because in the worst case, without compression, the backup will take up as much space as the original database, i.e. 800 Gb.
 3r33832. Please add space on the new server and proceed to copying the database files.
 3r33832. On the new server, a database has been created and the database schema has been restored - this will at least allow processing new jobs. The clinic will at least be able to accept new patients using this system.
 3r33832.
 3r33832.
14:36 ​​
Therefore, we proceed to option number one, although we don’t expect much success.
 3r33832. Stop the SQL Server, start copying the data file (mdf) and log (ldf).
 3r33832.
 3r33832.
16:13
After one and a half, the log file was copied successfully (48 Gb), and 50 GB of data file were already copied (795 of 846 GB left). At this speed, it will take about 12 hours to complete the copy.
 3r33832.
 3r33832.
4:30 p.m. The old database server was turned off while copying the file, which is quite expected.
 3r33832.
 3r33832.
17:09 3r3802. Therefore, we move on to the next option — setting up replication; in this case, we can specify which data will be replicated, that is, we can first eliminate the obviously damaged tables and copy the intact data first, and then transfer the problem tables in parts. But this option, unfortunately, also does not work, since we cannot even create a publication with certain tables due to database damage.
 3r33832. Consider more options for transferring data.
 3r33832.
 3r33832.
20:01
As a result, we start simply to transfer data from the old to the new server in a regular way by importing and exporting in order of priority.
 3r33832.
 3r33832.
21:35
First, the most critical data, then archived and less critical (~ 300 GB). In the first wave of exports, less than 300 GB of data remained. The Documents table (300GB) is also excluded. Run the copy process in the night.
 3r33832.
 3r33832. 3r33737. December 30th (Saturday) 9th day 3r3r7661.
 3r33832.
15:00
We continue to transfer data. Jobs table is not available at all. The main part of the tables was copied by this time.
 3r33832. But without 3r30101. Jobs 3r3802. it's all useless, because it is the main link between all the data and gives them meaning and value from a business point of view. Without it, we simply have a fragmented set of data that we can’t use.
 3r33832.
 3r33832. Also, at this point, the restoration of the database schema has been completed.
 3r33832.
 3r33832.
Consequences of the incident:
 3r33832.
 3r33832. At this point we have a huge loss of live data.
 3r33832. Those. Formally, we have some data in the database, but, in fact, there is no way to use them or link them together, so we can talk about complete data loss.
 3r33832. More than 75?000 patient admissions have been lost.
 3r33832.
 3r33832. This is really sad!
 3r33832.
 3r33832. 3r33811.  3r33832.
This is a huge blow to the reputation of our client, which can turn into big business problems for them when entering into new contracts and finding new customers.
 3r33832.
The loss of such amount of data for the clinic can face serious problems and fines, because This is confidential data containing medical confidentiality and, in a direct sense, people's lives depend on it.
 3r33832. 3r33825.
 3r33832. We began to think what we can do in this situation. Began to sort through the system to find clues.
 3r33832.
 3r33832.
15:16
Analyzing all aspects of the system, we understand that we can try to extract the missing data from the ElasticSearch index. The fact of the matter is that due to the incorrect configuration of the ElasticSearch indexes, it stores not only the fields for which full-text search is performed, but everything, that is, in fact there is a complete copy of the data and we can theoretically extract data from there about jobs and put them back into our database. It is hoped that the data will still be recovered.
 3r33832.
 3r33832. A bug that you can put a monument!
 3r33832.
 3r33832. 3r33588.
 3r33832.
 3r33832.
18:00
A utility for extracting data was quickly written, and after a few hours we make sure that the approach works and the data will be restored.
 3r33832.
 3r33832.
20:00
Started restoring work from ElasticSearch using a written utility. The approach worked, data about the work can be restored. In parallel, we begin to extract the latest versions of the document for each work.
 3r33832.
 3r33832. 3r33737. December 31 (Sunday - New Year) 10th day
 3r33832.
14:09 3r3802. Overnight, 18?811 works were restored.
 3r33832.
 3r33832.
20:13
Seeing our success, the clinic decides to postpone the transfer of the server to the HP service in order to give us time to extract the maximum data from the old server.
 3r33832. With such news we met the New Year))
 3r33832.
 3r33832. 3r33737. January 1 (Monday) 11th day
 3r33832.
11:23
Preparations are underway to start the system after the incident: 3r3809.  3r33832.
 3r33832. 3r33811.  3r33832.
reconfigured IIS on the App server;
 3r33832.
migrated all the necessary services to work with the new database server;
 3r33832.
restored triggers, stored procedures, functions.
 3r33832. 3r33825.
 3r33832.
14:28 3r3802. Then they began to copy the table of documents, which was skipped during the initial transfer due to its large size.
 3r33832. - The old DB server has shut down again. It is obvious that the Documents table is also damaged, it is with it that all information on patients is stored. Fortunately, it is not completely damaged, we can make requests to it, and when a request to us returns a damaged record, at this moment the server crashes and turns off. Part of the data in this case we can extract.
 3r33832.
 3r33832. Accordingly, we signal the client, they raise the server, and in parallel we continue to prepare the new database for the launch of the system.
 3r33832.
 3r33832.
18:01
Restoring all integrity constraints after transferring the main part of the data.
 3r33832.
 3r33832.
22:02
Restoring restrictions complete. We just carried the raw data to the maximum. The presence of integrity constraints would greatly complicate our task.
 3r33832.
 3r33832. 3r33737. January 02 (Tuesday) 12th day
 3r33832.
05:52 3r3802. The old DB server turned off again while copying the document. He is promptly picked up so that we can continue working.
 3r33832.
 3r33832.
09:00 3r3802. It was possible to recover in a batch about ??? documents (approximately 20%)
 3r33832. We started using different recovery methods: sorting by different columns to retrieve data from the end or beginning of the table, until we hit some damaged part of the table.
 3r33832.
 3r33832.
13:42
Started copying archival works in the table - it is, fortunately, not damaged.
 3r33832.
 3r33832.
17:08 3r3802. All archive work has been restored (49?380 pieces).
 3r33832.
 3r33832. The system is ready to launch: users can create and process new jobs.
 3r33832.
 3r33832. Unfortunately, due to partial damage to the table of documents, you can not just transfer all the data from it, as with other tables, because The table is partially damaged. Therefore, when you try to extract all the data, the request falls with an error when trying to read damaged pages. Therefore, we extract the data point by point using different sorts and sample size:
 3r33832.
 3r33832. 3r33811.  3r33832.
Sort by different fields (ID, DateTime);
 3r33832.
Sort ascending, descending;
 3r33832.
Work with small groups of lines (100? 100);
 3r33832.
Retrieving jobs by id.
 3r33832. 3r33825.
 3r33832.
 3r33832. 3r33737. January 3 (Wednesday) the 13th day of 3r3761.
 3r33832.
08:58 3r3802. Continued the process of recovering documents. Documents were restored only for active, unfinished work. By this time 1000 works (active) without documents.
 3r33832.
 3r33832.
11:38 3r3802. Moved all SQL Jobs
 3r33832.
 3r33832.
13:17
5 works without documents, 231 work is missing, but there is an audio file, you need to resynchronize.
 3r33832.
 3r33832. 3r33737. January 4 (Thursday) the 14th day of
 3r33832. Started manual restoration and verification of remaining work.
 3r33832. The system works, monitoring and correction of errors in online mode.
 3r33832.
 3r33832. 3r33737. January 5 (Friday) 15th day
 3r33832. Reports are scheduled for migration to SSRS.
 3r33832. Transfer to the new server is impossible, because In the clinic, an older version of SQL Server was installed and the database could not be moved from the old server.
 3r33832.
 3r33832. Options: 3r3809.  3r33832.
 3r33832. 3r33811.  3r33832.
Upgrade SQL Server from 2008 to 2008 R2;
 3r33832.
Customize everything from scratch.
 3r33832. 3r33825.
 3r33832. It was decided to wait for the update of SQL Server.
 3r33832.
 3r33832.
09:21 3r3802. Background restoration of documents for completed works started - the process is long and will take several days.
 3r33832.
 3r33832.
13:28 3r3802. Change the priority of recovery documents for departments.
 3r33832.
 3r33832.
18:18
The clinic gave access to SMTP, mail setup 3r3809.  3r33832.
 3r33832.
Result: 3r3808.
 3r33832. 3r33811.  3r33832.
Almost all data was recovered (only 5 works were lost);
 3r33832.
Recommendations on database maintenance were issued to prevent such situations;
 3r33832.
Configured database backups using SQL Server;
 3r33832.
Additional monitoring for back-ups from our side, alerts in case of file.
 3r33832. 3r33825. 3r33338. 3r33832. 3r33832. 3r33832. 3r33838. ! function (e) {function t (t, n) {if (! (n in e)) {for (var r, a = e.document, i = a.scripts, o = i.length; o-- ;) if (-1! == i[o].src.indexOf (t)) {r = i[o]; break} if (! r) {r = a.createElement ("script"), r.type = "text /jаvascript", r.async =! ? r.defer =! ? r.src = t, r.charset = "UTF-8"; var d = function () {var e = a.getElementsByTagName ("script")[0]; e.parentNode.insertBefore (r, e)}; "[object Opera]" == e.opera? a.addEventListener? a.addEventListener ("DOMContentLoaded", d,! 1): e.attachEvent ("onload", d ): d ()}}} t ("//mediator.mail.ru/script/2820404/"""_mediator") () (); 3r33831. 3r33832. 3r33338.