Get to Know SQL Server 2012's SQL Server Data Tools

SQL Server 2012 includes a new feature called SQL Server Data Tools (SSDT) that holds promise for assisting development projects using SQL Server or Windows Azure SQL Database. But even today, months since SQL Server 2012 was released, confusion abounds about what SSDT actually is.

Depending on which Microsoft employee (or, in some cases, which product installer) you want to believe, SSDT can be described in either of the following two ways:

(I don't want to get sidetracked here discussing this confusing state of affairs in more detail -- for more information, see my blog post "SSDT - What's in a name?")

I don't know which one of those is the correct description; regardless, in this article I will discuss SSDT from the second of these two perspectives: as a collection of features that aid in the development of SQL Server databases.

How to Get SSDT

SSDT is not delivered with the SQL Server 2012 installer, although when you run that installer there is an option to install SSDT. This is where some of the confusion that I mentioned stems from: SSDT actually comes as a web installer that you obtain from the SQL Server Data Tools Download page and which then gets surfaced within a Visual Studio shell. There are a number of reasons for delivering SSDT in this way:

SSDT's Heritage

SSDT is being marketed as a new offering within SQL Server, but in fact, the reality is slightly different. The features that comprise SSDT are the latest evolution of a product line that has existed in various incarnations since Visual Studio 2005. Those incarnations include Visual Studio Team System for Database Professionals and Visual Studio 2010 SQL Server Database Projects, plus some more colloquial names, such as "Data Dude," "TSData," and "DB Pro." If you've used any of those products, you are well set for coming to grips with SSDT; if you haven't used any of those products -- well, you're reading the right article.

A note for readers who are familiar with SSDT's previous incarnations: The data-generation and unit-testing features in those products have been removed in SSDT. Those features might return in the future; however, nothing has been announced at the time of this writing.

Another side note: I have often seen or read remarks that suggest some folks mistakenly believe that SSDT and its predecessors rely on Microsoft's application lifecycle management product, Team Foundation Server (TFS). That is not the case; you don't need to be using TFS in order to use SSDT.

Aims of SSDT

SSDT is an attempt to bring similar tools and consistency to SQL Server development to what .NET developers have had for years. Features such as IntelliSense, code refactoring, code navigation, find all references, sandboxed development and deployment, MSBuild support, and ease of deployment will be familiar to most .NET developers. However, the majority of these features are, in my experience, alien to many SQL Server developers who have become adept at toiling in SQL Server Management Studio (SSMS). SSDT aims to change that.

A New Paradigm: Declarative Database Development

In addition to bringing the aforementioned new features, SSDT pushes a new development paradigm: the notion of declarative database development. Put simply, declarative database development means that you define within SSDT what your schema looks like. Then, when you deploy (or in SSDT parlance, publish) your SSDT project to a target database, SSDT will determine what it needs to do to make your target look like the schema that you've defined.

To help you understand this concept, let's look at an example. Imagine I have the following table defined in my SSDT project:

CREATE TABLE [dbo].[Product](   [ProductId] INT NOT NULL PRIMARY KEY,      [ProductName] NVARCHAR(30) NOT NULL)

I decide I want to make a couple of changes. I want a new column to store a category, and I want to assign a name to the primary key:

CREATE TABLE [dbo].[Product](    [ProductId] INT NOT NULL,       [ProductName] NVARCHAR(30) NOT NULL,       [Category] NVARCHAR(30) NULL --New column,       CONSTRAINT [PK_dboProduct] PRIMARY KEY ([ProductId]) --Named PK)

After I publish the project, SSDT generates the script in Listing 1 to affect the required changes.

The script makes the appropriate changes to the deployed table while respecting the presence of any data that might be stored within it; it is fairly complex, to be sure, but that's not the point. In fact, under normal circumstances, you as the DBA or database developer would never even see this script because SSDT generates and executes it transparently during a publish operation (don't panic, all you DBAs out there -- you do have the option for SSDT to just generate the script and not execute it).

This example of adding a column and naming a constraint is simplistic, but it illustrates the concept of declarative database development. You don't have to define how you update your database schema from an old state to a new state: You simply tell SSDT what state you want the database schema to be, and SSDT will take care of it for you. Imagine a database schema with many changes, many of which will be more complex than simply adding a NULLable column as I did, and you can perhaps grasp the inherent value of this approach.

A quick note on the publish operation itself. You can initiate it using either a command-line tool called SqlPackage.exe or from inside the Visual Studio shell of SSDT. If you use the latter approach, you have available a useful addition not in previous SSDT versions called the Data Tools Operations window, shown in Figure 1, which provides a handy overview of your publish operations.

Figure 1: Using SSDT's Data Tools Operations

As you can see in Figure 1, the Data Tools Operations window provides these features:

In my opinion, declarative database development is the killer feature of SDDT. Put more simply, declarative database development is the reason that I use SSDT. This isn't just a developer crush, either. The devops guys on my current project were initially reticent about letting a tool govern our database deployments. But after carrying out numerous deployments to our production environment using this technique in the last couple of months, they've been won over and are now mandating that all development projects move to using SSDT. In fact, when asked for a quote for this article, the head of our devops team, Joe Pollock, had this to say:

It takes a leap of faith to start trusting SSDT in this manner, but with the proper precautions in place (see my tips a bit later in the article), it can be a leap worth taking.

Developer Productivity

Figure 2: Sample Refactor Log

Improving developer productivity is a real focus for SSDT. Let's look at some of the significant features in this area.
 

Refactoring. Refactoring code can generally be described as altering how a code module executes without changing what it actually does. SSDT provides support for such changes.

Renaming an object (such as a table) or moving it to a different schema will, ordinarily, break any code modules (e.g., stored procedures, views, triggers, user-defined-functions) that reference that object. This is an area where SSDT refactoring is intended to help. Using SSDT refactoring will not only affect the rename/move operation, but it will also alter any referencing code modules accordingly.

Such refactoring operations are commonplace in other development tools. However the very nature of a database means that SSDT refactoring is a little different. Not only does SSDT change the object and referencing code, it also records the change in a file called the refactor log, which exists as an artifact of the project, as shown in Figure 2.

Consider the example of a column being renamed. The Publish operation needs to know that it should issue a call to sp_rename rather than causing data loss by dropping the column with the old name and creating a new column with the new name. This is the purpose of the refactor log: to record the fact that a rename has taken place.

Go to definition. This is a code-navigation feature that has long existed in Visual Studio for .NET language development and is a welcome addition to SSDT. Simply placing your cursor on an object (say, a table) referenced in a code module and pressing F12 will take you to the DDL that defines that object. Very handy indeed.

Find all references. Find all references, another code navigation feature, shows you all code modules in which an object is referenced. In some ways, this is the inverse of the F12 code navigation that I mentioned earlier -- indeed, it is accessed by placing your cursor on the object in question and pressing Shift+F12.

Figure 3: IntelliSense in SSDT

IntelliSense. IntelliSense is a feature that helps you to write your code by making suggestions as to what you might type next. For example, if you type the name of a schema and press the period key (.), IntelliSense will present a list of tables in that schema from which you can choose, as shown in Figure 3.

It's worth noting that if you're used to other languages where IntelliSense is implemented well (such as the .NET family of languages), your expectations for this feature will most likely not be met -- T-SQL IntelliSense is not a patch on .NET IntelliSense. I have spoken to a person who writes code parsers, and he claimed that parsing T-SQL code is significantly more difficult than more modern languages such as C# -- thus it's more difficult to write an IntelliSense engine that works flawlessly. Nonetheless, I am of the opinion that IntelliSense is still a useful feature. (I find a competing IntelliSense product from Red Gate Software called SQL Prompt to be better than Microsoft's offering.)

Table designer. SSDT features a brand-new table designer that provides both a graphical and scripted representation of a table, as shown in Figure 4. What sets this table designer apart from other similar designers you might have seen is that you can edit the table definition by either using the GUI or by editing the script directly.

Figure 4: SSDT Table Designer

SSDT Power Tools. SSDT includes a suite of Power Tools, although currently the name "Power Tools" is a misnomer because the suite consists only of a single tool called SQL Server Object Explorer. You can install SSDT Power Tools by searching in Visual Studio's Extension Manager for "SSDT Power Tools." Note that if you've used a tool called Schema View in the previous incarnations of SSDT, you'll be familiar with SQL Server Object Explorer, as it provides pretty much the same set of features.

Figure 5: Script in SQL Server Object Explorer

SQL Server Object Explorer is complimentary to Visual Studio's Solution Explorer pane because it provides a logical view of the objects that are defined within the project in Solution Explorer. What does that mean exactly? Perhaps a better way of describing it is that Solution Explorer shows a collection of files (like Windows Explorer) whereas SQL Server Object Explorer shows a collection of database objects (like SSMS). A screenshot might explain this better: In Figure 5, we see the DDL script for a table, [dbo].[Product], open and in full view. That same script, Product.sql, is in Solution Explorer; and over on the left we see that SQL Server Object Explorer has a logical view of that same table and also its columns and primary key.

SQL Server Object Explorer can also be used to launch the refactoring operations that I discussed earlier. You cannot do so from Solution Explorer.

Tips

Through using SSDT and its predecessors over a number of years, I've learned many of their nuances. Based on those experiences, here are a few tips that I recommend you follow.

I mentioned earlier how on my current project we are successfully using SSDT to deploy to our production environment. I should also point out that we first test those deployments against a backup of that production environment. You should do the same.

SSDT includes a tool that called SQL Compare that I haven't covered here. Many people use SQL Compare to produce scripts that they can use to deploy changes made in their SSDT projects to their test and production environments. That works OK, but it is not a mechanism that I ever choose to use for these reasons:

If you are successfully using SQL Compare for your deployments, by all means continue doing so, but bear in mind the points I've raised here.

Always use the refactoring operations when renaming objects. If you do not do so, you risk causing errors in your deployments.

Use continuous integration (CI) to verify that your SSDT projects build and deploy successfully. SSDT projects are MSBuild compliant and so lend themselves very well to use in CI setups.

In many multi-developer project teams I've worked in, those developers shared the same central development server, which led to problems when different developers needed to use the same resources. SSDT supports, and indeed promotes, the use of "sandboxes" where each developer has their own development database, usually on their own workstation. I highly recommend this as a method of development.

Build and deployment of SSDT projects can be done from the command line (via msbuild.exe and sqlpackage.exe, respectively). Thus I recommend that you write a short batch file (in either DOS or PowerShell) that can automate these tasks for you. This enables the developers to carry on developing instead of locking up Visual Studio while a build/deployment is occurring. That script can also be shared throughout the team, thereby ensuring that each team member is deploying in the same consistent manner.

More Yet to Explore

Although I've I covered what I believe to be the most important features of SSDT, many more features remain for you to explore. For example, the SQL Server 2012 Data-Tier Application Framework (incorporating .dacpac files), sandbox development using LocalDB, F5 deployment to LocalDB, static code analysis, MSBuild support, connections to SQL Server instances within SQL Server Object Explorer, pre- and post-deployment scripts, and more are all worthy of inclusion here, but for reasons of brevity will have to be saved for another time.

Jamie Thomson is an independent consultant from the United Kingdom. He specializes in implementing data integration solutions on the SQL Server platform.