What is SSDT?

Today I installed the latest release of SQL Server Data Tools from this link.  SSDT, first shown at SQL Pass last year, is Microsoft’s vNext of Data Dude, or add-in tools to make the developing with databases in Visual Studio more like working in SQL Server Management studio.  In fact the product team blog for SSDT is actually named ‘Data Dude’

The idea is that developers want to develop databases using similar methods to working with regular (i.e. .NET code) object within VS.  To that end, SSDT includes offline projects, object creation interfaces (i.e. for tables, views, etc…) schema snapshot and compare, database version targeting (for SQL Server 2005, 2008, 2012 and for SQL Azure) and refactoring with preview. 

Also it’s FREE.  Here’s a link to the FAQ.  It’s interesting to note that this FAQ mentions that the plan for updating SSDT is on the release cycle of SQL Azure (which is listed as every 4-6 months), so if you plan to use SSDT with SQL Azure, you should plan to update this tool every time SQL Azure updates.   If you are using Visual Studio, SSDT will install as a project template, if you do NOT have Visual Studio, SSDT will install the VS shell, so VS is NOT required to use SSDT.

About the Install

I have Visual Studio 2010 with SP1 on my test machine.  I un-installed my previous version of SSDT before I started.  I found the install to be quick and painless, taking about 30 minutes to download and install from start-to-finish.

Here’s a screen of the install location

After you launch the install, you’ll see a list of exactly what is installing, as shown in screen below:

To verify the install, just open up Visual Studio and look for ‘Microsoft SQL Server Data Tools, CTP4’ to appear in the installed products list, as shown below:

Using SSDT

So what’s cool and new about this CTP?  The first new thing is the ‘SQL Server Object Explorer’ object.  Open this from the View menu, or by using shortcut Ctrl+\.  This Explorer is shown in the screenshot below. 

This Explorer allows you to connect to instances of SQL Server and SQL Azure.  After you connect, you can view the object properties (as shown here for the database) and also work with existing database objects.  You can also create new objects.  Shown below is the table designer.  This designer keeps changes that you make in top pane in sync with those in the script pane and also in reverse.  Also you can quickly create commonly-used objects, such as Keys, Indexes, etc…by right-clicking on the object list on the right side of the designer.  Then a code stub is generated for that object.  You just fill the script placeholder in with the actual value, i.e. add the column name for an index.

Another handy feature is Schema Compare.  To use this, right click on the source database in the SQL Server Object Explorer and then click Schema Compare.  You’ll see the dialog box shown below.  You can select an offline database project (more about that coming up soon in this blog post), another database or a DacPac (file) as the target of your schema comparison. 

For this, I’ll select a different version of AdventureWorks, that is AdventureWorks2008R2 and then I’ll click ‘Compare’.  The results of this schema comparison are shown below.  Be aware that this tool compares SCHEMA only, not data.

You’ll note from the screenshot above, that the default output is grouped by type of change, that is Delete, Change or Add.  Output can also be grouped by database schema or by object type (i.e. table, view…).  You can de-select individual objects if you don’t want their changes to be part of the overall comparison.  Also there is a visual comparison for the particular selected object, in the screenshot above for the HumanResources.Employee table, this shows differences (in the case of ‘Change’) line-by-line for the selected object.

After you are done reviewing the changes, you can either apply the changes to either the target (default) by clicking the ‘Update’ button, or you can reverse the source and target by clicking the small green arrow between them at the top of the schema comparison window.

More commonly you’ll probably generate a change script, which you can review, possibly edit and apply at your convenience.  To do that, just click the small script icon to the right of the ‘Update’ button at the top of the schema comparison window.  In this example, the generated change script was over 7,000 lines long!

Off-line Database Projects

Another option when working with SQL Server Object Explorer is to work disconnected (or offline) from the source database.  This is particularly useful if you are doing development with SQL Azure.  To create an offline project, you right-click the database in SQL Server Object Explorer and then click ‘Create New Project…’.  The dialog box shown below appears.

After you configure this dialog box, then a wizard opens.  This wizard shows you the progress of scripting all of the database meta data.  For AdventureWorks, the script creation and execution (to create local copies of all objects) took about 70 seconds.  Below is a screen showing wizard output.

After this wizard completes, you’ll see two changes to your work environment.  The first is in the SQL Server Object Explorer.  You’ll see that a new database node has been added, for the local copy of your database.  Shown below, for my sample, I’ve named the project ‘AdventureWorksNov21’.  Of course the version of this local copy is SQL Server 11.0.xxxx (or SQL Server 2012). 

Also you will see that the new database project has populated a template with metadata (scripts) in the Solution Explorer.  Shown below is the expanded view of the Human Resources schema.

To start exploring what you can do with this offline project, I suggest you right-click on the project name.  This will give you a menu with a couple of interesting options.  These options are as follows:

1) Import – here you can import additional meta data.  Meta data types supported are another database, a DacPac or a script

2) Snapshot – with this option you can create a compact version of your meta data, as a *.dacpac file. 

3) Schema Compare (already covered previously)

Another handy feature is the ability is exposed via the Refactor menu.  An easy way to see this is to open a table, for this example, I am using the HumanResources.Employee table again.  After you’ve opened the object, then highlight what you intend to change, in my case, the table name (from Employees to Employees1) and then right click and and then click on ‘Refactor’. 

SSDT will then present you with a preview dialog box.  This will show you where the rename will affect the database (i.e. each statement) and will also present you with a change script which you can apply immediately or can save for review, possible editing and later application.  An example is shown below.

What Else?

After you are done working with the offline database schema, then you can build and optionally publish your updated schema.  The ‘Publish’ (to) dialog box must be configured for a particular publish location, or publish will fail.  The default (un-configured) is shown below.

When you are ready to publish, you will first also want to configure the Build dialog of the project properties.  This is shown below.  You’ll note that you can set the ‘Target platform’ property to SQL Azure, SQL Server 2012, SQL Server 2008 or SQL Server 2008 R2, or SQL Server 2005.  This is an important setting, as Visual Studio will VALIDATE the schema objects for the particular edition and version of SQL Server selected in this drop-down list.  You may also note that you can optionally output a .dacpac file and / or a .sql file.

Of note is that when I attempted to explore the ‘limitations’ of the ‘Enable extended Transact-SQL verification for common objects’ my instance of Visual Studio crashed.  This release of SSDT is, of course, NOT RTM, rather CTP 4, so I will file a bug on this last issue.

Future Directions

From the SSDT FAQ, Microsoft announced two interesting additional inclusions in the SSDT RTM – they are Entity Framework and Business Intelligence Development Studio.  CTP 4 doesn’t appear to include either of these features yet.

“Finally, the new SSDT features strong cross-project support for VS solutions based on the ADO.NET Entity Framework and Entity designer (Note: this EF-integration functionality will be shipped in a separate release after CTP).”

“Yes; in the SQL Server 2012 RC0 installer, developers can install the BIDS tools by selecting SSDT during installation. The BIDS tools will run on Visual Studio 2010 SP1, as SSDT does. The SQL Server 2012 installer will also install SSDT’s prerequisites and a “stub project” that allows SQL Server 2012 users to acquire the latest SSDT version from the web.”