4. Design Microsoft SQL Server System
system requirement
System components and options
Database arrangement
application
Summary of this chapter
Before formally installing the SQL SEVER job system, you must plan how to set up your SQL Server system in advance to avoid the trouble of follow-up, even reinstalled. If this book refers to the SQL Server system, the computer is a computer that runs the SQL Server main program. If only the word system is mentioned, the representative is the computer with the SQL Sever database.
This chapter we will learn from the different versions of Microsoft Window 2000 and SQL Server, several front-end applications that can access SQL Server, and the combination of SQL Server and front-end applications on system ductility and execution performance.
system requirement
Before deciding the demand for the entire system, you must first consider the purpose of the system. See the purpose of using several computers and how to reach this system. This chapter we look at the purpose of the average people. By chapter 5 and 6, discuss how to design, install hardware devices.
In this chapter we will also discuss the functionality of the system and how to set these features. Before setting the service item, we must first understand which types of applications that will run will run.
System application
There is less application, and the SQL Server database has no need to exist. The application can make SQL Server use easy. Some apps look at it is a database, some otherwise. Regardless of the application, you have to resolve the needs of users in the shortest possible time. Otherwise, the user has to buy a better software!
The type of application can be functionally distinguished. It is generally divided into three major types: online transaction processing (OLTP) system, decision support system (DSS), and batch processing system. These three types have different system requirements, and they may also be used to use different types of application software.
Online transaction processing
When a bunch of users can access data online, they are classified as online transaction processing. There is also a very important qualities, that is, these users will wait for the system to respond. There are several modes of online trading processing systems, such as:
This model on the online shopping is of course, which is a flourishment of e-commerce. The scope of the application is, for example, when you buy information on the Internet, you will wait for the transfer, which is a mode of online trading. Of course, the time to access the data, you can make you complete the trading behavior quickly. Shop Shopping When the clerk brushes your credit card, your information is traded in the database through the layer system.
Business systems enters the database throughout your company. This can be a variety of systems such as salary systems, shopping systems. These applications can be an internal network application, or a program written in various languages such as C , Microsoft Visual Basic. In short, no matter what application, the source of information is from the database. All online trading systems have a common point, that is, the correct statement should be that the employers need to wait for the system response. So good system is a reasonable response time. (Just don't let others wait too long!) Decision support system decision support system, as the name suggests can support your system of decision-making. The user can throw out the problem and then refer to the result after the operation. The following questions are the problem that the decision support system can answer: find out who sells the company's most sold, what product is sold? Find the seasonality of the product. What if the price reduction is auction? How many commissions have been taken in each region? Decision support systems and online transaction processing systems, where the former can wait patiently, because the system may have to spend a little time to analyze these complex problems. This is not to say that users don't care about how long it is, but the total output value (time to waste a bunch of people) and reply time (less than a person's time) can be coordinated. Batch processing system batch processing system Offline processing There is no react-end user's components, the following is a typical form of operation of such systems: Daily data reorganization, decision support system requires re-organizing data per night, batch processing system Automatically automate this step. Data conversion This job is similar to the aforementioned homework, but information conversion will be performed here. Data Clear Clear Duplicate Account Users. Offline checks can be constructed by customers per night. Batch processing system usually does not have a user to wait for the completion of the job, but this type of system is still to complete the job within a certain time range. If the data reforms in the evening cannot come to the morning, it is not online. carry out. Device demand introduces these three different types of applications, you can know what type of application for system to run before designing a database server. In the next few, you will learn that each type of system has its own equipment requirements and qualities. Therefore, the equipment requirements and qualities determine if the decentralized processing system is used. The range of service range is required to design the most important part of a system. Service items are usually clearly included in the service scope consent (SLA). SLA is the consent of the service provider and customer, whether it really needs a formal SLA usually depends on the guest. For example, if you sign a database service, a company that provides this service will give you a SLA, which can be considered a consent of the legally effective. However, if it is a service providing a database inside a department, it may not be so formal. Regardless of the SLA, it will list the range of services that should be included, and sometimes it will be partially punished. When the service scope is confirmed, the appropriate system can be designed. The following we will discuss the level of impact design: Importance of the effectiveness of the SLA is to detail the minimum requirements for system equipment. A typical SLA will be attached to the form and list a variety of transactions supported by an application, and complete the basic services required by the transaction, and the time required to complete 90% - 95% transaction. Such as SLA may list a service content: "Join new customer list", if you need to complete this service for two seconds, you need three seconds. Companies or units that provide services must completely do the scope of service to avoid breach, and DBA is more confirmed to implement the service. You must regularly review system status and function development plan. When there is a problem, its system function must be enhanced. The operating capacity range includes the device requirements, and SLA can also consider joining the operating capacity range of the system.
The range of operational capabilities of the system can be divided into several types, such as hard disk space capability range, user capability range and network linking capabilities. You must make sure your system has enough capacity to perform demand. You often check your system, if you find the critical point of the capability range, you should process it immediately. In Chapter 6, we will discuss how to strengthen system resources in a timely manner. The optimal execution time of the system In addition to the performance of the planning system, SLA usually also specifies the best execution time of the system. The so-called system is the best execution time is our time for users to use the system. SLA may specify that 24 hours a day is the best execution time of the system, or specify a specific hour for the best execution time. To maintain this service, DBA must plan to fix backups and responses. System components and options Now you have an application type and a concept of a service scope, we can start to see how to decide to install the appropriate software. There are four versions of Windows 2000 and three versions of SQL Server 2000 for you to choose. This section will teach you the different sexuality and applicability of each version. Four versions of Windows 2000 Windows 2000 Four versions of functionality are Windows Data Center, Windows 2000 High-Order Server Edition, Windows 2000 Servo Edition, and finally Windows 2000 Professional. The following will be introduced to each version of the functionality, so you can find the version that suits your system, not the most expensive version of the most expensive version. Windows 2000 Professional WINDOWS 2000 Professional Applicable to General Table Computers. Usually, the Windows 2000 Professional computer uses only the SQL Server 2000 user-end component. Of course, if you want to run SQL Server 2000 on your computer, you can install SQL Server 2000. However, personal version only allows you to access information on your computer, not allowing access to other system access. Related Information The SQL Server Personal Edition (and user-end components) can be installed on the Windows 2000 Professional.
The Windows 2000 Server version of the Windows 2000 Servo version is used to set the server. That is to say, the Windows 2000 server version is installed, allowing other systems to access their resources. The SQL Server version of the Windows 2000 Servo version is the SQL Server Standard Edition. Windows 2000 supports only 4 of the following CPUs and 4 GB of memory, more than you can't! SQL Server 2000 also allows remote access.
Note that only SQL Server standards, personal and SQL Server user components can be installed on the Windows 2000 servo version.
Windows 2000 Higher Order Server Edition Windows 2000 Higher Order Server Edition is also used to set a server. Like the Windows 2000 server, the high-order servo version also allows other systems to be used remote access. In addition to the servo version of the function, the high-end version supports 8 CPUs and 8 memories. This version must also be used if you want to support the false features of Microsoft Cluster Services (MSCs). In addition to support MSCS, this version also supports Clustering Technology, which can be updated, with SQL Server 2000.
Note To use 8 CPUs and memories in SQL Server 2000, you must run the SQL Server Enterprise. Others such as SQL standard, personal version, and Server user-end components are available in Windows 2000 Higher Order Servo Edition SQL versions.
In addition to all features including other versions, Windows 2000 Information Center Edition adds support for CPUs and memory. This version can support 64 CPUs and 64 memories. To use the Windows 2000 Data Center version, you need to purchase the hardware vendor to its support, and the service of the product is provided by the manufacturer. Note To use 64 CPUs and memories in SQL Server 2000, you must run the SQL Server Enterprise. Others such as SQL standard, personal version, and Server user-end components are available in SQL versions of the Windows 2000 data center.
SQL Server's three versions are like Windows 2000, SQL Sever also provides several versions for users to choose. The direction in which the selected direction depends on how many CPUs and memory will be used. Let's take a look at these four versions. User-end Software A SQL SEVER user element is composed of NetWork Libraries and allows users to enter the information inventory. These two are allowing other systems to enter SQL Server access is important, so each SQL Server version has these features. Personal version This is a version designed for small database. This version only allows the host to access, and other systems cannot access host-end data through the connection. Standard Edition This version is one of the two servo dedicated versions of SQL SEVER 2000. All features of this version are the same as the Enterprise Edition, the only difference is that the standard version only allows access to up to 4 CPUs and memory. Enterprise Edition This version supports all the features of Windows 2000, but SQL Sever 2000 can only operate on the Windows 2000 high-order servo version or data center. Enterprise Edition also supports 2-Node false Clustering and updatable decentralized view tables. The functionality of Windows 2000 and SQL Server 2000 is listed below.
Table 4-1 Version Compare
Windows 2000 Personal Edition Windows 2000 Standard Edition Windows 2000 Enterprise Edition Windows 2000 Professional Edition Functions Unfiled provides information providing unused user access Windows 2000 server version function restrictions can support 4 CPUs and support to 4 CPUs and User-free access 2GB memory 4GB memory Windows 2000 high-order server version function restrictions can support 4 CPUs and support MSCS without user access 2GB memory can support 8 CPUs and 8GB memory Windows 2000 Data Center Function Restrictions can support 4 CPUs and support MSCS without user access 2GB memory supports 64 CPUs and 64GB memory
System Options In addition to selecting the appropriate SQL Server and Windows 2000 versions, there are several other system options that can also be selectively installed. These options are MSCS, SQL Server 2000 replication options, and updatable decentralized view tables (new features for SQL Server 2000 Enterprise Edition). The following will discuss these options. MSCS MSCS is an abbreviation for Microsoft Cluster Services (Microsoft Cluster Services), which is a feature that must be implemented in Windows 2000 and SQL Server 2000. The computer installed MSCS can be considered a standby or false computer. When the software or hard body generates an error, MSCs can reply to the information immediately. MSCS To have a sub-system that is shared by a disk to two systems in the Cluster, the transaction record files in the SQL Server, the data clips, and the executive files are placed in this subsystem. When there is a problem in the primary system, there will be a signal "lost heartbeat", and the backup computer will intercept the IP location and system name of the primary system computer, so that the system operates on the backup computer. The user will not feel that the main system has problems.
Related Information One Cluster Receipt and System The same needs to have a reply and reboot immediately. MSCS does not provide fault tolerance - only fast reply.
SQL Server Replication SQL Server Copy options can copy a data in the SQL Server database to another database system. There are three types of replication - snapshots, trading compounds and consolidated complications. All three types of copying are set, and the subscriber can receive a mode such as a replication version issued by the issuer. Snapshot-based reply snapshots are used in a timed snapshot to copy files for other systems. The snapshot movement requires the user to set up the work to avoid affecting the operation of the system. The disadvantage of snapshot is a copy of the version. The latest information is only available in the last copy. As for this so-called latest version, it may be a few times before the next time. Trading-type replication This type of reply method is the same at the front end processing and the previous snapshot. Different situations are after the transaction type replication method starts, the issuer is constantly reading the transaction record file and transmits the data to the subscriber. Subscribers will maintain a newer replication here, of course, there will be hysteresis during transmission. Combined complicated complicated compounds and other two types of reprocesses are that the merged copying will be updated at the issuer and the subscriber. SQL Server uses trigger programs and timestamps to integrate different replication systems. Combined replications Applicable when needed to perform a multi-performing mode, of course, the burden that the system to bear will be heavy than the other two reprocesses. Update Distributable Table SQL Server 2000 The updateable decentralized visual table that allows the system to share logical databases to enhance the system's ductility. The logical database can be distributed to many computers, making the database more functional. Chapter 18 will be discussed in more detail on the renewal of the decentralized check. Database Arrangement Design Data Library Another focus is data arrangement. The design here includes the output to the user's arrangement, such as the alignment of the transaction record file, the information file, and the like. Designed in advance is very important, it is very important because it is made up in the arrangement. Chapters and Chapters we will discuss the details of the arrangement. Transaction record file transaction record files are important to system stability. The transaction record file records all the information of all systems, and when the system has problems, the transaction record is replied to the system information, so the record file must be placed in a suitable place. It is best to use an array output into the device to protect the transaction record file, and when the hard disk has problems, this record file is not damaged. In addition, this file should be placed on a device having a high performance, because the transaction must first archive records to perform the next transaction, and if the device's system performs low, it will affect the performance performance of the entire system. Other points to note is that the record file needs to be fault, and the details we will discuss in the next chapter. When the space is placed in the transaction record file, the transaction will be stopped, so configure a sufficient space to store the transaction record file. The trading file can be released by backup, but since the backup may affect the performance efficiency of the system, some DBA will store the record file, about each hour, or back up every hour. In terms of space, it is best to design enough space to store eight hours of transaction volume and have no need to back up. You will find this later, this is a simplified transaction record file. Data archive information files and transaction records have different ways of presentation. The way the presentation depends on the way the user enters the file. The files should be dispersed on different hard disks, and the burden on each hard disk is dispersed. We will discuss this part in detail in the next chapter. You have to prepare enough space to deal with the growth of the information. When the data grows, the index is also growing. Therefore, you must perform a regular volume planning test to ensure that there is enough space storage information. In the development of planning data files, computational space, evaluation performance, and configure subsystem hard discs in array devices, the next thing to consider whether it is allowed tolerant. When the input and output device subsystem is confirmed, the data file can be allocated on each controller and hard disk. Application In this section you will learn how to design an app and consider its performance, ductility, and growth. Hierarchical Architecture A Application Basic Architecture can have many types, and the different types of different types are how many levels of system architectures are included. Many database applications are in the marketing focus on this software is a system architecture that takes several levels.
Hierarchical Architecture Compares each of the data library applications can be divided into three-layer different service scope, namely the database service: This is the rear end portion of the database system, where the repository is stored. Application Service: This is the application or calculation logic of the database. Display Service: This is a user's interface, which must be presented in the form that the operations can be appreciated by the user. These applications can be divided into a layer of architecture, two and three-layer architecture. The separation of these architectures is how the above three services are packet. Please refer to the architectural diagram of Figure 4-1, which will then be carefully introduced. Figure 4-1 Schematic Schematic Application Software Architecture
A layer of architecture is that three service components are installed on the same system. The application software does not process the needs of the system platform with this architecture. As Microsoft Access Database is an example. Nowadays, there is already a smuggling application only with a layer of architecture, especially Windows 2000 support. In SQL Server, it does not find a library application software that supports this architecture. There is an architecture to install Enterprise Manager and the database at the same time on a computer, it seems to be a model, but in fact, this is not a layer of architecture, because the application software is actually using the SQL Server network. Component to perform operation. Two-layer architecture two-level architecture is to place display services and databases on different systems, respectively. And the display service (user interface) has an application program logic. SQL Server Enterprise Manger is the architecture. The user interface and program logic here are placed in Enterprise Manager, but the data part is placed on the other equipped with the SQL Server library. Two-layer architecture is a common architecture. Many of this architecture application is written in supporting the language of WinodWS API (such as Microsoft Visual C or Visual Basic). In this architecture, the user must connect to the SQL Server database. However, this link is sometimes inefficient because it is likely that the apparatus for connecting is idle, but also occupies the connection. The three-level architecture three-level architecture is to place the database, the application, and the display service layer on a different system. This architecture utilizes the intermediate application layer, multi-worked to the display service layer, to reduce the user directly with the SQL Server connection. In addition, the intermediate application layer can also perform a large number of operational logic, so that SQL Server acts well in the role of delivery. For web architectural applications, it is some controversy. In fact, you can do a simple analysis. If the display server can browse the database information through the terminal processor or browser, this application is probably a two-layer architecture. As mentioned above, if the range of service is separated, you can use a lot of different systems. In actual applications, many systems are to connect a material library server to different application servers to serve more users. So when you design a system, you have to consider the use of users and the system will support which application. Performing Efficiency and Demonstration When you are architecture an application and database, you should consider whether this structure performs performance and ductility. The following options may affect the performance and ductility of this system. Using a temporary work temporary work table Suitable for smaller-scale repository, it is not applicable to larger-scale database. Use the summary function function to use the summary function function such as min (), max () and AVG () and the amount of data from the data. So be careful not to affect the system performance efficiency because of this feature. Using the index When the amount of data is getting bigger, you need to use an index, we will continue to discuss indexes in Chapter 17. Using a transaction clear transaction can ensure the integrity of the entire operation process. However, when the number of users connects the user grows, it is quite important to reduce resource lock as much as possible. As mentioned above, many levels must be considered when you design a system to grow against workload. If you can design a system with an optimized skill at the beginning of the design, you can set a durable system. This chapter summarizes this chapter, in the design of the SQL Server system, it is necessary to consider many things. And because each company's demand is not the same, even if your full-time system design, every system you designed is difficult to run the same design effect, let alone you can find a simple telling you how to design your The system is. This chapter discusses several design points. You must understand the best implementation time requirements of a company, and design the appropriate system based on this.
