SQL Server DBA Work details

Original: SQL Server DBA work details

In a Microsoft SQL Server 2008 system, the database administrator, or DBA, is the most important role in the administration. The DBA's goal is to ensure that the Microsoft SQL Server 2008 system runs properly and efficiently. The DBA's work is also the busiest job, whether it's performance tuning or disaster recovery, without the DBA's support.

In general, as a DBA, you should at least do the following 12 tasks:

• Task one: Installation and configuration;
• Task two: Capacity planning;
• Task three: application architecture design;
• Task four: Manage database objects;
• Task Five: storage space management;
• Task six: security management;
• Task Seven: Backup and recovery;
• Task eight: Performance monitoring and tuning;
• Task nine: scheduling the job;
• Task 10: Network management;
• Task 11: High Availability and high scalability management;
• Task 12: Fault resolution;

The tasks of these DBAs are briefly described below

Task one: Installation and configuration.

The first task of the DBA is to install and configure a Microsoft SQL Server 2008 software system that creates a good environment for the smooth use of Microsoft SQL Server 2008 software. Whether the installation or configuration, should be based on actual needs, so that the system to meet the actual needs of users. It should be noted that the system configuration is not once and for all, should be at any time according to the needs of changes and environmental needs, monitoring and appropriate adjustment.

Task two: Capacity planning.

Capacity planning is an overall plan for the entire Microsoft SQL Server 2008 system. The focus of planning should be on addressing bottlenecks. The capacity planning of the system can be considered from two aspects of content and duration.

From the content point of view, the main content should be considered: hardware capacity planning, software planning, network planning. Hardware capacity planning includes planning for disk space, CPU, I/O, and so on. Software planning includes installation and configuration planning of operating systems, database planning, database object content, and quantity planning. Network planning includes network hardware, network software and protocols, network traffic and distribution of customers, network topology and other planning.

In terms of the term, short-, medium-and long-term planning should be considered. Short-term planning is designed to meet the needs of today's daily business. Medium-term planning is mainly to meet the needs of business development and expansion. Long-term planning is mainly to meet business limits and so on. For example, if the current number of concurrent users to predict a system is 1000, 3 years after the user may reach 10 million, then can not be in accordance with the needs of 1000 users to design, and can not immediately follow the requirements of 10 million users to design, must take a compromise form.

Task three: Application architecture design.

Application architecture design includes database design, application design, and corresponding technical architecture design.

Database design should consider the logical requirements of the database, how and how the database is created, the physical location of the database data files and log files, and so on. In general, after a successful installation of the Microsoft SQL Server 2008 system, you can create a database manually, based on your planned goals.

Application design should consider the choice of development tools, API technology, the combination of internal resources and external resources, the distribution of application architectures, etc. It is necessary to emphasize that in the application design, the DBA should work with the developer to ensure that they write optimized code and use the server's resources as much as possible.

The technical architecture design mainly includes the distribution of the presentation layer, the logical layer and the data layer. These distributions should not take into account hardware resources and user requirements. Can not be one-sided pursuit of excessive hardware resources, and can not only be confined to the current environment, must be based on an extensible perspective to consider comprehensively.

Task four: Manage database objects.

Managing database objects is the most basic and important work for using a database. These objects include tables, indexes, views, stored procedures, functions, triggers, synonyms, and so on. To complete the task of managing database objects, DBAs should be able to answer these questions as well.

• What data should the system include?
• How should this data be stored?
• What tables should be created in the system?
• Which indexes should be created in these tables in order to speed up the retrieval?
• Should you create a view? Why do you create these views?
• What stored procedures, functions, CLR objects should be created?
• What tables should I create triggers on? What actions should be created for the trigger?
• Should you create synonyms?

Task Five: Storage space management.

Storage space Management task is how to allocate space for the data, how to keep the space to meet the growing data. As the volume of business continues and expands, the data in the database is gradually increasing and the transaction log is constantly increasing. The storage space management task is focused on the following issues.

• The current database consists of those data files?
• How should the size of the transaction log be set?
• How big is the data growth?
• How can I configure how data files and log files grow?
• When can data in a database be purged or transferred to another place?

Task six: Security management.

Security is one of the most important tasks for DBAs. The main content of security management includes account management and rights management. Account management is the number of accounts that should be added to the database, which roles these accounts should be grouped into, and so on. Rights Management is the management of object permissions and statement permissions, which should answer the following questions:

• Which objects should these accounts or roles use?
• What should these accounts or roles do with these objects?
• What actions should these accounts or roles perform in the database?
• How do I set up a schema? How do I build relationships between schemas and objects, schemas, and users?

Task Seven: Backup and recovery.

The catastrophic management of the system is indispensable no matter how the system is run. Natural disasters, manmade disasters and system defects all have the potential to cause system paralysis and failure. How to solve these catastrophic problems? The approach is to develop and implement backup and recovery strategies. Backup is the production of a copy of the data, recovery is to restore the copy of the data to the system. Backup and recovery work is an ongoing and important task for DBAs, and the frequency of execution is determined by the importance of the data and the degree of stability of the system.

Task eight: Performance monitoring and tuning.

According to the management efficiency of enterprises to evaluate the managerial level of the enterprise, according to the test results of students to evaluate the quality of learning. As a large software system, the operation of the Microsoft SQL Server 2008 system must be properly monitored, evaluated, and adjusted accordingly. This is a senior work of the DBA. With some tools and operational performance metrics, DBAs should be able to monitor the operation of the system. If there is a problem with some running metrics, the DBA should take remedial action in a timely manner so that the system remains in a high-performing state.

Task nine: Schedule the job.

DBAs cannot constantly stare at the operation of the system 24 hours a day and perform certain operations in a timely manner. Microsoft SQL Server 2008 systems provide many tools, and DBAs should take advantage of these tools and mechanisms to address some of the following issues.

• Which jobs are scheduled to be executed by the system?
• When should these jobs be executed?
• How do I ensure that these jobs are executed correctly?
• What should I do if my automated job execution fails?
• How do you make the system perform the appropriate operations in a balanced manner?

Task 10: Network management.

As a kind of distributed network database, the task of network management is more important. The Microsoft SQL Server 2008 system provides network management tools and services that DBAs should use to plan and manage network operations.

Task 11: High Availability and high scalability management.

As a DBA, you must maintain high availability and high scalability of your system. Availability is an indicator that measures the uptime of a computer system. Scalability describes the number of concurrent user accesses that an application can accept. The main factors that affect system availability include: network reliability, hardware failure, application failure, operating system crash, natural disaster, and so on. Both the database system administrator and the application designer should minimize the chance of system disruption and maximize system availability. When designing the availability of a system, you should determine what availability strategy to use to meet the availability requirements.

The requirements for availability can be described in 3 ways, namely, time to run, connectivity requirements, and tight and loose data requirements. When determining the availability requirements, first consider the system uptime. In general, database applications have two runtime times, which are available at work time and at any time. If only requirements are available during working hours, the maintenance of the system can be scheduled for the weekend. However, there are many applications that require 24 hours of operation per day, 7 days per week, such as online supermarkets, and must take steps to ensure that the system is always running. Different applications have different connectivity requirements. Most applications and e-commerce solutions require reliable network connectivity. In this case, a permanent online connection is required to minimize the occurrence of various anomalies. Some applications allow users to use offline. At this point, the availability requirements of the system are reduced. Most applications require data to be used synchronously. The system must respond immediately to the user's request for data. This is a tightly-knit data requirement, which must ensure high availability of the system. Some applications do not require data to be synchronized, and requests to the user can be deferred. This requirement is a data-lax requirement, when the availability requirements of the system are relatively low.

Task 12: Troubleshoot.

Although you do not want the Microsoft SQL Server 2008 system to fail, the failure may be unavoidable. These failures can occur every day. Some failures are caused by human carelessness, and some faults may be the result of defects in the system, some of which may be inexplicable. As a DBA, it is the authority of the Microsoft SQL Server System in the minds of other users in the system. Whether it's big or small, DBAs should be quick to diagnose, accurately judge, and quickly fix. In this sense, the DBA is a professional practitioner of a database system.

SQL Server DBA Work details