SQL Server Disaster recovery: hit historic data

This is one of the tasks that I hope you never face: You never need to recreate data at different points in time to clarify a suspicious action or to uncover lost or stolen data. Most databases store data on the core data tier, showing only the most recent state of data for end users and database administrators. This means you can only see the latest version of the data, and you can't identify the whereabouts of specific data at different points in the data lifecycle.

As a database administrator and advisor, I see that many databases store only the current snapshot of data, not the data rows for each historical period in which the data has changed in its lifecycle. In most cases, this is good for the database, because one iteration of each transaction will make your database 100 to 1000 times times larger than it is today. This is because it requires that the database is at a manageable level, that historical data rows are not usually stored and are not easily recreated.

The financial sector has adopted the opposite approach. Not just the most recent state of storing data, it also stores every transaction that occurs and restores the entry to the previous change. This means that the data is written, but it will never change. Any historical point of time can be easily displayed, and additional operations such as data need to be recreated. From a purely sensory perspective, the change in financial data is not as frequent as the other data you store in the database. That is, you should investigate what historical data you need to retrieve, and what types of data you need to store the latest version.

There are also such tools on the market, such as SQL compliance Manager of Lumigent Technologies Auditdb and Idera, which allows you to capture every phase of every change that occurs in a database. It uses a lot of space to store data, and only with the tools mentioned above can you retrieve different states of data over time-unless you modify your application to store each historical data row. Of course, there are other options, such as using triggers to capture each data change, but, still, your storage space needs to be large because it requires a lot of your server when using triggers.

By not using tools or modifying your application to capture every historical data row, you'll be left with endless pain and infinite hassle to try to recreate your data. A few years ago, I had the task of recreating the health records of a few years ago to detect suspicious behavior. At the time, the aforementioned tools did not exist, and I tried to use triggers and additional storage requirements that were not available.

The process of recreating each view of a historically specific collection of data begins with the retrieval of archived backup tapes. To our horror, we were told that only one tape per month was used for long-term storage, so we could only create snapshots once a month. When we started to reload the tapes, we were again frustrated to find that some of the tapes were unreadable. At that time the database was only 10GB in size, but it needed to be stored over and over again, and the data to be captured needed to be stored in the right place because these were 9GB disk-driven times and there was not enough storage space. Today, the 10GB is a very small number. The size of the database now ranges from 100GB to 500GB. So, even if there is a larger drive, the whole problem still exists.

I know that the task of recreating historical data is not always the case, but I also know that I have faced this challenge several times. As a database administrator, it is your responsibility to protect your data and help your company make it as much as possible. To understand the importance of real needs and data, you must ask some questions to help you determine your needs. Based on what you've learned, taking action in the right place will ensure that you can recreate what you need.

Again, here are 3 options to consider what is possible and what is not possible:

Third-party tools, such as Lumigent's Auditdb or Idera SQL compliance Manager

Use triggers or modify other applications

Methods of backup and re-storage

Depending on your choice, you need to understand what is possible and what is impossible. By using Third-party tools, you can recreate each occurrence of the change. These tools are built in business processes to minimize the performance impact on servers and databases, allowing you to selectively capture important data. Using triggers or other modified applications is another good choice, but if your system is very busy, your performance will be greatly affected if you use this approach.

The last way to use backup and storage is to investigate so you can understand the long-term backup storage. Find out how long backups are stored, what types of backups are stored, and the likelihood that you'll be storing all the steps again. Even if you have a full backup in one day, you still have a potential risk of losing one day's changes, so you need to recover the changes on that day. In the case I'm involved in, there are many, many actions that can occur every month that cannot be recreated.

The days of recreating computer records based on Paper-based records are gone. More and more information is only crawled online. Without proper action, the data will be lost forever and people will never know what happened. As a database administrator, you need to understand your role, keep the system online, your data, and in fact the company-wide patron.