1 / 48

SQL Server 2008 R2 for Developers

SESSION CODE: # DAT302. Lynn Langit Sr. Developer Evangelist Microsoft. SQL Server 2008 R2 for Developers. SQL Server 2008 R2 – So Many New Features…. For Developers. DACPACs (Data Tier Applications) T-SQL enhancements Geospatial data types XML data enhancements

haroun
Télécharger la présentation

SQL Server 2008 R2 for Developers

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. SESSION CODE: # DAT302 Lynn Langit Sr. Developer Evangelist Microsoft SQL Server 2008 R2 for Developers (c) 2011 Microsoft. All rights reserved.

  2. SQL Server 2008 R2 – So Many New Features… For Developers • DACPACs (Data Tier Applications) • T-SQL enhancements • Geospatial data types • XML data enhancements • Filestreams (BLOBS) • Sparse Columns • Filtered indices and full-text search • Entity Framework

  3. Database Development Pain Points • Developers • SQL expertise • Maintain script libraries • Versioned deployments • Keeping development/test/production synchronized • Database administrators (DBAs) • Hand reconcile upgrade scripts • Schema/data portability • Security/containment • Management at scale

  4. Defines all of the Database Engine schema and instance objects A single unit of management Simplifies development, deployment, and management lifecycle Contains policies that define the deployment prerequisite Data Tier Applications (DAC)

  5. The Database Model • The logical database model is the centerpiece • Services are provided on around the model DAC database model Build Deploy Upgrade Extract Import Export DACPAC

  6. Data-tier Application Lifecycle Runtime Container Definition Deployment artifact Develop Source code Contained database • CREATETABLE Orders • ( • id INT, • ordTime DATETIME, • . • . • . • ) -- ON OrdPS(ordTime) • CREATETABLE OrderEntr • ( Deploy Upgrade Build DACPAC • CREATETABLEOrders • ( • id INT, • ordTimeDATETIME, • . • . • . • ) -- ON OrdPS(ordTime) • CREATETABLEOrderEntr Reverse Engineer Extract DACFx Services

  7. Working with DACPACs • For developers in Visual Studio 2010 • For DBAs in SQL Server Management Studio • Supports SQL Server • Supports SQL Azure (c) 2011 Microsoft. All rights reserved.

  8. DACPAC project in Visual Studio 2010

  9. Creating DACPAC Import schema from – script, database or DACPAC (c) 2011 Microsoft. All rights reserved.

  10. DACpac Project Template • Schema objects • Ordered by Schema • Also Database Level Objects • Scripts • Post-Deployment • Pre-Deployment • Data Generation Plans • Schema Comparisons

  11. Schema Comparison (c) 2011 Microsoft. All rights reserved.

  12. Data Generation in DACPACs (c) 2011 Microsoft. All rights reserved.

  13. Build…Deploy (c) 2011 Microsoft. All rights reserved.

  14. DACPAC

  15. Monitoring DACPACs via UCPs

  16. DACPAC  SQL Server Database Project (c) 2011 Microsoft. All rights reserved.

  17. “Smart T-SQL” Utilizing SQL Server “data types” • Geospatial types • XML or Filestream • Full-text • Sparse Columns

  18. Key T-SQL Enhancements • Table-Valued Parameters • T-SQL Assign and Increment Operators • Row Constructors • Grouping Sets • MERGE statement • Dependency Views • Performance Enhancements

  19. T-SQL Performance Enhancements • MERGE and GROUPING SETS improvements • Less scans through table • Table-valued parameters improvements • Less round trips to database • Improvements for data warehouse queries • Earlier predicate filtering • Multiple bitmap iterators per query • Plan Guide Improvements • Easier to create plan guides • Plan guides on DML statements Also: Object reference tracking makes schema and procedural code versioning less error-prone

  20. Better T-SQL

  21. Sparse Column enhancements? XML enhancements? Geospatial enhancements? Filestream enhancements? Full-text enhancements? Other Data type enhancements

  22. Better Data types

  23. T-SQL Improvements Recap • Strongly typed table-valued parameters -- helps the database round trip problem • Grouping Sets -- allow arbitrary group by clauses for subtotals and totals • MERGE statement -- allows set-to-set comparison and multiple deterministic operations (ANSI standard compliance with extensions) • Object reference tracking -- makes schema and procedural code versioning less error-prone

  24. What is EF? Object/Relational Mapping (ORM) framework How to use EF issue queries using LINQ then retrieve and manipulate data as strongly typed objects LINQ to Entities provides IntelliSense and compile-time syntax validation for writing queries against a conceptual model Why use EF? enables you to work with relational data as domain-specific objects eliminates the need for most of the data access plumbing code that you previously wrote Entity Framework

  25. EF Stack & Development Process Types • Database first • Model first • Code first (new in 4.1) (c) 2011 Microsoft. All rights reserved.

  26. EF Templates

  27. POCO Classes - I (c) 2011 Microsoft. All rights reserved.

  28. POCO Classes - II (c) 2011 Microsoft. All rights reserved.

  29. Overriding Default Configuration (c) 2011 Microsoft. All rights reserved.

  30. EF Power Tools (c) 2011 Microsoft. All rights reserved.

  31. Using EF with MVC (c) 2011 Microsoft. All rights reserved.

  32. EF 4.2 templates for using DbContext with Database First or Model First are now available on Visual Studio Gallery (c) 2011 Microsoft. All rights reserved.

  33. Entity Framework

  34. Code to expose WCF service from EF

  35. Sample code to access EF data

  36. Entity Framework Profiling • Visual Studio Ultimate • Intellitrace • VS Profiler - article here • SQL Server Profiler • 3rd party tools • Article on Tracing - here

  37. You can use raw T-SQL

  38. New in SP1 • •New or Improved Dynamic Management Views • --sys.dm_exec_query_stats --additional columns (long-running queries) • --new DMVs and XEvents on select performance counters are introduced to monitor OS configurations/resource conditions related to the SQL Server instance • •Improved FORCESEEK index hint & New FORCESCAN query hint • --syntax modified w/optional parameters allowing it to control the access method on the index even further • --FORCESCAN complements the FORCESEEK hint allowing specifying ‘scan’ as the access method to the index • •Improved DACPAC - (DAC Fx) improved database upgrades: • --(DAC) Framework v1.1 and DAC upgrade wizard enable the new in-place upgrade service --New in-place upgrade service will upgrade the schema for an existing database in SQL Azure and the versions of SQL Server supported by DAC • •New Disk space control for PowerPivot: • --Introduces two new configuration settings that let you determine how long cached data stays in the system (c) 2011 Microsoft. All rights reserved.

  39. SQL Server 2008 R2 – So Many New Features… For Developers DACPACs (Data Tier Applications) T-SQL enhancements Geospatial data types XML data enhancements Filestreams (BLOBS) Sparse Columns Filtered indices and full-text search Entity Framework 4.1

  40. Next Steps LEARN MORE ABOUT SQL SERVER 2008 R2 • http://www.microsoft.com/sqlserver/2008/en/us/whats-new.aspx DOWNLOAD SQL SERVER 2008 R2 • http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx UPGRADE TO SQL SERVER 2008 R2 • http://www.microsoft.com/sqlserver/2008/en/us/how-to-buy.aspx

  41. Related Content • MSDN Webcast: New T-SQL Programmability Features in SQL Server 2008 (Event ID: 1032357753)

  42. SQL CAT (Dev) blog - here SQL Programmability blog - here SQL Query Processing blog - here Team Content – SQL Server

  43. Lynn’s Resources http://blogs.msdn.com/SoCalDevGal Twitter - @llangit http://www.slideshare.net/lynnlangit Other Related Content

  44. www.TeachingKidsProgramming.org • Do a Recipe  Teach a Kid (Ages 10 ++) • Microsoft SmallBasic  Free Courseware (recipes)

  45. Enrol in Microsoft Virtual Academy Today Why Enroll, other than it being free? The MVA helps improve your IT skill set and advance your career with a free, easy to access training portal that allows you to learn at your own pace, focusing on Microsoft technologies. • What Do I get for enrolment? • Free training to make you become the Cloud-Hero in my Organization • Help mastering your Training Path and get the recognition • Connect with other IT Pros and discuss The Cloud Where do I Enrol? www.microsoftvirtualacademy.com Then tell us what you think. TellTheDean@microsoft.com

  46. Resources • www.msteched.com/Australia • Sessions On-Demand & Community • www.microsoft.com/australia/learning • Microsoft Certification & Training Resources • http:// technet.microsoft.com/en-au • Resources for IT Professionals • http://msdn.microsoft.com/en-au • Resources for Developers (c) 2011 Microsoft. All rights reserved.

  47. © 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION. (c) 2011 Microsoft. All rights reserved.

More Related