1 / 25

The SSDT way or the highway

The SSDT way or the highway. ( sqlproj ). Craig Ottley-Thistlethwaite Data Platform Lead Data Science. Database Technologies. This is what the highway can look like. Agenda. SQL Server Data Tools History DAC Framework Declarative Schema Deployment Inside the dacpac

edita
Download Presentation

The SSDT way or the highway

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. The SSDT way or the highway (sqlproj) Craig Ottley-Thistlethwaite Data Platform Lead Data Science

  2. Database Technologies

  3. This is what the highway can look like

  4. Agenda • SQL Server Data Tools History • DAC Framework • Declarative Schema Deployment • Inside the dacpac • Pre/Post Deploy Scripts • Check/Default Constraints • Add/Remove/Rename Columns • Column Data Type Conversion • DacFx Public Model • Deploy Contributor

  5. 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

  6. 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

  7. 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

  8. Declarative Schema Deployment

  9. Agenda • SQL Server Data Tools History • DAC Framework • Declarative Schema Deployment • Inside the dacpac • Pre/Post Deploy Scripts • Check/Default Constraints • Add/Remove/Rename Columns • Column Data Type Conversion • DacFx Public Model • Deploy Contributor

  10. Inside the dacpac

  11. Agenda • SQL Server Data Tools History • DAC Framework • Declarative Schema Deployment • Inside the dacpac • Pre/Post Deploy Scripts • Check/Default Constraints • Add/Remove/Rename Columns • Column Data Type Conversion • DacFx Public Model • Deploy Contributor

  12. Pre/Post Deployment Scripts

  13. Agenda • SQL Server Data Tools History • DAC Framework • Declarative Schema Deployment • Inside the dacpac • Pre/Post Deploy Scripts • Check/Default Constraints • Add/Remove/Rename Columns • Column Data Type Conversion • DacFx Public Model • Deploy Contributor

  14. Check/Default Constraints

  15. Agenda • SQL Server Data Tools History • DAC Framework • Declarative Schema Deployment • Inside the dacpac • Pre/Post Deploy Scripts • Check/Default Constraints • Add/Remove/Rename Columns • Column Data Type Conversion • DacFx Public Model • Deploy Contributor

  16. Add/Remove/Rename Columns

  17. Agenda • SQL Server Data Tools History • DAC Framework • Declarative Schema Deployment • Inside the dacpac • Pre/Post Deploy Scripts • Check/Default Constraints • Add/Remove/Rename Columns • Column Data Type Conversion • DacFx Public Model • Deploy Contributor

  18. Column Data Type Conversions

  19. Agenda • SQL Server Data Tools History • DAC Framework • Declarative Schema Deployment • Inside the dacpac • Pre/Post Deploy Scripts • Check/Default Constraints • Add/Remove/Rename Columns • Column Data Type Conversion • DacFx Public Model • Deploy Contributor

  20. 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 class contains loosely typed TSqlObject’s that represent all the elements in your schema • 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 privatestaticvoidReadTheModel(TSqlModel model){ // This will get all tables. Note the use of Table.TypeClass! vartables = model.GetObjects(DacQueryScopes.Default, Table.TypeClass).ToList(); } • API does not fully support everything an SSDT project supports!

  21. Agenda • SQL Server Data Tools History • DAC Framework • Declarative Schema Deployment • Inside the dacpac • Pre/Post Deploy Scripts • Check/Default Constraints • Add/Remove/Rename Columns • Column Data Type Conversion • DacFx Public Model • Deploy Contributor

  22. Deployment Contributor

  23. Agenda • SQL Server Data Tools History • DAC Framework • Declarative Schema Deployment • Inside the dacpac • Pre/Post Deploy Scripts • Check/Default Constraints • Add/Remove/Rename Columns • Column Data Type Conversion • DacFx Public Model • Deploy Contributor

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

  25. Twitter: @Craig_Ottley Email: craig.ottley-thistlethwaite@dacpac.com Q&A

More Related