SQL Server 2005 Hyo Kun Oh Sr Technology

SQL Server 2005 소개 Hyo Kun Oh / Sr. Technology Specialist Enterprise & Partner Group Microsoft

Agenda ¢ ¢ ¢ 개요 기업 데이터 관리 (Enterprise Data Management) 개발 생산성 (Developer Productivity) BI (Business Intelligence) 요약

SQL Server 2005 Editions ¢ ¢ Editions 32 -bit X 64 -bit Enterprise O O O Standard O Developer O Express O Mobile O Management Studio O BI Dev. Studio O Express Editions은 MSDE가 이름이 변경된 것임 Mobile Editions은 CE Editions이 이름이 변경된 것임 O

기본적으로 Off인 서비스 및 기능 ¢ Is not installed ¢ ¢ ¢ Analysis Services DTS Notification Services Replication Is not enabled ¢ ¢ ¢ Database Mirroring SQL Debugging Reporting Services Service Broker SQLi. Mail ¢ ¢ SQLMail ¢ ¢ Sqlcmd –I Install_SQLi. Mail. sql Sqlcmd –I sqlmap 90_install. sql XP_Web

보안 – User Schema Separation ¢ 각 object는 Schema 하위로 정리 됨 ¢ 각 Schema는 사용자 및 역할 (role)에 대한 원칙을 가지고 있음. Database Has default schema Owns ¢ 각 사용자는 기본 Schema를 가 지고 있음 ¢ ¢ Owns 다른 Schema를 권한에 근거하여 접근할 수 있음 새로운 DDL 추가 ¢ Schema 1 CREATE/ALTER/DROP for USER, ROLE, and SCHEMA Approle 1 User 1 Role 1 Schema 2 Schema 3 SP 1 Tab 1 Owns Fn 1

보안 – 스키마와 소유자 분리 SQL Server 2000: SELECT cust. ID FROM User 1. Orders. Table Exec User 1. Insert. Order. Proc (@orderid) Table Function Database Object Contained in Owned By Schema by d ne w O Owned by User 2 User View Stored Proc SQL Server 2005: SELECT cust. ID FROM Orders. Table Exec Orders. Insert. Order. Proc (@orderid) 사용자 변경 후 언제나 응용프로그램을 수정해야 하는 것은 아님

관리 도구 – Management Studio

확장 – Table/Index Partitioning ¢ Function < Schema < Table/Index Filegroup DATA_2002 Filegroup DATA_2003 Filegroup DATA_2004 Order History Table Filegroup IDX_2002 Filegroup IDX_2003 Filegroup IDX_2004 Customer ID Index Order Date < ‘ 2003 -01 -01’ Customer ID Index Order Date >= ‘ 2003 -01 -01’ and Order Date < ‘ 2004 -01 -01’ Order Date >= ‘ 2004 -01 -01’

확장 – 복제 (Replication) ¢ 복제 기능 향상 ¢ ¢ ¢ Stand-alone Replication Monitor 양방향 복제 성능 및 확장성이 2~4배 향상됨. DDL 역시 복제됨 ¢ 웹 기반 양방향 동기화 (Merge Replication) ¢ Oracle Publishing (Transactional Replication) ¢ ¢ Oracle Publishers (v 8+ on any OS) SQL Server 방식으로, SQL Server 관리도구로 구성함. ¢ Standard Transactional and Snapshot Publications SQL Server 2005 Distributor Subscribers

성능 ¢ Dynamic Management Views (DMV) ¢ ¢ Server 현황에 대한 정보를 Table 형식으로 제공함 EX) “sys. memory_clerks” “sys. schedulers” Tuning Tools ¢ XML Show Plan ¢ ¢ Database Tuning Advisor ¢ ¢ ¢ Time bound tuning, What-if analysis, Scalability SQL Profiler ¢ ¢ Publicly available Schema, Programmatic access, Portability Integrated with Performance Monitor & Deadlock Graph SQLTrace Event Logs 통합 및 Filter 제공

SQL Server Mobile ¢디바이스 지원 확장 ¢Support for future release of Windows Mobile Smartphone ¢Support for Tablet. PC ¢Support for next generation Windows Mobile devices ¢SQL Server Mobile과 Visual Studio와의 통합 ¢SQL Server Management Studio에서 통합 개발 ¢SQL Mobile에서 SSIS(Integration Services, DTS)로 데이터 직접 송수신 ¢진보된 기능들 storage engine ¢더 빨라진 Query Processor ¢새로운 ¢강력하고 다양한 동기화 방법 지원

64 -bit Platform Scalability q Optimized for Windows Server 2003 q Support for AMD Opteron & Intel Itanium with Beta 2 q q Manageability Great performance n Large memory addressability (up to 32 TB) n Nearly unlimited virtual memory (up to 8 TB) n I/O savings due to larger memory buffer pools q T-SQL code-compatibility with SQL Server 2000 8 node clustering support Same on-disk format as 32 -bit for easy migration 64 bit support for relational engine and BI q Compelling alternative to expensive Unix solutions q q q Cost Savings Intel EM 64 T coming with Beta 3 The highly scalable database platform for memory intensive, performance-critical business applications

SQL Server 2005 엔진 아키텍처 SQL Engine ¢ SQL Engine은 다음의 기능을 조정함 (Hosting); ¢ ¢ CLR ¢ ¢ Hosting Layer ¢ SQL OS ¢ Windows Assembly Loading Memory management Security Model Reliability Threads & Fibers Deadlock detection Execution context SQL OS ¢ Connection Manager

SQL Server 2005 개발 환경 ¢ Visual Studio 개발 환경 통합 ¢ ¢ ¢ SOA 기반 새로운 응용프로그램 개발프레임웍: ¢ ¢ 개발 도구 및 전체 개발 과정 통합 개발 언어 및 프레임웍 통합 사용 (. NET Framework) SQL Service Broker SQL Server Notification Services SQL Server Reporting Services 새로운 서버 프로그래밍 지원 ¢ ¢ ¢ Stored procedures, triggers, & functions in C++, C#, or VB Server-side Abstract Data Types Industry leading programming tools

개발 –. NET과의 통합 VB, C#, C++ VS. NET Project Runtime hosted by SQL (in-proc) Build Assembly: “Tax. Lib. dll” SQL Data Definition: create assembly … create function … create procedure … create trigger … create type … SQL Server SQL Queries: select sum(tax(sal, state)) from Emp where county = ‘King’

향상된 T-SQL ¢ New Types ¢ Varchar(max), Varbinary(max) ¢ ¢ ASSEMBLY New Queries ¢ ¢ ¢ Alternative to Text, Image Uniform Programming model Exception Handling : BEGIN TRY/CATCH BLOCK Recursive Queries MARS (Multiple Active Result Sets) ¢ Isolation 기반 Row versioning 제공 ¢ Distributed transactions, including queries in distributed partitioned databases, are not supported.

향상된 XML 지원 ¢ 내장된 XML 지원 ¢ Unified XML & relational store ¢ ¢ ¢ Xquery를 이용한 데이터 직접 변경 XPath 2. 0 지원 빠른 성능의 XML Parsing & XSLT engines 제공 XML 데이터 형식 및 XML index 지원 ¢ Native XML Datatype ¢ ¢ ¢ Columns, Variables, Parameters XML Index ¢ ¢ 단일 엔진으로 SQL & XQuery 동시 지원 SQL engine & optimizer 를 최적화에 활용함 예: CREATE XML INDEX idx_1 ON docs (x. Doc) Client access using ADO. NET & SOAP XML views ¢ Schema Mapping을 통한 일반 형식 및 XML 형식 동시 지원

SOA 기반 개발 지원 ¢ Service Oriented Architectures (SOA)에 근거한 환경을 제공함. ¢ ¢ Web Services를 통한 SQL Server 접근 제공 (HTTP/SOAP) Function, SP. T-SQL Batch을 웹서비스화 Service Broker – Service based, decoupled, asynchronous, reliable, distributed messaging Query Notifications – Enabling responsive multi-tier data backed caches Web Clients Highly Responsive, Data -Caching Mid-Tier Queries with Notifications SQL requests via HTTP/SOAP Legacy Non-Windows System Service based transactional workflow

SQL Service Broker 아키텍처 ¢ ¢ 데이터베이스 기반 신뢰성 있는 메시지 송수신 프로그래밍 환경 (Asynchronous, Distributed, Decoupled programming environment) 다음의 정의함 ¢ ¢ QUEUE CONVERSATION MESSAGE Service Program 특징 ¢ ¢ ¢ 비동기 및 동기 트랜잭션 빠른 성능 제공 다중 메시징 구성 가능 Message

SQL Server 2005 Reporting Services Analysis Services OLAP & Data Mining Data Transformation Services ETL SQL Server Relational Engine Ma n a g e m e n t T o o l s Devel o p men t T o o l s 강력한 기업 데이터 관리 및 BI 솔루션 구축 환경

선도적 Analysis Services ¢ 통합된 Dimensional Model ¢ ¢ Pro-active caching ¢ ¢ Bringing the best of MOLAP to ROLAP 진보적인 BI (Business Intelligence) ¢ ¢ Integrating relational and OLAP views KPIs, MDX scripts, translations, currency… Web services 지원 ¢ Native XML/A

다양한 알고리즘 지원 Decision Trees Clustering Time Series Association Naïve Bayes Introduced in SQL Server 2000 Sequence Clustering Neural Net

향상된 SSIS (DTS) ¢ Enterprise ETL platform ¢ ¢ Best in class usability ¢ ¢ ¢ High performance High scale Trustworthy and reliable Rich development environment Source control Visual debugging of control flow and data Great range of transforms out-of-the -box Highly extensible ¢ ¢ Custom tasks Custom enumerations Custom transformations Custom data sources

SSIS (DTS) 구성 예 Alert & escalation Call centre: semi-structured data Text mining components Data mining components Custom source Merges Standard sources Data cleansing components Mobile data Warehouse Legacy data: binary files Application database SQL Server Integration Services • 실시간 및 다양한 데이터에 대한 강력한 데이터 통합 능력 • 감사 가능한 단일 데이터 로딩 및 처리 엔진 제공. • 보고서 및 알림에 대한 병렬 (비동기) 처리가 가능한 엔진 제공 Reports

향상된 Reporting Services ¢ Introduced with SQL Server 2000 ¢ ¢ ¢ Open, extensible enterprise reporting solution Report authoring, management, delivery Office System integration VS. NET development environment SQL Server 2005 enhancements ¢ ¢ Integration with AS, DTS, management tools Developer enhancements Improved report interactivity Rich end-user reporting

구축 사례 프로젝트 목표: 트랜잭션 발생 시점의 데이터를 수집하여 제고 관리의 문제를 미리 발견/대응하여 수익성을 높임. Business Challenge l l Instances of store out of stock Inappropriate stock levels Difficult to perform analysis of large data volumes Overburdened operational systems Solution: SQL Server Integration Services l l l Unique slowly changing dimension handling enables better handling of huge stock dimension High performance on commodity box – no contention with warehouse server 1. 6 TB Raw Data Results/Benefits l l l 50% reduction in development time over Oracle SQL Server Integration Services shows up to 400% performance improvement Deliver more meaningful, up-to-date info

“This new version - code-named Yukon - is slated to be Microsoft's greatest attack into corporate enterprise data centers to date. The three main themes of this release enterprise-level management, decision support, and a deep development environment integration - map well to many of the enterprise data managements challenging IT organizations today. ” Mark Beyer, Meta Group JULY 26, 2004 -“The only real downside to getting started with the beta (2) is that it's bound to make you impatient to deploy the real thing. “ JULY 26, 2004 – “In addition to… 64 -bit functionality, Yukon testers this week can begin to try out several other enhancements in SQL Server 2005 Beta 2 designed to bolster productivity, security and business intelligence. ” MARCH 10, 2004 - “(SQL Server Yukon is) seen as key to the software maker's push into enterprise computing…Yukon will add scalability and other improvements that should help Microsoft compete better with database market leaders Oracle and IBM. ”

참조 ¢ ¢ ¢ SQL Server 2005 ¢ http: //www. microsoft. com/korea/sql/ Tech. Net ¢ http: //www. microsoft. com/technet Microsoft Learning ¢ http: //www. microsoft. com/learning

¢ Runs Microsoft ¢ ¢ ¢ 15+ apps live on SQL Server 2005 today! ¢ ¢ 50+ applications on SQL Server 2005 before RTM Key mission critical apps live on Beta 2 ¢ ¢ Runs 100% on SQL Server Runs Microsoft products before customers SAP R/3 Deployment, 1. 7 TB “Feedstore”: Staging DW for all Microsoft data Ø 2 TB of Data, 1800+ Tables, 500 subscribing systems Microsoft Sales Revenue Reporting and BI system Ø 9, 000 users world-wide Ø 25, 000 reports/week Full data life-cycle in SQL Server 2005 ¢ [Sales] SAP Feedstore Microsoft Sales