SQL Server 2000 1 SQL Server 2000 1

SQL Server 2000 -1 -

SQL Server 2000 목차 1. SQL Server 2000 구성요소 2. SQL Server 2000 기능요약 3. 오라클과의 비교 4. SQL Server 2000의 로드맵 5. 사례 -2 -

SQL Server 2000 1. SQL Server 2000 구성요소 -3 -

SQL Server 2000 • • SQL Server 의 구성 Database Engine – OLTP(On-Line Transaction Processing) – Relational Engine / Storage Engine – High Availability XML & Web Support 4 Analysis Service 8 OLAP(On-Line Analytical Processing) 8 Data Mining 8 DTS(Data Transformation Service 4 English Query SQL Server Client Results High Availability XML HTML Query Relational Database Management System Client Application -4 - OLTP OLAP Data Mining English Query

SQL Server 2000 SQL Server 의 구성요소 관계 -5 -

데이터 액세스와 요소기술 SQL Server 2000 Applications and Components Access Excel Custom client Data Consumers Data Providers Browser IIS Active. X Data Objects (ADO) OLE DB Data Providers (Services) Cursors Query Processors Business Services OLE DB Data Providers (Data) ODBC SQL Server Jet Active Other OLE DB Exchange Excel Directory Providers -6 -

SQL Server 2000 SQL Server의 서비스 n MSSQLServer Service n n SQLServer. Agent Service n n n Data Management Transaction and Query Processing Data Integrity Jobs Alerts Operators Transaction Microsoft Distributed n Distributed Management Transaction Coordinator Server Microsoft Search -7 - n n Full-Text Catalogs Full-Text Indexes

SQL Server 2000 SQL Server의 관리/프로그램 툴 n Enterprise Manager n n Query Analyzer n n Profilier n n n Analysis Manager n n n SQL Server 관리 Database 관리 보안 관리 사용자 관리 SQL 스크립트, 쿼리 저장 프로시저 디버거 개체 브라우저 추적기능(Trace) 이벤트 로그 감시(Audit), 모니터링 ROLAP, MOLP, ROLAP 큐브 생성, 관리 데이터 마이닝 -8 - Server

SQL Server 2000 • • Security & Full-Text Search – Improved Performance • Full Crawl • Incremental Update • Change Tracking (new) – Content Filters (Excel, Word, etc. ) Security – Auditing – Secure out-of-the-box (setup) – Encryption – logon and server – Kerberos (with delegation) – C 2 certification -13 -

SQL Server 백업/ 복구 SQL Server 2000 • SQL Server가 제공하는 Backup/Restore 방식 – – • Performing a Full Database Backup/Restore Performing a Differential Backup/Restore Performing a Transaction Log Backup/Restore Performing a Database File or Filegroup Backup/Restore SQL Server의 Backup/Restore의 특징 – Allows Backups to Occur While Users Continue to Work with the Database – Backs Up Original Files and Records Their Locations – Captures Database Activities That Occur During the Backup Process in the Backup • Issues a checkpoint and records the LSN • Writes all pages to the backup media • Writes all transaction log records written during the backup process -14 -

SQL Server 2000 Windows 2000 SQL Server 운영체제 Windows NT Windows Me, 98, and 95 Third Party Browsers Internet Client Server Windows 2000 Windows NT Windows Me Windows 98 -15 - Windows CE

SQL Server의 통합 SQL Server 2000 Microsoft Windows 2000 with Solution Accelerator (SA) for the Internet Storefront Internet SQL Server Microsoft Systems Management Server Microsoft Exchange Server IBM Host Microsoft Host Integration Server 2000 Data and Applications -16 -

SQL Server 2000 Presentation High Availability of Microsoft Business Data SQL Server § Network Load Balancing § Web Farms § Component Load Balancing § Queued Components § RAID § Replication § Failover Clustering § Standby Servers -17 -

Active/Passive Clustering SQL Server 2000 Failover Clustering은 최대 4개 노드까지 지원 Client Primary Node \Virtual. Server 1 Windows 2000 Secondary Node Windows 2000 RAID Array SQL Server -18 -

Active / Active Clustering SQL Server 2000 Clie nt Client //Virtual. Server 1 //Virtual. Server 2 Windows 2000 Primary Node for //Virtual. Server 1 Secondary Node for //Virtual. Server 2 SQL Server -19 - Primary Node for //Virtual. Server 2 Secondary Node for //Virtual. Server 1

Scale-Out : 분산 파티션 뷰 SQL Server 2000 IIS Svr 1 IIS Svr 2 IIS Svr 3 IIS Svr 4 IIS Svr 5 IIS Svr 6 SQL Server CREATE VIEW Customers AS SELECT <local>. Customers 1 UNION ALL SELECT <Svr 2>. Customers 2 SELECT <Svr 3>. Customers 3 UNION ALL SELECT <Svr 3>. Customers 3 SELECT <Svr 4>. Customers 4 UNION ALL SELECT <Svr 4>. Customers 4 SELECT <Svr 5>. Customers 5 UNION ALL SELECT <Svr 5>. Customers 5 SELECT <Svr 6>. Customers 6 CREATE VIEW Customers AS SELECT <Svr 1>. Customers 1 UNION ALL SELECT <Svr 1>. Customers 1 SELECT <Svr 2>. Customers 2 UNION ALL SELECT <Svr 2>. Customers 2 SELECT <Svr 3>. Customers 3 UNION ALL SELECT <Svr 3>. Customers 3 SELECT <Svr 4>. Customers 4 UNION ALL SELECT <Svr 4>. Customers 4 SELECT <local>. Customers 5 UNION ALL SELECT <Svr 6>. Customers 6 -20 - IIS

SQL Server 2000 Log Shipping – Standby Server 주기적으로 로그정보 전달, 장애 발생시 Standby Server에서 지속적인 서비스 Monitor Standby (Destination server) Primary (Source server) Transaction Log Restore Backup Network Users Client workstations -21 -

SQL Server 복제 SQL Server 2000 Merge Replication Snapshot Replication with Immediate or Queued Transactional Replication Updating Subscriptions Transactional Replication with Immediate or Queued Distributed Updating Subscriptions Transactions • Higher Autonomy Higher Latency Snapshot Replication – Lower Autonomy • Lower Latency • Transactional Replication – Replication of incremental changes Merge Replication – -22 - Periodic bulk transfer of new snapshots of data Autonomous changes to replicated data are later merged

SQL Server 2000 • • SQL Server 2000 XML 기능 HTTP를 통한 URL 액세스 – URL Query – XML View – Template Annotated Schema – XML view of the DB Retrieving XML data – FOR XML – SQL language extension to retrieve XML instead of rowsets – XPath – allows XML developers to query via the XML view of the DB defined by an Annotated Schema – Both can utilize XSL/T to transform/format results • For example, convert XML to HTML for browser apps Storing XML data – Open. XML – Convert XML file into relational rowset and allow TSQL developers to map XML to tables in stored procedures – Updategrams – updates the database directly with XML (can use Annotated Schema) – Bulkload - Shred huge XML files into existing tables -24 -

SQLXML 아키텍쳐 SQL Server 2000 SQLXML SOAP Browsers WEB Apps I S IIS A P I XQuery XPath Templates and XSLT FOR XML SQL Server 7 Row Row sets Rowset XQuery/XPath processor XML Views SQLXML OLE DB Provider SQL ADO Access . NET Client SQL Server 2000 Updategram SQL FOR XML XML SQLXML Managed Classes Middle Tier -25 - Row Row sets Open XML SQL Server

SQL Server Data Warehousing SQL Server 2000 Data Sources Intermediate Data Stores Warehouse Servers SQL Server RDBMS/ OLAP Services OLEDB XML SQL Server RDBMS & DTS XML Business Intelligence Excel, EQ & Third Party OLEDB XML OLAP Services Third Party OLAP Services Data Mining Services XML Microsoft Meta Data Services -26 - XML

Analysis Services Architecture SQL Server 2000 Analysis Services Analysis Manager OLAP Store OLAP Engine Processing Data Mining Engine TCP/IP HTTP Querying -27 - A p p l i c a ti o n Analysis Server A D O MD Other OLE DB Providers O L E D B fo r O L A P O L E D B fo r D M DSO P i v o t. T a b l e S e r v i c e SQL Server Data Warehouse

SQL Server 2000 데이터 마이닝 서비스 • 한박스로 통합 – Oracle 보다 앞섬 Clustering • OLAP과 함께 시너지 효과 – Model-driven analysis (OLAP) – Data-driven analysis (Data Mining) • 두개의 주요 알고리즘 – Clustering Classification – Classification (Decision Trees) Income • OLE DB for data mining Good (2) Poor (3) • SQL Server와 통합됨 High Low Good (2) Good (0) • Developed in partnership with MS Research Poor (1) Poor (2) Married Good (2) Poor (0) -32 - Not married Good (0) Poor (1)

SQL Server 2000 데이터 변환 서비스 (II) • 데이터 변환의 필요성 – Change the Format of Data – Transform and Map Data – Make Data Consistent – Validate Data – Schedule the Operation – Import and Export Data Between Heterogeneous Environments • SQL Server에서 제공되는 데이터변환 툴 – DTS Import/Export Wizard – DTS Designer – DTS Object Transfer – DTS Bulk Insert – Bulk Copy Program (bcp Utility) – Replication -34 -

SQL Server 2000 Meta Data 서비스 • Infrastructure for sharable and reusable metadata about applications, components, Repository data, … • Components – – Information models COM interfaces and SQL schema DTS Repository engine VBA Modeling and admin tools -35 - Form MTS

SQL Server 2000 Windows CE 에디션 • SQL Server 2000 for devices/appliances – Full relational database management system – ~1 MB footprint – Wireless ready with HTTP support • Flexible access to enterprise data – SQL Server 6. 5+ – Remote Data Access (RDA) – SQL Server 2000 – merge replication • Familiar, consistent programming model – APIs –ADOCE and OLE DB for CE – Tools – e. Mbedded Visual Tools 3. 0 – Query language – SQL grammar -36 -

SQL CE 아키텍쳐 SQL Server 2000 e. VB 3. 0 ADO CE v 3. 1 Active X Objects e. VC 3. 0 OLEDB CE SQL Server 2000 Windows CE Edition OLEDB QP/Cursor Engine/ES OLEDB Storage Engine / Repl Tracking Client Agent: Replication and Remote Data Access CLIENT -37 - IIS HTTP Server Agent: Replication and Remote Data Access SERVER

SQL Server 2000 • • • SQL CE 1. 1의 주요기능 • Multi-column indexes Referential integrity Sub-selects (IN) Defaults Inner/outer joins 32 indexes per table One file database Null support Unicode support Transactions Platform Builder Support • • • -38 - Data-types – Real, Numeric, NVar. Char, NText, Image, Integer, Date. Time, Var. Binary, Unique. Identifier, Money Having/group by Seek on index Set functions Identity attribute Local security - DB encryption (128 bit) and database password SQL Server CE Relay

SQL Server 2000 2. SQL Server 2000 기능요약 -39 -

SQL Server 2000 3. 오라클과의 비교 -49 -

Microsoft & Oracle SQL Server 2000 Oracle Microsoft Oracle 8 i EE SQL Server 2000 EE Oracle 8 i Std SQL Server 2000 Std Oracle 8 i Personal SQL Server 2000 Personal Oracle Lite MSDE, SQL Server CE Express Analysis Services: OLAP Warehouse Builder Analysis Services and DTS Darwin Analysis Services: Data Mining Internet Application Server Windows 2000 and App Center Message Broker/Workflow Biztalk Server JDeveloper 오라클은 기능 추가를 위하여Visual Studio 별도의 제품을 추가로 구입해야 i. Portal Access Webforms 하지만, 마이크로소프트는 기본 기능이 DBMS 엔진과 함께 제 공되므로 통합과 상호연동의 문제가 해결되며, 관리의 용이성 과 개발기간의 단축은 물론, 비용절감의 효과가 있다. -50 -

Clustering Architecture - Oracle SQL Server 2000 l Shared Disk 방식: Ø OLTP환경하에서 실제적으로 Lock 오버헤드가 발생함 Ø 데이터 웨어하우스 내의 저장공간 서브시스템의 사용으로는 무리임 Ø 작은 노드로 여러 대를 구성할 경우 사용하는 것이 좋음 Ø 데이터 파티셔닝이 요구됨 Server Shared Disk Server Instance DLM Server Instance Disk Array DB Instance Oracle Parallel Server -53 -

Clustering Architecture – Microsoft SQL Server 2000 l Shared Nothing 방식: 잠재적으로 무한의 scalability (100’s of nodes) 대용량 OLTP시스템에서 사용됨 (e. g. Compaq Tandem) 대용량 데이터 웨어하우스에서 사용됨 (e. g. NCR Teradata) 주된 DB 제품으로서 사용됨 (e. g. IBM, Informix) 업계 표준 벤치마크를 통해 확인됨 (e. g. TPC-C 벤치마크) Ø Ø Ø Server Shared Nothing Instance Server Instance DB Part 2 DB Part 1 Server Instance DB Part 3 -54 -

SQL Server 2000 4. SQL Server 2000 로드맵 -56 -

SQL Server 2000 SQL Server 제품 로드맵 SQL Server 7. 0 1998/12 l l l l New Query Processor Dynamic Row Level Locking Dynamic Memory Configuration Auto Update/Create Statistics VLDB support Heterogeneous Query Multi-site update Replication Win 9 x Platform Support DB Monitor/Profiler OLAP Services DW Data Transformation Services Content Indexing Profiler Tuning Wizard SQL Server 2000/10 l l l l XML support Distributed Partitioned Views AWE memory support (64 GB) Parallel Create Index User-def. Functions Cascading Referential Integrity Column Level Collations Online Reorg Multi-Instance support Enhanced OLAP Services Data Mining Integration Queued Replication Active Directory support Improved Security Win. CE support (full replication) -57 - Yukon 2002+ l l l l Shared Nothing Clusters Lang. Independent SPs Complex Record Support Deeper XML integration Self Managing SQL Server Self-Tuning Databases Fully Integrated Data Warehouse

SQL Server 2000 5. 구축사례 -58 -

SQL Server 2000 대용량 DB 안정성 Proof of Concepts SAP BW 2 TB 예상 CRM 500 GB 예상 Data Mart 1 TB DW 총 9 TB, 한 개 DB에 2. 7 TB 99%, 500 GB 430만명, 1, 200만 뷰 99. 98% 99. 9985% 99. 98% B 2 C/B 2 B 4, 000 명 B 2 C 2, 000 명 동시 사용 자 트랜잭 션 처리 량 SAP 1. 2 TB 하계올림픽 웹사이드 초당 11, 880 개 처리 하루 2백만건 초당 200개 -59 - SAP 1, 500 명 Web/ASP 3 -노드 클러스터 초당 3, 200 개 최대 24, 000 개

SQL Server 2000 미션 크리티컬 사이트 5. 5 M users Throughput: Fin svcs ASP, 3200 txns/sec 1. 3 TB data warehouse SAP: 900 GB, 100 K peak ds/hr SAP: 1. 2 TB, 108 K peak ds/hr Scale out: Site w/ 3. 8 Br, 650 GB 63 million users, 13, 000 apps, 122 procs 9 TB total, 2. 5 TB db, 7500 CSRs Web services today: saved 6 months -60 -

SQL Server 2000 SQL Server 기반의 SAP 레퍼런스 사용 중인 회사 동시 사용자 수 Comp. USA Fed Prison Industries (DOJ) Lyondell Bridgestone Telefonica de Argentina Pennzoil 구축 중인 회사 Microsoft Bridgestone – Logistic Information System Freie und Hansestadt Hamburg 1, 500 1, 300 1, 200 1, 000 800 동시 사용자 수 650 3, 000 2, 000 가장 최근에 결정된 회사 동시 사용자 수 Japan Airlines 2, 000 -61 -

SQL Server 2000 SQL Server 국내 대표적인 레퍼런스 OLPT 시스템 ERP CRM/e. CRM DW/OLAP EC (B 2 C/B 2 B) 기타 모바일 SQL CE -62 - Mainframe Downsizing

롯데카드 시스템 SQL Server 2000 Unisys ES 7000 (CMS 시스템 파티션: DB ) Unisys ES 7000 (Application Server : COM+, MSMQ) CPU : PIII Xeon 550 MHZ 8 EA MEM : 4 GB ATL P 1000 DLT Library Unisys ES 5045 (개발 시스템) DLT 7000 Drive 4 EA Cartridge 30 EA 144 GB/hr BCV CPU : PIII Xeon 550 MHz 4 EA MEM : 2 GB CMS 100 MB/sec 3 Fiber Channel 금융 100 MB/sec 3 Fiber Channel 40 MB/sec 2 SCSI Channel Cluster Heart. Beat 100 MB/sec 1 Fiber Channel User LAN 승인서버 인터페이스용 전용 네트웍 • W 2 K Data. Center , SQL 2000 • ES 7000 1대에 Domain 에 의해 분 리 • Named User 400 • DB Size : 200 GB STRATUS 승인서버 -64 -

㈜ 대우 무역 부분 ERP SQL Server 2000 Unisys ES 7000 DLT Library DLT 7000 4 Drives Net. Vault S/W EMC 3430 Real 648 GB Mirror 324 GB 4 SCSI Channel PIII Xeon 700 MHz/1 MB 8 CPU 12 GMB Memory 18 GB x 2 internal Disks External Disk R/3 DB Server Backup Device Dual Channel SCSI NT 4. 0 Oracle Unisys ES 2043 R PIII Xeon 550 MHz/1 MB 4 CPU 2048 MB Memory 18 GB x 2 Internal Disks APP Servers 1, 2, 3, 4, 5, 6 4 Channel SCSI NT 4. 0 APP Servers 7~9 • DEV Server Unisys AQUANTA Pentium Pro 200 MHz/1 MB 4 CPU Pentium Pro 200 MHz/2 MB 512 MB Memory 6 CPU 4 GB Memory Unisys AQUANTA • • • -67 - App 서버당 최대 200 User 사용으로 구 성 Concurrent User : 500 DB Size : 270 GB W 2 K D. C 로 Upgrade 예정

한전 – DW App 구조 SQL Server 2000 Data Warehouse Server Source System DTS 기반의 추출 Application IBM SQL 7 Microsoft SQL Server 7. 0 Enterprise Edition Mining Server IMS SAM File OLAP Server Windows NT Server 4. 0 Enterprise (MTS, Clustering지원) Web App. Server Web Application Web OLAP Application SQL Server 7. 0 (DSS) SQL Server 7. 0 MTS MDB IIS 4. 0 NT Server 4. 0 (MTS) DW 분석가 DW 일반사용자 PC Win 95 & Win NT Web Browser Office 2000 (OLAP Client) Win 95 & Win NT -69 - Web Browser Office 2000 (OLAP Client) 통계 도구

한전 – H/W 구성 SQL Server 2000 Data Warehouse Server Source System HOST Unisys Hitachi 4 CPU 400 MHz 8 GB DRAM 1 TB NT X. 25, TCP/IP UDS, XDM (SAM File전환 DCOM Web/OLAP Server 500 GB D/W 4 CPU 400 MHz 4 GB DRAM NT TCP/IP HTTP DW 일반사용자 PC Win 95 & Win NT 32 MB TCP/IP DW 분석가 Win 95 & Win NT -70 - 64 MB

SQL Server 2000 • • • 동아제약 신정보 시스템 교체 동기 – TCO – 새로운 IT 환경의 변화에 따 른 경쟁력 확보 완벽한 객체지향 모델에 의한 개 발 – 700개 Component – 3000개 Program – 1500개 화면 성능 – 평균 3초 Response – CPU Peak 시 60% (DB Server / 마감 시) 500 User, 50~70 동시 사용자 비용 : 5억(H/W 포함) + 100 M -73 - Active-Passive 에 의한 1개 DB 서버 NLBS 에 의한 2개의 App 서버 Cluster Compaq 4 x Xeon 500 MHz 2 GB RAM 4 x Xeon 500 MHz 1 GB RAM IIS(Web Svr) SQL Server™ 7. 0 EE Windows 2000 COM+ Windows 2000®

SQL Server 2000 대표적인 Win-Back 사례 Switched from Sun/Oracle. Took 45 days 18 Million hits a month. Achieving 99. 998% Chose Microsoft over Sun. Took 60 days Transacted $2 B YTD. Achieving 99. 999% Switched from Unix/ Sun. Took 42 Days 4 th most visited site. Scaling to Billion page views Switched from SUN/Oracle. Took 90 days 3 rd most read book. End to End e-solution Switched from SUN/Oracle. Took 120 days Startup wasted $3 m/ 150 days on SUN/Oracle, Then built e-site on Microsoft $800 k/120 days Nation’s largest electronics retailer Switched from Java Largest B 2 B site. Achieving 99. 999% availability -74 -

Terra. Cluster – 3 Active / 1 Passive SQL Server 2000 3 Active 데이터베이스 서버 SQLInst 1 - Topo & Relief Data SQLInst 2 – Aerial Imagery SQLInst 3 – Aerial Imagery Compaq Controller E L S 논리적 볼륨 구조 One rack per database All volumes triple mirrored (3 x) Meta. Data on 15 k rpm 18. 2 GB Image Data on 10 k rpm 72. 8 GB Meta. Data 101 GB Image 1 339 GB Image 2 339 GB Image 3 339 GB Image 4 339 GB 2 spare volumes allocated per cluster 6 Additional 339 GB volumes to be added by year end (2 per Db Server) Compaq 8500 Controller F G Controller H I -76 - Controller M N T U Controller O P V U SQLInst 1 Compaq 8500 SQLInst 2 Compaq 8500 SQLInst 3 Compaq 8500 Passive Srvr Compaq DL 360 DL 360 Web Servers 8 2 -proc “Photon” DL 360

SQL Server 2000 • • • T 3 프로젝트 OS – Windows 2000 Data. Center DBMS – SQL Server 2000 Data warehouse – Initial 1. 2 terabyte Data – 700, 000 over products – 7. 7 billion fact rows Record – Complex queries complete in less than a second. 참여사 EMC Knosys 역 할 Storage design, storage hardware, backup mechanisms Client tools, Web client design Project concept and management, warehouse and Microsoft analysis designs, database management, performance analysis Unisys -77 - Server and network design, server and network hardware, initial query and GUI design