Visual Studio Team System 2008 Database Edition GDR – RTM

Finally the moment is there, the final version of the Visual Studio Team System 2008 Database Edition GDR has been released to the web.

Although the name "GDR", which stands for "General Redistribution Release", implies otherwise, this really is a complete new release, based on a new architecture. We have taken the architectural changes from the Rosario project (Visual Studio Team System 2010) and rolled them forward in time. Rolling forward these changes help us align the GDR release with the upcoming Visual Studio Team System 2010 release and provide numerous new features and extensibility points in this release. If you would compare the GDR release with the "Visual Studio 2005 Team Edition for Database Professionals" or "Visual Studio Team System 2008 Database Edition" you will agree this is a completely new product!

The key architectural changes

Model based architecture - Everything underneath the covers is based on a true model representation of the SQL Server schema. This facilitates a true offline declarative database development system where the source code defines the shapes of the schema objects.

Models are implemented by Database Schema Providers, DSP's for short. - The introduction of a provider model enables multiple things at once. First of all the decoupling of release vehicles. For example when SQL Server will release a new version or adds new functionality in a service pack, like they did in SQL Server 2005 SP2 when adding "vardecimal" support, we can simply update an existing provider or provide a new one in case of a new SQL Server release. Overtime we will go to a model where the SQL Server providers will be an integral part of the SQL Server release. The provider model is also a key extensibility point, allowing 3rd parties to extend database project ecosystem and add support for other database platforms in Visual Studio Team System 2010.

Tool extensibility - In this release external tool writers have full access to the T-SQL parsers (for SQL Server 2000, 2005 and 2008), the SQL Script DOM and the schema model (when inside Visual Studio). This adds on top of the ability to write extend the tools inside the platform. You can extend T-SQL refactoring by writing your own refactoring types (operations) and refactoring targets; add T-SQL Static Code Analysis rules, data generators, data distributions and test conditions. Database Projects (.dbproj) now provide a truly extensible declarative database development platform.

Separation of BUILD and DEPLOY. - The separation of build & deploy makes it possible to deploy the output of your database project to many different targets and different points in time. Build now produces a single artifact file hat describes the schema inside your database, called a .DBSCHEMA file. This file is used by the deployment engine to deploy your schema.

Standalone Deployment Engine. - The inclusion of a standalone and redistributable deployment engine makes it possible to deploy the output of your database projects (.DBSCHEMA files) to a target database without the need of having Visual Studio Team System Database Edition installed. This enables key scenarios like the inclusion of database schema deployment as part of your application installation.

All these architectural changes enable many new usage scenarios and interesting new features. So lets walk through the list of new features and major changes.

Feature list

All database objects are now modeled, therefore they are compared and part of deployment. The side-effect of this is that there are no more objects that need to be stored in pre- and post-deployment scripts.

Server vs. Database projects

This change allows you to model the different behavior of shared and server level objects inside SQL Server vs. user database level objects.

Partial project allow you to share implementation between projects, with a single definition of the code

Composite projects

Composite projects extend database references, allowing you to add objects from other project to the name database schema namespace. So you can compose your database from other projects and/or DBSCHEMA file references

Database references using literals, in addition to SQLCMD token support

The addition of literal support facilitates that you do not have to change your code of 3-part names, when do do not require the flexibility of name independent deployment

Reference support for XML Schema Collections to include .XSD files

Enables single sourcing of XML Schema Collections from an .XSD file which itself can be used by other developers or tools.

Reference support of SQL-CLR projects and / or SQL-CLR assemblies

Enables the inclusion of a SQL-CLR project or a binary, to represent a SQL-CLR assembly. Like XSD references, the goal is single sourcing of artifacts inside the development environment

This allows the creation of macros or programmatic add-in to drive these common tasks.

No more "DesignDB"

The change to a fully model driven implementation makes the need for a local SQL Server database instance obsolete. Having a sandbox for your development environment is still am advised practice though, so you can test the changes independently and in an isolated environment.

Build

Build validates the consistency of the schema model and produces all the files that you need to deploy the database

Deploy

Deploy takes the output from the build stage and deploys this information based on the settings supplied

Deployment settings are now isolated in to the .SQLDEPLOYMENT file, which allows you to standardize and re-use the deployment settings

Most of the "Schema Compare" comparison options are now available as the deployment options, to increase the symmetry between the two

VSDBCMD.EXE, is a command line deployment tool, which enables the standalone deployment of Database Project

Schema Compare:

Compares any combination of live databases, Database Projects and .DBSCHEMA files

Session level option support, which can be persisted and re-used

Object type level filtering allow you to exclude object types from the comparison

You can now quickly navigate back and forward between difference via the toolbar and optionally configurable keyboard shortcuts

Substitution of SQLCMD variables, allows for the comparison of projects that rely on the use of SQLCMD variables

New refactoring types: Expand Wildcards and Fully-qualify Names (in addition to Rename, Move Schema and 3/4 part name refactoring)

The patented "preservation of intend" functionality makes is possible that the Deployment Engine will deploy renames and move schema operations as actual renames and move schema operations, instead of a DROP / ADD operation.

Extensibility enables the creation of your own refactoring types (operations) and refactoring targets.

Database Unit Testing

Added support for SQL Server 2008

Data Generation

BulkCopy based inserts in addition to the existing INSERT data sync

Sequential Databound Generator is now included in the product

T-SQL Static Code Analysis

Execute analysis using MS Build and/or as part of the VS IDE build process

Including the ability to write you own T-SQL Static Code Analysis rules

Extensibility

Public full fidelity .NET based T-SQL parser for SQL Server 2000, 2005 and 2008.

Public .NET based SQL Script DOM, for programmatically generation of SQL code (equivalent to the Code DOM in .NET)

Access to the Schema Model from within Visual Studio, this enables for example ability to write code generators based on the schema model.

Feature extensibility:

T-SQL Static Code Analysis rules

Refactoring types, these are the refactoring operations

Refactoring targets, these are the sources that you want to update as part of a refactoring operation

Data Generators

Data Distributions

Test Conditions

As you can see the GDR release is chockfull of new and improved functionality! And the good things is you can get it now.

Installation

Uninstall old GDR versions

If you have a previous version of the GDR installed, you will have to uninstall these first. You can do this via Add/Remove Programs or from the command line using:

msiexec /X {DDF197C6-4507-3A19-A4B5-0E17CC931370}

Prerequisites

Before you start downloading and installing please check if the following pre-requisites are present on your machine!

But I’ve got some issues with the conversion to the new project format. The PrimaryFileName.sqlfile.sql and PrimaryLogFilename.sqlfile.sql are not added to source control automatically during the upgrade. All other files are. Is that a bug?

I have several SPs and Tabular Functions that reference each other and I get this error on a lot of them.

Function: %FullyQualifiedFunctionName% contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[%Table1%].[dbo]::[%Function1%], [dbo].[%Table2%].[dbo]::[%Function1%], [dbo].[%Table3%].[dbo]::[%Function1%],[dbo].[%Table4%].[dbo]::[%Function1%] or [dbo].[%Function1%]

I can’t seem to find any mention of the documentation scenarios supported by the GDR? I’ve found a couple of old MSDN posts indicating that documentation integration was under development, was it ever released?

Hello. Will Visual Studio’s Data Compare allow me to compare Data on two different SQL Servers that have two different Database schemas also? I have redesigned my database and now need to move over data from the old to the new database. They now have different schemas because of column name changes, etc.

How would you recommend I map the two? Is my only choice to manually write scripts for the data migration. Any suggestion is very much appreciated.