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:
- SSDT is a Visual Studio shell to enable development of solutions that include SQL Server databases, SQL Server Integration Services, SQL Server Analysis Services, and/or SQL Server Reporting Services.
- SSDT is a collection of features that aid in the development of SQL Server databases.
(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:
- It can be updated out of band, meaning you don't have to wait for a new release of SQL Server to get a new version of SSDT.
- It's free. You can download SSDT today and start using it without paying a penny.
- There are no SQL Server-related prerequisites -- you do not need to run the SQL Server installer in order to use SSDT.
- SSDT can be delivered for new versions of Visual Studio (it is already available for Visual Studio 2012).
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.
As you can see in Figure 1, the Data Tools Operations window provides these features:
- an at-a-glance indication of the success or failure of the publish operation
- an error message where appropriate
- the amount of time taken to complete the publish operation
-
links to:
- Preview: a textual description of what the Publish operation is going to do
- Script: the script that SSDT generates transparently, as explained previously
- Results: the output from running the script
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:
"Having been used to database releases being packaged as a set of scripts, variously doing create and alter DDL statements, suddenly being handed an SSDT release was an uncomfortable change. Letting the release itself determine the schema changes required based on the target database? This required a lot of trust in the process, even though historically the hand-crafted scripts were prone to mistakes, were labor-intensive to run and required before and after compares to have confidence in them.
"But this much simpler method of defining the schema once using create statements and then trusting SSDT to do what it needs to on the target makes everybody's job easier. Harnessing the power of this tool has reduced the complexity of managing releases, both in packaging and deploying them, and I am now a huge fan of this method."
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
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.
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.
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.
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:
- SQL Compare is a graphical tool within Visual Studio. Thus, if you want to use it in a production environment, you'll have to install Visual Studio over there – which is generally not a good idea. Headless deployments (i.e., deployments done with a GUI) are generally preferred in a production environment. You can do so via an SSDT publish operation using the SqlPackage.exe tool.
- SQL Compare only compares schema and does not make any provision for deploying data. On the other hand, the SSDT publish operation provides for deployment of data via SQL scripts, which are called post-deployment scripts.
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.
- Email: [email protected]
- Twitter: @jamiet
- Blog: http://sqlblog.com/blogs/Jamie_thomson
