SQL Server Data Tools (SSDT): A Great Set of Tools for Database Dev
By Don Kiely | November 26, 2012
Microsoft introduced a new set of database development-oriented tools in SQL Server 2012, called SQL Server Data Tools. These tools are the latest in a long line of attempts to make creating database and database objects like creating software. Microsoft's earlier attempts were credible and usable, but always seemed to fall short in one or more significant ways. We're happy to report that with SSDT Microsoft nailed it, and both developers and administrators have a great set of tools for database development work.
Here is Microsoft's description of the Data Tools:
SQL Server Data Tools (SSDT) transforms database development by introducing an ubiquitous, declarative model that spans all the phases of database development and maintenance/update inside Visual Studio. You can use SSDT TSQL design capabilities to build, debug, maintain and refactor databases whether working with a database project, or directly with a connected database instance located on or off-premise.
Every word of that description is true, but it certainly isn't a good 60-second elevator talk about what the Data Tools are! What the Data Tools really does is to put database development into the context of writing software in VisualStudio, in every sense.
DBAs are probably going to want to stick to Management Studio for their daily work with databases and SQL servers, but developers, particularly seasoned Visual Studio developers, will be able to completely live in the Data Tools, except when they put on their DBA hats for smaller clients that don't have full-time DBAs.
The crux of the matter is that for simple admin tasks, you can use the new dedicated SQL Server Object Explorer in the Data Tools and do most of what you need to do-from a developer's perspective-instead of using Management Studio. And when you create a new database project, you can act as though it were a new software project, a nifty abstraction from one context to another.
The best part of Data Tools is that if you have experience with Visual Studio, you'll feel right at home with the Data Tools. They have support for code navigation, IntelliSense, language support for T-SQL that is similar to C# and VB, platform-specific validation, debugging and declarative editing in the TSQL Editor, and plenty more. You can save database object definitions in source control, and when you're ready to deploy the database you can publish the project like you do for a Web site, to any version of SQL Server from 2005 on and including SQL Azure.
Another use for the Data Tools is the replacement for the Business Intelligence Development Studio (BIDS) of earlier versions of SQL Server. BIDS was built using Visual Studio 2008, and provided templates for creating various Business Intelligence projects. Data Tools replaces BIDS and provides a much better development environment than BIDS did.
Installing SQL Server Data Tools
The system requirements for Data Tools are the same as for Visual Studio 2010 SP1, so if you have that version of Visual Studio installed you're good to go. There are four ways to install the Data Tools:
- As part of a SQL Server 2012 installation
- When creating a Visual Studio 2010 project
- Platform Web installer
- Administrative installation point
Data Tools doesn't require prior installation of VS 2010. If you do not have Visual Studio 2010 Professional Edition or above installed, installing SSDT will automatically install the Visual Studio 2010 Integrated Shell, apply SP1, and install the SSDT components. The Integrated Shell will only contain SSDT tools, and does not include Visual Studio programming languages and the features that support their respective project systems.
If you do have VS 2010 Professional or better installed, Data Tools integrates into that installation and you'll find that Visual Studio has new features and project templates.
When you install Data Tools as part of SQL Server 2012, and you don't have Visual Studio pro installed, you won't actually end up with Data Tools fully installed. Instead, you'll have the Visual Studio shell installed with a project in the SQL Server node of Templates with a "SQL Server Data Tools (Web install)" item. When you create a project using that template, it will download and install the actual tools. This is kind of a funky way of doing things (and potentially VERY confusing), but we're sure Microsoft had a great reason for doing it that way!