Here’s a summary of the talk I did at the Adelaide SQL Server User Group last week. Most of the talk was a demo (only a couple of PowerPoint slides) so hopefully this is a useful reference for those who attended.

Intro to SSDT

  • Evolution of DataDude
  • The new way of shipping BIDS
  • Ships with SQL Server 2012 and Visual Studio 2012
  • Replaces ‘Database Projects’ from previous versions

  • Updates every 4-6 months (in time for SQL Azure updates)

    • Ships with VS 2012 and with SQL 2012

    • Free tool - use VS shell or integrate with existing
    • Will update frequently to keep up with SQL Azure features

Connected

  • SQL Server Object Explorer

  • Connect to local or Azure

  • Queries

  • Execution Plans
  • Client stats

Disconnected

  • Database projects
  • Model-based
  • Edition-aware targeting (project properties)
    • Can switch to azure to check if database is compatible with azure
  • Table designer/code view
    • Synchronised
    • Demo deleting a column
      • Can see errors (even before building) of related objects (eg. Views) that reference column
  • Build creates a ‘dacpac’ (Data-tier Application Package)
    • ‘Upgrade data-tier application’ from SMSS
  • Declarative model
  • ‘Create New Project’ via SQL Server Object Explorer
  • F5 builds and deploys database to localdb
  • Snapshots
  • Import .sql files
  • Code analysis

Schema Compare

  • Connected, Project, dacpac or snapshot
  • Update changes (defaults to not losing data)

Refactoring

  • Expand wildcards to column names - SELECT *
  • Semantic refactoring through model - not just find/replace
  • Go to Reference, Find all references

Publish database

  • Directly
  • Via SQL script
  • DACPAC
    • C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\sqlpackage.exe

“c:\Program Files (x86)\Microsoft SQL Server\110\dac\bin\SqlPackage.exe” /action:driftreport /tsn:.\sql2012

/outputpath:c:\tmp\driftreport.xml /tdn:SsdtSample

Unit testing

  • Right-click on stored proc to create unit tests

3rd Party Integration and extensions

  • Red Gate SQL Compare (beta)
  • Version control
  • Laan SQL Formatter (soon)

Further reading