sql server data tools

SQL Saturday #176 – Pordenone, Italy17/11/2012 | 1 |

SQL Saturday #176 – Pordenone, Italy

SQL Saturday #176

Pordenone, Italy

SQL Server Data Tools

17/11/2012 |


#sqlsat176

17/11/2012 | 3 |


Marco Parenzan

.NET Developer, SQL User 1nn0va Speaker (www.innovazionefvg.net) Trainer and consultant on application

development in companies and in University (of Trieste)

Head of Development in CGN S.P.A. (www.cgn.it)

17/11/2012 | 4 |

http://www.innovazionefvg.net/

http://www.cgn.it/


DATABASE DEVELOPMENTThe Developer’s experience

17/11/2012 | 5 |


Database Development

Often not integrated into application life cycle management and processes

What is a database development experience?

Install SSMS into developer machine…. It’s an «admin» tool Outside Visual Studio (it’s Visual Studio yes)

Third party tools… What does »deployment» means?

17/11/2012 | 6 |

IMPERATIVE DATABASE DEVELOPMENT

The premise

17/11/2012 | SQL Saturday #176 – Pordenone, Italy7 |

Database Development 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


Simple Schema Versioning Example

-- version 1CREATE TABLE dbo.Speakers( SpeakerId INT NOT NULL, FirstName NVARCHAR(64) NOT NULL, LastName NVARCHAR(64) NOT NULL,

)

-- version 2ALTER TABLE dbo.SpeakersWITH CHECK ADD CONSTRAINT Speaker_PK

-- version 3ALTER TABLE dbo.SpeakersWITH CHECK ADD CONSTRAINT Speaker_PK UNIQUE (LastName, FirstName)

Revision HistoryV 1 V 2 V 3


Imperative

Imperative scripts 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

There are third party tools


Imperative Versioning

-- version 1 Add table dbo.Auction IF OBJECT_ID (N'dbo.Auction', N'U') IS NULL BEGIN

CREATE TABLE dbo.Auction (` id INT NOT NULL, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL )

END -- version 2 Add PK Au_PK IF NOT EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'Au_PK' AND type = 'PK') BEGIN

ALTER TABLE Auction WITH CHECK ADD CONSTRAINT Au_PK PRIMARY KEY (id)

END -- version 3 Add UC Au_SK IF NOT EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'Au_SK' AND type = ‘UQ') BEGIN

ALTER TABLE Auction WITH CHECK ADD CONSTRAINT Au_SK UNIQUE (name) END


Missing/Manual

Object Dependencies Intercept errors before deployment Don’t loose data Sync DB Managing different versions

DEMO

DECLARATIVE DATABASE DEVELOPMENT

The foundation


Declarative

Define what you want in the form of a model Think about a table without thinking about the

specific SQL

Fill the model using a DSL (domain specific language) T-SQL

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


Declarative Versioning

-- version 1CREATE TABLE dbo.Auction( id INT NOT NULL, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)

-- version 2CREATE TABLE dbo.Auction( id INT NOT NULL PRIMARY KEY, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)

-- version 3CREATE TABLE dbo.Auction( id INT NOT NULL PRIMARY KEY, name VARCHAR(25) NOT NULL UNIQUE, start DATETIME NULL, len INT NULL)

Revision HistoryV 1 V 2 V 3



Model as the starting point “blueprint”

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

17/11/2012 | 16 |

Declarative Schema Deployment

17

Model Compare

SourceSchemaModel

Reverse Engineer

TargetDB

TargetSchemaModel

Diff List

PlanOptimizer

Deploy Plan

Script Generator

ScriptExecutor

Additional schema artifacts

Incremental target update


Imperative vs. Declarative

Manual vs. generated / programmatic Point in time vs. always current Sequential vs. differential


Model Based

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

SqlScriptDom Interpret Schema

Model Validate


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)

Script

FragmentT-SQL Parser

Script Gen T-SQL

Script

Fragment

A

TS


ScriptDom

SQL Server 2012 managed parser Supports SQL Server 2005+

Class TSqlXXXParser XXX = [80, 90, 100, 110]

SQLDom.msi (redist x86/x64) Microsoft.SqlServer.TransactSql.Scri

ptDom.dll C:\Program Files (x86)\Microsoft SQL

Server\110\SDK \Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll

GAC


Basic ScriptDom loop

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);

}


DEMO

SQL SERVER DATA TOOLS OVERVIEW

23


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


Database Project

Evolution of VS database project We have more things

We don’t have Data generation Unit testing Data compare Use a DB Project for this

http://social.msdn.microsoft.com/Forums/en-US/ssdt/thread/33664902-a4e2-415a-ad4e-a480fbc56158/

Not available in Professional or Express Edition

Migration http://geekswithblogs.net/80n/archive/2012/09/11/vsdb-to-ssd

t-series--introduction.aspx

Comparison



http://geekswithblogs.net/80n/archive/2012/09/11/vsdb-to-ssdt-series--introduction.aspx

http://geekswithblogs.net/80n/archive/2012/09/11/vsdb-to-ssdt-series--introduction.aspx


DataDude Vs SSDT Comparison (1)

17/11/2012 | 26 |

http://blogs.msdn.com/b/ssdt/archive/2011/11/21/sql-server-data-tools-ctp4-vs-vs2010-database-projects.aspx




DataDude Vs SSDT Comparison (2)

17/11/2012 | 27 |




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


Availability

Visual Studio 2010 Integrated Shell SQL-CLR requires Visual Studio 2010 Standard

Visual Studio 2010 SP1 Visual Studio 2012 SQL Server 2012



Features of SQL Server Data Tools

17/11/2012 | 30 |

03/29/2012

CONNECTED DEVELOPMENT

PROJECT BASED DEVELOPMENT

SCHEMA DEPLOYMENT


CONNECTED DEVELOPMENT

17/11/2012 | 31 |

31

Connected Development

SQL Server Object Explorer (SSOX) T-SQL Editor/Debugger/IntelliSense (New Query) Power-Buffer (New Object/View Code) Table Designer (View Designer) Data Editor (View Data) Execute Procedure/Function

Schema Compare

03/29/2012 SQL Saturday #176 – Pordenone, Italy32 | 17/11/2012 |

SQL Server Object Explorer (SSOX)

The SQL Server navigation tool window Modeled after the SSMS Object Explorer Server scoped vs. Server Explorer which is database scoped Launch point for T-SQL Editor, Debugger, Object Execution,

Designers, …

Supports connecting directly to SQL Server SQL Server {2005, 2008, 2008R2, 2012} and SQL Azure

Supports connecting directly to a (contained) database

Constructs a partial model in the background and under the covers

Supports: Cascading Drops and Renames Drift detection

Automatically updates the tree/source as changes occur on the server


T-SQL Editor/Debugger/IntelliSense

T-SQL IntelliSense SQL Server 2005+ incl. SQL Azure

Result to Grid | Text | File Execution modes

Execute Execute with debugger

Reusable debug script generation


Power-Buffer/Table Designer

Declarative deferred online schema management Brings “project-oriented” features to online / connected development Live errors and warnings as changes are made against code “Project-oriented” validation during editing

Table designer Code-behind designer Works on top of Power-Buffer (partial model) or project model Strives to be the same experience online as experienced in the

project system Coordinated execution of buffers against the same target

Diff scripts by default are transactional (Tools\Options\SQL Server Tools\Online Editing\Include transactional scripts)


Data Editor

View and Edit Data via grid editor Copy-Paste support Generate as INSERT to file or query window


Execute Procedure/Function

Dialog based input for parameter collection Generates SQL script for execution/debugging


DEMO

PROJECT BASED DEVELOPMENT

38


Creating and Importing

Single project template VSDB note:

No distinction between server or user projects Server projects are projects with target database set to master

Import from: Database DACPAC SQL Script (multi-file import enabled) Selective import by using Schema Compare

“Create New Project” from SSOX Inherits version from source database


Folder Structure

Options on import: None Schema Object type Schema\Object type


Project Directory Guidelines…

Do not host more then one table inside script file Table designer will not see additional tables

inside script


Additional Language Services

Projects enable additional language services Go To Definition Find All References Refactoring

Rename (sp_rename) Move to Schema (ALTER SCHEMA schema_name

TRANSFER) Wildcard Expansion Fully Quality Name

Invoked from Editor, Table Designer and SQL Server Object Explorer for project based schema objects


T-SQL Static Code Analysis

Rule set Suppression

File level Project level

MSBuild support


Multi-Targeting

Target version aware: SQL Server 2005 SQL Server 2008 & SQL Server 2008 R2 SQL Server 2012 SQL Azure


Isolated Sandbox

F5 – Run by default configure to use the LocalDB instance associated with the project Data Source=(localdb)\<project name>;Initial

Catalog=<project name>;Integrated Security=True;Pooling=False;Connect Timeout=30

To retarget to a different SQL Server version and/or shared SQL Server change: Project properties\Debug\Target Connection

String

SQL Server 2012 Express LocalDB

Simplify embedded usage of SQL Server Simple installation Zero-admin database

Same programming surface as User Instances of SQL Express The same sqlservr.exe as in service-based SQL Server with the same language and

features Connection through the same client-side APIs, using a new connection string option

SQL Server programming symmetry Installation similar to .NET Framework model

One set of installed binaries (per major version), no global configuration, no constantly-running service

Activation model similar to RANU (Access, SQL Compact, …) The application connects to a database file, SQL activates automatically No service: SQL process starts in the context of current user

Default connection string (localdb)\v11.0

http://msdn.microsoft.com/en-us/library/hh510202(v=SQL.110).aspx




Build Time Validation

Build validates the complete model Platform specific validation

Example – Enforces clustered index requirement on tables in SQL Azure

Integration with Compiler Services Engine-level build-time validation without schema

deployment

Pre & Post Deployment scripts Build validates syntactical correctness of pre- and

post- deployment scripts using the SQLCMD variables


Build Actions

Build - This script will be interpreted as T-SQL (or an XML Schema in the case of XML Schema Collections) and reflected in the T-SQL Model

Compile - C# or VB (depending on your project properties) to be deployed as a SQLCLR assembly

None - Use this for any loose artifacts (like text files) PreDeploy - The pre deployment master script (there can only be one) PostDeploy - The post deployment master script (there can only be

one) RefactorLog - This is a refactor log which notifies the DaxFx

deployment engine that particular changes should be interpreted as refactoring operations and not drop/adds

Build Extension Configuration - Reserved for future use, for this is interpreted as None



Pre & Post Deployment Scripts

Can be used for any “arbitrary” SQL actions that you need to perform “before” and “after” deployment of the schema objects

The pre deployment block comes after the generic database context validation block

The post deployment block comes after the schema objects block

Pre and post deployment scripts must be idempotent They are “executed” each deployment! http://blogs.msdn.com/gertd/archive/2007/01/25/idempote

nt-scripts-required.aspx

17/11/2012 | 49 |

http://blogs.msdn.com/gertd/archive/2007/01/25/idempotent-scripts-required.aspx

http://blogs.msdn.com/gertd/archive/2007/01/25/idempotent-scripts-required.aspx

Database References

3-part name usage: SELECT * FROM

[LiteralDBRef].[Schema1].[Table1] SELECT *

FROM [$(DBRef)].[Schema1].[Table1]

4-part name usage: SELECT *

FROM [$(ServerRef)].[$(DBRef)].[Schema1].[Table1] SELECT *

FROM [$(ServerRef)].[LiteralDBRef].[Schema1].[Table1]


DEMO


Project Deployment

Build serializes the model in to model.xml stream inside .dacpac file

17/11/2012 | 51 |

What is a Data-Tier Application (dacpac)?

Data-Tier Application Component (DAC) SQL Server database registered as Data-Tier Application

Component

DAC is a self contained entity of a database used by an application Unit of deployment and management

Authored along side your application code Compiled into a DAC package (DACPAC)

Extracted for existing databases Database extracted (DACPAC) or exported (BACPAC) into

packages

Package is deployable to SQL Server and SQL Azure

03/29/2012Copyright © 2012 Gert Drapers - All Rights Reserved.

53

DACFX Packages

.dacpac - Packaged Schema representing the declarative model of the database Not SQL, but a declarative Schema Model representation of the database Compiled from source code or Extracted from existing databases Deploy as new database or Incremental Upgrade an existing database Supports all SQL Server Objects and Types

.bacpac - Packaged Schema and Data representing the state of the database Composed of Schema Model with stateful properties and Data Streams Exported from existing databases Imported as a new database or populates an empty database Supports most SQL Server Objects and Types (SQL Azure parity)

03/29/2012Copyright © 2012 Gert Drapers - All Rights Reserved.

54


55

DAC Framework v3.0 (DACFX)

Core SQL Server redist component providing modeling, reverse engineering and deployment capabilities

Managed Public API Exposes verbs for DACPAC and BACPAC

package operations

Command-line tool (SqlPackage.exe) Executable over the managed public API

03/29/2012

Database Schema Snapshots

Point-in-time representation of schema model Model must be “error-free” state Produces same file format (*.dacpac) as build or

extract

To be used for: Versioning Comparison Sharing schema definitions or changes


Schema Compare

Performs a model comparison between: Databases Projects DACPAC files (snapshots, build outputs, extracts)

Supports: Refactoring log SQLCMD variables

03/29/2012 SQL Saturday #176 – Pordenone, Italy57 | 17/11/2012 |

Schema Definition

Script

Fragment

Model Builder

SourceSchemaModel

ScaffoldingDB

SchemaPackage

ModelDeserializer


Deployment & Schema Compare

Model Compare

Script

Fragment

Model Builder

SourceSchemaModel

ScaffoldingDB

SchemaPackage

ModelDeserializer

Script

Fragment

Model Builder

Scaffolding DB

SchemaPackage

ModelDeserializer

TargetSchemaModel

Diff List

PlanOptimizer

Deploy Plan

Script Generator

DiffVisualizer


Schema CompareNext 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

60


Schema Compare


Deployment Options

Options to influence deployment behavior Ignore*

Deployment validation options Do not alter Change

Data Capture objects Do not ALTER replicated

objects Verify deployment

03/29/2012Copyright © 2012 Gert Drapers - All Rights Reserved.62

DEMO

SQL CLR


03/29/2012


63

SQL-CLR Support

Embedded C# or VB.NET code artifacts Deployed as SQL-CLR objects

Binary assembly deployment and SQL-CLR object association Example:

CREATE TYPE [ClrSchema].[IPv4]EXTERNAL NAME [IPAssembly].[IPv4];



CLR Needs

SQL Server CLR usage Geographical support

Your needs Web communication Custom function

String management Aggregate functions

«extreme calculus» in sp

Don’t abuse

17/11/2012 | 65 |

DEMO

SQL AZURE


03/29/2012


66

SQL Azure

It’s Cloud Computing! SQL Server e SQL Azure sono diversi

Diverse esigenze

Connected development on SQL Azure Change targetFix & Build


DEMO


SQL SERVER DATA TOOLSConclusions

17/11/2012 | 68 |


Already third party tools First release

Many step forward Some step back (compared to DataDude)

17/11/2012 | 69 |


Conclusions

Modeling infrastructure One development environment More checks over actions Use targets

A tool for SQL Azure

Debug and schema deployment Go under TFS

17/11/2012 | 70 |

Resources

Articles MSDN Magazine Sept 2011

The "Juneau" Database Project

SQL Server Data Tools Online Installation http://msdn.microsoft.com/data/tools

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


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

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

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


References

twitter: @marco_parenzan email: [email protected] blog: http://codeisvalue.wordpress.com/ facebook: http://www.facebook.com/parenzan.marco linkedin: http://it.linkedin.com/in/marcoparenzan slideshare: http://www.slideshare.net/marco.parenzan/

17/11/2012 | 72 |

http://codeisvalue.wordpress.com/

http://www.facebook.com/parenzan.marco

http://it.linkedin.com/in/marcoparenzan

http://it.linkedin.com/in/marcoparenzan

http://www.slideshare.net/marco.parenzan/

http://www.slideshare.net/marco.parenzan/


GRAZIE!Commenta la sessione che hai

appena seguito su Twitter

#sqlsat176

17/11/2012 | 73 |

sql server data tools

Technology

thespecific sql

null int

auction table null

different sql server

intiddatetime null

sql azure versions8

len int null end

sql user 1nn0va speaker