3. Microsoft SQL Server Database Administrator's role and task Microsoft Windows 2000 Platform
SQL Sever DBA Basic / Selection Work Project
DBA small technology
Summary of this chapter
Microsoft SQL SEVER Database Administrators (DBA) do not have a set of formulated task models, and the size and management skills of the database administrators of each enterprise organ are not the same. Some companies' DBA has been developed from the development to maintenance, and some companies make different DBAs responsible for a small part of the system operation. There are advantages in the two management models, and each company can self-reserve in need.
Today's database system management is a new expensive in high-tech workplace, and a good DBA is more guilty on the work. If you can continue training in this area, I believe that you will be more competitive in your workplace.
In this chapter, you will learn a SQL Sever DBA's first and advanced work content, as well as the skills to be a top DBA. Although there are many working projects listed, many projects are not routine-royal matters every day, and there are many projects that need to be viewed when they encounter problems. Regardless of which project is processed, DBA needs to handle events in a stable attitude and flexibility. This chapter will teach you how to deal with your work.
SQL Sever DBA Basic / Selection Works Project Although the work content of each DBA is different from the requirements of each company, some basic working items are all DBAs. If the items mentioned here are different from your work content, you don't have to worry too much, and each DBA will have some differences in nature. In this chapter we introduce a complete DBA work content, we provide new DBA comprehensive work content introduction, so some work content is not very familiar. We categorize these work items, but their classified methods and difficultness or workload of work content are independent. Installing and setting SQL Sever DBA is often required to support new software installation and assisting soft hardware settings, sometimes simply listing the equipment required to install or set hardware, or is required to participate in and install settings painting. No matter which case, DBA must determine that the installed or set system and database is the optimization setting of the SQL Sever. Software Installation Installing SQL Sever DBA has to be responsible for installing software, have to consider installing Microsoft Windows 2000 and other software installations. Must pay attention to the necessity of each option, it is easy to install in Windows 2000, which is easy to install components that do not want to install on the system, such as Internet Information Server (IIS), Dynamic Host Configuration Protocol (DHCP) Servo, message is column, and the service on some files or printers that increase system burden. To avoid unnecessary components, a list of installation lists before setting Windows 2000 is a good way. The contents of the list can be listed which are the components to be installed, you can reuse this list to install new Windows 2000 to ensure consistent consistency of Windows 2000. In addition to setting Windows 2000, DBA must also install SQL Server 2000 correctly. Because many options do not have prior planning when you install SQL Sever at the point, you may have to reinstall the software to change the setting, such as the SQL Server binary code archive and the storage location of the data file is an example. If you are installing SQL Server 2000 for the first time, you can install the software immediately on the officially operated system, and consider installing the software on the test system. This way you can try different options and are familiar with the system operation. As previously recommended, it is best to write the installation step when you install SQL Sever. Soft hardware settings Although DBA generally does not need to make a work of the hardware server, there is also an exception. When installed, if you don't actually set the soft hardware, you should be very clear and guaranteed that the settings are done by your request. As the DBA of SQL Server, in order to be responsible for the effectiveness and stability of the entire system, you must use your knowledge and experience to set the SQL Server system in the best performance and effect, as well as the maximum capacity and growth space. In addition, you also need to have the number of removable dischargers and controllers, as well as the ability to provide any RAID controller details. When installing the software, in order to remember the entire set process and logic to contribute to the time and effort saving time to set up the time, it is best to record the decisions of the first installation and the process of logical thinking. These records can also be used as a reference for future modifications and soft hardware upgrades. Of course, the recorded content remember how to use which PCI buses and RAID controllers are set, and the like. Because some set parameters must be found in the diagnostics when restarting the computer, if you hold the details of the original settings at this time, you will help you save your own time and physical strength.
Remember: Since the DBA's responsibility is to ensure the effectiveness and stability of the SQL Server library, the setting of the entire system is within the scope of responsibility. Thousands of to determine that the SQL Server system has been completely set. Detailed decisions made when the record settings will help others understand and confirm that the choices you have done are effective. The security of the security monitoring system and the problem found at any time is another responsibility for SQL Server DBA. It usually provides this related business in this area of professionals in our own companies or outside companies. What kind of safety system is required for a system, depending on your system's permission range. Suppose a system that is not connected to a network is only available to several trusted employees, apparently, the safety device required for such systems is not complicated than the security device required for the network connected to the network. The system's security device is very important, because if the company's system is stolen or destroying information, it will face a more tragic price. The system's security device begins with the management user, and this part will be discussed in the next section. In addition to managing users, DBA also needs to design and implement network preservation plans. This work is usually assigned to senior personnel with network preservation related experience. Therefore, if you have this experience in this area, you can become an administrative commissioner of the Internet or DBA professionals. There are many companies that have sold online preservation solutions, and those responsible for online preservation should be responsible for procurement, installation, and configuration proxy and security Gateway. In SQL Server, the main networks are preserved in the user's audit and management. The system audit system audit includes monitoring SQL Sever error record files and Windows 2000 event record files, as well as the Events that use SQL Server Profiler to monitor SQL Server itself. SQL SEVER record files and event record files records important information related to SQL Sever, Windows 2000, and preservation. These materials must be monitored to ensure security within the system. Events within SQL Sever can be used through SQL Server Profiler, such as documenting logging in error. You can also archive events such as Data Definition Language (DDL) Statement and insert, update, and delete features. SQL Server PROFILER can also be used to monitor specific events, log in time, user name, and related activities. Operating daily routine work may take the maximum time of SQL Sever DBA. Although these routines are cumbersome, they must do it. For example, DBA is responsible for the problem when operating, and backup and reply during operation. Backup and Reply Perform rules replication and periodically test backups are part of the work. If the system has failed, in many cases, the use of backup is the only way to reply the repository. But if there is no correct execution backup, it is impossible to reply to the database, which will lead to the loss of information and possible stopping, and may even lose millions of dollars. Therefore, the backup and reply process is a key responsibility of DBA. DBA must perform this task regularly and correctly. So, although this work seems to have no challenging, you can make sure you can also retrieve the information. Users manage user management is another routine of DBA, including managing SQL Server login and user permissions. Anyone who needs to use the library must first set its permissions through the DBA to enter the system. Typically, when the initial permission, DBA needs to obtain approval of the human resources department. Don't fully open a person's permissions, but you should open the relevant permissions in accordance with the information required by the department. Other routine maintenance work Other routine maintenance work contains monitoring libraries available space, re-column index, check the database object, and monitoring system. Because any changes in the system may be caused by the problem of system problems, the change in monitoring system is very small.
If you feel that the routine work per day is quite cumbersome, you can also use the automatic monitoring system to perform some routine checks, but you should also regularly monitor all routine work. Service Level Make sure the system can provide a particular level of service is very important. Some service levels will be described in detail in the service contract, and some have no relevant service contracts. DBA is responsible for determining that these systems that do not provide service contracts have been set in the best working state, such as setting maximum workload, and maximizing performance via performance adjustment. The monitoring of adjustment and monitoring systems requires accumulation experience in the usual observation. If the reaction time is suddenly met, the CPU capacity used is large, and it may be a sign of problems in the system. When you encounter different systems, the results of monitoring and interpretation monitoring will also be different. Therefore, it is necessary to judge and solve the problem based on the system encountered. You must also regularly monitor the system's resource usage. In this way, the system can be increased immediately when the system performance is lowered. Once the system capacity reaches the situation of nothing, the process of increasing the system is more expensive and fees. SQL Server provides some tools for monitoring systems, as listed below: System Monitoring System Monitoring is a Windows 2000 function, which can be obtained from the start inventory. This feature is to monitor the use of SQL Server and Windows 2000 resources. SQL Server Enterprise Manager provides resource usage information and some limited performance information. The RDBMS monitor of the workforce provides a combination of monitoring and warning functions of the database relevance management system (RDBMSS). The network monitor is used in some cases of online monitoring; you can use Microsoft Systems Management Server (SMS) or other coordination vendor tools. The user survey used to collect information about the user's feeling of system performance. Keep the contact with the user to determine if they are satisfied is very important. There should be frequent communication between DBA and users. Tools for monitoring disk space Use These include Microsoft Windows Explorer and other monitoring tools such as coherent vendors. Some tools can also provide monitoring of Windows 2000 and SQL Server. When the number of users increases and when the workload is increased, the system may be adjusted. Capacity Planning DBA must also understand the system's workload in addition to the monitoring and adjustment of the system. In some cases, you need to ask an expert to set the size of the system and make capacity planning to the system. However, in general, DBA is responsible for determining whether system capacity is exceeded, and is responsible for determining when efficient or when resources are not enough, to conduct an existing capacity plan. The system's optimization is as described earlier, and the optimization of the maintenance system is the responsibility of DBA. Because if the system is not maintained in optimization, the situation in which the performance is reduced will cause your customers to pay very costs to repair. So try to keep the system in optimization is the responsibility of DBA. The planning system interruption time is planned and arranged to reduce the system interruption time, which can reduce the unnecessary sudden interruption of the system. As long as the interrupt time is planned in advance and notify all users who are pre-prepared and arranged, unnecessary damage caused by system interrupts can be reduced. If your database has a support network system, you must make another arrangement. The planning system performs regular interrupt repair processing allows users to remain in the preparation of system interrupts. When the interruption time is set to get off work, try not to affect the user, such as the first Sunday of each month. Failure reply to another method of reducing system interruption is ready to respond to the failure or when it is. There are many reasons for the system, sometimes because of hardware failure. Typically, users only need to replace some failed ingredients and restart the computer, they can reply to the improper operation caused by the hardware failure. If the problem is in a disk, the protection of RAID can avoid the loss of information.
But if it is the entire array fail, you need to reply the database with a backup repository. Any of the above situations usually need to take hours to fix. Restart your computer or reply to the database, although it can solve the computer caused by the software, it is very annoying. The more serious problem is because of the data reservoir damage caused by earthquake, flood or typhoon and the failure of computer systems. The natural disasters will also lead to problems such as power outages and discontinues, so even if the data center has completed repairs after the natural disasters, the system is still unable to connect to the network. Establishing a pre-ready-to-use data center can solve this problem, because when the original data center is invasive, the pre-ready-to-use data center can be enabled immediately. Although there is no way to reserve the data center to reply to all the trading and information that occurred at the time of all natural disasters, it still avoids the company's operating stop. Such a preparatory data center should be taken and executed by DBA. Document Management Archives Management DBA has a responsible management database system all files, including hardware and software settings, installation procedures, maintenance records, software upgrade records, and applications. These records can be used to help reconstruct the system if necessary. The archives of the management system are boring, but it is also necessary to work. The file can be saved with paper or electronic format, how to use it to use it, depending on DBA. The following is how you can use: You can record all equipment with a separate file. This file contains each system in the device. System administrators, DBA, and system operators can access and modify this file. You can record each system in the equipment with a separate file. Also emphasized, each member of the system operation must be able to establish a log item in this archive. System administrators, DBA, and system operators can record different files. Anyone in the administrative group can modify these logs, so these logs must allow each relevant personnel to view. In an event that is lost or lost, the complete history of the system can help identify the location of the original error, or you can help you restructuring the system. The reason why the tracking error is to avoid future errors. However, such a process must use a complete file. Don't delete any files; you can only increase your file. The following list is a guidance that can be used to help you organize your files so that these files are useful. The file can be divided into two main types: setting and log. Settings files in a serious failure event, setting files can provide all information needed to rebuild systems. These information should include the following: Hardware Settings Record the hardware, the original setting of the hardware in your system, and all increased changes, such as increasing the disk or memory. Software components record each software component and setup program mounted to the system. It is very important to install which sub-components and details of which options have been installed. The database settings should include the planning and architecture of the database, the name and location of all data files, the archive groups belonging to each file, and how these groups are established. These information provide you with a reference that you can identify which information files have been lost in the event of the disabled array failure. Soft adjustment includes all system and database setting parameters. New settings should be recorded when making adjustment changes. System log system logs reveals its importance when system failure or performance is reduced. You can understand the reply help provided by the system log in accordance with the information provided below. An important part of observing DBA work is to pay attention to changes in the system and predict the problem. Observation on abnormal behavior is required. Even if the record is very simple, for example, it is simple to "the system looks very slow", such records are also effective clues for subsequent system failure events. System Change DBA should record all changes executed on hardware, job systems, and database systems. Various items should be arranged in the order of events and are complete, but there should be no unnecessary details. The system invalidates this event in the system log when any disk or other component is invalid. These information is useful when determining the cause of component failure.
The backup / recovery operation is not necessary to record each time the system is backed up. However, the execution of the reply information should be recorded to display the mode of the user behavior, the application of the fault point or the architecture of the database. When scheduled maintenance is performed, DBA should record what the system is done. These information can be investigating the starting point of the system failure after the labeling maintenance. Keep key events and set information records, you can make sure there is a problem and learn how to return the system to it should be in. The design files according to the company's organization and demand for the information technology department, and DBA sometimes needs to participate in the design of the system. In any case, familiarity is familiar with the system to make DBA to integrate system. Operation and maintenance plans Because some companies require DBA to perform backup, reply, and maintain the routine work of the user account; some companies require system operators to perform these work, the general operation program file requires careful saving for DBA or system operation Reference. These operating procedures must carefully explain that every step is to let the novice understand. Otherwise, because most of these backups have been implemented after get off work, novices have to disturb DBA without understanding the operational procedure. In order to avoid this situation, the program is preferably more detailed, more complete, and the effect is achieved. One of the responsibilities of damage to reply DBA is that whenever the system fails, it can be saved quickly, so damage reply is used to reply to the failed system. Because the basic system may fail at any time, in order to make other DBA or operators can perform reply when you are not present, damage to reply is a major resource. DBA should first analyze the risk of systematic needs and the risk of system effective demand and system. For small-scale companies, the results of damage to the reply may be recommended that the company prepares a system that uses a backup to reply because small-scale companies do not require the system to be 100% best, which is caused by damage. Failure time is allowed. To increase the effective time of the system to reduce the trouble of system failure, you can use errors tolerance, such as Microsoft Cluster Services -Microsoft Cluster Services (MSCs). Some companies will lose millions because of the system failure, so these companies should implement a more effective and complete damage to reply. Usually, such a reply system will be set up from the company far away from the company. It can operate independently after the original system is disconnected, so when the natural disaster affects the original system, the system of another location can Instead of the original system. About this corrupted reply's plan file needs to be carefully reserved so as to understand and use any technical personnel of the company. Some companies will also use DBA to engage in system development in addition to traditional database related responsibilities. Since DBA is very familiar with the current system's needs and job capacity, design content useful for new systems can be provided. The responsibilities of these design and development are as follows. Data modeization and analysis information model is an important part of the design process. It involves logic design of the database, including the integrity limitations of the specified data. To make this difficult process becomes easy, you can display the structure of the database architecture with graphics and see how individual components are interrelated. The data model shows the logical view of the database, and then you can abstainly become the physical database design. The correct data library mode is the establishment of a valid logic and entity's database design - efficiency can be maximized. Database design library design is usually done by the database designer, but often needs to refer to DBA. Sometimes DBA may also play a role of a database designer. DBA will be a unique view for the database, which helps to design the database in the database. The pre-deposit program has sometimes, and DBA will also need to design or develop pre-deputable procedures. Because DBA is very familiar with the system database and the information, ask them to perform this task is not yet. The difficulty of designing the pre-deputable procedure is fully demand for the application or company.
The development of the application DBA also participates in the development of the company's database in some companies. The application of the company's database uses the application, such as the application of the pre-stored program, can help the development of its application with a certain degree of understanding and familiarity for the company's database. Resource sharing DBA is sometimes required to be a developer, designer, and user consultant role. This consultation includes the following tasks: Helping general users about assistance, or develop training courses, and even professor courses. In many cases, special SQL is also used in the packet query of Decision Support System (DSS). Help developers provide information about the use of past systems and new progress to users. This may include notifying the user available new information tables and indexes, and all new features that may be useful. Help designers provide different design features to have information on users. Sometimes designers developed applications may lack some of users' hopes or needs, and delivering these information to them may help future design. If the user has a problem with some features, they are more likely to ask you, so you are a good information provider.
Analyze the information in the database and the access to the information. These information can be used to help capacity planning and adjustment processes. This can also provide information on the improvement of the database architecture. Other DBA duties have many other DBA responsibilities as described below: Cluster Management If you execute the MSCS with SQL Server, you must do cluster maintenance and collection of work. In general, the work of the cluster will be automated in the system, but when there is a hard body join and the change of the cluster, it is necessary to manually implement these administrative work. The concept of cluster is currently only used to process the failure of the server. In the future, the new version of Microsoft Windows and SQL Server will introduce larger clustered features to handle more complex installation and management. Before that, the work of cluster management is very easy to understand. Copy Management If you use SQL Server replication, you may need to perform regular maintenance tasks with the development of the cluster. These maintenance tasks involve changes in chase attributes and upgrade systems. Other maintenance tasks may involve increasing the memory or disk capacity of the system. Any changes to the cluster should be only coordinated between system administrators and DBA. In addition to the responsibilities mentioned above, the service desk can also support consulting work. Although the consultation work assists a professional support personnel, DBA is sometimes required to assist since the training support commissioner uses new applications and database functions. For procurement, DBA will also participate in hardware and software assessment. Such evaluation work includes reviewing the details of the soft hardware or sets and manages such standard inspections. For convenience, DBA also needs to get a software backup to test the system and evaluate the effectiveness of the product to certain use groups. At different requirements, some DBA also requires a complete review and evaluation report for the details of the hardware body. Performance Monitoring DBA should monitor the system on time and plan to grow. To this end, DBA can evaluate the size of the system and the training of the performance plan by other professionals. However, under normal conditions, DBA needs to determine if system performance is extended and the system will be increasing and the resources will be exhausted. Thereby, DBA can recommend companies to increase other resources to achieve the best performance status. If you accidentally plan monitoring procedures, the system is very likely to encounter insufficient memory, and the disk space is insufficient or the computer's central processing machine is overweight. This case often interrupts all transactions, so carefully monitor the system to avoid such a situation. In a smaller-scale company, DBA is sometimes responsible for safeguarding the company's webpage. Companies in size usually have employees specializing in maintaining and developing company web pages. Maintaining the web page work and DBA is dependent on the web page access database. SQL Server and Windows 2000 provide many ideal tools for web pages. DBA's small technology is as follows from this section, and DBA has many different work and duties. How to make the system use a group to your company's DBA and Data System (IS) department, how do you have a good impression of how you demonstrate and complete your work. This section is to provide some techniques for you how to deal with yourself and how to deal with problems before system users. How do I actually use the tips described below, they need to be regarded as the situation and your subjective opinion. Dealing with users in accordance with different responsibilities of each person, you may not have to contact the user directly. However, know how to communicate with users, and it is also a help for DBA. Several methods can be more smooth to communicate with users. Customers are always pair of sensitive diplomatic bracers. Because the customer may not be able to understand the problem or not to describe the problem, do not have a dispute with the customer when the customer provides your wrong message, but should do the key to the problem from them. Because these users' response is the key to the key to the problem. Listen to listen carefully to the heart of the user.
