Something that is very important for administration of large systems and databases are logs.
Logs in the sense, log files as well as log of all transactions by maintaining history tables, in case of databases.
Why is it that logs are so important?From the point of view of administrator of DB or any system, various logs allow him/her to monitor parameters, activities etc and trace back reasons for any errors/malicious activities.It also keeps the administrator safe from accusations, as he would have immediate proof of all activities.
In case of DBs, it helps capture historic data either for analysis at a later stage, or for the same reasons as mentioned above. It also gives indications when and why your application/system behave in ways it was not meant to.
It is an excellent guide for debugging, and sometimes the only resource in your disposal to identify and correct major issues. If you don't have a robust logging mechanism in place when disaster befalls your crucial data, you better start looking for a new job( or even worse ).
How and what to logNow, w.r.t a system related more to users,files and devices, logs must be present to capture login, file related and device related data. All major changes to any of these must be recorded for maximum debugging and recovery ability. A standard Unix server which hosts files for multiple users and allows access to multiple devices is a an ideal place to learn about good administrative practices and policies. There will always be a competent administrator behind every well-administered Unix system.
But in case of Windows servers, I won't say that much. The minimal administrative tasks that are configurable will certainly allow the administrator to handle 80-90% of the scenarios smoothly. But for the remaining 10% , a call to Microsoft tech support might be required, which might only be 50% successful in resolving the issue. But they will 90% of the time be successful in closing the request you raise, irrespective of whether they solved your problem or not. Anyway, lets not stray from the topic( I just couldn't resist ).
In a production database handling millions of records with 100+ columns on an average in each table, database designers end up loading a huge amount of logic flows. In this scenario,if any one of these flows end up being wrong, and data has already gone into the DB, it is a hellish task to restore it without substantial downtime. This is where logging and history tables come into the picture.
There are many things that need logging which are dependent upon the business logic of the database. Excluding that, the other things that require logging are DB usage details, abnormal disk/memory usages, logins, transactions etc. Most of the databases have system tables to record almost all these information. But sometimes, the designer/administrator will have to incorporate additional logging tables to record some indirectly available data.
History tables are an excellent tool for debugging and analysis. But,they take up lots of disk space. Sometimes, it is acceptable to archive or purge historical data. In that case, regular maintenance tasks can be scheduled to trim the history tables. But in cases where data is required, we might have to think of extra storage facilities or mechanisms to automatically archive and restore data. Those solutions do have their own processing requirements and hence provide extra overhead, but sometimes are the only solutions suited to the business need.
So, whenever you design a database for transactions involving lots of data, design a robust history logging mechanism as well. You will not regret this decision. It helps in validating logic changes to procedures, migration packages, schema changes etc.
As an administrator or designer of either Operating systems, Web Servers or Databases , it is a very good policy to maintain proper logs/history tables and utilize them effectively to get the required information out of complex systems, and make your life easier. Don't end up giving yourself a nervous breakdown ;)
