SQL SERVER DATA TOOLS
WORKSHOP
Introduction
Thanks for hosting
@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
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
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 Visual Studio 2008 Team System for Database Professionals Re-architected to be declarative model based system
Visual Studio 2008 Team System for Database Professionals GDR R2 Visual Studio 2010 Team System for Database Professionals
Transitioned to SQL Server 2009 SQL Server Data Tools
2/18/2012 Copyright © Gert Drapers
SQL Server Object Explorer
SQL Static
Code Analysis Database Publishing Language SQL Services
Buffered Declarative
Editing Table
Designer Comparison Schema 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
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
The foundation
Declarative Database Development
Declarative Database Development
13
Define WHAT you want, not HOW you want to get
there
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
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
Imperative vs. Declarative
16
Manual vs. generated / programmatic
Point in time vs. always current
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)
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.
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)
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
23Reverse 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
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
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 Diff Visualizer 2/18/2012 Copyright © Gert DrapersModel 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
SQL Server Data Tools
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
Installation
SQL Server Data Tools
Setup Options
32
Install from online Web Platform Installer feed
Install from SQL Server 2012 DVD
Install from Visual Studio 11
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
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)
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
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
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
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
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 39Setup 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 http://support.microsoft.com/kb/2544514
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
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
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
DAC Evolution
SQL Azure Management Portal (SAMP)
47
Subscriber and Operator oriented toolset for managing SQL Azure databases through DAC Verbs
Experiences Enabled Connected Development 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
Connected Development Project Based Development Application Lifecycle & Tools
DACPAC verbs
Author DACPAC declaratively and build from source
Compare DACPAC to project, database, and other DACPACs Import DACPAC into project
SQL Server Management Studio (SSMS)
49
DBA-focused tools for deploying and extracting DACPACs; importing and exporting BACPACs
Experiences Enabled
Administration and Monitoring Connected Development
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
Connected Development
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
Connected Development
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
Connected Development
“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
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
Schema as source code and projects
Offline Schema Management
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
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
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
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
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,
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
Publishing your Database Changes
65
Target version aware:
SQL Server 2005
SQL Server 2008 & SQL Server 2008 R2
SQL Server 2012
SQL Azure
Publish
Team development and central build services
Application Life Cycle Integration
Application Lifecycle & Tools
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
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
DacUnpack.exe
70
C:\Program Files (x86)\Microsoft SQL
Server\110\DAC\bin
Explorer file handler for unpacking .DACPAC
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
Summary
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
