My notes on my first impressions of Visual Studio Team System for Database Professionals.
I installed VSTS for Database Professionals using the DVD image from MSDN. This also involved installing MSDN Library for Visual Studio; it seems the integrated help will only work with that version of the library and not the Jan 2006 one I had installed for integrating with Visual Studio 2003.
The first action I undertook was to use the wizard to create a new project. It asks for an instance of SQL Server and then creates a DB on that instance with a name of the form "<name-of-project>_DB_<some-guid>". This database instance is used by Visual Studio to perform validation of the model, and so I guess should not be tampered with!
The project creation wizard doesn’t prompt for a database to sync with, though it is possible to import schemas or sync with an existing database after the project creation wizard has completed.
When I add a table in the project in Visual Studio, it generates a CREATE TABLE script and a couple of dummy columns. Not a great design experience. I was expecting something more graphical. When I try and rename the columns, using the Schema View tab, this must be done via a ‘refactor’. When I later created foreign keys to a column, this feature (as expected) updated those foreign key scripts too. It also optionally creates a refactoring XML log under a hidden "Refactoring logs" directory in the project root. I cannot see where this is used yet.
If I want to do anything more complex than changing a column name then it means editing the SQL script for the CREATE table. This includes adding a column, changing a column’s data type, nullability, identity, etc. It doesn’t feel like you’ve got the full design capabilities of Management Studio in an offline mode. There also doesn’t seem to be any form of entity-relationship diagram capability, for those of us used to using Visio or Erwin to maintain our database schemas.
Choosing "Build" on solution explorer context menu for the project creates a script under a hidden "sql" folder in the project root. This script puts together all the individual scripts in the project to create a single deployment script. Individual scripts include tables, views, SPs, functions, post- and pre-build scripts, security scripts… the list goes on.
Choosing "Deploy" on the solution explorer context menu for the project results in a build followed by the deployment. The deployment creates a database named <name-of-project>. The end result is two databases of this name in the server.
At this point it seems easier to start using Management Studio to edit the database, at least for a lot of coarse grained tasks like creating tables from scratch and references between tables.
In order to synchronise these changes back into the project it is necessary to create a new "Schema Comparison". This compares a project with a database, or compares one database against another.
The default comparison assumes your "target", i.e. the thing you want to update to be the same as the "source", is a database. In my case I had made changes to the database named <name-of-project> and I wanted to reflect those changes in the project. There’s a handy button in the middle of the schema comparison window that switches source and destination around, though.
My changes to be compared and synchronised were trivial; added tables, renamed columns, added foreign keys. These were flagged as a list of update actions split down to the table level. A button in the toolbar allows all the changes to be applied to the target. Individual update actions can be skipped if necessary. It would be nice if the schema comparison refreshed to reflect the state of play after the updates had been applied, but it’s possible that on an enterprise level schema this would be a bit slow.
A nice feature of the schema comparison is that when an update action row is selected, "source object" and "target object" scripts are shown side by side with changes highlighted in various colours.
My investigations continue, but I though some might find these first impressions useful.