What Are the Different Database Administrator Tools?

Database Administrator (DBA) is a collective name for related staff engaged in the management and maintenance of the database management system (DBMS). It belongs to a branch of operation and maintenance engineers and is mainly responsible for the entire process of designing, testing, and deploying business databases. Life cycle management.

The core goals of the DBA are to ensure the stability, security, integrity and high performance of the database management system.

In other countries, DBAs are called Database Engineers by companies. The work content of the two is basically the same. Both guarantee the stable and efficient operation of the database service 7 * 24 hours, but it is necessary to distinguish between DBA and Database Developer. ):

1) The main responsibility of a database development engineer is to design and develop database management systems and database application software systems, focusing on software development;

2) The main responsibility of the DBA is to operate and manage the database management system, focusing on operation and maintenance management.

Chinese name: Database administrator
Foreign name: Database Administrator

Short name: DBA
Definition: The person responsible for managing and maintaining the database system
Related terms: Database, operation and maintenance, operation and maintenance engineer

Database administrator's main responsibilities

Database administrator narrow sense

In the general sense, the database administrator is a person who is responsible for managing and maintaining the database server. The database administrator is responsible for comprehensively managing and controlling the database system, including the basic work of database installation, monitoring, backup, and recovery. The main responsibilities of a database administrator are as follows:

Design database designs, including fields, tables, and key fields; how resources are used on auxiliary storage devices; how to add and delete files and records; and how to find and remedy losses.

2. Monitor the warning log of the monitoring database and make backup deletions regularly. Monitor daily database sessions. Monitor fragmentation and remaining table space to keep abreast of table space expansion and distribution of remaining space. Changes to monitor. Regularly list all changed objects. Install and upgrade database servers (such as Oracle, Microsoft SQLserver), and application tools. Database design system storage plan, and formulate future storage requirements plan. Develop a database backup plan and restore database information in the event of a disaster. Maintain archived or backed up data on appropriate media. Back up and restore the database. Contact the manufacturer of the database system to track technical information.

3. Backup It is very important to monitor the backup of the database and manage the backup of the database. The backup strategy of the database should be changed according to the actual requirements, and the daily backup of the data should be monitored.

4. Modify passwords: Standardize the management of database users to periodically change the passwords of important users such as administrators. For each project, a user should be established. The DBA should communicate with the corresponding project management personnel or programmers to determine how to establish the corresponding underlying database model. Finally, the DBA will uniformly manage, establish, and maintain the database. Any changes to database objects should be handled by the DBA as required.

5.SQL statement: The writing specification of the SQL statement requires an SQL statement. If it is not written satisfactorily, it will have a great impact on the database. Therefore, each programmer or corresponding staff should strictly follow the article "SQL Writing Specification" when writing the corresponding SQL statement, and finally it must have a DBA check before it can be officially run.

6. End-user service and coordination: The database administrator specifies user access rights and allocates resources for different user groups. If different users conflict with each other, the database administrator should be able to coordinate users to optimize the arrangement.

7. Database security: The database administrator can specify different access rights for different database management system users to protect the database from unauthorized access and destruction. For example, allowing one type of user to retrieve data only, while another type of user may have permission to update data and delete records. ^[1]

Database administrator broad

But in a broad sense, the responsibilities of the DBA are much larger than this. It needs to cover the entire life cycle of the product from requirements design, testing to delivery. In this process, it is not only responsible for the construction and operation of the database management system, but also to participate in the early stage. Database design, mid-term database testing and later database capacity management and performance optimization.

For start-up companies, the job of a DBA may be concurrently performed by an operation and maintenance engineer, starting from applying for a domain name, to putting servers on the shelf, configuring network devices, deploying operating systems, installing databases, designing and deploying monitoring, preventing vulnerabilities and attacks, and so on. And large companies have higher and higher requirements for DBA work. Let's look at the responsibilities of DBA work from various dimensions.

Product life cycle dimension

The DBA is responsible for the full life cycle management of the business database from design, testing to deployment and delivery ^[2] , and the responsibilities at each stage include:

Before product launch

The DBA's role at this stage is database access, which mainly includes:

1) familiar with the business of the product;

2) Product database design review: including the rationality assessment of the architecture, whether the storage capacity and performance meet the requirements, whether caching is required, whether redundant backups are required, etc., and the rationality suggestions for the database schema design are required to enable the product to meet the online release And the basic requirements for stable operation;

3) Resource assessment, including required server resources, network resources, and resource distribution, etc. At the same time, check the rationality of the product's resource budget application and control service costs;

4) Resources are in place, and the application server and basic environment / domain name are prepared in place.

2. Product Release

At this stage, the DBA is responsible for the specific work of the database release, after the specific database installation and deployment and initialization are completed and provide external services. The upgrade of the online database also belongs to the release category. At this time, the product release generally guarantees the online release, and the database upgrade is completed without interrupting external services. For large and complicated changes, there are cases where the service is suspended and then re-provisioned. However, this situation needs to be avoided by the DBA through technical means as far as possible.

3. Product operation and maintenance

Highlights of this phase include:

1) Monitoring: Real-time monitoring of the running status of the database service, including database sessions, database logs, data file fragmentation, table space monitoring, user access monitoring, etc., to find abnormal operation of the database service and resource consumption at any time; output important Daily database service running reports to assess the overall operating status of the database service and discover hidden database risks;

2) Backup: Develop and implement a database backup plan, restore database information in the event of a disaster, and maintain archives or backup data on appropriate media. The backup strategy of the database should be changed according to the actual requirements, and the daily backup of the data should be monitored.

3) Security audit: Specify different access rights for different database management system users to protect the database from unauthorized access and destruction. For example, allowing one type of user to retrieve data only, while another type of user may have permission to update data and delete records.

4) Troubleshooting: Handle any anomalies that occur in the database service in a timely manner, and try to avoid expanding the problem or even stopping the service. Prior to this, the DBA needed to formulate and deal with various service anomalies, such as computer room / network failures, program bugs, and other problems. When problems occurred, the plans could be executed automatically or manually to achieve the purpose of stopping losses.

5) Capacity management: including planning and specific implementation of resource evaluation, capacity expansion, computer room migration, and traffic scheduling after database expansion.

4. Database performance optimization

The most important point for a product to provide services to the outside world is the user experience. Very important in the user experience is the availability and response speed of the product. And how to use the most reasonable resources to support the product to provide a highly available and high-speed user experience is also an important responsibility of the DBA.

Database Administrator Technical Division

The responsibility of the database administrator throughout the product's life cycle is important and extensive, which has spawned various vertical operations and maintenance technology directions, and everything related to database quality, efficiency, cost, security, etc., and the technologies and components involved, mainly include:

Database monitoring technology: including the development, application of monitoring platform, service monitoring accuracy, real-time, comprehensive guarantee.
Database fault management: including fault plan design for services, automated execution of plans, summary of faults and feedback to product / system design level for optimization to improve product stability.
Database capacity management: measure service capacity, plan service room construction, capacity expansion, migration, etc.
Database performance optimization: from all directions, including SQL optimization, parameter optimization, application optimization, client optimization, etc., to improve database performance and response speed, and improve user experience.
Database security: including database access security, anti-attack, and permission control.
Automatic database deployment: R & D of deployment platforms / tools and use of platforms / tools to achieve secure and efficient release services.
Database cluster management: Including database server management and distributed cluster management.
Database model design: Including the design of database logical and physical models, how to achieve optimal performance, scalable architecture, and service operation and maintenance.

Database administrator competency requirements

The database administrator is based on technology and provides higher quality services through technical guarantee database. DBA's job responsibilities and position in the business determine that the DBA needs to have more extensive knowledge and deep technical capabilities. In the management and maintenance of database environments, technical tasks can be grouped into many different categories. Listed below are some of the techniques that a DBA should master (in no particular order).

1. Understand data backup / recovery and disaster recovery

Recovering a damaged database is the most important skill every DBA should master. The DBA needs to fully understand all possible backup and recovery methods of the database, and the correspondence between different backup methods and different recovery strategies. In addition, the DBA also needs to work with business units to confirm business requirements and clarify the bottom line of data loss that users can tolerate. In addition, business users need to determine how long their business can be maintained in the event of a system failure. Understanding these needs can help DBAs develop a backup / recovery method that meets the needs of business users. An excellent DBA should regularly test the backup and recovery process to ensure that they have the ability to recover business data and meet the business data loss and recovery requirements specified by the enterprise.

2. Use of toolset

The so-called toolset means that they have a set of scripts for performing different DBA tasks. This toolset should contain different small pieces of code that can quickly diagnose problems or perform a specific task. These tool scripts should be classified according to the type of DBA activity, such as backup, index maintenance, performance optimization, capacity management, and so on. A good DBA will constantly add new scripts to this toolset, as they will always perform new tasks, discover new problems, or find useful scripts developed by others. In addition, he should know where to find some free tools and scripts online. A good DBA knows when to take advantage of scripts written by others, thereby saving himself time and improving his toolset.

3. Know how to find answers quickly

Databases face challenges of all kinds of failures every day, from hardware to the network, from performance pressure to program bugs, the DBA must calmly deal with and eliminate them one by one. Even if the database is big, it is not possible to be omniscient. Therefore, each DBA needs to continuously cultivate himself and accumulate theoretical foundations such as operating systems, networks, hardware, storage systems, and distributed computing. The ability to find new solutions quickly. If a database instance isn't working as expected, quickly finding solutions to new problems is also an important capability. A good DBA knows how to quickly find a solution to an unknown problem online. In addition, they may already know some very good websites, they also know that industry experts will provide some good suggestions, and also know when to ignore some bad suggestions. You may not imagine that there are some suggestions that are not worthy of reference.

4. Know how to monitor and optimize database performance

For any database product, performance is particularly important, it will directly affect the product's response speed and user experience. For a DBA, performance optimization generally requires 50% of the working time, so the DBA needs to know how to monitor and optimize data performance.

Taking SQL Server as an example, performance is a key issue, so DBAs need to know how to fix failures and monitor performance issues. There are many third-party performance monitoring tools that can help DBAs optimize performance. If the DBA only uses third-party tools and does not use the native tools that come with SQL Server to monitor performance, then I believe that problems will soon occur. Although it is also good to use third-party tools to monitor performance, DBAs must understand some of the native tools that come with SQL, such as SQL Server Profiler, Database Engine Tuning Advisor, Dynamic ManagementViews, system / extended stored procedures, Extended Events, etc. Many third-party tools are actually using these underlying native tools. Therefore, understanding these native tools will help to enhance the DBA's experience with third-party tools.

5. Research the new version

In technology, nothing is set in stone. Every two or three years, major database vendors release a major version of the update. The DBA should keep up with the changes made by the new version. They may have many changes. The best way is to intervene in this process sooner or later. Download and install the beta version as soon as it is open, and gain first-hand experience as soon as possible. A good DBA is always in the vanguard of learning and always installs and tests new versions as soon as possible. This way they can understand the new features early, and then make some reasonable new suggestions to help the organization make better use of the new version of the database.

6. Understand code best practices

DBAs should understand how to write efficient code. There are many bad coding practices that can lead to poor performance. A good DBA needs to be able to understand and recognize these bad coding practices and know how to modify these bad codes to make them efficient code. In addition, they document best practices for writing code and share them with others.

7. Continuous learning

The database and its components are very extensive. DBAs have difficulty understanding all aspects of a technology. DBAs need to continuously learn how to manage databases. There are many ways to do this learning process. One of them is to participate in formal training. However, not everyone has such ample events and money, and not everyone can lay down the work at hand and go out for formal training. However, there are many other ways to get training, and most are free. A good DBA must subscribe to some community websites that regularly release new database tips and articles. In addition, he should also join some user organizations to participate in some free local salon events on weekends.

8. Database Security

Security is a hot topic. The DBA should fully understand how to achieve secure access to the database. They should understand the difference between operating system authentication and database authentication, and their respective use cases. They should understand how to use database roles to manage security configurations for different types of users. They should understand the ports and protocols used to connect to the database. In addition, they should understand how to encrypt the entire database, or a field in a table in a database, and understand various issues about encrypted data.

9. Database Design

A key issue in determining database performance is database design. DBAs need to understand all aspects of database design. They need to be able to understand the difference between good and bad design. They need to understand why using the correct foreign key constraints, primary keys, check constraints, and data types can maintain the data integrity of the database and enable efficient data querying and updating.

10. Index Design

Database indexing is an important part of improving the speed at which applications retrieve and update data. DBAs need to know how indexes work. They should know the difference between clustered and non-clustered indexes, and the physical storage of these indexes. The DBA should know how to use these indexes in the execution plan. They should understand how to find index usage statistics, understand index fragmentation, and find missing indexes. They should know how to maintain indexes, and the important role that index statistics play in query engines.

11. Capacity monitoring and planning

Databases often use a lot of resources, including CPU, memory, I / O, and disk space. The DBA should understand how to monitor the usage of different host resources required by the database. They should be able to understand how these resources are used at different times and use historical usage data to plan future capacity needs. During the monitoring process, the DBA should be able to foresee when there will be problems with capacity planning in the future, and then take necessary measures to keep the database from being interrupted due to capacity constraints.

12. Database License

There are many different ways to license different products. Moreover, there are many different versions of the same product. The DBA should understand the different authorization models for the database version he is responsible for. They should be able to provide guidance on how to reduce the total cost of ownership of the database by purchasing licenses reasonably, and how to use the licensing method reasonably to reduce the cost of upgrading future versions.

13. Automate as much as possible

DBAs need to perform many daily tasks every day. Some tasks need to be performed daily, while others are performed weekly, monthly, or yearly. A good DBA needs to understand how to schedule his time efficiently. One way is to establish workflows that automate these daily tasks. By automating routine tasks, DBAs can spend more time focusing on serious problems encountered in database environment management. ^[3]

Database administrator professional character

The DBA needs to deal with various people, which may be vendors, users, developers, or managers. This shows that the DBA must have the following personality characteristics:

Self-confidence: sufficient self-confidence can help the DBA make a decisive decision in an important emergency, rather than overwhelmingly watching the accident happen
Curiosity: Almost all database systems are constantly updated. But not all updates have technical documentation. For a good DBA, curiosity is required. DBAs without curiosity and curiosity are always waiting for someone to tell them the answer. A knowledgeable DBA will install the latest version of the database system and immediately start searching for even minor functional and performance differences and enhancements to improve their work. An inevitable question when you take the test is: What reference materials do you have? How do you use them? No doubt, if you only answered the database documents, or you have nt even read them, your "stock market value" will Greatly decreased. Curiosity will drive DBAs to understand the Data Dictionary, Tools, or other Support Packages.
Perseverance and calm mind: DBAs often encounter difficult problems. Finding the answer is a personality trait that requires tenacity and can withstand a beating. At the same time, in the face of emergencies, we should be able to deal with the situation in a calm and calm manner.
Self-drive: Self-drive is important for everyone, especially for DBAs. The DBA needs to be able to find a way to make the problem happen, not wait for it. Self-driving DBAs often try to obtain or write some necessary scripts to monitor items including Table Size and Tablespace Usage. If these items are ignored, they will encounter trouble. When taking the exam, DBAs are often asked about their experience in PL / SQL, SQL or SQL * PLUS. These questions will distinguish you from those DBAs who have never written the scripts they need.
Good communication skills and teamwork spirit: DBA's work requires contacting many engineers across departments and types of work. Good communication and teamwork can play a good role. Many times, developers, managers, and users will make unreasonable demands, and DBAs need to guide and revise their requirements and convince them.
Careful and meticulous work, diligent in thinking: Finally, talk about attention to details, this personality tendency is very important. The DBAs who pay attention to the details are neatly dressed, have their own schedules, and have conducted surveys with the units they hired before taking the exam. The DBA who pays attention to the details deeply understands the kernel of the database and can understand the relationship between views and tables.
Time management capabilities: Database operation and maintenance work is very tedious, so it needs to make full use of fragmented time.

Database Administrator Career Level

DBA levels are not very strict. Based on my knowledge of the database, I simply divided it into three levels: Primary, Intermediate, and Senior.

The primary DBA is also called DBBS, which is the abbreviation of English Database Baby Sitter. Junior DBAs are often part-time, and they are often programmers or concurrently performing other jobs. The junior DBA often writes a great resume, and is involved in many database-related projects or work. However, these projects or tasks are often: Third-party software vendors have installed and configured the database, and they only do some monitoring work. They can deal with some simple problems, but most of the time they ask the application software vendor for help. Junior DBAs prefer graphical database management or monitoring tools. They like desktop databases such as Access to be easy to use, and simply apply the experience of these small databases to large database related tasks.

A junior DBA is best distinguished. The intermediate DBA and advanced DBA are not well distinguished. Their differences lie in differences in experience and differences in personality characteristics and abilities. There are many intermediate DBAs, and they can perform most of the tasks of advanced DBAs, including:

1. Database installation;

2. Database configuration and management;

3. Permission setting and security management;

4. Monitoring and performance adjustment;

5. Backup and recovery;

6. Solve general problems;

Intermediate DBAs often work for about a year and are familiar with databases in a certain operating system environment. Because for intermediate DBAs, Windows NT and Unix are very different. Intermediate DBAs are more familiar with SQL. They have bought several books on database and delved into them. Intermediate DBAs often serve as database programmers at the same time. Their work is not very high in pursuit of performance, stability, and security. They often cooperate with advanced DBAs to do some routine work.

There are very few senior DBAs in China. They are proficient in reading English language information on databases and are familiar with several large databases on many operating platforms. They know the advantages and disadvantages of different databases in different environments, and can make decisions on the choice of database platform and database environment. They are generally familiar with system architecture and database design, and can perform various levels of optimization on the database. Senior DBAs are usually equipped with assistants, and they are more inclined to make decisions and plans. Advanced DBAs often play an important role in key business processing areas that require high stability, security, and performance, such as banking, insurance, and online transactions.

In many cases, it is not important to obtain a database expert certification. Many database vendors receive certificates whenever you go. There are many companies that provide commercial training, and their service quality is also good or bad. So certificates are not particularly meaningful.

Database Administrator Industry Competitiveness

Salary competitiveness

1) Influencing factors: Experience and ability determine the level of DBA, familiar database system determines the field of DBA, personality characteristics and potential determine your appreciation space.

2) Vertical comparison: As a high-quality talent in a special field, DBA has high salary competitiveness and strong market demand, especially for middle- and high-end DBAs, which often have a higher shortage than software R & D engineers.

3) Third-party salary survey report, for reference only:

"2013 Database Engineer Salary Survey Report ^[4] "

Database Administrator Salary ^[5]

Market demand

1) Market value of database operation and maintenance industry: refer to ITOM (IT Operation Management). The market size in 2013 was 19 billion US dollars

2) Staffing requirements: Due to the large scale of business, medium and large companies are in short supply of mid- to high-end DBAs, and start-up companies are in short supply of junior and intermediate DBAs due to their new business characteristics.

Database administrator career prospects

Database as a part of the entire system, its performance is directly affected by the quality of SQL statements in servers, operating systems, storage, networks, applications, the quality of database design, and many other factors. These factors are very complex and experience combined Plays a very important role. Therefore, in addition to the knowledge as a basis for a good DBA, the amount of experience and the breadth of knowledge often determine whether it is qualified and excellent.

Excellent DBAs are not only concerned with the principles and development of database systems that they operate and maintain, but also closely follow the industry's cutting-edge database technology and pay attention to the top conferences in the database field. These include the three internationally renowned database conferences SIGMOD, VLDB, ICDE, Percona Live hosted by the well-known database company Percona Live, and Oracle Open World, and the well-known DBA event China Database Technology Conference (DTCC).

From another perspective, the DBA work area requires high practical experience and independent work ability. Without a lot of hands-on practice, it is difficult to be competent for DBA related work ^[6] . It is precisely because of the above reasons that the status quo of the workplace is that it is not easy to enter DBA positions, and it is difficult for employers to find suitable employees, and the gap is very large.

It is precisely for the above reasons that with the increase of working years, the experience of the DBA is increasing. Just like a doctor, its value will become higher and higher, and it can gradually grow into a senior DBA, system architect, and information chief (CIO) And so on, without many career development problems facing software development practitioners.

In addition, from the perspective of career prospects, there are more job opportunities in DBA ^[7] . Generally speaking, the software and hardware in the system are provided by industry-leading manufacturers such as IBM, HP, Oracle, etc. In the process of negotiation, cooperation, testing, implementation, maintenance, optimization, etc. with the manufacturers, many excellent workplace opportunities will be generated. This is difficult to compare in the development work.

From the salary statistics of DBA, with the accumulation of work experience, the increase in DBA salary will be much larger than other computer directions.

From the perspective of job stability, the complexity of the system and the importance of experience have determined the irreplaceability of the DBA position.

From the perspective of knowledge accumulation, update, and substitution, the foundation of the database has not changed. What has changed is the ever-increasing functions and expanding application scope. Therefore, the knowledge and experience gained in different periods are superimposed and accumulated.

Therefore, the DBA profession is a highly challenging and highly rewarding profession, and capable and intelligent technicians should challenge themselves and enter this increasingly promising profession that has been continuously proven by the facts for more than two decades ^[8] .