Welcome!

In this initial blog post, we want to give you a general idea of what our product offers. Stay tuned as we roll out more posts in the near future. We look forward to your feedback via this blog as we progress through the project!

A Lap Around SQL Server Developer Tools

SQL Server Developer Tools (SSDT) provides functionalities for both data-tier and app-tier developers to perform all their development work against any SQL Server platform within Visual Studio.

The Server Explorer in VS now provides you with an SSMS-like view of your database objects. Developers will also appreciate the familiar VS tools we bring to database development, specifically; code navigation, IntelliSense, language support that parallels what is available for C# and VB, platform
specific validation, debugging and declarative editing in the TSQL Editor, as well as a visual Table Designer for both database projects and online database instances. You can also integrate your database projects with Entity Framework projects. When it’s time to deploy your project, you can choose to deploy to all available SQL platforms, including SQL Azure and SQL Server “Denali”. You can also output to a DACPAC directly from the database project.

The following sections will guide you around the major scenarios that SSDT can help you in your development work.

You can also watch this demo which was presented at TechEd Europe 2010 to see SSDT in action.

Online Database Development

Browsing Existing Database Objects

You can use the Server Explorer in Visual Studio to connect to a running SQL instance (from any platform including SQL Azure), and browse all of its objects in an SSMS-like hierarchy. You can then create or edit tables on-the-fly using the Table Designer. From the Table Designer, you can switch to a script pane which allows you to directly edit the script that defines this table. Edits in either the script or designer pane will always be in sync.

Fig. 1 Viewing a table structure from a connected database

Creating and Editing Database Objects

From the Server Explorer, you can also create or edit views, stored procedures, functions, triggers, user-defined-types, etc., inside the TSQL Editor which supports connected IntelliSense. This means that the identifiers that are listed in the completion list are enumerated by querying the database that is currently selected in Server Explorer.

Troubleshooting Errors

Any errors caused by editing in either the TSQL Editor or Table Designer immediately show up in the Error List pane, which enables you to follow the errors identified for further troubleshooting.

Committing Changes

After you are satisfied with your edits, you can commit your pending changes to the live database. You have a choice of committing only the changes in the active editing pane to the database, or committing all the changes across all open panes. The commit operation will provide a deployment report, which contains a preview of all the actions it is going to take, together with potential issues it has identified, as seen from the following screenshot. Notice that SSDT has generated a change script at the background automatically. Clicking the Script button gives you the flexibility to view the change script directly and manually edit it before executing.

Fig. 2 Preview changes before committing

Project-Oriented Offline Database Development

Managing Database Scripts

Inside the Server Explorer, you can create a new database project from a running database for offline development. The schema of the current database is then imported into the database project, with each database object represented by a script in the Solution Explorer. For a better viewing experience, you have the option of creating a folder in the Solution Explorer for each schema and/or each object type during the import operation.

Fig. 3 Objects are organized neatly in their own folder

While you are working offline, you can invoke the same visual designer tools (TSQL editor and Table Designer) available for online development to make changes to the database scripts, and save all changes to be deployed later.

Deploying Scripts

Using the Project Properties dialog box, you can change the target platform to different versions of SQL (including Azure), or choose whether you want to output to a DACPAC.

Fig. 4 Target platform options

When it’s time to publish your project, SSDT will automatically resolve all object interdependencies and validate the project model. At this point, you can fine-tune your desired deployment behavior, as seen from the following screenshot. Your publishing preference can also be saved in a profile to be reused later.

Fig. 5 Deployment options

Source Control

The offline development experience also provides you with source control functionalities to manage all your scripts.

Seamless Integration with SQLCLR

You can now add SQLCLR objects directly to the same database project that is opened, without resorting to opening a specific SQL CLR project. Your TSQL store procedures can interact with your SQLCLR objects within the same project. Debugging and deployment can also happen seamlessly.

First Class Coding Experience

The enhanced TSQL Editor provides you with similar coding support that C# and VB programmers have always enjoyed. For example, IntelliSense; navigation tools that are familiar to VS developers: Go To Definition and Find All References; the Refactor contextual menu which enables you to rename or move an object and do a preview of all affected areas before committing to the change; debugging across scripts, etc.

In addition, SSDT provides platform-specific validation while you edit your script. Depending on the target platform specified in the Project Properties dialog box, SSDT will catch any error caused by illegal syntax for that specific SQL platform.

Schema Compare

The Schema Compare tool allows you visually compare the differences between two databases (e.g., a production database and a test database), and update one of them to synchronize with the other.

Modern Code and Designers

Using the Table Designer to Manage Tables and Relationships Online and Offline

The Table Designer provides a visual experience alongside the TSQL Editor for creating and editing table structure, including table specific programming objects, for SQL Server databases. It is launched when you create a new table either from the Server Explorer (online) or Solution Explorer (offline). You can also edit an existing table in the designer by explicitly choosing it from the contextual menu.

The designer consists of the Columns Grid, Properties Window, Script Pane and Context Pane. The Columns Grid is the major designer surface, which lists all the columns in the table. You can also view and edit the properties of any object in the Properties Window.

The Context Pane gives you a quick view of objects related to the table (Keys, Constraints, Triggers, etc.), and enables you to add new objects to the table. To view relationships between objects and columns, you can highlight one of them in either the Context Pane or Columns Grid and have the other highlighted in its own pane.

Script Pane shows you the scripts of the selected object in the Context Pane, and enables to you edit the code side-by-side with the Columns Grid in view. Any changes from either side will propagate to the other side immediately.

Using the TSQL Editor to Edit Scripts Effortlessly

The TSQL Editor provides you with a rich editing and navigation experience when you are working with scripts. In addition to IntelliSense which is available both online and offline, the TSQL Editor for offline project development provides two very useful navigation tools that are familiar to VS developers: Go To Definition and Find All References. For example, you can right click on a table name and use “Find All References” to list all references to the table in the database. You can double click a search result to go to the specific code file. In this file, you can right-click the table name again, and choose “Go to Definition” to go back to the table definition. In addition, using the Refactor contextual menu, you can rename an object directly in the editor and do a preview of all affected areas before committing to the change. All the applied changes will propagate to all open editors to ensure that you have the most updated references to work with. The TSQL Editor also provides you with the ability to partially execute a fragment SQL statement by just highlighting it.

The Refactor contextual menu in the TSQL Editor enables you to rename or move an object and do a preview of all affected areas before committing to the change. Committed changes will propagate to all open editors to ensure that you always have the most updated references to work with.

Fig. 6 Navigation tools in TSQL Editor

Application Project & Database Project Integration

Whether you are working on an application, class or web project, you can create an ADO.NET Entity Data Model and choose to have the model contents generated from an existing database project. You can then control how the entities are mapped to database objects (e.g., tables, columns and store procedures) in the project.

You can use the Entity Designer to visualize and edit your model. Fine-grained synchronization control settings are provided to enable you to specify how changes are propagated between the entity data model and the database project. If you specify to always have changes sync’d up, any changes in the entity data model will result in an update of the mapped database object, and vice versa. This can happen even when one of them is closed. For example, if you create a new column in the Table Designer for a mapped table in the database project, the relevant entity will immediately show the new property. Since they now reside in the same solution, debugging can also flow across your existing application and the database project.

Tags

Join the conversation

None of this was particularly exciting to me given that its all in VS2010 until I got to the last bit "Application Project & Database Project Integration". Build-time dependency checking between the database tier and the app tier is the biggest thing that's been missing from the whole VS ecosystem – thank you for closing the hole.

Now, if you could just bring the same to SSIS/SSRS/SSAS projects that would be swell 🙂

The product is not currently available. It will be shipped with the next CTP release of Denali. We have not announced pricing yet. Please check back at this blog or our dev center at msdn.microsoft.com/…/tools.aspx for the latest news.

Seems an awful lot like Visual Studio Database Project (which you get with Ultimate version of VS2010). Is the intention that this will replace the Database projects? If not, in what situations would you use one vs the other?

As for features, please, support data build scripts. That is, please improve the story around creating and maintaining data in "lookup" type of tables (i.e. reference data).

Will Juneau finally support Add–Ins like VS has for so long… While we were quite successful in maing them work ing SSMS, each rev meant re-writing the add-in. Hopefully since this is now fully Powered By Visual Studio, the Add-In intrastructure will be available to SQL folks.

On the question of price, per the SQL Server Developer Tools, Code-named "Juneau" FAQ at msdn.microsoft.com/…/hh322942 , we are not yet ready to discuss pricing for the product. Announcements will be made closer to the release date.

Have you had chance to download the CTP of the product and try it yet? You can get CTP3 via msdn.com/…/tools.aspx

Quick question. How do I get the folder structure in solution explorer to re-sync with your database after refactoring the schema and/or table names? Now its odd when I'm trying to edit a renamed/schema transferred table and I have to remember the old tablename/schema name? Any ideas?

Question: Is it possible to use a user-define function if your are using LINQ SQL request
and if so, can you give example about calling a function in a VB programme, I want to use a function by calling Rtn_Txt with the 2 keys, and returning (as you can see) 6 fields declared as varchar(Max) :
here my function, but using only 1 field for testing purpose:
Thxs in advance