SQL Server DBA Work content detailed

In a Microsoft SQL Server 2008 system, the database administrator (DB administration, referred to as DBA) is the most important role. The goal of the DBA is to ensure that the Microsoft SQL Server 2008 System runs normally and efficiently. The work of the DBA is also the busiest, both for performance tuning and for disaster recovery, without DBA support.

Generally, 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 operations;

Task 10: Network management;

Task 11: High Availability and high scalability management;

Task 12: Fault resolution;

The following is a brief description of the tasks of these DBAs

Task one: Installation and configuration.

The first task for DBAs is to install and configure the Microsoft SQL Server 2008 software system to create a good environment for the smooth use of Microsoft SQL Server 2008 software. Whether it is installed or configured, it should be done according to actual needs, So that the system can meet the needs of users. It should be noted that the system configuration is not once and for all and should be monitored and appropriately adjusted according to changes in requirements and the needs of the environment.

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 solving bottlenecks. The capacity planning of the system can be considered from the content and the term two.

From the point of view of content, the main topics to be considered include: hardware capacity planning, software planning, and 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 system, database planning, database object content and quantity planning. Network planning includes network hardware, Network software and protocols, network customer number of traffic and distribution, network topology and other planning.

In terms of the term, short-, medium-and long-term planning should be considered. The goal of short-term planning is to meet the needs of the current business. The medium-term plan is mainly to meet the needs of business development and expansion 3 years after the user may reach 10 million, then can not be in accordance with the needs of 1000 users to design, also can not be a while in accordance with the needs of 10 million users to design, we must take a compromise form.

Task three: Application architecture design.

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

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

The application design should consider the selection of development tools, the combination of API technology, internal resources and external resources, and the distribution of application architectures. It should be emphasized that in applying design, DBAs should work with developers to ensure that they write optimized code and use server resources wherever possible.

The technical architecture design mainly includes the representation layer, the logical layer and the data layer distribution. These distributions should not take into account hardware resources and user requirements. Neither can the one-sided pursuit of excessive hardware resources, nor is it limited to the current environment, must be considered in accordance with the extensible view.

Task four: Manage database objects.

Managing database objects is the most basic and important work of 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 respond well to these questions, such as the following.

What data should the system include?

How should this data be stored?

What tables should be created in the system?

What indexes should be created in these tables in order to expedite retrieval?

Should you create a view? Why do you want to create these views?

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

Which tables should I create triggers on? What actions should I create triggers for?

Should synonyms be created?

Task Five: Storage space management.

The storage space management task is how to allocate space for the data and how to keep the space to meet the growing data. As the volume of business continues and expands, the data in the database increases gradually and the transaction log increases. The storage management tasks revolve around the following issues.

What is the current database made up of those data files?

How should the size of the transaction log be set?

How fast is the data growing?

How do I configure how data files and log files grow?

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

Task six: Security management.

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

What objects should these accounts or roles use?

What actions should these accounts or roles perform on 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, regardless of system operation. Natural disasters, man-made disasters, system defects can cause the system paralysis, failure. How to solve these catastrophic problems? The approach is to develop and implement a backup and recovery strategy. Backup is the production of a copy of the data, Recovery is the restoration of a copy of the data to the system. Backup and recovery work is a continuing and important task for DBAs, whose execution frequency is determined by the degree of importance of the data and the degree of stability of the system.

Task eight: Performance monitoring and tuning.

According to the business benefit of the enterprise to evaluate the management level, according to the students ' examination results to evaluate the students ' learning. As a large software system, Microsoft SQL Server 2008 systems must be properly monitored, Evaluation and corresponding adjustments. This is an advanced work for the DBA. With some tools and performance metrics, DBAs should be able to monitor the operation of the system. If there are problems with some of the running metrics, the DBA should take remedial action in a timely manner so that the system remains in a state of efficient operation.

Task nine: Schedule the job.

The DBA cannot keep an eye on the system 24 hours a day and perform some specified actions in a timely manner. The Microsoft SQL Server 2008 System provides a number of tools that DBAs should take full advantage of to address some of the following issues.

What jobs should be scheduled to be executed by the system?

When should these jobs be executed?

How do you ensure that these jobs are executed correctly?

What should I do if the automatically executed job fails?

How do you make the system perform the appropriate operation 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 keep your system highly available and highly scalable. Availability is a metric that measures the uptime of a computer system. Scalability describes the number of concurrent user accesses that an application can accept. Major factors that affect system availability include network reliability, hardware failure, application failure, Operating system crashes, natural disasters, and so on. Both the database system administrator and the application designer should minimize the likelihood of system corruption and maximize system availability. When designing system availability, you should determine what availability strategy to use to meet availability requirements.

The requirements for availability can be described in 3 aspects, namely, the time of the run, the connectivity requirements, and the tight and loose requirements of the data. When determining the requirements for availability, the system's uptime is considered first. Generally, database applications have two running times, That is, available at work time and available at any time. If it is only required to be available during working hours, the maintenance of the system can be scheduled for the weekend. However, many applications require running 24 hours a day, running 7 days a week, such as an online supermarket, It is time to take steps to ensure that the system is always running. Different applications have different connectivity requirements. Most applications and e-business solutions require reliable network connectivity. At this point, a permanent online connection is required to minimize the occurrence of various anomalies. Some applications allow users to use offline. , the availability requirements of the system are reduced. Most applications require data to be synchronized. The system must respond immediately to a user's request for data. This is a tight-type data requirement that must ensure high availability of the system. Some applications do not need data to be synchronized, Requests to the user can be deferred. This requirement is data loose type requirements, then the system's availability requirements are relatively low.

Task 12: Troubleshoot.

Although you do not want a 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 formed by defects in the system. Some faults may be inexplicable. As a DBA, other users in the system are in the heart of Microsoft's SQL Server System. Both big and small, DBAs should be quick to diagnose, accurately judge, and quickly fix. In this sense, A DBA is a professional practitioner of a database system.