With SQL Server Denali CTP3 released just two weeks ago, I’ve been ramping up fast as I write the new edition of Tallan’s SQL Server book (Programming Microsoft SQL Server 2012, O’Reilly) and prepare for upcoming presentations at the NJSQL User Group (Sep 20) and the SDC event in the Netherlands (Oct 2/3). I recently spent a lot of time working with the Juneau CTP3, code name for the new SQL Server Developer Tools (SSDT), set to ship with SQL Server 2012 (code named Denali) next year. I’m extremely impressed with the features of this new tool, and delighted at the notion that most developers will no longer need to toggle between Visual Studio and SQL Server Management Studio (SSMS), because they may very well never again need to use SSMS at all!
This is not to say that SSDT is intended to be a replacement for SSMS—au contraire, SSMS is alive and well in Denali, and continues to evolve as the primary management tool for database administrators respsonible for maintaining running SQL Server installations. But programmers have been using SSMS to conduct development tasks for years (and before 2005, we were using two dba tools—SQL Enterprise Manager and Query Analyzer). It’s always been necessary to switch away from our primary application development tool (Visual Studio) to a database management tool (SSMS) in order to get our database development done.
But no more.
SQL Server Developer Tools finally gives application developers what they need to get everything done without ever leaving Visual Studio—and it delivers many powerful new capabilities as well. In this post, I’ll describe the most important features that developers are certain to enjoy when they start working with this new tool.
The key concept in SSDT is that it uses a model-based approach. That is, there is always an in-memory representation of what a database looks like, and all the SSDT tools (designers, validations, IntelliSense, schema compare, etc.) operate on that model. This model can be backed by a live database (on-premises or Azure), an offline database project, or a snapshot taken of an offline database project at any point in time. But to reiterate, the tools are agnostic to the model’s backing, and work exclusively against the model itself. Thus, you enjoy a rich, consistent experience in any scenario—regardless of whether you’re working with on-premises or cloud databases, offline projects, or versioned model snapshots.
SSMS in Server Explorer
The Server Explorer now provides most of the functionality that developers are accustomed to in SSMS. Although the experience is strikingly similar when working against a connected database, remember that (and I’ll risk overstating this) SSDT tools only operate on a model. So when working connected, SSDT is actually creating a model from the database—on the fly—and then allowing you to edit that model.
When you “save” a schema change, SSDT works out the necessary script to update the database to reflect the change made to the model. Of course, the end result is the same as the connected SSMS experience, so it isn’t strictly necessary to understand the distinction if you don’t exploit the capabilities of SSDT beyond connected development (but why wouldn’t you?). Once you do understand it, then SSDT’s offline development and versioning capabilities will immediately feel natural and intuitive, as those scenarios are simply different backing types of the very same model. It’s just that when you’re working in Server Explorer, the backing happens to be a live connected database.
You can also open query windows to compose and execute T-SQL statements directly against the database. Overall, these Server Explorer-based features give you the same connected management experience you are accustomed to in SSMS; which is an imperative, script-based approach to directly managing a stateful database.
Offline Development in Visual Studio
SSDT offers so much more than a mere replacement for the connected SSMS experience, by providing a rich offline experience with the new SQL Server project type in Visual Studio 2010. If you’re familiar with the previous Database Professional editions of Visual Studio (commonly known as “data dude”), you can think of this project type as the next version of that edition (data dude projects will even load and upgrade to SSDT automatically when opened in Visual Studio 2010). The T-SQL script files in a SQL Server project are declarative in nature (all CREATE statements; no ALTER statements), which is an entirely different approach to how you’re accustomed to “developing” databases in SSMS against a live stateful database. Essentially, you get to focus on “this is how the database should look,” and let the tool worry about and figure out the appropriate T-SQL statements to actually update the live database to match your definition.
The SQL Server project enjoys all the same capabilities of any Visual Studio project. This includes not only source control (as mentioned), but all the common code navigation and refactoring paradigms (like Rename, Goto Definition, and Find All References) that have come to be accepted as indispensible tools of the modern IDE. And the model’s rich metadata provides a far better IntelliSense than what SSMS has been offering since SQL Server 2008. You really get that “strongly-typed” feeling. You can also set breakpoints, single-step through T-SQL code, and utilize the Locals window in Visual Studio much like you can when debugging .NET project types. With SSDT, application and database development tooling has now finally been unified under one roof: Visual Studio 2010.
The beauty of the model-based approach is that you can generate models from almost anything. As explained, when connected directly via Server Explorer, SSDT creates a model from the connected database. When you create a SQL Server project (which can be imported from any existing database, script, or snapshot), you are creating an offline source-controlled project inside Visual Studio that fully describes a database. Now SSDT generates a model backed by your project, and so the design experience offline is just the same as when connected; the designers, IntelliSense, validation checks, and all other SSDT features all work exactly the same way.
As you conduct your database development within the project, you get the same “background compilation” experience that you’re used to experiencing with typical .NET development using C# or VB .NET. For example, making a change in the project that can’t be submitted to the database because of dependency issues will immediately raise errors in the Errors pane. You can click on the errors to navigate directly to the various dependencies so that they can be dealt with. Once all the build errors disappear, you’ll be able to submit the changes to update the database.
SSDT also provides a new local database runtime for testing and debugging. This is a lightweight, single user instance of SQL Server that spins up on demand when you build your SQL Server project. This is a great way to test your offline work before deploying to a live server or the cloud.
Versioning and Snapshots
A database project gives you an offline definition of a database. Like all projects, each database object (table, view, stored procedure) lives as a source file that can be placed under version control. Thus, the project system enables you to preserve and protect the definition of the database, as opposed to having the definition live within the database itself.
At any point in time, and as often as you’d like, you can create a database snapshot. A snapshot is nothing more than a persisted serialized version of the model base on the current project at the time the snapshot is taken. As such, you can use SSDT to develop, deploy, and synchronize database structures across local/cloud databases and differently versioned offline database projects.
Targeting SQL Azure
SSDT projects have a target switch that lets you specify which SQL Server edition or platform the project is intended to be deployed to. All the validation checks against the project-backed model are based on this setting, so it’s simply a matter of choosing SQL Azure as the target to ensure that your database can be deployed to the cloud without any problems. If your database project defines something that is not supported in Azure (a table with no clustered index, for example), it will get flagged as an error automatically when you attempt to publish.
The New BIDS
Are you not sufficiently impressed yet? But wait! SSDT is even bigger than what I’ve described so far. As announced at SQL PASS at the end of 2010, SSDT is an umbrella, a packaging of what was the VS2008-based Business Intelligence Developer Studio (BIDS) tools for SSAS, SSRS, and SSIS, combined with a new service, the “database services”. So they’ve really now brought together under the SQL Server umbrella all of the database development experiences inside of Visual Studio 2010. Here’s the picture:
Get Started Now, and Meet Me in New Jersey
SSDT is the next generation database development tool Visual Studio developers, and its coming soon! Hopefully this post inspired you to give SSDT a test drive. Download the Juneau CTP3 bits from http://www.microsoft.com/web/gallery/install.aspx?appid=JUNEAU10 and check it out for yourself.
I’ll be presenting a session at the NJ SQL User Group (http://njsql.org/default.aspx) with lots of demos that show off some of the coolest things about SSDT. The meeting is being held at SetFocus in Parsippany (directions here: http://www.setfocus.com/about/headquarters.aspx) on Sept 20 from 6 – 8:30. Hope to see you then!