sql server data tools workshop

Everything you need to know to get started

SQL SERVER DATA TOOLS

WORKSHOP

Introduction

2/18/2012

2

Copyright © Gert Drapers

Thanks for hosting

2/18/2012 Copyright © Gert Drapers

3

@DataDude


4

1986-1988 IBM Corporation

1988-1991 Ashton-Tate

1991-present Microsoft Corp.

SDE in the Developer Relations Group (the Netherlands)

Senior Development Consultant in MCS (the Netherlands)

SDE in Storage Engine (DBCC, Bulk Insert and Convert)

Program Manager SQL-DMO and Query Analyzer

Product Unit Manager SQL Server Management Tools

Software Architect DTS 2005 (now SSIS) and SMO

Software Architect MS-DTC, COM+, System.Transactions, WS-AT

Software Architect SQL Server Customer Advisory Team (Europe)

Principal Group Engineering Manager “Visual Studio Team System Database Edition”

Principal Software Architect Azure Active Directory

Principal Software Architect Cloud Programmability (SQL)

Principal Group Program Manager SQL Server Data Platform

Agenda

Declarative Database Development

SQL Server Data Tools

Online Schema Management

Offline Schema Management

Application Life Cycle Integration

Programmatic and Command Line Usage


5

Database Development is Hard!


6

Challenges:

Databases are inherently stateful

Focus is on ALTER instead of CREATE

Dependencies complicate scripts

Errors are often found only when scripts are executed

Synchronizing application and database versions

Targeting different SQL Server and SQL Azure versions

There is a Better Way…


7

Design time separated from runtime

Model-based approach

Declarative representation (CREATE scripts)

Hydrated from database, T-SQL scripts in project, dacpac

Connected and offline development with consistent tools

Validation for specific SQL Server/Azure versions

Diff design model and database model to generate change scripts

Projects allow application & database code to be managed together

Model-based differencing used to synchronize schemas

History of the “DataDude” Project

Project funded April 2005

Project started July 1st 2005

Visual Studio 2005 Team System for Database Professionals


Re-architected to be declarative model based system

Visual Studio 2008 Team System for Database Professionals GDR R2


Transitioned to SQL Server 2009



8

SQL Server Object

Explorer

SQL Static Code Analysis

Database Publishing

SQL Language Services

Buffered Declarative

Editing

Table Designer

Schema Comparison

Isolated Local Database Runtime

SQL/CLR SQL Debugging

Introducing SQL Server Data Tools


9


SQL Server Data Tools (SSDT)


10

Developer-focused toolset for building SQL Server & SQL Azure databases

Experiences Enabled

Connected Development

Project Based Development

Application Lifecycle & Tools

Fundamentals

Declarative, model based database development

Integrated tools with modern language services

Connected and offline with local testing

Target SQL Server and SQL Azure

Detecting and managing database drift

Resources

Online Setup

http://msdn.microsoft.com/data/tools Get It

Team blog

http://blogs.msdn.com/b/ssdt/

MSDN Forum

http://social.msdn.microsoft.com/Forums/en-US/ssdt/threads

Articles

MSDN Magazine Sept 2011 The "Juneau" Database Project


11

http://msdn.microsoft.com/data/tools

http://msdn.microsoft.com/data/tools







http://msdn.microsoft.com/en-us/magazine/hh394146.aspx

http://msdn.microsoft.com/en-us/magazine/hh394146.aspx

The foundation



12



13

Define WHAT you want, not HOW you want to get

there

Declarative vs. Imperative

Imperative


14

Why? Imperative script hard codes knowledge about:

The state of the target system:

Presence of objects, dependencies, unbinding, rebinding

Required ordering of operations

Cumulative changes need to be serialized

v1v2v3v4v5 instead of v1v4v5

Validating the end-result against expected end-state is hard

No easy way to compare expected vs. actual

Batching or separating multiple changes

Declarative


15

Define what you want in the form of a model

Fill the model using a DSL (domain specific language)

T-SQL

Use the model as the starting point “blueprint” for all operations

Deployment/publish, start by comparing the current state of the target with the required state (blueprint)

Use the resulting difference knowledge to programmatically generate an deployment plan

Use plan to create sequence of (SQL) statements required to make the target state become like the blueprint state

Imperative vs. Declarative


16

Manual vs. generated / programmatic

Point in time vs. always current

Sequential vs. differential

Script Fundamentals

Requirement to be able to round trip DSL artifacts

Script Model

SQL script Parse Script fragment

Script fragment Script Gen SQL script

Script fragment is AST (Abstract Syntax Tree)

2/18/2012

17


Script

Fragment

T-SQL Parser

Script

Gen T-SQL

Script

Fragment

A

T S

ScriptDom


18

SQL Server 2012 managed parser

Supports SQL Server 2005+

Class TSqlXXXParser

XXX = [80, 90, 100, 110]

SQLDom.msi (redist x86/x64)

Microsoft.SqlServer.TransactSql.ScriptDom.dll

C:\Program Files (x86)\Microsoft SQL Server\110\SDK \Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll

GAC

Basic ScriptDom loop


19

static void Main(string[] args) { bool initialQuotedIdentifiers = false; TSqlParser parser = new TSql110Parser(initialQuotedIdentifiers); StreamReader sr = new StreamReader(@".\test.sql"); IList<ParseError> errors; TSqlFragment fragment = parser.Parse(sr, out errors); StreamWriter sw = new StreamWriter(Console.OpenStandardOutput()); Sql110ScriptGenerator scriptGen = new Sql110ScriptGenerator(); scriptGen.GenerateScript(fragment, sw); }

Reusable Building Blocks


20

Model Store

Model Builder

Model Serialization

Model Comparison

Model Copy

Model Store

Elements

Relationships

Properties

Identifiers

Annotations

Model stored in ESE

database

*.dbmdl (Database

Model) file extension

used by local model

cache.

2/18/2012

21


Model Builder

The schema model is populated using information from the AST representation of the SQL parser

From a model element you can recreate a script fragment (AST)

2/18/2012

22


Model Serialization

Act of serializing and de-serializing a schema model to and from disk or in-memory persistence model

Model is serialized as XML

See digesting a DACPAC

2/18/2012

23


Reverse Engineer

Reverse Engineering is a special form of model builder, which scripts the content of a source database in the form of SQL scripts (model DSL input format) to construct a model

2/18/2012

24


Model Copy / Compare

Comparing two declarative models to determine the difference

The comparison engine exposes options for excluding object instances, object types, specific options on objects and certain common use cases

2/18/2012

25


Schema Compare & Deployment

Model

Compare

Script

Fragment

Model

Builder

Source

Schema

Model

Reverse

Engineer DB

Schema

Package

Model

Serializer

Script

Fragment

Model

Builder

Reverse

Engineer DB

Schema

Package

Model

Serializer

Target

Schema

Model

Diff List

Plan

Optimizer

Deploy Plan

Script

Generator

Diff

Visualizer


26

Model Based


27

All schema objects are represented inside a model

What is in the model is defined by the provider

To load/hydrate a model instance

Parse SqlCodeDom, based on parsers Abstract Syntax Tree (AST)

Interpretation Schema Model

Symbol list

Object References (hard and soft dependencies)

Validation

.SQL Source

Parse SqlScript

Dom Interpret

Schema Model

Validate



28

Database is a project that builds into DACPAC

Code can be tested locally (F5) Syntax and semantic validation

during compilation

LocalDB

T-SQL DOM

T-SQL Compiler

Connectivity

DAC FX

Visual Studio

SSDT

Digesting a .DACPAC


29

OPC file

Unpack handler associated at file system level

Rename .dacpac to .zip to open

Compressed

Versioned

SQL Server 2012 ships with DACPAC version 3.0.0.0

http://en.wikipedia.org/wiki/Open_Packaging_Conventions



.DACPAC streams


30

Installation



31

Setup Options


32

Install from online Web Platform Installer feed

Install from SQL Server 2012 DVD

Install from Visual Studio 11

Install from offline administrative install

Supported OS Platforms

Windows Vista SP2+

Windows 7 SP1+

Windows Server 2008 SP+

Windows Server 2008 R2 SP1+

Note: this is different then the Visual Studio 2010 platforms


33

Visual Studio Requirements

Visual Studio 2010 Integrated Shell+

SQL-CLR requires Visual Studio 2010 Standard+

Visual Studio 2010 SP1+

Notes:

SQL Server 2012 only installs VS 2010 Integrated Shell SP1

If you have an existing Visual Studio 2010 installation you must install SP1 yourself (?KB VS2010 SP1 checks)

Watch for mixed mode (?KB detecting mixed mode)


34

SSDT Online Acquisition


35

Install online via Web Platform Installer (WebPI)

SSDT download link for latest bits

http://msdn.microsoft.com/en-us/data/hh297027

Requires WebPI v3.0 or better to be installed

User is prompted on first attempt when not installed or updated

Automatically downloads all required new or updated dependencies

Invokes SSDTSetup.exe (chained installer)





http://www.microsoft.com/web/downloads/platform.aspx

http://www.microsoft.com/web/downloads/platform.aspx

Installing from SQL Server 2012 DVD


36

Install SSDT from SQL Server 2012 DVD image

Install VS 2010 SP1 shell if you do not have VS installed

If you do have VS 2010 install, user needs to install SP1!

DVD puts down a “stub” which will acquire latest SSDT bits

online via (WebPI)

See http://sqlproj.com/index.php/2011/11/ssdt-setup-

from-sql-server-2012/ for detailed step through

http://sqlproj.com/index.php/2011/11/ssdt-setup-from-sql-server-2012/












Creating an Admin Install

SSDTSetup.exe /layout C:\SSDTAdminInstall

Pulls down all the latest dependencies from the web

Enables generating a local install point which can be shared via a file share or USD drive

DMZ or offline installation scenario

For detailed steps see:

http://sqlproj.com/index.php/2011/11/creating-an-administrative-install-for-ssdt


37














SSDTSetup


38

SSDTSetup.exe

Setup chainer (486 KB)

SSDT\x86\SSDT.msi

Actual tools (5.75 MB)

SSDT\x86\SSDTBuildUtilities.msi

MSBuild targets and utilities (970 KB)

SSDT\x86\DbSqlPackageProvider.msi

Web Deploy Provider (729 KB)

Setup Dependencies (payload directory)

x86\DACFramework.msi

x86\NDP40-KB2468871-v2-x86.exe

x86\SharedManagementObjects.msi

x86\SQLDom.msi

x86\SqlLocalDB.msi

x86\sqlls.msi

x86\SQLSysClrTypes.msi

x86\SSDTDBSvcExternals.msi

x86\TSqlLanguageService.msi

x86\VS10sp1-KB983509.exe

x86\VSIntShell.exe

dotNetFx40_Full_x86_x64.exe

NDP40-KB2544514-x86-x64.exe

x64\DACFramework.msi

x64\NDP40-KB2468871-v2-x64.exe

x64\SharedManagementObjects.msi

x64\SQLDom.msi

x64\SqlLocalDB.msi

x64\sqlls.msi

x64\SQLSysClrTypes.msi

x64\TSqlLanguageService.msi

2/18/2012

39


Setup Dependencies


40

.NET 4.0 CLR runtime

dotNetFx40_Full_x86_x64.exe

.NET 4.0 CLR general update

http://support.microsoft.com/kb/2468871

NDP40-KB2544514-x86-x64.exe

Update 4.0.2 for Microsoft .NET Framework 4 – Runtime Update


NDP40-KB2544514-x86-x64.exe

LocalDB support

Visual Studio 2010 integrated shell

VSIntShell.exe

Visual Studio 2010 SP installer

VS10sp1-KB983509.exe







Setup Dependencies…


41

SQL Server 2012 Data-Tier Application Framework v3.0

DACFramework.msi

SQL Server 2012 Express LocalDB

SqlLocalDB.msi

SQL Server 2012 Script DOM (parser)

SQLDom.msi

SQL Server 2012 Language Services (compiler and binder)

sqlls.msi

SQL Server 2012 CLR types (geo and hierarchy)

SQLSysClrTypes.msi

SQL Server 2012 Management Objects (SMO)

SharedManagementObjects.msi

SQL Server 2012 Language Services (IntelliSense parser)

TSqlLanguageService.msi

DAC

2/18/2012

42


DAC Framework v3 (DACFX)


44

DAC Framework

DACFX is the core SQL redist providing modeling, reverse engineering and deployment pipeline capabilities

v3.0 supports the full domain of SQL Server 2005, 2008/R2, 2012, and SQL Azure

Managed Public API

Exposes verbs for DACPAC and BACPAC operations

Command-line tool (SqlPackage.exe)

Exposes DACPAC verbs, project publish

DACUnpack.exe

Windows file handler for unpacking DACPAC to disk

DACFX Clients

SSMS, SSDT, SAMP, I&E, VS Web and DB Publishing

DACFX Packages


45

.dacpac

Packaged Schema representing the declarative model of the database

Not SQL, but a declarative Schema Model representation of the versioned database

Built from source code or Extracted from existing databases

Deploy as new database or Upgrade an existing database

.bacpac

Packaged Schema and Data representing the state of the database at the time of Export

Composed of Schema Model with state-ful properties and Data streams

Exported from existing databases

Imported as a new database or populates an empty database

DAC Evolution


46

SQL Azure Management Portal (SAMP)


47

Subscriber and Operator oriented toolset for managing SQL Azure databases through DAC Verbs

Experiences Enabled


Portal Based Tools

Application Lifecycle support

DACPAC verbs supported

Deploy Data Tier Application (create new database from dacpac)

Upgrade Data Tier Application (upgrade database to match dacpac)

Extract Data Tier Application (create dacpac from existing database)

BACPAC Verbs supports

Export Data Tier Application (export database as bacpac to Windows Azure Blob Storage)

Import Data Tier Application (import bacpac as new database from Windows Azure Blob Storage)

SQL Server Data Tools (SSDT)


48

Developer-focused toolset for authoring, building and publishing DACPACs

Experiences Enabled


Project Based Development


DACPAC verbs

Author DACPAC declaratively and build from source

Compare DACPAC to project, database, and other DACPACs

Import DACPAC into project

Publish DACPAC/Project to database

SQL Server Management Studio (SSMS)


49

DBA-focused tools for deploying and extracting DACPACs; importing and exporting BACPACs

Experiences Enabled

Administration and Monitoring


Configuration and Task Wizards

DACPAC Verbs Supported

Deploy Data Tier Application

Register/Unregister Data Tier Application

Upgrade Data Tier Application

BACPAC Verbs Supported

Import Data Tier Application

Export Data Tier Application

Migrate Database to SQL Azure

Imperative & Declarative Schema Authoring and

Deployment

Online Schema Management

2/18/2012

50




51

SQL Server Object Explorer (SSOX)

Modeled after SSMS object explorer

Modern T-SQL Coding Experience

Buffered Declarative Object Editing

Model-based with Error Detection

Imperative Script Execution

IntelliSense

Code-backed Table Designer

View/Edit/Script Data (incl. copy/paste)

Execute/Debug entry points


SQL Server Object Explorer (SSOX)

Developer-oriented “SSMS style” interaction with live instances

Platform support

Supports connecting to SQL2005, SQL2008, SQL2012, and Azure

Supports connecting directly to a contained database

Specific developer-oriented features slanted towards the TSQL developer

Cascading drops and renames

Auto-update of tree / source as changes occur on the server



“Power Buffer” – Declarative deferred online schema management

Brings “project-oriented” features to online / connected development

Table designer

Strives to be the same experience online as experienced in the project system

Live errors and warnings as changes are made against code

“Project-oriented” validation during editing

Debugging


Schema Compare


54

Next and Previous move between top-level

objects, no longer expand the tree.

Simpler set of columns are aligned around the central action

column echoes alignment of schemas above

Group-by Action is the

default

Same column

order is used

for all

groupings;

cleaner and

easier to parse

regardless of

grouping

Schema no longer used as

a parent for top-level

objects; only shown when

changed

Improved script diff’

algorithm for tables

emphasizes columns as

unit of change. Gives

much improved

alignment. Also improved

look

Controlling Refresh Rate on SSOX

HKEY_CURRENT_USER\Software\Microsoft\VisualS

tudio\10.0EXP\SQLDB\DialogPage\Microsoft.Visua

lStudio.Data.Tools.Package.ToolsOptions.OnlineEditi

ng.OnlineEditingOptionsSettings]

"PollingDriftCheckSeconds"="30"

Defaults to 30. If set to <= 0 will be infinite (3

days). Cannot be less than 30.


Schema as source code and projects

Offline Schema Management


56

Project-based Development


57

Database definition managed in a Visual Studio project

Multi-target SQL Server {2005, 2008, 2008R2, 2012, Azure}

Advanced Language Services for T-SQL

Go To Definition/Find All References/Refactoring

F5 debugging with LocalDB

Visualize schema differences and migrate changes

Publish direct, via SQL script or DACPAC

Integrated database drift detection

Point-in-time Snapshot

Drag & Drop import from SQL Server Object Explorer

Design-time - Database Specific Features

SSDT adds database specific design time features to the VS environment

Project properties pages Specific target database platform

Modeling of database properties

Configure Debug environment

Innovative SqlCLR support

Import schema functionality

Split-Pane Table Designer

Design-time - Database Specific Features

Build-time validation

Platform specific validation

Example – Enforces clustered index requirement on tables in Azure

Integration with Compiler Services

Engine-level build-time validation without schema deployment

Leverages CDB and SQL2012 engine capabilities

Debug database

Integration with LocalDB provides default database to host schema for execution during F5 debugging

Design-time - Language Parity Features

SSDT provides tools that elevate database development to the level of other managed languages like VB.NET and C#

Intellisense

Snippets

Debugger integration

Find all references / Go to definition / Refactoring Refactoring changes carried through incremental deployment

Static code analysis (FXCop)

Design-time - Project Productivity

Selective import using Schema Compare

Provides object-level granularity and control over the import process

Ideal for “in-cycle” project updates and synchronization

Composite Projects

Schema distributed across multiple SSDT database projects

Enable division of schema along security or organizational boundaries

Published as one logical database!

Referenced objects brought into parent model on build

Controlled by “Include Composite Objects” advanced publish option

Deploying Changes

SSDT provides a core update engine that generates TSQL to incrementally update a target

A target is usually a database but can also be a project or .dacpac

Over top of the core engine SSDT provides a number of different “heads” Each deployment head enables different scenarios to

integrate into different customer update processed

Deploying changes - UI Deployment

SSDT provides a number of different ways to deploy changes inside the IDE

F5

Integration with Visual Studio’s debug / run features

Publish

Meant for formal updates of a target database from a project

Can utilize publish profiles in team development environment

Schema Compare

Can be thought of a project’s Visual Deployment

Supports various comparisons between other sources / targets - .dacpac, database, project

Deploying changes - Other Heads

Outside of Visual Studio there are a number of other options

MSBuild – deploy (F5), publish tasks

Sqlpackage.exe – command line tool (similar to vsdbcmd.exe in VSDB)

MSDeploy provider

Public deployment API – support for creation and consumption of .dacpac / .bacpac

Publishing your Database Changes


65

Target version aware:

SQL Server 2005

SQL Server 2008 & SQL Server 2008 R2

SQL Server 2012

SQL Azure

Publish

Direct, via SQL script, or DACPAC snapshots

Team development and central build services

Application Life Cycle Integration


66



67

MSBuild tasks for:

Build

Publish

T-SQL Static Code Analysis

MSBuild in a redist

Database projects in build server environment (like Team Build) without installing full VS on build server

Integrates with all standard VS SCCS providers

DACFX v3

Schema Compare

Publish

Programmatic and Command Line


68

SqlPackage.exe


69

Part of DACFramework.msi (redist)

Location:

%ProgramFiles(x86)%\Microsoft SQL Server\110\DAC\bin

/Action:

{Extract|DeployReport|DriftReport|Publish|Script}

Supports response file @file

Command line override settings in response file

DacUnpack.exe


70

C:\Program Files (x86)\Microsoft SQL

Server\110\DAC\bin

Explorer file handler for unpacking .DACPAC

Note: not a command line tools

MSBuild Tasks


71

"C:\Program Files

(x86)\MSBuild\Microsoft\VisualStudio\v10.0\SSDT

\Microsoft.Data.Tools.Schema.SqlTasks.targets“

Microsoft.SqlServer.Dac.dll


72

C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin

Public API

Implements the common DAC verbs

Deploy, ExportBacpac, Extract, GenerateCreateScript, GenerateDeployReport, GenerateDeployScript, GeneratedriftReport, ImportBacpac, Register, Unregister, Unpack

Microsoft.SqlServer.Dac.Extensions.dll


73

Provides read-only schema model access

NOTE: Not part of the main product, ships as external

assembly/library

Summary


74

SQL Server Data Tools – Summary


75

Developer-focused toolset to author, debug and publish SQL Server & SQL Azure databases

Supports SQL Platform

Free, web updates for SQL Server and SQL Azure releases

Works in concert with other SQL Server tooling (SAMP, SSMS)

Compatible with your development environment

Supports Visual Studio 2010 & Visual Studio 11

Migrates VS2010 database projects

sql server data tools workshop

Documents