Administrators of Oracle , MySQL , MS-SQL , DB2 , Postgress or any other database technology have following duties in common to deal, with only change in architecture and command lines to manage them.
1. Monitoring
Database monitoring has huge significance in database administrations , if not done efficiently it can lead to service outage i.e. application unavailability to users.
Days are gone when DBA used to monitor the database manually through a checklist after every hour or two and making note of DB server load , locking/ blocking sessions , OS mount point utilization , tablespace / container space consumption , Archive log utilization , errors in logs etc .
Monitoring is automated now using tools like OEM / Cloud control , Tivoli , Graphite etc. which has capability to monitor the database 24*7 and generate the event which will further be integrated with ticketing tool to raise an incident ticket or email/message notification for immediate action based on criticality of the issue.
DBA needs to ensure that no production alert is taken lightly or ignored as it may turn out into tragedy later if left open or not taken action within short time span of its first occurrence itself. As during outage post mortem (analysis done to identify root cause of issue) first pin point lands on monitoring and later on the change or dependency which caused it.
2. User Management
As a part of access management in database , dba needs to create , modify or delete users having privilege to read/write data written by application.
Every user created in database belongs to certain set of group called as roles based on common interest of data requirements. Actions that it can perform on data tables or code is decided by privilege as simple as read , write or execute. In database terms their is Data Manipulation (DML) & Data definition language (DDL) instead of read/write.
In User management various attributes linked to the user which is called user profile which decides connection or password related attributes.
it is most important that database users are audited on periodic basis for their capabilities and existence to avoid security breach.
3. Data Management
Every database technology has their own concept of storing data in databases such as Oracle uses data files , table spaces ; MySQL stores using MyISAM technique to store data in .MYD / . MYI files ; DB2 stores data in container ; Postgress in PGDATA in separate file for each table / index etc.
data management is surrounded by various topics such as availability , security , transfer & storage. Database stores data in their own format which can be accessible by internal framework processes and through SQL or NoSQL command only.
Sufficient storage space , configuration and disk IOPS decides overall availability and performance of database.
Historic data archival is done for regulatory requirement and for faster access to current data.
4. Installation
There are two different type of streams while working as a dba for any project i.e. project & operations. Project dba looks into new developments which includes Installation and operation dba manages the existing setup without new major changes. Many dba's fall in both roles based on requirements.
Installation task includes deploying new database software or a code in to production or any other setup. Prior to installation many pre-requisites to be fulfilled at operation system , network and storage level in order to receive no/less warnings/errors during installation. Installation has various post tasks such as running few scripts for inventory, license addition etc. and many more actions like creating a database , listener.
Now days this task is disappearing from dba due to emerging technologies in cloud
5. Patching
Similar like our mobile operating systems and Apps , database software also need patching on regular basis. Patches are released by the product company quarterly or half yearly or sometime more frequently to keep the software protected from latest bugs , security threats.
DBA need to ensure that their production database software is at-least at n-1 patch-set for particular release, where n is the latest patch.
Patching has been automated in cloud environment however it is not scheduled at fixed freq. as the downtime required for patching may vary as per business.
In High availability environments where presence of more than one node for a database , patching can be done online without need of downtime to database service such patching is called rolling patch.
6. Up gradation
Upgrading a database software is most critical part of the life-cycle management , in which database , binaries , libraries are upgraded to new release of the database.
Most of the time upgrade is done out of place where installation location changes and database services moves to new release. Up gradation is considered as a Project dba task but operation dba can assess the impact of database upgrade on application and dependent components very well.
Up gradation is necessary to enhance the features of the database in terms of performance & security and many time to be compliant. It is considered to be major change and attracts many other dependent changes in monitoring , backup , performance tuning etc.
7. Replication
Database replication is required to keep dual sites in sync in case of disaster. Replication database environments includes primary and secondary sites , few setup may have cascaded standby sites to make it more robust.
Primary site is the original database serving application/ client requests , and every transaction flows to standby site to keep it in sync. In case of disaster the standby site can be brought up as a new primary and all requests are diverted to maintain availability.
DBA has very crucial task of maintaining the replication without letting it go out of sync , if so happens and detected very late then standby needs to be rebuild from latest backup of primary. Also the occasional switchover to be performed as a part of regulatory requirements and to build confidence in engg. through this drill to baseline the plan of action for disaster recovery.
8. Clustering
Unlike replication , in clustering more than one node resides on primary sites hosted on shared storage and serves the client request in parallel to balance the load.
Clustering mainly contains shared storage , high speed network , more than one server hosting the database nodes.
In case one node of the cluster goes down due to outage then the connections gets fail-over to other serving node transparently provided that application is compatible to clustering environment.
Clustering doesn't improve the sql performance but it distributes the load almost equally on all serving nodes of the cluster.
Clustering helps to keep service available in all possible outages except storage. If storage outage happens which brings down the disks then whole cluster goes down which rarely happens though.
9. Performance Tuning
Performance Tuning keeps dba all the time busy as its a ultimate motive to keep database available and tuned too.
Various performance tuning approach such IO , segment , sql , memory , parameter exists , however SQL tuning has greater impact.
Tuning helps to improve overall application performance, many reference reports are made available to dba and developers to identify the performance bottleneck in database which could be either due to SQL or database configuration as well.
Tuning exercise needs high amount of testing in lower environment before gets roll out in production for users. As many of the tuning solution may lead to adverse effect in the area which is not considered.
SQL performance tuning generally shared between developers and dba , by appropriately writing the sql code keeping performance in mind.
10. Backup & Recovery
The most important task after monitoring is the backup & recovery support. Although in large setup backups are scheduled through media library & mangers which is the responsibility of backup engg. but DBA is responsible in identifying backup strategy keeping recovery point objective & recovery time objects in mind.
Recovery point objectives (RPO) refer to your company’s loss tolerance: the amount of data that can be lost before significant harm to the business occurs. The objective is expressed as a time measurement from the loss event to the most recent preceding backup.
Recovery Time objective (RTO) refers to how much time an application can be down without causing significant damage to the business. Some applications can be down for days without significant consequences. Some high priority applications can only be down for a few seconds without incurring employee irritation, customer anger and lost business.
Backups can be logical such as table/code level backup or physical such as complete disk backup. Each backup method has different purpose , logical backups are for recovering from human error such as loss of code or accidental table deletion and physical backups are to recover from disk failure or for rebuilding the site etc.
Recovery involves restoring the backup image from media servers and applying all committed transaction logs to database to make it live. Recovery drills are important to test media tapes or storage capability along with baseline of recovery procedures.
Also Read ..
No comments:
Post a Comment