Transcript SESSION CODE: # DAT302 Lynn Langit Sr. Developer Evangelist Microsoft SQL SERVER 2008 R2 FOR DEVELOPERS (c) 2011 Microsoft.

SESSION CODE: # DAT302
Lynn Langit
Sr. Developer Evangelist
Microsoft
SQL SERVER 2008 R2 FOR DEVELOPERS
(c) 2011 Microsoft. All rights reserved.
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
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
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Develop
Definition
Runtime
Source code
Deployment
artifact
Container
CREATE TABLE
Orders
( CREATE TABLE
id
Orders
INT,
(
ordTime
DATETIME,
id
.
INT,
.
.
) -- ONordTime
DATETIME,
OrdPS(ordTime)
.
.
.
CREATE
) --TABLE
ON
OrderEntr
OrdPS(ordTime)
(
Deploy
Build
Upgrade
Reverse
Engineer
Extract
CREATE TABLE
OrderEntr
DACFx
Services
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 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
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 DbContext 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
• 3rd party tools
• Article on Tracing here
You can use raw T-SQL
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.
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
Next Steps
LEARN MORE ABOUT SQL SERVER 2008 R2
DOWNLOAD SQL SERVER 2008 R2
UPGRADE TO SQL SERVER 2008 R2
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/SoCalDevGal
– Twitter - @llangit
– http://www.slideshare.net/lynnlangit
www.TeachingKidsProgramming.org
• Do a Recipe  Teach a Kid (Ages 10 ++)
• Microsoft SmallBasic  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. [email protected]
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, 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.