SQL Server DBA work details

In Microsoft SQL Server 2008, Database administrators (DBA) are the most important roles. DBA's goal is to ensure that the Microsoft SQL Server 2008 system runs properly and efficiently. DBA is also the busiest job. Both Performance adjustment and disaster recovery are inseparable from DBA support.

Generally, as a DBA, the following 12 tasks should be completed:

Task 1: installation and configuration;

Task 2: Capacity Planning;

Task 3: Application Architecture Design;

Task 4: manage database objects;

Task 5: bucket Management;

Task 6: Security Management;

Task 7: backup and recovery;

Task 8: Performance Monitoring and Tuning;

Task 9: Schedule A Job;

Task 10: Network Management;

Task 11: high availability and high scalability Management;

Task 12: troubleshooting;

The following is a brief description of the DBA tasks.

Task 1: installation and configuration.

The first task of DBA is to install and configure the Microsoft SQL Server 2008 software system and create a good environment for the smooth use of Microsoft SQL Server 2008 software. both installation and configuration should be performed according to actual needs, so that the system can meet users' actual needs. it should be noted that the system configuration is not once and for all, and should be monitored and adjusted at any time according to the changes in requirements and the needs of the environment.

Task 2: Capacity Planning.

Capacity planning is an overall planning for the entire Microsoft SQL Server 2008 system. The focus of the planning should be on solving the bottleneck problem. You can consider the system capacity planning from two aspects: content and duration.

From the aspect of content, we should consider the following main aspects: hardware capacity planning, software planning, and network planning. hardware capacity planning includes disk space, CPU, I/O, etc. software planning includes operating system installation and configuration planning, Database Planning, database object content and quantity planning. network Planning includes network hardware, network software and protocols, network customer traffic and distribution, and network topology.

In terms of term, short-term, medium-term, and long-term planning should be considered. short-term planning aims to meet the needs of daily business. the interim plan mainly meets the needs of business development and expansion. long-term planning is mainly to meet the business limit needs. for example, if you predict that the number of concurrent users in a system is 10 million, and the number of users may reach 1000 in three years, you cannot design the system according to the requirements of users, it cannot be designed to meet the needs of 10 million users at once. It must take a compromise.

Task 3: 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, the method and quantity of database creation, the physical location of database data files and log files, and so on. generally, you can create a database manually after the Microsoft SQL Server 2008 system is successfully installed.

The application design should consider the selection of development tools, API technology, the combination of internal resources and external resources, and the distribution of application architecture. it should be emphasized that in application design, DBA should work with developers to ensure that they write optimized code and use server resources as much as possible.

The technical architecture design mainly includes the distribution of presentation layer, logic layer and data layer. these distributions should not take into account hardware resources and user needs. we can neither unilaterally pursue excessively high hardware resources, nor simply limit ourselves to the current environment. We must consider this from a scalable perspective.

Task 4: manage database objects.

Managing database objects is the most basic and important task of using databases. these objects include tables, indexes, views, stored procedures, functions, triggers, synonyms, and so on. to manage database objects, DBAs should be able to answer the following questions well.

What data should the system contain?

How should we store this data?

What tables should be created in the system?

What indexes should be created in these tables to accelerate retrieval?

Should I create a view? Why create these views?

What stored procedures, functions, and CLR objects should be created?

On which tables should I create a trigger? What operations should I create a trigger?

Should I create a synonym?

Task 5: bucket Management.

The storage space management task is how to allocate space for data and how to maintain the space to meet the constant growth of data. as the business volume continues to expand, the data in the database will gradually increase and the transaction logs will also increase. bucket management tasks focus on the following issues.

Which data files does the current database consist?

How should I set the transaction log size?

How fast is data growth?

How do I configure the growth of data files and log files?

When can data in the database be cleared or transferred to another place?

Task 6: Security Management.

Security is one of the important daily work of DBA. security management includes account management and permission management. account management refers to what accounts should be added to the database and what roles should these accounts be combined. permission management is the management of object permissions and statement permissions. You should answer the following questions:

What objects should these accounts or roles use?

What operations should these accounts or roles perform on these objects?

What operations should these accounts or roles perform in the database?

How to set the architecture? How to establish the relationship between architecture and objects, architecture and users?

Task 7: backup and recovery.

No matter how the system runs, catastrophic management of the system is indispensable. Natural disasters, man-made disasters, and system defects may cause system paralysis and failure. How can we solve these catastrophic problems? The solution is to develop and implement backup and recovery policies. backup is to make a copy of the data, recovery is to restore the copy of the data to the system. backup and recovery are an important continuous task of DBA. The execution frequency is determined based on the importance of data and the stability of the system.

Task 8: Performance Monitoring and Tuning.

Evaluate the management level of an enterprise based on the enterprise's operating benefits and evaluate the students' learning quality based on their examination scores. as a large software system, the Microsoft SQL Server 2008 system must be correctly monitored, evaluated, and adjusted. this is a high-level work of DBA. with some tools and performance indicators, DBA should be able to monitor system operation. if some running indicators have problems, DBAs should promptly take remedial measures to keep the system running efficiently.

Task 9: Schedule A Job.

It is impossible for the DBA to keep an eye on the system operation 24 hours a day and perform some specified operations in a timely manner. microsoft SQL Server 2008 provides many tools. DBA should make full use of these tools and mechanisms to solve the following problems.

Which jobs should be scheduled by the system?

When should these jobs be executed?

How can we ensure that these jobs can be correctly executed?

What should I do if the execution of the automatically executed job fails?

How can the system perform operations in a balanced manner?

Task 10: Network Management.

As a distributed network database, network management tasks are more important. microsoft SQL Server 2008 provides network management tools and services. DBA should use these tools to plan and manage network operations.

Task 11: high availability and high scalability Management.

As a DBA, the system must be highly available and scalable. availability is an indicator that measures the normal running time of a computer system. scalability describes the number of concurrent user accesses that an application can accept. main factors affecting system availability include network reliability, hardware faults, application failures, operating system crashes, and natural disasters. whether it is a database system administrator or an Application Designer, the probability of system failure should be minimized to maximize system availability. when designing the system availability, determine the availability policies to meet the availability requirements.

Availability requirements can be described in three aspects: running time, connectivity requirements, and tight and loose data requirements. when determining availability requirements, first consider the system running time. generally, database applications have two types of running time: available during working time and available at any time. if it is only required to be available during working hours, you can schedule system maintenance and other work on weekends. however, many applications require 24 hours a day and 7 days a week, such as online supermarkets. Measures must be taken to ensure that the system is always running. different applications have different connectivity requirements. most applications and e-commerce solutions require reliable network connections. at this time, a permanent online connection is required, and various abnormal phenomena must be minimized. some applications allow users to use them offline. at this time, the availability requirements of the system are reduced. most applications require that data be synchronized. the system must immediately respond to user requests for data. this is a tight data requirement, which must ensure the high availability of the system. some applications do not need to synchronize data and can respond to user requests in a delayed manner. this requirement is the requirement of Data loose type, and the availability requirement of the system is relatively low.

Task 12: troubleshooting.

Although you do not want a Microsoft SQL Server 2008 system failure, the failure may be unavoidable. these faults may occur every day. some faults are caused by human carelessness. Some faults may be caused by system defects, and some faults may be inexplicable. as a DBA, other users in the system have the authority of Microsoft SQL Server. DBAs should quickly diagnose, accurately judge, and quickly fix problems and problems. in this sense, DBA is a professional doctor of database systems.