1 / 60

Microsoft SQL Server 2000 New Features and Enhancements 10 June 2002

Microsoft SQL Server 2000 New Features and Enhancements 10 June 2002. David Rodriguez Microsoft SQL Server Technology Specialist . What You Will See Today. New and Enhanced Features in SQL Server 2000, Including Multiple instances XML Cascading RI Replication

emele
Télécharger la présentation

Microsoft SQL Server 2000 New Features and Enhancements 10 June 2002

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Microsoft SQL Server 2000New Features and Enhancements 10 June 2002 David Rodriguez Microsoft SQL Server Technology Specialist

  2. What You Will See Today • New and Enhanced Features in SQL Server 2000, Including • Multiple instances • XML • Cascading RI • Replication • Analysis Services and Data Mining

  3. Agenda • Product Roadmap • Installation/Upgrade • Administration/Tools • Engine Enhancements • XML Support • Replication • Analysis Services • Desktop and Windows CE

  4. SQL Server 2000A Major Release • New data types • Column-level collations • Virtual Cube Editor • Linked cubes • MDX Builder • Dimensions • Security in Analysis Services • OLAP Actions • Custom rollups • Cascading referential integrity and actions • INSTEAD OF triggers • Indexes on computed columns • Queued replication • DTS enhancements • Online reorg • Copy Database Wizard • Distributed Partitioned Views • Log Shipping • Parallel CREATE INDEX • Parallel scan • Parallel DBCC • Failover clustering • Failover cluster management • 32 CPU SMP system support • 64 GB RAM support • VI SAN support • Indexed views • ROLAP dimension storage • Distributed Partitioned Cubes • Online index reorganization • Differential backup • User-defined functions • Server-less snapshot backup  • SQL Query Analyzer debugger • XML support • SELECT … FOR XML • OpenXML • XML Views • XML Updategrams • XML View Mapper • XML Bulk Load • URL and HTTP db access • HTTP access to cubes • Multi-instance support • Integrated Data Mining • Full-Text Search in formatted docs • English Query for the Web • C2 security rating (NSA) • Installation disk imaging • Active Directory integration • Self-management and tuning

  5. SQL ServerProduct Roadmap Since RTM CY2002 SQL Server “Yukon” 2003 • SQLXML 1.0 • XML Updategrams • XML Bulk Load • XML View Mapper 1.0 • Service Pack 1 • SQLXML 2.0 • Service Pack 2 Driver for JDBC Web Services Toolkit SQL Server CE 2.0 Notification Services SQL Server 2000 64-bit Programmability • Deep XML support • Common Language Runtime integration Scalability • Self-tuning Availability • Online operations Business Intelligence • End to End data warehousing

  6. Installation/UpgradeMulti-Instances • Default Instances • Prior versions of SQL Server • Multi-Instances • Multi-instance is a paradigm • A named instance is an installation • SQL Server 2000 Setup Can Create a Default Instance and Named Instances

  7. Installation/UpgradeInstances { • 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

  8. Installation/UpgradeA “Default” Instance • A “Default” Instance • Looks like SQL Server 7.0 did • Has an MSSQLServer and SQLServerAgent service • Registry keys are basically the same • HKLM/SW/Microsoft/MSSQLServer • Connects to the server with just the server name • osql -E -Sservername

  9. Installation/Upgrade“Named” Instances • A “Named” Instance • Additional SQL Server service on the same computer • For example, connect to instance “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

  10. Installation/UpgradeFailover Clustering Installation • Improvements Over Prior Versions of SQL Server • No External Wizard • “Just Click Next” • Cluster Management • Add and remove nodes • Multiple IP addresses • Multiple virtual servers – each with a single instance • No Unclustering in SQL Server 2000 Service Packs

  11. Installation/UpgradeUpgrade Strategies • 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

  12. Administration/ToolsSQL Enterprise Manager • 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, Incl. Service Manager

  13. Administration/ToolsCopy Database Wizard • Allows DBA to Copy or Move Databases Between SQL Server Computers or SQL Server Instances • 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

  14. Administration/ToolsData Transformation Services • Multi-Phase Data Pump (Package / Step Restart Ability, Aggregation) • DTS Package – Save as VB • Support for Run-Time Parameters • Improved Usability / Performance • New Custom Tasks • New Custom Transformations

  15. Administration/ToolsSQL Query Analyzer • Increased Usability / Authoring • Multiple result sets to grid • Editor enhancements • Block indent, block comment out • Bookmarks in editor • Object Browser • Drag / drop scripting, editing • Open Table (table editor) • Extended Property Editor • Browser docked left or right in shell • Object Search • Templates and Parameter Search / Replace

  16. Administration/ToolsSQL Query Analyzer • Integrated Statement Tracing • Basic statement, duration, CPU, I/O • Complete Integration of Tuning Wizard Instead of Index Analysis • Client Statistics (Client-Side Performance Counters) • Shortcut Queries • Assign SPs, XPs, short SQL Statements to pre-defined key combinations • Alt + F1, Ctrl + F1, Ctrl + 1 … 0 • Custom Commands • Basic shell out like Microsoft Visual Studio 6.0 • Command-line version control integration

  17. Administration/ToolsSQL Query Analyzer • Open Table with Updateable Grid • Need PK or unique index on table, using server-side keyset cursor • Integrated T-SQL Debugger • Stored procedure debugging • Global, local variables, call stack • Version-Dependent and Case-Sensitive Color Coding for SQL Server 6.5, 7.0, and 2000 • Manage Indexes, Index Distribution Information

  18. Administration/ToolsIndex Tuning Wizard • Added Support for Indexed Views and Indexes on Computed Columns • Projected Volumes (Via Scaling Factor) • Index Analysis in Query Analyzer Replaced With Full Index Tuning Wizard • Works on current script file or • Highlighted selection within script • Display Indexes to Be Deleted on Recommendation Page

  19. Administration/ToolsAuditing • Driven by C2 Security • Implemented Using SQL Trace • File roll-over on size • Shut down SQL Server when out of space • SQL Profiler can be used to read audit trail • No separate auditing UI in this release • C2 auditing via switch on the server exec sp_configure 'c2 audit mode',1 • Finer-level granularity available via auto starting traces

  20. Administration/ToolsSQL Profiler • Trace Templates • Replaces Create Trace wizard and Trace definitions saved in the Registry • Canned scenario-based traces, like Dead-Lock, Replay, Tuning, Stored Procedure (steps), etc. • Support Server-Side Trace File Creation • Table and File as Trace Destination • Support for Large Traces / Replays

  21. Administration/ToolsScripting Options • SQL-DMO • Introduced in SQL Server 6.0 (SQL OLE) • Abstract server changes • WMI (Windows Management Instrumentation) • Core component of Windows 2000 and available for all other Windows platforms • “The Way” to manage everything • SQL Server is just one of many providers

  22. Administration/ToolsActive Directory Integration • Service Publication for SQL Server • Also Databases, Replication Publications • Domain-Wide “Locator” Service • “What SQL Servers are on the network?” • Objectives • 1. Directory lookup • 2. Location transparency – “HR” Server • Generic AD SQL Server Browser UI • Integrated within all management tools • Schema Ships with Windows 2000

  23. Administration/ToolsDEMO 2: Administration Tools • Enterprise Manager • Active Directory • Copy Database wizard • Query Analyzer • Object browser, object search • Templates • Debugger

  24. Agenda • Installation/Upgrade • Administration/Tools • Engine Enhancements • XML Support • Replication • Analysis Services • Desktop and Windows CE

  25. Engine EnhancementsCascaded 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))

  26. Engine EnhancementsUser-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)… • Strongly Typed Input Args with Return Value • No output parameters • Inline Relational Functions • Effectively a parameterized view

  27. Engine Enhancements Scalar UDF Example CREATE FUNCTION ExtractNamePart(@InName varchar(100), @part tinyint) RETURNS varchar(30) AS BEGIN DECLARE @offset tinyint SET @offset = charindex(' ', @InName) RETURN CASE @part WHEN 1 THEN substring(@InName, 1, @offset-1) WHEN 2 THEN substring(@InName,@offset+1, len(@InName)) ELSE NULL END END

  28. Engine Enhancements 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 • Full Backward-Compatibility

  29. Engine Enhancements 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

  30. Engine Enhancements New Data Types • BigInt • 8-byte integer • SQL_Variant • Can store any base type (except LOB) • Can be used to implement an open schema • Table • Return type for table-valued UDFs • Allows easier programming of iterative operations • Rowversion (new alias for TimeStamp)

  31. bigint Data Type • 8 byte integer • -263 (-9,223,372,036,854,775,808) through 263-1 ( 9,223,372,036,854,775,807) • Example: DECLARE @MyBigintVar bigint SET @ MyBigintVar = 97876908907 • sysindexes.rowcount is now a bigint rather than binary(8)

  32. sql_variant Data Type • Can store any of the base SQL Server types except BLOB data or timestamp • Similar to VB variant data type • A single column, parameter, or variable storing data values of different data types • Each instance stores two items: • Data value • Metadata describing the value • Base data type, scale, precision, and collation • Maximum length is 8016 bytes • sysproperties.value is the only system table sql_variant column

  33. table Data Type • Can ONLY be used to define: • Local variables • The return value of a user-defined function • Includes definitions of: • Columns, their data type, precision, and scale • Optional PRIMARY KEY, UNIQUE, and CHECK constraints • New way to work with temporary result sets • Can be used to replace temporary tables • Scope is the batch, stored procedure, or function • Not affected by transaction rollback or commit

  34. Using table Data Type vs. Temporary Table

  35. rowversion Data Type • Synonym / Replacement for timestamp • Otherwise currently identical to timestamp • From BOL:The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type. A future release of Microsoft® SQL Server™ may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.Microsoft® SQL Server™ 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements. rowversion is subject to the behaviors of data type synonyms. For more information, see Data Type Synonyms.In a CREATE TABLE or ALTER TABLE statement, you do not have to supply a column name for the timestamp data type:CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp)If you do not supply a column name, SQL Server generates a column name of timestamp. The rowversion data type synonym does not follow this behavior. You must supply a column name when you specify rowversion.

  36. 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 Where T1,… Tn have disjoint constraints on C2 Engine Enhancements Partitioned View Enhancements

  37. Engine Enhancements Indexes on Computed Columns • Computed Columns Were Introduced in SQL Server 7.0 • SQL Server 2000 Allows You to Create Indexes on Computed Columns • The expression defining the computed column must be DETERMINISTIC • Certain SET options must be specified

  38. Engine Enhancements Indexes on Views • Defining an Index on a View • A.k.a., Materialized views • The view may be a join, an aggregation, or their combination • Once the index is created, the contents of the view are persisted • The index is maintained automatically, as with any other index • The optimizer may use the index on a view even if the view is not directly referenced in the query

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

  40. XML SupportFOR XML – Raw Mode • One <Row> Element Per Row in the Result Set • No Nested Elements • Columns / Values in the Result Set Are Attributes / Values on the <Row> • Similar to CSV but in XML Format

  41. XML SupportFOR XML – Auto Mode • Table / View Name in the Database Used for the Element Name • Column Names Used for Attributes • Supports Nested XML Output • Nesting determined by ordering of columns in SELECT clause • Sibling relationships not supported • Change Names Using Table and Column Aliases

  42. XML SupportFOR XML – Explicit Mode • Provides Complete Control Over Format of XML Result • Columns Can Be Individually Mapped to Attributes or Sub-Elements • Supports arbitrary nesting • Sibling relationships supported • Collapses / Hoists Hierarchy • Constructs ID/IDREF Relationships • CDATA Sections in XML Output • Stores XML Annotations

  43. XML Support OpenXML • Used with T-SQL Stored Procedures • Provides a Relational View on XML • Specifies Row and Column Selectors Using an XPath • Supports • Attribute and element-centric mappings • XML annotation / overflow column • Hierarchy support

  44. 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

  45. SQL XML 3.0 • Web services (SOAP) support • Web services support offers SQL Server functionality to the client • can send SOAP HTTP requests to the server that is running SQLXML to executestored procedures • user-defined functions (UDFs) • XML templates • XML views using annotated XSD schemas • Create XML views of relational data by using annotated XML Schema Definition language (XSD) schemas. • Client-side XML formatting In SQL Server 2000 • Data access components • SQLXMLOLEDB Provider This OLE DB provider supports client-side and server-side XML formatting of the rowsets that are generated by SQL Server. • SQLXML Managed Classes exposes the functionality of SQLXML within the Microsoft .NET Framework. • can write a C# application to access XML data from an instance of SQL Server, bring the data into the .NET environment, process the data, and then send the data back in a XML document (the DiffGram) to apply the updates to SQL Server. • DiffGrams - DataSet component of the Microsoft .NET Framework.

  46. 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

  47. ReplicationSchema Replication • Add / Drop Column • Automatically added to published articles • Supported by replication stored procedures • No need to Drop/Add publications because a new column is added to a table • One of the top requests today • On-Demand Script Execution • Post a general SQL script for execution at all subscribers

  48. 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

  49. 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

  50. Analysis ServicesSecurity • Authentication Mode • Windows NT integrated • Using Internet Information Server • Windows NT challenge/response; Anonymous; SSL • Cell Security • Declare any set of cell ranges in the cube as protected • Dimension Member Security • Hide parts (or all) of the dimension members • “Shrinks” the multi-dimensional space • Intuitive Visual Tools to Define Security Permissions

More Related