SQL Server 2000 Overview: 新特性 郝 雪莹 Microsoft Corporation

1. SQL Server 2000 Overview: 新特性郝雪莹Microsoft Corporation

2. 专注于质量 • SQL Server 2000 vs. SQL Server 7.0 • 50% more functional tests • 300% more stress tests • 25% more early adopters • 500% more beta users • 40 internal systems on SQL Server 2000 before release • More customers deployed earlier

3. Agenda • 安装/升级 • 管理工具 • 引擎功能的增强 • 支持XML • 复制 • Analysis Services • Desktop 与Windows CE

4. 安装/升级多实例 • 缺省实例 • Prior versions of SQL Server • 多实例 • Multi-instance is a paradigm • A named instance is an installation • SQL Server 2000 安装程序可以建立缺省实例和命名实例

5. 安装/升级实例 { • Default • Only one active • Version switch • Named • All instances can be active SQL Server 6.5 { SQL Server 7.0 or 2000 SQL Server 2000 SQL Server 2000 SQL Server 2000 Up to 15 SQL Server 2000

6. 安装/升级缺省实例 • 缺省实例 • 看起来象 SQL Server 7.0 做的那样 • Has an MSSQLServer and SQLServerAgent service • Registry keys are basically the same • HKLM/SW/Microsoft/MSSQLServer • 使用服务器名与服务器连接 • osql -E -Sservername

7. 安装/升级命名实例 • 命名实例 • 在同一台硬件服务器上的附加的 SQL Server服务 • 例如，连接到实例 “SQL2000” • osql -E -Sservername\SQL2000 • Services reflect the instance name • MSSQL$SQL2000, SQLAgent$SQL2000 • Registry keys reflect the name • HKLM/software/Microsoft/Microsoft SQL Server/SQL2000 • File path reflects the name • C:\Program Files\Microsoft SQL Server\MSSQL$SQL2000

8. 安装/升级Server 引用 • Services • Default: net start MSSQLServer • Named: net start MSSQL$Instance1 • Applications • Default: <ComputerName> • Named: <ComputerName>\Instance1

9. 安装/升级故障转移集群 • 在以前版本的SQL Server上有了很大提高 • 不需要其它的外部向导 • “Just Click Next” • 集群管理 • Add and remove nodes • Multiple IP addresses • Multiple virtual servers – each with a single instance • No Unclustering in SQL Server 2000 Service Packs

10. 安装与升级升级策略 • Versions Earlier than SQL Server 6.5 • Migrate to SQL Server 6.5, install SQL Server 2000, and then use the SQL Server 2000 Upgrade wizard • SQL Server 6.5 • Install SQL Server 2000 and then use the SQL Server 2000 Upgrade wizard • SQL Server 7.0 • Use the SQL Server 2000 setup program

11. Agenda • 安装/升级 • 管理工具 • 引擎功能的增强 • 支持XML • 复制 • Analysis Services • Desktop 与Windows CE

12. 管理/工具平台 • 支持多实例 • 在每台机器上所有的SQL Server 实例只需要一套管理工具 • 支持的平台 • Window 98, Windows 98 SE, Windows Me • Windows NT 4.0 SP5 and higher (including Embedded) • Windows 2000 (including Terminal Server) • IE 5 Required (for Unicode and XML parser) • MMC 1.2 • MDAC 2.6(微软数据访问组件)

13. 管理/工具SQL 企业管理器 • UI Support For • Attach / Detach database • Offline database • Column-level permission (new to SQL Server 2000) • Server Network utility • Register server using AD browser • Copy Database Wizard • Visual Data Tools Full Unicode Support • Cluster-Aware

14. 管理/工具Copy Database Wizard • 允许DBA 在SQLServer 服务器或实例的之间拷贝或者移动数据库 • Move or Copy 1 or N Database(s) • From SQL Server 7.0 / 2000 to 2000 • Copy and resolve logins if not present on destination • Optionally copy global namespace objects from master, error messages and jobs from msdb • Restartable Operations

15. 管理/工具Copy Database Wizard

16. 管理/工具与活动目录的集成

17. Agenda • 安装/升级 • 管理工具 • 引擎功能的增强 • 支持XML • 复制 • Analysis Services • Desktop 与Windows CE

18. 引擎功能的增强Cascaded DRI • Automatic Cascading of Deletes and Updates from PK to FK Tables • ANSI Standard Restrict and Cascade Semantics • CREATE TABLE country ( • country_name NVARCHAR(75) NOT NULL PRIMARY KEY ) • CREATE TABLE employee ( • employee_name NVARCHAR(75) NOT NULL, • country NVARCHAR(75) NOT NULL REFERENCES country • ON UPDATE CASCADE • ON DELETE NO ACTION, • passport_number VARCHAR(25) NOT NULL, • PRIMARY KEY (nationality, passport_number))

19. 引擎功能的增强User-Defined Functions • Multi-Statement T-SQL Routines • Scalar-Valued • Select f(c1) … • Select … where f2(c2) • Usable in any expression (Order By, Group By..) • Table-Valued (Also Called Relational) • Select c1 from f(arg)…

20. 引擎功能的增强Column-Level Collations • Multilingual Applications, Application Hosting, and Server Consolidation • Per-Database Collations • Multiple apps with different collations • Per-Column Collations • Deeper multi-lingual applications • Attach and Restore Databases with Different Collations from Server

21. 引擎功能的增强Instead-Of Triggers • Trigger Executed Instead of INSERT, DELETE, or UPDATE Operation • Application • Allows any view to be updateable • Implement before triggers • Supported on View or Table • Inserted / Deleted Tables Available

22. 引擎功能的增强新数据类型 • Table • Return type for table-valued UDFs • Allows easier programming of iterative operations • BigInt • 8-byte integer • SQL_Variant • Can store any base type （except text, ntext, timestamp, sql_variant) • Can be used to implement an open schema • User Define

23. Partitioned View: A View of the Form SELECT C1, C2,…, Cn FROM T1 UNION ALL SELECT C1, C2,…, Cn FROM T2 UNION ALL .... SELECT C1, C2,…, Cn FROM Tn 引擎功能的增强Partitioned View Enhancements

24. 引擎功能的增强Partitioned View Enhancements • Query Processor Eliminates Member Tables Not Necessary for Given Query or Update • Can Be Used to Implement Partitioned Data Across Multiple Tables in the Same or Different Servers • Distributed Partitioned Views • “Scale Out” feature that allowed us to achieve the record TPC-C numbers

25. 引擎功能的增强Partitioned View Enhancements

26. 引擎功能的增强在计算列上建索引 • Computed Columns Were Introduced in SQL Server 7.0 • SQL Server 2000 Allows You to Create Indexes on Computed Columns

27. 引擎功能的增强视图上建索引 • Creating an Index on a View – Considerations • SET options consideration and DETERMINISM • CREATE VIEW with SCHEMABINDING • CREATE INDEX

28. Agenda • 安装/升级 • 管理工具 • 引擎功能的增强 • 支持XML • 复制 • Analysis Services • Desktop 与Windows CE

29. XML SupportXML Query Results • SQL Language Extension SELECT… FROM… WHERE… ORDER BY… FOR XML (raw | auto [, ELEMENTS] | explicit) [, XMLData] [, BINARY base64])

30. XML SupportFOR XML – Raw Mode • Query: • SELECT CustomerID, OrderID • FROM Customers, Orders • WHERE Customers.CustomerID = Orders.CustomerID • FOR XML raw • Result: • <row CustomerID=“ALFKI” OrderID=“10643”> • <row CustomerID=“ALFKI” OrderID=“10643”> • <row CustomerID=“ANATR” OrderID=“10308”> • … • <row CustomerID=“MAISD” OrderID=“11004”>

31. XML SupportFOR XML – Auto Mode • Query: • SELECT Customers.CustomerID, OrderID • FROM Customers, Orders • WHERE Customers.CustomerID = Orders.CustomerID • FOR XML auto • Result: • <Customers CustomerID=“ALFKI”> • <Orders OrderID=“10643”> • <Orders OrderID=“10643”> • </Customers> • <Customers CustomerID=“ANATR”> • <Orders OrderID=“10308”>

32. XML Support HTTP Access – URL Types • URL Query • http://server/vroot?sql=“…” • Direct Query • http://server/vroot/dbobject/xpath • Template • http://server/vroot/vname?params • XML View • http://server/vroot/vname/xpath?params

33. Agenda • 安装/升级 • 管理工具 • 引擎功能的增强 • 支持XML • 复制 • Analysis Services • Desktop 与Windows CE

34. ReplicationGeneral Enhancements • Attach and Go Subscriptions • Copy a subscription database data file • Attach it and get a new subscription • Much easier to package and deploy subscriptions • In SQL Server 7.0 – each subscriber had to first create a database for its subscription • Also acts as a container to make it easy to create and deploy non-replicated objects

35. Agenda • 安装/升级 • 管理工具 • 引擎功能的增强 • 支持XML • 复制 • Analysis Services • Desktop 与Windows CE

36. Analysis ServicesDimension Architecture: Manageability • No Re-Processing Required For • Modifying slowlychanging dimensions • Changing the number of levels • Adding virtual dimensions • Support for Hierarchies inVirtual Dimensions • Support for Very Large Dimensions • Use ROLAP for > 10M members • Use automatic bucketing

37. Analysis ServicesDimension Architecture: Completeness • Ragged and Unbalanced Hierarchies • Custom Roll-ups • Write-Back to Dimensions • Easy to Define and Maintain Multiple Hierarchies in a Dimension • Multiple hierarchies now used indesigning aggregations • 128 Dimensions Per Cube • 128 Levels Per Dimensions - 256 Per Cube

38. Analysis Services“Ragged” Dimensions Country No States! State City • Variable Depth

39. Analysis Services“Parent-Child” Dimensions

40. Analysis ServicesMaking Data Mining Easy • Direct Support for Relational and Multi-Dimensional (OLAP) Data • No more text import routines! • Wizards for Setting Model Parameters, Without Being an Algorithm Expert • Data Mining on OLAP Data Automatically Uses the Semantics Already in the Cube

41. Data to Predict Training Data Mining Model Mining Model Mining Model Predicted Data Analysis ServicesData Mining Process Illustrated DM Engine DM Engine

42. Analysis ServicesA DMM Is a Table • A DMM Structure Is Defined as a Table • Training a DMM means inserting data into the table • Predicting from a DMM means querying the table • All Information Describing the Case Is Contained in Columns • All of the Operations (Creation, Training, Predictions) Will Treat the DMM As a “Table”

43. Analysis ServicesEnglish Query Integration • English Query Is a Component of SQL Server • Engine • Translates English to SQL or MDX • Modeling Tool • Development environment for defining semantics of a database (entities and relationships) • Now integrated with the Visual Studio shell

44. Analysis ServicesInternet Support on Client • Uses HTTP to Pass Through Firewalls • Uses IIS to Provide Authentication Over the Internet • Great Scalability – Middle Tier Is Not a Bottleneck • Supports a “Thick Client Model” – Pivot Table Service on Client “All the power of the client is available”

45. ConnectionString = “Provider = MSOLAP; Data Source = http://www.cubeserver.com; … P T S IIS OLAP Server P T S “Data server in the sky” Analysis ServicesHTTP Connectivity: Architecture msolap.asp

46. Agenda • 安装/升级 • 管理工具 • 引擎功能的增强 • 支持XML • 复制 • Analysis Services • Desktop 与Windows CE

47. Desktop and Windows CEWhat’s New on the Desktop • Multi-Instance Support • Windows Installer-Based Setup • Same Feature Set As the Server Version • T-SQL • XML • DTS • DMO • Replication enhancements • Some Limitations Based on OS Support (AD, etc.)

48. Desktop and Windows CE Windows CE Edition • Upward-Compatibility with SQL Server • Same resultsets • Compatible data types • Bi-Directional Merge Replication • Remote Data Access • Right Footprint Size for Devices • Approximately 1MB • OS Support • Windows CE 2.11 forward

49. Questions?

50. More Resources msdn.microsoft.com www.microsoft.com/sql msdn.microsoft.com/sqlserver www.microsoft.com/technet/sql msdn.microsoft.com/xml