SQL Server Data Tools in SQL Server 2012 – Part 1

Introduction

As developers, we have always wanted to have a single Integrated Development Environment (IDE) for not only writing application code but to also to write database code in a single place without switching the environments. SQL Server Data Tool (SSDT) is a new feature in SQL Server 2012 that provides this capability primarily intended for developers and lets developers write their application as well as database code in a single development environment of Visual Studio.

Introduction to SQL Server Data Tool (SSDT)

SQL Server Data Tool (SSDT) is a new feature in SQL Server 2012 and a free web platform installer, which can be installed from here for Visual Studio 2010 and from here for Visual Studio 2012. Once having installed SQL Server Data Tool, a developer no longer needs to have SQL Server Management Studio (SSMS), which is primarily intended for managing SQL Server and database instances, for database development and design activities. SSDT provides writing database code in both connected (either on or off-premise) as well as in disconnected mode and the best part is the experience is seamless while working in either any of these modes. SSDT provides the ability to the database developer to build, debug, maintain and refactor database objects in a simple and interactive way.

Getting Started with SQL Server Data Tool (SSDT) – Disconnected Mode

SSDT provides the capability to use Visual Studio IDE itself for database design, development, debugging and testing for developers with limited or no access to a live (production) database. Developers can create a SQL Server Database Project in the SSDT and without being connected to any live SQL Server instance they can design and develop their database code. SSDT includes a visual Table Designer for creating and editing tables in either database projects or connected SQL Server instances. Right within the IDE, you can use version control for all of the files if you are working in a team-based environment. Once you are done with your database development, you can publish your project to all of the supported SQL platforms, including SQL Azure.

For debugging your code in the SQL Server Database project (or in disconnected mode), SSDT includes a local on-demand server instance, called SQL Server Express Local Database Runtime, which gets activated when you start debugging your code in the SQL Server Database project and it comes in handy for developers who have limited or no access to production (live) databases. Acting as sandbox environment, this local instance allows for building, testing and debugging your database code in the database project. SQL Server Express Local Database Runtime is independent of any of your installed SQL Server instances, and is not accessible outside SQL Server Data Tools (SSDT). In addition, when you are developing a database code for SQL Azure, you can utilize the convenience provided by this local server (SQL Server Express Local Database Runtime, which comes with SSDT) to develop and test your database project locally, before deploying it to the cloud. You can learn more about this here.

Unlike previous versions of Visual Studio database project where an individual has to create one project for each SQL Server version (by choosing a specific version of the project template) when creating the database project, SSDT allows you to create a single database project for all of the SQL Server versions (starting with SQL Server 2005), which can include both Transact-SQL (T-SQL) scripts and code that defines SQL CLR objects.

New Project
New Project

Later on, when compiling the database project, it can be compiled for publishing the database to a specific version of SQL Server or SQL Azure by going the properties of the database project and selecting Target platform appropriately. SSDT validation capability ensures that your scripts work on the target you choose.

Target Platform
Target Platform

Even though you work in disconnected mode, the SSDT provides all of the features like tools to edit, compile, refactor, Intellisense, Schema Comparison, creating snapshot the database project, etc.

There are different options available to create a SQL Server database project as mentioned below:

  • Create a SQL Server database project from scratch and adding each object and writing modules (stored procedure, user defined function, etc.) required
  • Create a SQL Server database project and then by importing from the database deployed on SQL Server instance
  • Create a SQL Server database project and then by importing from the data tier application package
  • Create a SQL Server database project and then by importing the objects from the script files

Conclusion

In this article, I talked about a new feature of SQL Server 2012 called SQL Server Data Tool (SSDT). I also talked about different modes it works in and demonstrated in detail how it works in disconnected mode and all of the possibilities it provides while working in disconnected mode. In the next article of this series, I am going to talk in detail about how it works in connected mode and the possibilities it provides while working in connected mode; I will also talk about some of the cool features SSDT provides, which are available in both of these modes.

Resources

Data Tier Application

SQL Server Data Tools (SSDT)

SQL Server Data Tools Team Blog

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles