SQL Server database administrator (DBA) work content _mssql2008

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;
• Mission 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: Trouble shooting;

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 the installation or configuration, should be based on the 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 changes in demand and the needs of the environment, 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 the plan should be on solving the bottleneck problem. System capacity planning can be considered in terms of content and duration two.

From the content point of view, should consider the main content includes: 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 system, database planning, database object content and quantity planning. Network planning includes network hardware, network software and protocols, network customer quantity flow and distribution, network topology structure and so on.

In terms of duration, short-term, medium and long-term planning should be considered. The goal of short term planning is to meet the needs of the current day-to-day business. Medium-term planning is mainly to meet the needs of business development and expansion. Long-term planning is mainly to meet the business limit needs. For example, if the current number of concurrent users of a system is predicted to be 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 be all of a sudden according to the needs of 10 million users to design, 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 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 a 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 when designing is applied, DBAs should work with developers to ensure that they write optimized code and use the resources of the server as much as 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. Can not be one-sided pursuit of excessive hardware resources, and can not only be limited to the current environment, must be based on an extensible view of the comprehensive consideration.

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 accomplish 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 to expedite retrieval?
• Should a view be created? Why do you want to create these views?
• What stored procedures, functions, CLR objects should be created?
• Which tables should you create triggers on? What actions should I create triggers for?
• Should you create synonyms?

Task Five: Storage space management.

The storage space management task is how to allocate space for 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 will increase gradually, and the transaction log will continue to increase. Storage-space management tasks revolve around the following issues.

• The current database consists 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 contents of security management include account management and authority management. Account management is what accounts 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 you 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 and system defects may cause paralysis and failure of the system. How do you 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 to restore the copy of the data to the system. Backup and recovery work is an ongoing important task for DBAs, whose execution frequency is determined by the degree of data importance 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 quality. As a large software system, the performance of the Microsoft SQL Server 2008 system must be properly monitored, evaluated, and adjusted accordingly. This is an advanced work for DBAs. With some tools and running performance metrics, DBAs should be able to monitor the operation of the system. If there is a problem 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 assignments be performed?
• How do you ensure that these jobs are executed correctly?
• What should I do if the automatically executed job fails?
• How can 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 keep your system highly available and highly scalable. Usability is a metric 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 failures, operating system crashes, natural disasters, and so on. Both the database system administrator and the application designer should minimize the chance of system disruption and maximize the availability of the system. 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, that is, time to run, connectivity requirements, and tight and loose data requirements. When determining the requirements for availability, first consider the running time of the system. Generally, database applications have two running times, which are 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, there are many applications that require running 24 hours a day, 7 days a week, such as an online supermarket, 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. At this point, the availability requirements of the system are reduced. Most applications require that data be used synchronously. The user requests the data, the system must respond immediately. This is a tight-type data requirement that must ensure high availability of the system. Some applications do not require the data to be synchronized, and the user's request can be deferred. This requirement is data loose type requirements, when 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 can be unavoidable. These failures can occur every day. Some faults are caused by artificial carelessness, some faults may be formed in the system, some faults may be inexplicable. As a DBA, the authority of the Microsoft SQL Server system is in the minds of other users in the system. Both big and small, DBAs should be quick to diagnose, judge accurately, and fix quickly. In this sense, a DBA is a professional practitioner of database systems.