What is SQL Server Data Tools (SSDT)?

SQL Server Data Tools (SSDT) is a toolset that allows professional database and application developers to carry out all their database design work for SQL Server and SQL Azure within Visual Studio. SSDT provides a rich SQL Server development experience with first class language services and VS integration, as well as declarative, model-based tools that can be utilized for both online and offline development.

Database developers can use the new SQL Server Object Explorer to easily create and edit database objects and data, or execute queries. SSDT’s connected editing experience has all the benefits of a model-backed database, allowing for declarative development in an online context and design-time dependency analysis. Instead of writing ALTER statements to modify a database object, developers can define the desired state of objects as if they are being created for the first time. The deployment engine then compares the defined state to the live database objects and generates the script to make the necessary changes to update the database.

The SSDT project system allows developers to have a fully representative source-code backed model of their database for offline development. SSDT Database Projects also enable the database schema to be version controlled using database snapshots and other source code control mechanisms. While using the T-SQL editor offline, developers will appreciate the familiar set of VS tools that we bring to database development, such as IntelliSense, refactoring, Go To Definition and Find All References. Database Projects also provide platform-specific validation at design time and debugging capabilities that allow the developer to test their database locally, and then publish to SQL Server or SQL Azure.

In both project and connected scenarios, SSDT offers two valuable tools that greatly enhance productivity in database development: Schema Compare and the Table Designer. Database developers can utilize the Schema Compare tool to compare and synchronize schemas between live databases, database projects and project snapshots, the three different database representations in SSDT. Schema Compare offers an at-a-glance view of the differences between two schemas, as well as the ability to drill down to the object and code diff level to deploy changes. SSDT’s new Table Designer features a split view with a code window so that as the table is edited in the graphical design view, the table’s script definition updates in real time.  The Table Designer gives developers the flexibility to define tables via designer, script, or a combination of the two, and brings the table’s related objects into the same workspace, such as indexes and triggers.

• Anonymous
  January 16, 2012
  Hi Adam, previously I've worked on projects where, as I understand it, "data dude" was used such that, if changes were made to items in Visual Studio, when the database project was built and deployed, those schema changes would get propagated to other developers via TFS.  That worked nicely in a situation where each developer had a local copy of the database on their virtual machine.  Now however, I'm on a different project where there is a common dev database server that all developers use.  There are sometimes problems where they have difficulty synchronizing, say, changes to the schema in the dev environment, which then need to be propagated to a target environment (say, a UAT environment).  Would the Schema Compare tool help us compare, for example, if changes that exist in the dev environment also exist in the target environment?  Thank you -Dave

• Anonymous
  January 17, 2012
  Hi David, Thanks for the question.  Yes, from your description, Schema Compare could help you compare your development environment schema and target database/environment.  Schema Compare describes the differences between a source and target schema in terms of actions that need to be taken on the target to make it the same as the source.  Schema Compare can be used on any combination of schemas, which may be project, database, snapshot or dacpac based.  You can inspect the results and choose which actions to include/exclude.  If the target is a database or project you can choose to update the target directly, or generate a script to apply the actions later (for a database, snapshot or dacpac target).  In your situation you could also switch the source and target around and apply changes from the target database to the development environment as a way of propagating changes between developers. I hope this helps.  Let us know if you have any follow on questions. Cheers, Bill Gibson, Microsoft  

• Anonymous
  January 17, 2012
  Thanks for your response Bill, much appreciated.  We'll take a look at it. Best regards, Dave

• Anonymous
  January 24, 2012
  David, What you've described is already possible using "Visual Studio 2008 Database Edition with the GDR2 add-on" (for obvious reasons aka "data dude") or the Schema Compare functionality in VS2010.  I've been using it that way for years already :) Just create different schema comparisons in your DB project: one to compare DEV to TFS (or DEV to project), one to compare DEV to UAT, one to compare UAT to PRD (very handy for troubleshooting when a deploy script has failed). Best regards, Valentino.

• Anonymous
  January 02, 2016
  I found the below article on SSDT very useful -- dotnetvibes.com/.../continuous-integration-using-ssdt-in-visual-studio