Sql Server 2000 - Cisco

Application Note

Microsoft SQL Server 2000 Executive Overview

Microsoft’s SQL Server 2000 allows access

Microsoft SQL Server 2000 is a relational

to multiple users at the same time. A single

database management and analysis system

instance of SQL server can consist of

for Microsoft enterprise servers. SQL Server

multiple databases and applications. An

2000 offers rich Extensible Markup

SQL server can scale to meet the

Language (XML) support, comprehensive

requirements of a large enterprise ranging

analysis services, and simplified database

in users from 50 up to thousands. This is

administration. These features jointly

achieved by distributing the database across

produce a solution that delivers reliable,

different servers and hence several

scalable e-commerce, data warehousing,

multiprocessors for optimal performance.

and line-of-business applications. These

In addition, its dynamic tuning features

features combine together to make SQL

allow it to proactively acquire resources to

Server 2000 a compelling relational

meet the requirements of a large number of

database management system (RDBMS) for

users. Because of its ease of use, very little

enterprises of small to midrange sizes.

administration is required to manage an

Generally speaking, databases, similar to files, store data but require applications to access and present it in the user-defined format. A relational database is one based on the relational mode, and is designed to implement set operations on the stored data using the Structured Query Language

SQL environment. This feature alone makes Microsoft SQL Server an ideal RDBMS for small to midsize enterprise as well as the departmental database application. It also has built-in features to prevent data corruption caused by data files simultaneously edited by multiple users.

(SQL). Data objects are organized into

Microsoft’s SQL meets the scalability

normalized relational tables, thereby

requirements of most enterprises. This

allowing faster access to data. Databases

makes it suitable for a wide variety of

are much more efficient because they avoid

enterprise solutions ranging from data

storing multiple copies of records and,

warehousing, e-commerce, and online

therefore, duplicate updates of data that an

transaction processing (OLTP)

application or a user must provide. A

applications. These solutions offer different

database consists of two main parts—the

features that may range from bulk data

physical structure holding the data and the

movement to data analysis. OLTP is used to

database management system (DBMS) that

manage more dynamic bits of data. These

is used by applications for data access and

types of databases allow more than simply

is responsible for enforcing the relational

viewing archived data. They allow

structure.

modifications to the data and are used to track real-time information. With different features and unique enterprise requirements

Cisco Systems, Inc. All contents are Copyright © 1992–2002 Cisco Systems, Inc. All rights reserved. Important Notices and Privacy Statement. Page 1 of 9

guiding database designs, many different versions of SQL are available for deployment. These versions address features such as performance, runtime, and price requirements, to name a few. This offers enterprises maximum flexibility by allowing them to deploy this in both high- and low-end user environments. Some responsibilities of a relational database include input/output (I/O) and data buffer management, transaction management, locking and logging, file and physical resource management, and fault recovery. Enterprise applications drive parameters for each of these features. Other reasons for the popularity of SQL include its ease of use, low cost, dynamic tuning capability, availability, reliability, scalability, and easier manageability. Enhanced capabilities within SQL allow database administrators to manage servers more easily by consolidating them in a central location. This lowers the cost of management and provides a framework for storage resource consolidation. Centralized servers can now be shared among different departments with capacity allocated on an as-needed basis. Multiple servers can be provided access to storage resources via Small Computer Serial Interface over IP (iSCSI) solutions that provide consolidation. Customers can not only gain cost advantages by consolidating storage resources but also attain a more efficient use of their storage capacity. The storage subsystem I/O access is a key component of any RDBMS. A successful RDBMS implementation requires careful planning at the early stage of the project, for example, defining business, performance, and scalability requirements including ease of management, cost benefit analysis, and so on. The evolving IP storage standard, iSCSI, is currently under development in the Internet Engineering Task Force (IETF) and can complement or provide an alternative to Fibre Channel storage-area networks (SANs). This technology allows end users to mix and match IP and Fibre Channel architectures or use an end-to-end IP storage configuration. One of the advantages of iSCSI is that it lets IT shops take advantage of existing infrastructure (TCP/ IP networks) and expertise. In addition to providing a lower-cost alternative to Fibre Channel, iSCSI also benefits applications such as storage consolidation, business continuance, remote backup and restore, and remote data access. Cisco SN 5420 storage routers can be used to deploy iSCSI solutions in SQL server environments, as shown in this document. Given the cost advantages of managing servers in a central location, the iSCSI solution can be deployed to optimize sharing of resources for storage consolidation. The Cisco SN 5420 not only allows a server to access multiple storage devices over IP, but it enables multiple servers to access multiple targets/logical unit number (LUN) devices on a single storage subsystem, all taking advantage of the IP protocol over Ethernet. This provides significant cost advantages by reducing both capital and operational expenditure. Cisco SN 5420 storage routers also provide a seamless end-to-end solution and interoperate in heterogeneous storage environments and solutions consisting of multiple vendor products. The Cisco SN 5420 Storage Router makes storage systems accessible at a block level over IP regardless of the operating system. Implementing iSCSI within the server provides seamless block-level access for SQL server applications and does not require additional software layers for IP network intelligence. With the Cisco SN 5420 Storage Router, any application that can access storage using the SCSI protocol becomes an IP application. This capability enables the existing application software suites to operate without modification to the methods they use to conduct I/O. More information on the Cisco SN 5420 is available at: http://www.cisco.com/warp/public/cc/pd/rt/5420/

Cisco Systems, Inc. All contents are Copyright © 1992–2002 Cisco Systems, Inc. All rights reserved. Important Notices and Privacy Statement. Page 2 of 9

Options In most environments today, storage is directly attached to servers via either SCSI or Fibre Channel. In some cases, customers have started to deploy Fibre Channel-based SANs for shared access to storage. The direct-attached solution is expensive because storage capacity tends to be stranded behind servers, where each server and its storage are managed separately. As such, storage utilization is minimized in the direct-attached configuration. The SAN provides a scalable, reliable, manageable solution to network storage subsystems and the application servers that access them. With iSCSI, all resources within the enterprise can be consolidated and shared across workgroups. In the case of Fibre Channel-based SANs, iSCSI can be implemented to overcome the cost and distance limitations of Fibre Channel. It easily extends the Fibre Channel SAN for both local and remote block-level access in the enterprise. The iSCSI solution can be deployed across a shared IP infrastructure and span a larger distance. Enterprises can thus provide inexpensive access to centralized resources from the remote branches over the existing IP infrastructure. Scenarios This paper addresses two key goals. The first was to provide a validation of iSCSI deployment of the Microsoft SQL Server with the Cisco SN 5420 as a storage router for block-level access. The second goal was to provide a performance comparison between Fibre Channel and iSCSI implementation. The Fibre Channel SAN performance baseline emulated small to midsize enterprise or departmental SQL deployment with numerous concurrent users ranging from 50 to 200. Then the same test was recreated via iSCSI for a performance comparison. The tests demonstrated that there is virtually no performance impact of an iSCSI implementation in this low to midrange typical departmental OLTP application. The baseline compared parameters such as transactions per second and CPU utilization as well as the I/O throughput. These metrics were chosen because in the case of iSCSI, these performance numbers may be impacted because the CPU is handling the additional load of the iSCSI driver. In the case of Fibre Channel, the local processor on the Fibre Channel host bus adapter (HBA) offloads the CPU. Thus, these parameters would demonstrate the worst-case scenario and identify any potential performance impact of iSCSI. Although CPU-offloaded iSCSI HBAs are available today, these tests were run with only the iSCSI software driver to demonstrate the worst-case scenario. These tests were repeated with different numbers of users in order to emulate different enterprise environments. “Database Hammer,” a performance tool provided by Microsoft, was used for stress testing. All read/write transactions were random and in equal proportion (50/50) to emulate an OLTP environment. The proportion of read/write traffic can be varied in the tool to effectively create patterns related to different applications. For example, data warehousing applications will have more writes because the information is backed up to a storage device, whereas e-commerce, human resources, supply chain, and so on will perform a combination of both. In addition, data archiving applications will perform sequential writes, whereas OLTP will generate mostly random reads and writes, with multiple users trying to access different records. The two scenarios used for testing are shown in Figures 1 and 2. The first scenario (Figure 1) provided switched Fibre Channel connectivity between the server and an EMC Symmetrix array. The second scenario, Figure 2, utilized the Cisco SN 5420 to extend the Fibre Channel SAN for iSCSI connectivity. It is important to note that both tests utilized an identical test hardware setup including server, storage (EMC Symmetrix), and a Fibre Channel switch. The server was equipped with an HBA for the Fibre Channel connection to access the storage in scenario 1. In scenario 2, a Gigabit Ethernet network interface card (NIC) equipped with an iSCSI driver was used to enable it to act as an initiator communicating with iSCSI targets provided by the Cisco SN 5420. Both scenarios were configured with the same LUNs. In scenario 2, an Ethernet switch was used between the Cisco SN 5420 and the server to provide Layer 2 connectivity, but this can be any low-latency IP network, further maximizing the advantages of iSCSI technology. Cisco Systems, Inc. All contents are Copyright © 1992–2002 Cisco Systems, Inc. All rights reserved. Important Notices and Privacy Statement. Page 3 of 9

Scenario 1 Figure 1 SQL Server Connectivity to EMC Symmetrix with Fibre Channel

SQL Server

Brocade Fabric Switch

EMC Symmetrix

Scenario 2 Figure 2 SQL Server Connectivity to EMC Symmetrix via iSCSI with Cisco SN 5420

SQL Server

Low-Latency IP Network

Cisco SN 5420

Brocade Fabric Switch

EMC Symmetrix

Test Setup As indicated previously, the tests focused on creating a scenario that would emulate the I/O patterns for enterprises of different sizes by varying the total number of users. Microsoft’s Database Hammer tool was used to populate the database in both scenarios as well as generate user traffic. All read/write transactions were random, with each at 50 percent of total. Table 1 describes the test environment. Each scenario comprised four tests that were run for different user load simulations. CPU utilization, transactions per second, and throughput comparisons were made for both scenarios. The same hardware was used to run both tests to ascertain similarity in hardware configuration of the server and Fibre Channel switch. The hardware configuration is given in Table 2. Table 1 Test Criteria Test criterion

Value

Read/Write Ratio

50/50

Database Size

10 million rows

Test Time

2 hours

Number of Concurrent Users

50 100 125 150

Cisco Systems, Inc. All contents are Copyright © 1992–2002 Cisco Systems, Inc. All rights reserved. Important Notices and Privacy Statement. Page 4 of 9

Table 2 Hardware Configuration Hardware

Configuration

Server

Dell PowerEdge 2550 Dual processor—933 MHz Dual hard drive—18 GB each Memory—1 GB

Fibre Channel Switch

Brocade Silkworm 2800 Firmware—v2.4.1f

Storage Subsystem

EMC Symmetrix 3830 Microcode 5267.21.14S

Database Configuration and Layout SQL 2000 Design configuration For a SQL server setup, Microsoft recommends striping data files via either Microsoft software in SQL (file groups) or in hardware. For the software striping option, the data files must be located on different physical disks, and not different logical disks, to optimize performance. It is also recommended that the database log files be either hardwareor software-mirrored and striped (Redundant Array of Inexpensive Disks [RAID] 1+0) and located on separate disk(s) from the data file. The log files were striped for this test. The SQL server uses a tempdb database that is used for temporary working storage and sort operations. A separate disk was used for the tempdb database to maximize performance, especially for the complex queries. It is important to note that because an EMC Symmetrix was utilized for this test, the disks (LUNs) in use were also hardware mirrored (RAID 1) within the Symmetrix itself. The logical layout of the SQL Server 2000 database named CSN is shown in Figure 3. Figure 3 Logical Layout of SQL Server Database CSN

SQL Server Database Engine For CSN

Disks 1–4: Data Files Striped in Software Across Four Disks

Disks 5, 6: Striped Volumes for Log Files

Disks 7: Tempdb

Cisco Systems, Inc. All contents are Copyright © 1992–2002 Cisco Systems, Inc. All rights reserved. Important Notices and Privacy Statement. Page 5 of 9

Seven EMC disks (LUNs) were configured in the disk group named SQLdg, as shown in Table 3. Table 3 Volume Label Mapping for Physical Disks Disk

Drive letter/type

Disk 1

E/simple

Disk 2

F/simple

Disk 3

G/simple

Disk 4

H/simple

Disks 5 and 6

I/striped

Disk 7

J/simple

The database was called CSN and configured as shown in Table 4. The data files were automatically striped across four disks by SQL server file group implementation. The log files were separated on to a different disk, as shown in Table 4. A separate disk was created for tempdb. Table 4 Database Layout Across Multiple Volumes Data file

Location

File group

Csn_datafile1

E:\CSN

Primary

Csn_datafile2

F:\CSN

Primary

Csn_datafile3

G:\CSN

Primary

Csn_datafile4

H:\CSN

Primary

Csn_log1

I:\CSN_Log

Csn_log2

I:\CSN_Log

Tempdb

J:\

Cisco Systems, Inc. All contents are Copyright © 1992–2002 Cisco Systems, Inc. All rights reserved. Important Notices and Privacy Statement. Page 6 of 9

Test Results The results are summarized in tabular (Table 5) and graphical format (Figures 4–6). Table 5 Test Results Number of Users

Scenario 1 (Fibre Channel)

Scenario 2 (iSCSI)

Percent CPU utilization

Transactions per second

Throughput

Percent CPU utilization

Transactions per second

Throughput

50

4.7

408

1.9

11.8

301

1.9

100

20.9

779

3.3

28.1

809

3.3

125

47.6

955

3.8

52.6

962

3.8

150

75.9

1088

3.9

78.4

1066

3.7

Figure 4 CPU Utilization

% Processor Time

CPU Utilization 100 80 60

iSCSI FC

40 20 0 50

100 125 Concurrent Connections

150

Figure 5

No. of Transactions Per Sec

Transactions Per Second

Transactions Per Second 1200 1000 800 600 400 200 0

iSCSI FC

50

100 125 Concurrent Connections

150

Cisco Systems, Inc. All contents are Copyright © 1992–2002 Cisco Systems, Inc. All rights reserved. Important Notices and Privacy Statement. Page 7 of 9

Figure 6 Throughput

Throughput MB Per Sec

5 4 3

iSCSI FC

2 1 0

50

100 125 Concurrent Connections

150

These tests demonstrated that performance in both the Fibre Channel and iSCSI scenarios was comparable. As expected, CPU utilization was 3 percent higher, in the case of 150 users with iSCSI because it is handling much more load with the normal Gigabit Ethernet NIC and iSCSI software driver combination in this scenario versus the Fibre Channel case with an HBA. In the latter, the processor on the HBA is utilized to process the I/O that offloads the sever CPU. With the availability of iSCSI TCP offload engine adapters (TOE), this issue can be addressed by implementing a TOE NIC in the server to offload both iSCSI and TCP processes onto the NIC, thereby alleviating the CPU. From a total cost of ownership perspective, however, this extra CPU utilization by iSCSI driver can be considered negligible, depending on the application. For instance, in the case of 50 users, CPU was up to 11.8-percent utilization, versus 4.7 percent in the Fibre Channel case. Even with the increase in CPU utilization with iSCSI, the total CPU utilization was fairly low and not a cause for concern. This may, however, vary if other applications are running on the same server. Customers can thus make implementation decisions based on analyses provided in this document. Other results were fairly comparable, as shown in the figures. Transactions per second were slightly higher with Fibre Channel because the processor on the HBA is directly forwarding traffic. In the case of iSCSI, the traffic has to hit the CPU and gets stored in memory before a forwarding decision is made, and that impacts the total number of transactions that can be processed. The reduction in transactions varied between 2 and 26 percent. This is consistent with expectation and will be alleviated by the TOE cards, as mentioned previously. Summary These tests demonstrate that the Cisco SN 5420 implements an iSCSI solution that offers a cost-effective alternative for enterprises to consolidate their SQL server resources. Because of its ease of use, Microsoft’s SQL Server is widely deployed for various enterprise applications. This document demonstrates that SQL server applications, requiring block-level access, work well with iSCSI as the underlying transport, with little configuration and no major operational concerns. The Cisco SN 5420 enables optimal use of the enhanced, built-in features implemented in the SQL server software. Enterprises can use the Cisco SN 5420 to deploy a more centralized SQL server architecture and utilize built-in features such as dynamic tuning and clustering, combining with the high-availability features of the Cisco SN 5420. More information on high-availability designs with the Cisco SN 5420 can be obtained at Cisco.com. Enterprises can use iSCSI technology to reduce total cost of ownership without compromising performance. This allows a centralized implementation of storage resources where servers can be located in a central data center facility. This also alleviates other costs via easier manageability and reduction in training requirements. Centralizing

Cisco Systems, Inc. All contents are Copyright © 1992–2002 Cisco Systems, Inc. All rights reserved. Important Notices and Privacy Statement. Page 8 of 9

resources via Cisco SN 5420 routers allows uptime, availability, and

Cisco Service and Support for Cisco Storage

provisioning to be significantly enhanced. As enterprises migrate

Networking

their data “online,” dynamic upgrades and scalability become key

The Cisco SN 5420 is supported by a service portfolio designed to

requirements. Central management enables greater efficiency where

align your unique business strategies and goals with a

resources can be shared across multiple organizations and

high-performance, high-availability storage networking system.

applications.

Technical Support Services and Advanced Services covering the

Finally, the tests demonstrated that iSCSI meets performance

entire product life cycle are delivered by Cisco and via its ecosystem

expectations for I/O performance as compared to Fibre Channel.

of best-in-class partners.

These tests emulated a lower-end enterprise environment with up to 150 users accessing applications on a single server. This could be similar to a scenario in a remote-office server that typically hosts a small number of users.

Corporate Headquarters Cisco Systems, Inc. 170 West Tasman Drive San Jose, CA 95134-1706 USA www.cisco.com Tel: 408 526-4000 800 553-NETS (6387) Fax: 408 526-4100

European Headquarters Cisco Systems Europe 11 Rue Camille Desmoulins 92782 Issy-les-Moulineaux Cedex 9 France www-europe.cisco.com Tel: 33 1 58 04 60 00 Fax: 33 1 58 04 61 00

Americas Headquarters Cisco Systems, Inc. 170 West Tasman Drive San Jose, CA 95134-1706 USA www.cisco.com Tel: 408 526-7660 Fax: 408 527-0883

Asia Pacific Headquarters Cisco Systems, Inc. Capital Tower 168 Robinson Road #22-01 to #29-01 Singapore 068912 www.cisco.com Tel: +65 317 7777 Fax: +65 317 7799

Cisco Systems has more than 200 offices in the following countries and regions. Addresses, phone numbers, and fax numbers are listed on the

Cisco Web site at www.cisco.com/go/offices Argentina • Australia • Austria • Belgium • Brazil • Bulgaria • Canada • Chile • China PRC • Colombia • Costa Rica • Croatia Czech Republic • Denmark • Dubai, UAE • Finland • France • Germany • Greece • Hong Kong SAR • Hungary • India • Indonesia • Ireland Israel • Italy • Japan • Korea • Luxembourg • Malaysia • Mexico • The Netherlands • New Zealand • Norway • Peru • Philippines • Poland Portugal • Puerto Rico • Romania • Russia • Saudi Arabia • Scotland • Singapore • Slovakia • Slovenia • South Africa • Spain • Sweden S w i t z e r l a n d • Ta i w a n • T h a i l a n d • Tu r k e y • U k r a i n e • U n i t e d K i n g d o m • U n i t e d S t a t e s • Ve n e z u e l a • Vi e t n a m • Z i m b a b w e All contents are Copyright © 1992–2002, Cisco Systems, Inc. All rights reserved. Cisco, Cisco IOS, Cisco Systems, and the Cisco Systems logo are registered trademarks of Cisco Systems, Inc. and/or its affiliates in the U.S. and certain other countries. All other trademarks mentioned in this document or Web site are the property of their respective owners. The use of the word partner does not imply a partnership relationship between Cisco and any other company. (0203R) LW3071 5/02