SESSION CODE DAT 302 Lynn Langit Sr Developer

SESSION CODE: # DAT 302 Lynn Langit Sr. Developer Evangelist Microsoft SQL SERVER 2008 R 2 FOR DEVELOPERS (c) 2011 Microsoft. All rights reserved.

SQL Server 2008 R 2 – 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

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

Data Tier Applications (DAC) • 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

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

Data-tier Application Lifecycle • • • • . . . CREATE ) --TABLE ON Order. Entr Ord. PS(ord. Time) ( Deploy Build Reverse Engineer DACPAC • • CREATE TABLE Orders ( CREATE TABLE id Orders INT, ( ord. Time DATETIME, id. INT, . . ) -- ONord. Time DATETIME, Ord. PS(ord. Time) Container Deployment artifact Source code • Runtime Definition Develop Upgrade Extract CREATE TABLE Order. Entr DACFx Services Contained database

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.

DACPAC project in Visual Studio 2010

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

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

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

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

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

DACPAC

Monitoring DACPACs via UCPs

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

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

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

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 errorprone

Better T-SQL

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

Better Data types

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

Entity Framework ► 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 Intelli. Sense 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

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

EF Templates

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

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

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

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

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

EF 4. 2 templates for using Db. Context with Database First or Model First are now available on Visual Studio Gallery (c) 2011 Microsoft. All rights reserved.

Entity Framework

Code to expose WCF service from EF

Sample code to access EF data

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

You can use raw T-SQL

New in SP 1 • 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 v 1. 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 Power. Pivot: --Introduces two new configuration settings that let you determine how long cached data stays in the system (c) 2011 Microsoft. All rights reserved.

SQL Server 2008 R 2 – 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

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

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

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

Other Related Content ►Lynn’s Resources – http: //blogs. msdn. com/So. Cal. Dev. Gal – Twitter - @llangit – http: //www. slideshare. net/lynnlangit

www. Teaching. Kids. Programming. org • Do a Recipe Teach a Kid (Ages 10 ++) • Microsoft Small. Basic Free Courseware (recipes)

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. Tell. The. Dean@microsoft. com

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

© 2010 Microsoft Corporation. All rights reserved. Microsoft, 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.