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: scheduling 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: install and configure.
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. It 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 plan should focus on solving bottlenecks. You can consider the system capacity planning in terms of content and duration.
From the aspect of content, the main considerations include: hardware capacity planning, software planning, and network planning. Hardware capacity planning includes disk space, CPU, I/O and other planning. 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 extreme business 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: design the application architecture.
Application architecture design includes database design, application design, and corresponding technical architecture design.
Database Design should consider the logical needs of the database, the method and quantity of database creation, and the physical location of database data files and log files. Generally, you can create a database manually after the Microsoft SQL Server 2008 system is successfully installed.
Application Design should consider the selection of development tools, API technology, the combination of internal 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 synonyms be created?
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. The bucket management task focuses on the following issues.
■ What data files does the current database consist?
■ How should I set the transaction log size?
■ How fast is data growth?
■ How to configure the growth mode 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 DBAs. 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, and 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 task of DBA. With some tools and performance indicators, DBA should be able to monitor the operation of the system. 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. Both Database System Administrators and Application designers should minimize the probability of system damage and maximize system availability. When designing the availability of the system, determine the availability policy 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 you only need 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. In this case, a permanent online connection is required, and all kinds of exceptions 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 and 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.
1.8 summary of this Chapter
This chapter first analyzes why the database is used to manage business data, and then provides the basic concepts of the database. Next, we will review the development process of SQL Server. Then, the composition and features of Microsoft SQL Server 2008 architecture are studied. Then, the characteristics of databases and database objects are analyzed and studied. Then, the main tools provided by the system are analyzed. Finally, the work characteristics of the database administrator are discussed.
[Thinking and exercises]
1. What are the main disadvantages of using workbooks to manage data?
2. What is a database? What is DBMS? What is a database system?
3. What are the main database products in the current database market?
4. According to the development process of SQL Server, what do you think is the development speed?
5. Collect technical whitepaper from Microsoft for Microsoft SQL Server 7.0/2000/2005/2008, and study and discuss the functional evolution of Microsoft SQL Server.
6. What is the system database of Microsoft SQL Server?
7. What are the characteristics of stored procedures, triggers, and view objects?
8. What are the features of Microsoft SQL Server Management studio?
9. What is the main purpose of the SQL Server Profiler tool?
10. analyze the characteristics of DBA's 12 tasks.