Database Development with SQL Server Data Tools

SQL Server Data Tools (SSDT) is a powerful integrated development environment for designing, testing, and deploying SQL Server databases — whether you’re working locally or remotely, connected or offline, or in the cloud on Microsoft Azure — all from right inside of Visual Studio. While SQL Server Management Studio (SSMS) continues to serve as the primary tool for database administrators, SSDT plugs in to Visual Studio as a special database project type designed specifically for the application developer.

In this session, Lenni describes the various difficulties that developers face, and demonstrates how SSDT can be used to remedy those pain points. You will learn how to use features such as code navigation, IntelliSense, and refactoring with your database model — indispensable tools traditionally available only for application development in Visual Studio. We’ll also cover the declarative model that allows you to design databases offline and under source control right from within solution in Visual Studio, as well as how to deploy to SQL Database on Microsoft Azure. Don’t miss out on this demo-centric information-packed session on the current generation of database tools for application developers!

Transcript

1. October 4, 2016 1:30pm – 2:45pm Database Development with SQL Server Data Tools Level: Intermediate Leonard Lobel Chief Technology Officer Sleek Technologies
2. About Me Leonard Lobel • CTO & Co-Founder – Sleek Technologies, Inc. • Principal Consultant – Tallan, Inc. • Microsoft MVP – Data Platform • Co-organizer – NYC .NET Developers Group • Trainer/Speaker/Author • Programming since 1979 Contact • Email: lenni.lobel@sleektech.com • Blog: lennilobel.wordpress.com • Twitter: @lennilobel sleek technologies Developers Group
3. http://bit.ly/ vsldc2016_ssdt (all lower case!) Download Slides and Code
4. What is SSDT? • Database tooling in Visual Studio – Major step forward from previously available tooling • SQL Server Management Studio (SSMS) • Visual Studio Database Professional Edition (DbPro) – Aka “Data Dude” • Not an SSMS Replacement – SSMS remains the primary management tool • Replaces DbPro – …but not entirely
5. Database Development Pains • Architecturally challenging – Database, schema, relational design – T-SQL intricacies – Performance tuning – …and more • Development process – Lots of dependencies to manage – Late-bound error detection – Production is a moving target – Versioning and source control – Target different editions and the cloud
6. Declarative vs. Scripted • How do you deploy a table, view, stored procedure, etc? – If it’s new, then it’s a CREATE statement – If it exists, then it’s an ALTER statement • Working with a declaration of what you believe (or want) the database to be – Focus on design – Let the tool worry about writing scripts “just right” • Model-based Design – In-memory representation of database structure (not a database!) – Populate from local/cloud database or source control • Same tools whether connected or offline – Works the same against a connected database or any source-controlled version of a database
7. Database Model Declarative Model-Based Development Azure SQL Database SQL Server 2005, 2008, 2008 R2, 2012, 2014 Database Project (LocalDB) Database Snapshot File (.dacpac) SQL Server Data Tools (SSDT) On-Premise DataCenter Offline Dev/Test Cloud Version History
8. Connected Development • SQL Server Object Explorer – New panel inside Visual Studio – Implements most developer-oriented features from SSMS • Query Window – Provides a query execution window similar to SSMS • Power Buffer – Edit multiple objects – Real-time validation on memory-resident edits – Automatically generate and execute change script
9. Offline Database Projects • The database as a set of artifacts – Offline development with Visual Studio project system – Put in source control alongside the application • Test locally “offline” with (localdb) – SSDT includes a lightweight, single user instance of SQL Server for offline development and testing • Create snapshots – Save the database structure in a .dacpac file • Flexible deployment – One database definition with a targeting switch – Target different editions of SQL Server or Windows Azure SQL Database
10. Schema Differencing • Compare any two database models – Real databases – Database projects – Database snapshots • Use for – Incremental import and deployment – Publish to Windows Azure SQL Database – Drift detection and reconciliation
11. SSDT Limitations • No data generation • Other notable omissions – No graphical query designer – No database diagrams – No spatial results viewer
12. SQL Server Data Tools
13. Questions?
14. Thank You! • Contact me – lenni.lobel@sleektech.com • Visit my blog – lennilobel.wordpress.com • Follow me on Twitter – @lennilobel • Thanks for coming! 