Document

Download Report

Transcript Document

The SSDT way or the highway

(sqlproj)

Craig Ottley-Thistlethwaite

Data Platform Lead Data Science

Database Technologies

Experian Public.

This is what the highway can look like

Experian Public.

Agenda



SQL Server Data Tools History



Check/Default Constraints



DAC Framework



Add/Remove/Rename Columns



Declarative Schema Deployment



Column Data Type Conversion



Inside the dacpac



Pre/Post Deploy Scripts

 

DacFx Public Model Deploy Contributor

Experian Public.

SQL Server Data Tools History



Project funded April 2005 (Data Dude)



Project Started July 2005



VS 2005 Team System for Database Professionals



VS 2008 Team System for Database Professionals



Re-architected to be declarative model based system



VS 2008 Team System for Database Professionals GDR R2



VS 2010 Team System for Database Professionals



Transitioned to SQL Server 2009



SQL Server Data Tools

Source: http://sqlproj.com/wp-content/uploads/2012/03/SSDT-Workshop-2012-03-29-final.pdf

Experian Public.

Evolution

.dbschema v1.1 VS 2008 GDR .dbschema v1.2 VS 2010 dbproj .sqlx v1.0

SSDT CTP3 .dacpac v2.5

SSDT CTP4 .dacpac v3.0 redist consumed by ALL SQL Products .dacpac v1.0 SQL 2008 R2 RC .dacpac v1.05

SQL 2008 R2 RTM .dacpac v1.1 SQL 2008 R2 SP1 .dacpac v2.0

SQL 2012 RC0 Source: http://sqlproj.com/wp-content/uploads/2012/03/SSDT-Workshop-2012-03-29-final.pdf

Experian Public.

DAC Framework (DacFX)

 Core SQL Server redistributable component which provides modelling, reverse engineering and deployment capabilities!    Managed Public API Command-Line tools (SqlPackage.exe) DACUnpack.exe

► Windows file handler for unpacking DACPACs to disk  DACFx Clients ► SqlPackage.exe, SSMS, SSDT, SAMP, I&E, VS Web and DB Publishing

DacFx does not support state/operational properties!

Source: http://sqlproj.com/wp-content/uploads/2012/03/SSDT-Workshop-2012-03-29-final.pdf

Experian Public.

Declarative Schema Deployment

Experian Public.

Agenda



SQL Server Data Tools History



Check/Default Constraints



DAC Framework



Add/Remove/Rename Columns



Declarative Schema Deployment



Column Data Type Conversion



Inside the dacpac



Pre/Post Deploy Scripts

 

DacFx Public Model Deploy Contributor

Experian Public.

Inside the dacpac

Agenda



SQL Server Data Tools History



Check/Default Constraints



DAC Framework



Add/Remove/Rename Columns



Declarative Schema Deployment



Column Data Type Conversion



Inside the dacpac



Pre/Post Deploy Scripts

 

DacFx Public Model Deploy Contributor

Experian Public.

Pre/Post Deployment Scripts

Agenda



SQL Server Data Tools History



Check/Default Constraints



DAC Framework



Add/Remove/Rename Columns



Declarative Schema Deployment



Column Data Type Conversion



Inside the dacpac



Pre/Post Deploy Scripts

 

DacFx Public Model Deploy Contributor

Experian Public.

Check/Default Constraints

Agenda



SQL Server Data Tools History



Check/Default Constraints



DAC Framework



Add/Remove/Rename Columns



Declarative Schema Deployment



Column Data Type Conversion



Inside the dacpac



Pre/Post Deploy Scripts

 

DacFx Public Model Deploy Contributor

Experian Public.

Add/Remove/Rename Columns

Agenda



SQL Server Data Tools History



Check/Default Constraints



DAC Framework



Add/Remove/Rename Columns



Declarative Schema Deployment



Column Data Type Conversion



Inside the dacpac



Pre/Post Deploy Scripts

 

DacFx Public Model Deploy Contributor

Experian Public.

Column Data Type Conversions

Agenda



SQL Server Data Tools History



Check/Default Constraints



DAC Framework



Add/Remove/Rename Columns



Declarative Schema Deployment



Column Data Type Conversion



Inside the dacpac



Pre/Post Deploy Scripts

 

DacFx Public Model Deploy Contributor

Experian Public.

DacFx Model API – What is it?

    SSDT model’s a database’s schema, the API lets you access that model programmatically. You can load, query and manipulate the schema to do what ever you like (within reason) API model is loosely typed, the TSqlModel elements in your schema class contains loosely typed TSqlObject ’s that represent all the Each object will have some properties that describe it’s state and the relationships to other objects in the model Use strongly typed classes to find the properties/relationships of an object using TsqlModel.GetObjects

 private static void ReadTheModel(TSqlModel model) { // This will get all tables. Note the use of Table.TypeClass!

var tables = model.GetObjects(DacQueryScopes.Default, Table.TypeClass).ToList(); }  API does not fully support everything an SSDT project supports!

Feature Supported

Refactor Log Deployment Contributors Pre/Post Deployment Scripts References CLR Objects XML Schema Collections

Experian Public.

Yes Yes No No No No

Agenda



SQL Server Data Tools History



Check/Default Constraints



DAC Framework



Add/Remove/Rename Columns



Declarative Schema Deployment



Column Data Type Conversion



Inside the dacpac



Pre/Post Deploy Scripts

 

DacFx Public Model Deploy Contributor

Experian Public.

Deployment Contributor

Agenda



SQL Server Data Tools History



Check/Default Constraints



DAC Framework



Add/Remove/Rename Columns



Declarative Schema Deployment



Column Data Type Conversion



Inside the dacpac



Pre/Post Deploy Scripts

 

DacFx Public Model Deploy Contributor

Experian Public.

Resources

  

http://dacsamples.codeplex.com/ https://github.com/dacpac http://blogs.msdn.com/b/ssdt/archive/2013/12/23/dacfx-public-model-tutorial.aspx

Experian Public.

Q&A

Email: [email protected]

Twitter: @Craig_Ottley