Introducing SQL Server Data Tools

• —Leonard Lobel

With the release of SQL Server 2012, Microsoft delivers a powerful, new integrated development environment (IDE) for designing, testing, and deploying SQL Server databases—locally, offline, or in the cloud—all from right inside Microsoft Visual Studio (both the 2010 version and the upcoming Visual Studio 11, currently in beta at the time of this writing). This IDE is called SQL Server Data Tools (SSDT), and it represents a major step forward from previously available tooling—notably, SQL Server Management Studio (SSMS) and Visual Studio Database Professional edition (DbPro).

SSDT is not intended to be a replacement for SSMS, but instead can be viewed much more as a greatly evolved implementation of DbPro. Indeed, SSMS is alive and well in SQL Server 2012, and it continues to serve as the primary management tool for database administrators who need to configure and maintain healthy SQL Server installations. And although DbPro was a good first step towards offline database development, its relatively limited design-time experience has precluded its widespread adoption. So for years, programmers have been primarily using SSMS to conduct development tasks (and before SQL Server 2005, they typically used two database administrator [DBA] tools—SQL Enterprise Manager and Query Analyzer). It’s always been necessary for programmers to use management-oriented tools (such as SSMS) rather than developer-focused tools (such as Visual Studio) as a primary database development environment when building database applications—until now.

The release of SSDT provides a single environment hosted in Visual Studio, with database tooling that specifically targets the development process. Thus, you can now design and build your databases without constantly toggling between Visual Studio and other tools. In this chapter, you’ll learn how to use SSDT inside Visual Studio to significantly enhance your productivity as a SQL Server developer. We begin with an overview of key SSDT concepts and features, and then walk you through demonstrations of various connected and disconnected scenarios.

Introducing SSDT

Database Tooling Designed for Developers

The inconvenient truth is: database development is hard. Getting everything done correctly is a huge challenge—proper schema and relational design, the intricacies of Transact-SQL (T-SQL) as a language, performance tuning, and more, are all difficult tasks in and of themselves. However, with respect to the development process—the way in which you create and change a database—there are some particular scenarios that the right tooling can improve greatly. SSDT delivers that tooling.

Here are some of the challenges that developers face when designing databases.

• Dependencies By its very nature, the database is full of dependencies between different kinds of schema objects. This complicates development, as even the simplest changes can very quickly become very complex when dependencies are involved.

• Late Error Detection You can spend a lot of time building complex scripts, only to find out that there are problems when you try to deploy them to the database. Or, your script may deploy without errors, but you have an issue somewhere that doesn’t manifest itself until the user encounters a run-time error.

• “Drift” Detection The database is a constantly moving target. After deployment, it’s fairly common for a DBA to come along and tweak or patch something in the production database; for example, adding indexes to improve query performance against particular tables. When the environments fall out of sync, the database is in a different state than you and your application expect it to be—and those differences need to be identified and reconciled.

• Versioning Developers have grown so accustomed to working with “change scripts” that it makes you wonder, where is the definition of the database? Of course you can rely on it being in the database, but where is it from the standpoint of preserving and protecting it? How do you maintain the definition across different versions of your application? It’s very difficult to revert to a point in time and recall an earlier version of the database that matches up with an earlier version of an application. So you can’t easily synchronize versions and version history between your database and application.

• Deployment Then there are the challenges of targeting different versions, including most recently, SQL Azure. You may need to deploy the same database out to different locations, and must account for varying compatibility levels when different locations are running different versions of SQL Server (such as SQL Server 2005, 2008, 2008 R2, 2012, and SQL Azure).

Many of these pain points are rooted in the notion that the database is “stateful.” Every time you build and run a .NET application in Visual Studio, it is always initialized to the same “new” state but as soon as the application goes off to access the database, it’s the same “old” database with the same schema and data in it. Thus, you are forced to think not only about the design of the database, but also about how you implement that design—how you actually get that design moved into the database given the database’s current state.

If the root of these problems lies in the database being stateful, then the heart of the solution lies in working declaratively rather than imperatively. So rather than just working with change scripts, SSDT lets you work with a declaration of what you believe (or want) the database to be. This allows you to focus on the design, while the tool takes care of writing the appropriate change scripts that will safely apply your design to an actual target database. SSDT takes a declarative, model-based approach to database design—and as you advance through this chapter, you’ll see how this approach remedies the aforementioned pain points.

Declarative, Model-Based Development

We’ve started explaining that SSDT uses a declarative, model-based approach. What this means is that there is always an in-memory representation of what a database looks like—an SSDT database model—and all the SSDT tools (designers, validations, IntelliSense, schema compare, and so on) operate on that model. This model can be populated by a live connected database (on-premise or SQL Azure), an offline database project under source control, or a point-in-time snapshot taken of an offline database project (you will work with snapshots in the upcoming exercises). But to reiterate, the tools are agnostic to the model’s backing; they work exclusively against the model itself. Thus, you enjoy a rich, consistent experience in any scenario—regardless of whether you’re working with on-premise or cloud databases, offline projects, or versioned snapshots.

The T-SQL representation of any object in an SSDT model is always expressed in the form of a CREATE statement. An ALTER statement makes no sense in a declarative context—a CREATE statement declares what an object should look like, and that’s the only thing that you (as a developer) need to be concerned with. Depending on the state of the target database, of course, a change script containing either a CREATE statement (if the object doesn’t exist yet) or an appropriate ALTER statement (if it does) will be needed to properly deploy the object’s definition. Furthermore, if dependencies are involved (which they very often are), other objects need to be dropped and re-created in the deployment process. Fortunately, you can now rely on SSDT to identify any changes (the “difference”) between your model definition and the actual database in order to compose the necessary change script. This keeps you focused on just the definition. Figure 1-1 depicts the SSDT model-based approach to database development.

Figure 1-1 SSDT works with a model backed by connected databases (on-premise or in the cloud), offline database projects, or database snapshot files.

Connected Development

Although SSDT places great emphasis on the declarative model, it in no way prevents you from working imperatively against live databases when you want or need to. You can open query windows to compose and execute T-SQL statements directly against a connected database, with the assistance of a debugger if desired, just as you can in SSMS.

The connected SSDT experience is driven off the new SQL Server Object Explorer in Visual Studio. You can use this new dockable tool window to accomplish common database development tasks that formerly required SSMS. Using the new SQL Server Object Explorer is strikingly similar to working against a connected database in SSMS’s Object Explorer—but remember that (and we’ll risk overstating this) the SSDT tools operate only on a database model. So when working in connected mode, SSDT actually creates a model from the real database—on the fly—and then lets you edit that model. This “buffered” approach is a subtle, yet key, distinction from the way that SSMS operates.

When you save a schema change made with the new table designer, SSDT works out the necessary script needed to update the real database so it reflects the change(s) 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 this buffered behavior that’s occurring behind the scenes. But after you do grasp it, the tool’s offline project development and snapshot versioning capabilities will immediately seem natural and intuitive to you. This is because offline projects and snapshots are simply different backings of the very same SSDT model. When you’re working with the SQL Server Object Explorer, the model’s backing just happens to be a live connected database.

There’s an additional nuance to SSDT’s buffered-while-connected approach to database development that bears mentioning. There are in fact two models involved in the process of applying schema changes to a database. Just before SSDT attempts to apply your schema changes, it actually creates a new model of the currently connected database. SSDT uses this model as the target for a model comparison with the model you’ve been editing. This dual-model approach provides the “drift detection” mechanism you need to ensure that the schema compare operation (upon which SSDT will be basing its change script) accounts for any schema changes that may have been made by another user since you began editing your version of the model. Validation checks will then catch any problems caused by the other user’s changes (which would not have been present when you began making your changes).

Disconnected Development

The new SQL Server Object Explorer lets you connect to and interact with any database right from inside Visual Studio. But SSDT offers a great deal more than a mere replacement for the connected SSMS experience. It also delivers a rich offline experience with the new SQL Server Database Project type and local database runtime (LocalDB).

The T-SQL script files in a SQL Server Database Project are all declarative in nature (only CREATE statements; no ALTER statements). This is a radically different approach than you’re accustomed to when “developing” databases in SSMS (where you execute far more ALTER statements than CREATE statements). Again, you get to focus on defining “this is how the database should look,” and let the tool determine the appropriate T-SQL change script needed to actually update the live database to match your definition.

If you are familiar with the Database Professional (DbPro) edition of Visual Studio, you will instantly recognize the many similarities between DbPro’s Database Projects and SSDT’s SQL Server Database Projects. Despite major overlap however, SSDT project types are different than DbPro project types, and appear as a distinct project template in Visual Studio’s Add New Project dialog. The new table designer, buffered connection mechanism, and other SSDT features covered in this chapter work only with SSDT SQL Server Database Projects. However, and as you may have guessed, it’s easy to upgrade existing DbPro projects to SSDT projects. Just right-click the project in Solution Explorer and choose Convert To SQL Server Database Project. Note that this is a one-way upgrade, and that DbPro artifacts that are not yet supported by SSDT (such as data generation plans, see the following Note) will not convert.

The new SQL Server Database Project type enjoys many of the same capabilities and features as other Visual Studio project types. This includes not only source control for each individual database object definition, but many of the common code navigation and refactoring paradigms that developers have grown to expect of a modern IDE (such as Rename, Goto Definition, and Find All References). The SSDT database model’s rich metadata also provides for far better IntelliSense than what SSMS has been offering since SQL Server 2008, giving you much more of that “strongly-typed” confidence factor as you code. You can also set breakpoints, single step through T-SQL code, and work with the Locals window 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.

A major advantage of the model-based approach is that models can be generated from many different sources. When connected directly via SQL Server Object Explorer, SSDT generates a model from the connected database, as we explained already. When you create a SQL Server Database 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 real database. But it’s actually a project—not a real database. Now, SSDT generates a model that’s backed by your SQL Server Database Project. So the experience offline is just the same as when connected—the designers, IntelliSense, validation checks, and all the other tools 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 Visual Basic (VB) .NET. For example, making a change in the project that can’t be submitted to the database because of dependency issues will result in design-time warnings and errors in the Error List pane. You can then click on the warnings and errors to navigate directly to the various dependencies so they can be dealt with. Once all the build errors disappear, you’ll be able to submit the changes to update the database.

Versioning and Snapshots

A database project gives you an offline definition of a database. As with all Visual Studio projects, each database object (table, view, stored procedure, and every other distinct object) exists as a text file that can be placed under source code control. The project system combined with source control enables you to secure the definition of a database in Visual Studio, rather than relying on the definition being stored in 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 file (in the Data-tier Application Component Package, [dacpac] format) that holds the serialized state of a database model, based on the current project at the time the snapshot is taken. It is essentially a single-file representation of your entire database schema. Snapshots can later be deserialized and used with any SSDT tool (schema compare, for example). So you can develop, deploy, and synchronize database structures across local/cloud databases and differently versioned offline database projects, all with consistent tooling.

Pause for a moment to think about the powerful capabilities that snapshots provide. A snaphot encapsulates an entire database structure into a single .dacpac file that can be instantly deserialized back into a model at any time. Thus, they can serve as either the source or target of a schema compare operation against a live database (on-premise or SQL Azure), an offline SQL Server Database Project, or some other snapshot taken at any other point in time.

Snapshots can also be helpful when you don’t have access to the target database, but are expected instead to hand a change script off to the DBA for execution. In addition to the change script, you can also send the DBA a snapshot of the database project taken just before any of your offline work was performed. That snapshot is your change script’s assumption of what the live database looks like. So the DBA, in turn, can perform a schema compare between your snapshot and the live database (this can be done from SSDT’s command-line tool without Visual Studio). The results of that schema compare will instantly let the DBA know if it’s safe to run your change script. If the results reveal discrepancies between the live database and the database snapshot upon which your change script is based, the DBA can reject your change script and alert you to the problem.

Targeting Different Platforms

SQL Server Database Projects have a target platform switch that lets you specify the specific SQL Server version that you intend to deploy the project to. All the validation checks against the project-backed model are based on this setting, making it trivial for you to test and deploy your database to any particular version of of SQL Server (2005 and later), including SQL Azure. 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 SQL Azure (a table with no clustered index, for example), it will get flagged as an error automatically.