Thinking About SQL Server Developer Tools, Codename “Juneau”– Part 2

This is the second post in my series on thinking about SQL Server Developer Tools, Codename “Juneau” (SSDT). The intent of this series is to orient new SSDT users with the product by comparing SSDT’s program model to the program model of SQL Server Management Studio (SSMS). The following table, copied from the previous post in this series, summarizes the comparisons:

As illustrated in the table above declarative database development is central to the SSDT product. In looking at the table above arguably the differences in connected database development between SSMS and SSDT will be the most common point of conceptual friction for new SSDT users (I know it was for me when I first joined the team), so I’ll start there.

Connected Declarative Database Development – The Happy Path

One of the things that database developers will notice when firing up SSDT for the first time is the addition of the SQL Server node in the Server Explorer. The following screenshot illustrates how this looks in SSDT CTP3 integrated with Visual Studio 2010 Ultimate and connected to a SQL Server Code Name “Denali” CTP 3 instance:

The SQL Server node has been purposefully designed to have an experience that closely mirrors SSMS. This was done to allow SQL Server database developers the luxury of using their existing SSMS “muscle memory” in SSDT. However, the experience in the SQL Server node has been optimized for the most common database developer tasks. As such, not all SSMS features are available from the SQL Server node. This reflects the product philosophy where SSMS is the manageability tool for DBAs and SSDT is focused on developers. Our intent with the SQL Server node was trying to target the sweet spot where most developers will not need to leave the Visual Studio shell most of the time while they are developing T-SQL code. Feedback on our success on this would be most appreciated.

Unlike SSMS, SSDT’s default connected development experience is declarative. We can see this exhibited in the following screenshot where I’ve navigated to the Customer table in the Northwind database and double-clicked on it in the SQL Server node:

By default tables are opened in the new SSDT Table Designer. The Table Designer is very cool because it allows an optimized experience for developing SQL Server tables. As a developer I can use the design grid in the top pane if that’s the way I prefer to work or I can use T-SQL code in the bottom pane. Both panes are synchronized so I can leverage both if that’s the way I like to work (more in-depth posts on developing using SSDT will be coming soon). Regardless of the pane used, the T-SQL code remains the source of truth (this is true in every feature of SSDT).

Declarative connected development is implemented using SSDT’s Power Buffer technology. The easiest way to think about Power Buffer is to imagine that your connected development is done in a sort of database scratch pad. Using Power Buffer you can alter table structures, rename columns, add new tables, create new FKs, write sprocs, etc., all without actually altering the physically executing database. As an example, the following screenshots illustrate me adding a CustomerSegmentation table and adding a FK from Customer to Customer segmentation using Power Buffer:

Notice how all the code in the above screenshots are based on CREATE (i.e., you don’t see an ALTERs anywhere in the code). This is a core idea in SSDT. When I develop my database I target the desired end shape of the structures without worrying about the current shape of those structures. When the time comes to alter the physically running database I tell SSDT to take my scratch pad containing how the database should look and figure out how to change the running database to match my scratch pad. Clicking the Update Database button (which is present in all Power Buffer windows) tells SSDT to do exactly this. The following screenshot illustrates the result of requesting SSDT to update the Northwind database:

In looking at the Preview Database Updates dialog it is worthy to note that a Power Buffer scratch pad is scoped on a per-Database basis. This is a critical concept because, as we’ve seen above, multiple Power Buffer document windows actually operate over the same scratch pad (i.e., each document window is a viewport into the same scratch pad). As such, clicking the Update Database on any Power Buffer window automatically incorporates all the changes from any dirty document window that is a viewport into the same scratch pad. In our example, I clicked Update Database on the Customers window and the changes from the CustomerSegmentation window were automatically incorporated.

Taking a closer look at the Preview Database Updates dialog we can see the net result of SSDT incorporating all the scratch pad changes and comparing them with the actual running database. As listed in the dialog, SSDT has determined that the Customers table needs to be altered (to add the new CustomerSegmentation column), that a new table needs to be created (CustomerSegmentation), and that a new FK needs to be created. The SSDT engine is very intelligent. The algorithms involved are deliberately engineered to produce changes that have minimal adverse impacts on the running database (e.g., using sp_rename to change the name of a column) and also prevent inadvertent data loss. Just in case you are still skeptical about how SSDT does things, the dialog offers the “Generate Script” button which allows you to see exactly what T-SQL SSDT will send to the running database to update it’s structure . Since I trust the product, I’ll just go ahead an click “Update Database” and voila! My running Northwind database is updated. No fuss, no muss.

Next Time

Having taken a very quick look at the happy path through SSDT declarative connected development, the next installment in the series will take a look at some of the alternate paths for connected development – namely how do you work imperatively against live databases with SSDT and some of the gotchas that arise when trying to use Power Buffers windows like SSMS-style T-SQL document windows.

As always, feedback much appreciated.

Until next time, thanx for reading!