Database Repository-SQL Server
| < Day Day Up > |
Database Repository ”SQL ServerAside from the operating systems, when it comes to scalability, SQL Server is the champion of Microsoft servers for scalability. As the data store for nearly all the enterprise servers with the exception of Exchange, Microsoft depends on SQL Server as the center for its offensive line. SQL Server 2000 introduced a host of features to boost scalability and availability, including:
SQL Server 2000 has achieved impressive performance on a single server, and even more stellar results in a clustered environment. A single eight-processor SQL Server node can support more than 40,000 concurrent users accessing billions of records on a 4-terabyte (TB) disk array. This server can process more than 50 million business transactions per day, according to "Microsoft SQL Server MegaServers: Achieving Software Scale-Out" (msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/megasrvs.asp). Although 2-way, 4-way, and 8-way processors are most common, SQL Server 2000 and Windows 2000 run on 32-way SMP hard ware with up to 32 GB of memory for 32-bit Intel architecture and up to 4 terabytes of RAM with Intel's 64-bit Itanium architecture. When 40,000 users and 50 million transactions per day are simply not enough, clustering takes SQL Server up to the stratosphere. Servers configured using Cluster Service share common data and work as a single system, while servers can operate independently of other servers in the cluster. This means that if one server fails, another server takes over its functions. Each node has its own independent resources, including memory, system disk, operating system, and a subset of the shared resources in the cluster. If one node fails, another one takes ownership of the resources of the failed node. The cluster service then registers the network address for the resource on the new node so client traffic is routed to the new server. When the failed server is brought back online, the cluster service can be configured to redistribute resources and client requests . You may consider several other server clustering techniques, including shared disks and mirrored disks. Servers can be configured as active/active, in which each server runs applications while also serving as a backup for the other server; or active/passive, in which one server runs applications while the other one serves as a backup. Table 14.1 lists three techniques you can use to share disk data in a server cluster. Table 14.1. SQL Server Data-Sharing Techniques
The Microsoft Windows 2000 Server Deployment Planning Guide provides additional information on planning your clusters. The Windows Cluster Service Configuration wizard can help you create and configure your server cluster. For prescriptive information on installing Cluster Service, see the "Step-by-Step Guide to Installing Cluster Service" at www.microsoft.com/windows2000/techinfo/planning/server/clustersteps.asp. Whether or not you cluster your servers, you face the decision among several disk configuration options, as shown in Table 14.2. To achieve optimal performance and fault tolerance, we recommend that you use a redundant array of independent disks (RAID) controller. There are several levels of RAID with different performance characteristics. Table 14.2. Disk Configuration Options
Higher RAID numbers do not imply that the controller is better or more advanced than one with a lower number. In fact, you can also combine multiple RAID levels to optimize performance. While RAID 0 provides the best performance, it does not provide fault tolerance. On the other hand, RAID 1 provides the best fault tolerance, at the cost of performance. The number of available disk controllers and disk channels also impacts performance. A one-to-one ratio of controllers to disks outperforms a one-to-five ratio, all other things being equal. For a look at a sample SQL Server hardware configuration for the enterprise, see the Unisys ES7000 at www. unisys .com/datacenter/msa/. For the Microsoft Enterprise Systems Architecture, the database server was configured as follows : A Unisys ES7000 was set up as a 24-processor production partition and an 8-processor development/test partition. These two partitions were clustered so the 8-processor development partition acted as the standby for the production environment. Each node ran Windows 2000 Datacenter and SQL Server 2000 Enterprise Edition. The 24-way production partition included the following hardware:
The 8-way development/test partition included the following hardware:
You can view benchmarks for SQL Server on the ES7000 at the Microsoft and Unisys web sites. The tested configuration was designed to illustrate a high-end enterprise configuration, but is not the maximum scalability that can be achieved on the platform. Unisys can support up to a 4-node cluster with 32 processors each for larger data services capacity and performance requirements. The performance of the Unisys ES7000 is an illustration of the kind of horsepower that can be brought to bear with SQL Server. Other vendors offer similar capabilities, and there are many possible server configurations that yield high database throughput. Scaling Out with SQL ServerScaling up is usually the most productive approach with SQL Server. Unlike with web servers, it is difficult to produce a high-performance SQL Server cluster with small, inexpensive servers. If you ultimately end up scaling out with SQL Server, it will likely be with servers that are also scaled up to a rather high level. New features in SQL Server 2000 help with scaling out by simplifying administration of partitioned data. The servers that share partitioned data are called a federation . Each server is administered separately but shares a portion of the database workload. More resources can be applied to the database access than on a single server. For instance, if your application supports orders from customers who are distributed around the world, and you ship from regional warehouses close to the customers, you might want to partition the database geographically . This approach might result in one server handling Latin American transactions and another covering Europe. In cases in which queries need data from both geographies, SQL Server could look across the federation and pull together the appropriate results. The techniques for scaling out include:
You can combine these techniques in many different ways, depending on your requirements. A common scenario calls for distributed partitioned views with data-dependent routing, along with some form of replication. When data is partitioned, SQL Server creates a view across multiple tables with identical structures and treats them as a single entity. Data-dependent routing is a method of accessing partitioned data that programmatically determines where data is located and routes the connections to the appropriate server. The developer determines how data is distributed with the goal of optimizing use of the computing and network resources at hand. While this approach yields benefits, these must be considered against the costs. For instance, maintenance of the application becomes more complicated when data-dependent routing is employed. If the use of the database is quite dynamic, it may be difficult to predict which partitioning scheme will produce the desired load balancing. For more information on scaling out with SQL Server, see "Designing Applications to Use Federated Database Servers" in SQL Server Books Online (msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_10_48oj.asp). |
| < Day Day Up > |
EAN: 2147483647
Pages: 164