Posted by Ami Levin on Sunday, 20 May 2012
I've recently encountered some confusion regarding one of the new components of SQL Server 2012, the SQL Server Data Tools AKA SSDT. From the questions in the MSDN SQL Server forum which I moderate, I realized that the distinction between SQL Server Management Studio (SSMS) and SSDT is not clear enough to many. I have to agree that Microsoft did leave some space for confusion and a lot of duplicate functionality in both tools so let's try to clear up a bit of this confusion.
Before SQL Server 2012, the only management / development tool that shipped with SQL Server as part of the installation was SSMS. SSMS actually serves 2 main purposes - Management and development. You can manage all your SQL Server instances using graphical tools, wizards and using T-SQL. SSMS also included the visual schema designers (A separate blog topic...), Query designers, scripting features and many others that are used by both DBAs and developers.
What a lot of DBAs didn't know, that since 2007, Visual Studio (in some versions) offered a complementary database development tool called "Visual Studio Team System Edition for Database Professionals". A short and friendly name if you abbreviate it to VSTSEDBP. This project was the baby of Gert E. Drapers that gave it its more common name, "Data Dude". Data dude was built to help developers use their familiar and comfortable VS environments while allowing integration of the database development into the VS ALM (Application Lifecycle Management). Data Dude offered schema and object creation using database projects inside VS, off line 'virtualized' development without an actual SQL Server instance, unit testing, data generation capabilities, schema and data compare features, code review, smart debugging and of course the ability to deploy these projects along with the application while maintaining strict version control - a term most production DBAs only heard of theoretically.
SSDT is actually an evolution of data dude. Microsoft seized the opportunity to kill 3 birds with one stone here. The 'less than expected' adoption of data dude and VSTE in general, the every tightening integration of VS infrastructure into the SQL Server tools and the highly accelerated investment in SQL Azure all led to the decision to provide SSDT as a free tool for all SQL Server users with SQL Server 2012. The FAQ page says "SSDT provides a modern database development experience for the SQL Server and SQL Azure Database Developer. As the supported SQL Azure development platform, SSDT will be regularly updated online to ensure that it keeps pace with the latest SQL Azure features.". With VS 11 (still in beta), "SSDT will both replace and provide conversion capability for existing Visual Studio database projects".
Microsoft provides a clear and concise explanation of the use cases of SSDT for developers and DBAs:
"SSDT is for SQL Server database developers, who often develop database schemas, views, stored procedures, and other database objects while developing their application logic.
- Tooling for both SQL Server and SQL Azure Development:
SSDT offers new capabilities in a single cohesive environment to compile, refactor, and deploy databases to specific editions of SQL Server and SQL Azure. The toolset makes it easy, for example, to migrate on-premise SQL Server schemas to the cloud on SQL Azure, and develop and maintain databases across both on premise and cloud deployments. SSDT can target SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, and SQL Azure databases, including all editions of these database servers.
- For SQL Server DBAs:
SSDT provides a central and unified toolset targeted to the specific needs of DBAs to develop and maintain databases, with visual tools for developing tables, schema compare, and rich T-SQL support for refactoring databases, building views, stored procedures, functions and triggers. The toolset provides both a live development mode, and an offline project mode that tracks and manages all artifacts associated with a database. This mode optionally fully integrates with Visual Studio 2010 for team development, source control and change tracking. All change operations are automatically transformed into optimized T-SQL alter scripts, and can optionally be applied immediately to the online database or saved for later execution."
It also answers the natural question - "Is SSDT a replacement for SQL Server Management Studio (SSMS)"?
Well - not really... "SSMS is targeted at core administration of connected databases, and will continue to be a part of SQL Server, and a core component of SQL Server 2012. As such, SSMS will continue to be the graphical tool for such tasks as creating and managing database backups, creating and viewing extended events, and other such central DBA administration tasks for production databases."
So, although currently there is definitely some significant overlap between SSMS and SSDT which was left intentionally to allow users to gradually become familiar with the new SSDT, we can expect future releases to remove some of this overlap until some day, not too far ahead, we will have 2 distinct tools - SSMS for online instance management, and SSDT for development.
If you are into database development, I highly recommend you to install and learn how to use SSDT. It will make your life easier in many ways.
