1 / 45

SQL 2005 New Features

SQL 2005 New Features. Three Categories Database Administration Features Database Development Features Business Intelligence Features. Database Administration Features. Native 64 Bit Support NUMA Support Support for Hyper-Threading Integration of .NET Framework Supports XML Data types

ruthvela
Download Presentation

SQL 2005 New Features

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. SQL 2005 New Features Three Categories • Database Administration Features • Database Development Features • Business Intelligence Features

  2. Database Administration Features • Native 64 Bit Support • NUMA Support • Support for Hyper-Threading • Integration of .NET Framework • Supports XML Data types • Security Enhancements • New Set of Management Tools • Updated Set of Performance Tools • Server Management Framework completely revamped • Database Availability Enhancements • New Backup and Restore Features

  3. Database Development Features • Integration of .NET Common Language Run-time (CLR) • T-SQL Enhancements • ADO.NET Enhancements • Notification Services added as a new subsystem • SQL Server Service Broker added as a new subsytem • New Native XML data type • XQuery Support • Native HTTP SOAP Access

  4. Business Intelligence Features • New Reporting Services subsystem • Totally rewrote DTS and renamed Integration Services • Analysis Services Engine Enhancements • New Analysis Services Management and Development features • New Object Definition Language (ODL) • New Unified Dimensional Model • New Data Mining Algorithms

  5. Administration - Native 64 Bit Support • 32 bit version of SQL Server can address a max of 32GB of RAM using Advanced Windowing Extensions(AWE) – physical addressable memory limited to 4GB (2GB for OS and 2GB for applications) • Native 64-bit – raising the max addressable memory to 32TB

  6. Administraiton - NUMA Support • Non-Uniform Memory Architecture Support • A system architecture – manages CPU and memory usage more efficiently • The processors are able to access the data in RAM faster than the RAM and system bus can provide it

  7. Administration- Hyper Threading • CPU technology • Creates two logical processors for each physical processor in a system • Each logical processor – executing separate threads simultaneously • Provide better resource consumption

  8. Administration –Enhanced Multiple Instance Support • SQL 2000 – 15 instances • SQL 2005 – 50 instances

  9. Administration – New Data Types • Varbinary(max) • XML data type create table MyXMLDocs (DocID INT Primary Key Identity, MyXmlDoc XML) insert into MyXmlDocs Values (‘<tag1> <id>1</id><text>text</text></tag1>’)

  10. Administration – Native HTTP Support • Process incoming web services requests without the presence of IIS or another web server • Support both Windows and SQL Server authentication, as well as SSL

  11. Administration – Database Data File Enhancements • Change the path of a database’s data and log files – offline operation • Alter database <name> modify file(name=<‘data_file_name’>, filename=<‘new path’> • SQL 2000 only supports such changes for tempdb database

  12. Administration – Data Partitioning • Break a single databse object such as a table or an index into multiple pieces

  13. Administration – Online Indexing Operations • Older version: didn’t allow any access to an index while it was being rebuilt • 2005 enables applications to access the index as well as perform update, insert, and delete operations on a table while the index rebuilding operation is running • Rebuilt a clustered index – no need to rebuild the related non-clustered indexes

  14. Administration – Bulk Data Loading • Uses an XML-base format file • Old BCP format file can still be used • Supports logging of bad rows so the data loading can continue even if invalid row and data are encountered

  15. Administration – Full Text Search • Old version: require the use of stored procedure • 2005 version: new DDL statements, such as “create fulltext catalog” and “create fulltext index” • 2005 version: backup and restore; ability to use thesaurus to find synonyms of search words.

  16. Administration – Security Enhancement • User-Sechema Separation • Stored Procedure Execution Context • More Ganular Permissions Control • Password Policy Enforcement • Catalog Security

  17. Administration – Security Enhancement • User-Sechema Separation

  18. Administration – Security Enhancement • Stored Procedure Execution Context

  19. Administration – Security Enhancement More Ganular Permission Control • add more permission at different scope • the principle of least privileges • DBA controls exactly what permission are • Same permission can be applied at multiple scopes

  20. Administration – Security Enhancement Password Policy Enforcement • Enforce password strength • Password expiration • Account lockout policies • Enabled in the default installation

  21. Administration – Security Enhancement Catalog Security • Implemented as “catalog views” • Secured by default • Use “View Definition” to grant permission Server 2000: system tables in the individual database and in the master database

  22. New Set of Management Tools SQL Computer Manager

  23. New Set of Management Tools SQLServer Management Studio

  24. New Set of Management Tools SMS – Solution Explorer

  25. Business Intelligence Development Tools Business Intelligence Developemnt Studio

  26. Administration - Performance Tools Database Tuning Advisor

  27. Administration –Database Mirroring • Enables a second standby database to be available in about 2-3 seconds • Zero data loss and up-to-date with the primary database server

  28. Administration – Snapshot Isolation • Can be access in read-only mode for reporting

  29. Administration – Fast Recovery • SQL 2000, not available until all of the redo and undo operations were completed • SQL 2005, immediately available

  30. Administration Dedicated Administration Connection • DBA has access to the server regardless of the server’s current workload • Kill any runaway processes • Command line operation: c:\Sqlcmd -A

  31. Administration – Online Restore • SQL 2000, the database is not available during a restore operation • SQL 2005, the database is available as soon as the primary filegroup is restore; only the data being restored is unavailable

  32. Administration – Others • Concurrent database and log backup • Backup of Full-text Catalogs • Database page checksum • Continue Past Restore Errors • Backup Media Mirroring • Hot-Plug Memory • Improved Dynamic Configuration

  33. Database Development Features • Integration of .NET Common Language Run-time (CLR) • T-SQL Enhancements • ADO.NET Enhancements • Notification Services added as a new subsystem • SQL Server Service Broker added as a new subsytem • New Native XML data type • XQuery Support • Native HTTP SOAP Access

  34. Development- Integration of .NET Common Language Run-time • “Assembly” is the unit of deployment for .Net objects with the database • Create a DLL using Visaul Studio 2005 • Import that DLL into SQL Server as an assembly • Link that assembly to a database object such as a store procedure or a trigger

  35. Development-T-SQL Enhancement • Top clause can use a variable or a scalar subquery, such as “select top @number ..” • Common Table Expression – handle recursive queries • PIVOT-transforms a set of rows into columns • UNPIVOT – transforms pivoted columns back into rows …..

  36. Development-ADO.Net Enhancements • Server side cursor for the application • Asynchronous support – issue multiple database requests on different threads without blocking the threads • Multiple Active Result Sets • Paging – bind to a range of rows from a result set • Bulk insert • Common Connection Model – a new Provider Factory

  37. Development-Notification Services

  38. Development-Notification Services

  39. Development-SQL Service Broker

  40. Development-XML Integration • Native XML data type • Xquery Support • XML indexes • FOR XML Enhancement • OPENXML Enhancement • XML Bulk Load • Native HTTP SOAP Access • XML Enhancement for Analysis Server • System XML Caatelog Views

  41. Business Intelligence Features • New Reporting Services subsystem • Totally rewrote DTS and renamed Integration Services • Analysis Services Engine Enhancements • New Analysis Services Management and Development features • New Object Definition Language (ODL) • New Unified Dimensional Model • New Data Mining Algorithms

  42. Business Intelligence – Reporting Service

  43. Business Intelligence –Reporting Service

  44. Business Intelligence –Integration Service

  45. Business Intelligence –Analysis Services Data Mining prediction can help make better decision and make the best use of its resources Decision Tree -2000 Times Series - 2005 Custering and Sequence Clustering-2000 Naïve Bayes -2005 Association Rules -2005

More Related