What’s new in SQL Server 2008

2. What’s new in SQL Server 2008 Thavash Govender BI Consultant iSolve Business Solutions

3. GameChangers FormFactors Hard Drive Prices StorageInnovation DataTypes IT & Regulatory Compliance

4. Enterprise Reporting Engine • Internet Report Deployment • Block Computations • Scale out Analysis • BI Platform Management • Export to Word and Excel • Author reports in Word and Excel • Report Builder Enhancements • TABLIX • Rich Formatted Data • Personalized Perspectives • … and many more • SQL Server Change Tracking • Synchronized Programming Model • Visual Studio Support • SQL Server Conflict Detection • FILESTREAM data type • Integrated Full Text Search • Sparse Columns • Large User Defined Types • Date/Time Data Type • LOCATION data type • SPATIAL data type • Virtual Earth Integration • Partitioned Table Parallelism • Query Optimizations • Persistent Lookups • Change Data Capture • Backup Compression • MERGE SQL Statement • Data Profiling • Star Join • Transparent Data Encryption • External Key Management • Data Auditing • Pluggable CPU • Transparent Failover for Database Mirroring • Declarative Management Framework • Server Group Management • Streamlined Installation • Enterprise System Management • Performance Data Collection • System Analysis • Data Compression • Query Optimization Modes • Resource Governor • Entity Data Model • LINQ • Visual Entity Designer • Entity Aware Adapters

5. Beyond Relational Beyond Relational • Location Data Type • Spatial Data Type • Virtual Earth Integration • FILESTREAM Data Type • Integrated Full Text Search • Sparse Columns • Large User Defined Types • Date/Time Data Type

6. Beyond Relational Beyond Relational • Location Data Type • Spatial Data Type • Virtual Earth Integration • FILESTREAM Data Type • Integrated Full Text Search • Sparse Columns • Large User Defined Types • Date/Time Data Type Store and Consume Any Type of Data

7. Beyond Relational Beyond Relational • Location Data Type • Spatial Data Type • Virtual Earth Integration • FILESTREAM Data Type • Integrated Full Text Search • Sparse Columns • Large User Defined Types • Date/Time Data Type Store and Consume Any Type of Data Deliver Location Intelligence

8. Dynamic Development Dynamic Development • Synchronized Programming Model • Visual Studio Support • SQL Server Conflict Detection • Entity Data Model • LINQ • Visual Entity Designer • Entity Aware Adapters • SQL Server Change Tracking • Merge SQL Statement

9. Comparison – Beyond Relational SQL Server 2005 SQL Server 2008 • Large UDTs • Flexible Columns • Wide Tables • Filtered Indices • HierarchyID • User Defined Types • Remote BLOB Store API • FILESTREAM • Integrated FTS • Full Text Indexing • Fully supported Geometry and Geography data types and Functions Documents & Multimedia Relational Data Spatial XML • XML Data Type and Functions • XML Upgrades

10. FileStreams • Storage Attribute on VARBINARY(MAX) • Unstructured data stored on file system • Dual Programming Model • TSQL (Same as SQL BLOB) • Win32 Streaming APIs with T-SQL transactional semantics • Data Consistency and Transactions • Integrated Manageability • SQL Server Security Stack

11. Spatial Data • Spatial Data is about mapping …. • Many applications make very direct use of mapping. • The map may very well be the primary output of these applications • Examples: • Consumer mapping products (Virtual Earth, etc.) • Cadastral mapping • Utility (electrical / water / gas) grid layouts • Business geographics

12. Beyond Mapping • Many applications may make use of spatial data, even if they do not explicitly make maps. • Examples: • Send warehouse pickers on efficient runs • Predict bus arrival times • Applying for building variances • Your favorite LOB app here

13. What is Spatial Data ? • Vector • Points • LineStrings • Polygons (Areas, Regions) • Raster • Satellite Imagery • Digitized Aerial Photos

14. Example Which roads intersect Microsoft’s main campus? SELECT * FROM roads WHERE roads.geom.Intersects(@ms)=1

15. Example Code • Create an instance: declare @g geography set @g = geography::Parse(‘POINT(47.6456 -122.12551)’) • Create a table: create table T(id int, region geography) • Select some data select * from T where region.STIntersects(@g) = 1

17. What’s new for developers ? • ADO.NET Data Entities • New T-SQL statement : MERGE • Table-Valued-Parameters • GROUPING SETS • New data types / storage options : • HierarchyID • User-Defined-Types (CLR) (Unlimited size) • DATE / TIME / DATETIME2 / DATETIMEOFFSET

18. Enhanced Date and Time Types • Problems with existing types • Application Complexity and storage cost when only date or time is used. • Narrow Ranges • Lack of time zone awareness limit scenarios.

19. DATE / TIME • DATE • January 1st, 0001 A.D.  December 31st, 9999 • Precision : 1 day • Storage : 3 bytes • TIME • 00:00:00.0000000 (midnight)  23:59:59.9999999 • Precision : default is 7 decimal places (100 ns) • Storage : 3 – 5 bytes, depending on precision

20. DateTime2 • January 1st, 1 A.D.  December 31st, 9999 23:59:59.9999999 • Precision : 100 ns, up to 7 decimal places (default) • Storage : 6 – 8 bytes, depending on precision • New system functions that return DATETIME2 values: • SYSDATETIME() • SYSUTCDATETIME() • New arguments for DATEPART, DATEDIFF: • MICROSECOND • NANOSECOND

21. DateTimeOffset • Jan. 1st, 1 A.D.  Dec. 31st, 9999 23:59:59.9999999 +- 14:00 • Precision : like others, 100 ns (7 decimal places) • Storage : 8 – 10 bytes, depending on precision • New system functions / arguments: • SYSDATETIMEOFFSET() • SWITCHOFFSET() • TODATETIMEOFFSET() • DATENAME(TZOffset, <value>) • Now we can centrally store local time information and still report using UTC, without any elaborate schema changes

22. Enhanced Date and Time Types • DateTimeOffset additionally stores time zone offset from UTC.

23. HierachyID • System data type,variable length • CLR UDT • Microsoft.SqlServer.Types • Compact Varbinary encoding • Represent position in hierarchy • Logically encodes information about a single node in a hierarchy tree by encoding the path from the root of the tree to the node • Rich built-in methods for manipulating hierarchies • Simplifies storage and querying of hierarchical data • Support for arbitrary insertions and deletions

24. Table Valued Parameters Existing solutions to an old problem • Pivoting large numbers of stored procedure parameters • Complex application and stored procedure code • Bulk inserting to temporary tables out-of-band • Limited ability to apply business logic • Leveraging parameter arrays in client applications • Non-optimal performance for large data volumes

25. Demo – Table Valued Parameters

26. Merge • UPSERT and then some (also supports DELETE) • Allows you to process multiple changes (e.g. from a staging table) in a single statement MERGE <target> USING <source> ON (<join condition>) WHEN MATCHED [AND…] THEN… WHEN NOT MATCHED THEN… WHEN SOURCE NOT MATCHED THEN…

27. Demo – The Merge Statement

28. Management Challenges • Huge number of features with each new release • Data Centre and Server Consolidation – more apps per server , more mission critical apps per data centre. • Remote Management • You want to “lock down” SQL just like any other environment, according to your company policies.

29. Declarative Management • What is Declarative Management: • A new policy-based management framework for the SQL Server Database Engine • Declarative Management Benefits: • Ensure compliance with policies for system configuration • Prevent/monitor changes to the system by authoring policies for the desired configuration • Scale management across multiple servers • Reduce total cost of ownership by simplifying administration tasks

30. Demo – Declarative Management

31. Reporting Services • New Tool – Report Designer • Improved Data Pane • Enhanced design experience – snap to lines , zoom etc. • Dundas gauges and maps for charting ! • No need for IIS • Performance improvements • Report Builder improved too !

32. Simplified SQL 2005 Reporting Services Architecture Report Server Web Report Mgr Web Report Processor Report Server Service Report Models Another Web Report Proc Scheduling Extensions Report Models Subscrip / Deliv ASP.Net Extensions DB Maint Internet Information Server Service Manager Windows Server OS

33. Simplified SQL 2008Reporting Services Architecture Single Reporting Services Process Boundary Report Web Service Background Processing Report Manager “On Demand” Report Proc Report Mgr UI Scheduling Report Models Subscrip / Deliv Another Web Extensions Extensions DB Maint Memory Management ASP.Net SQL OS – Network, CLR, ASP.Net IIS Service Manager Paging Windows Server OS

34. Demo – Report Designer

35. What’s new in SSIS ? • New Script environment - VS shell • C# as a language !!! • Can reference all .NET assemblies • Enhanced SQL statements eg. Merge • New Data Types eg. Date, Time , DateTime2 • New Wizards are much smarter !

37. What’s new in Analysis Services • New Attribute Relationship Designer • Cube – improved Auto Build algorithm • Simplified and Enhanced Dimension Wizard • Backup and Restore Improvements • Core Engine Performance Improvements • New Server Infrastructure for Resource Monitoring and Stats Collecting • MOLAP writeback • And many more.......!!!!

38. Resources • My blog • http://thavash.spaces.live.com/blog • Microsoft • www.microsoft.com/sql/2008/learning/webcasts.mspx

39. Thank you http://www.microsoft.com/southafrica/ucs/2007