SSDT Talk Notes
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)