The .NET Platform
Development Tools
COM & COM+
Data Access
Web Development
XML Technologies
Windows Servers
Wireless & Mobile
Security issues
Design & Process
Career Development
Analysis & Comment
Disposable Objects
We found this book useful!
You are not logged in: login here to access all areas.
Visual Studio 2005 for Database Professionals
Visual Studio 2005 Team Edition for Database Professionals brings the data dude into the fold. Mark Whitehorn investigates this latest addition to Microsoft's Visual Studio Team System.
Author: Mark Whitehorn
Last updated: Jun 2007
A great deal of time and money has been spent in recent years trying to make application development a more controlled process. The result is that all kinds of application lifecycle management applications have appeared, culminating for Microsoft developers in Visual Studio Team System.
Application developers can now check code out and back in, code catastrophes can be tracked to the specific developer (good or bad news, depending on your coding abilities) and much else. In the end, all of these tools do assist the development process because experience has shown that, unless it is highly controlled, the result is chaotic.
In the database development world, however, things are rather different. In some ways there is much more control, and it has been in place for a long while, but in other ways control is still sadly lacking.
So, what did we database guys do right? Well, very early on, we learned the hard way that getting the overall design of a database wrong is both very easy and very expensive. So back in 1975 the ANSI/SPARC (American National Standards Institute Systems Planning and Requirements Committee) looked at the problem. It suggested that the design process should focus on thinking in terms of user, logical and physical models.
This worked and in turn led to the development of entire job roles, such as the business analyst, and to entirely new ways of modelling business processes, such as Entity Relationship (ER) modelling. These techniques have stood us in good stead for 30 years and show no sign of falling from favour.
So we may have the database design process under control, but by the time we get to implementing the associated code, the situation is still a mess. We can easily have a dozen developers working on a single database with no source control system to manage the day-to-day development. As one writes a stored procedure against a table, another is changing the table structure or even the table name. This can lead to full-and-frank discussions, fist fights and other fracas.
There are, of course, tools out there that are designed to help. For example, Embarcadero has a fine range of products in DBArtisan, ER/Studio, Change Manager and so on. However they tend to address parts of the problem rather than the entire development lifecycle, which is where Microsoft's new product comes in.
Introducing Data Dude
Since I am paid by the word, I am delighted to introduce you to Visual Studio 2005 Team Edition for Database Professionals. An unwieldy name at the best of times, however its code name was 'Data Dude' so, for the sake of the magazine's finances, we'll shorten it to DD.
Working with stored procedures in Visual Studio 2005 Team Edition for Database Professionals.
Apart from the obvious characteristics, such as tracking which developer has broken which particular block of code, DD has some rather cool and unexpected features up its sleeves which are probably best described by walking you through how the product could be used in practice.
Imagine you have an existing database which you wish to bring under source code control. You open a project in Visual Studio, point DD at the database and simply import the schema (you can work with schemas from both SQL Server 2000 and 2005). The entire database schema is imported by reading the system tables and turning the meta data therein into a SQL script. This in turn is broken down into the smallest possible SQL components.
The net result is that your entire schema is rendered into fragments of SQL code that describe each table, stored procedure, index and so forth. The code is fragmented but all of the components are held perfectly in place by what is, essentially, an object model. This means that individual components or groups of components can be checked out by developers, edited and checked back in.
DD actually stores not only the code fragments but the entire development process of the database as a series of rows in a set of tables that it holds in another database. The unique identifier for each developer is stored along with the date, time and the actual changes made. The transactional nature of this database means that the entire development cycle can, if necessary, be rolled back to any point in time.
What I'm describing is standard practice for application development. The great news is that database development can now be tracked in the same way.
But there's more. For a start Microsoft has implemented what it refers to as 'refactoring for databases'. Suppose you change the name of a table. DD 'understands' all the source code and the relationships between all the objects, so it can identify every SQL object that refers to the table, work out the implications of changing that table name and present you with the SQL code necessary to change it. If you're happy with this, you go ahead and implement it. (Microsoft Access has actually been able to do this for a number of years, although not in the same highly controlled way.)
Of course Microsoft has made much of the new ability of SQL Server 2005 to run procedural code written in languages such as Visual Basic and C#. It is worth noting that the refactoring described above only works for the SQL code, not for components written in other languages. That doesn't stop it being a very useful feature.
The system also contains a relatively sophisticated test data generator that can generate random test data, skewed test data, data with particular characteristics, and so on. Data can be generated algorithmically from a set of seeds, and the description of the test data together with the seeds are treated as metadata which is stored alongside the development metadata. At any time you can regenerate the exact set of test data used at a precise moment in the development lifecycle.
Suppose that you have imported an existing schema, worked on it, tested the changes and are now ready to deploy. DD has one more trick up its sleeve. It has a schema compare facility which as the name suggests, can look at the new schema, compare it with the existing production schema and generate a change schema. This is a set of SQL commands that you can run against the existing schema which will update the existing structure to the new.
One problem with change schemas is that they have the potential to damage existing data. For example, if you need to change a table name the change schema can issue a SQL command to do that, or it could drop the table and rebuild a new, identical table structure with the new name. Since the act of dropping a table also empties it, the latter course is generally considered to be less than optimal for a production database. DD tries to find the optimal solutions and will also warn you when data damage might occur. This is a non-trivial task since the ramifications of some of these changes can be complex.
In my opinion this verbosely-named product has the potential to be incredibly useful for Microsoft database developers. However it is worth remembering that none of this addresses the issue of the full database development lifecycle: it deals only with the deployment of the physical model and does nothing towards capturing or controlling development of the user, logical and physical models themselves. For this process there are other tools, such as ERWin Data Modeler, but it would be great, in the fullness of time, to see all of this wrapped up in a single working environment.
The good news is that Microsoft sees this release as a first step towards regularising database development as a whole. The company is quite well aware that this product addresses just part of the entire issue, and not the whole process.
Click here for our Privacy Statement. Copyright © Matt Publishing. All rights reserved. No part of this site may be reproduced without the prior consent of the copyright holder.
