IS Department

Managing The Database Development Lifecycle Part 2

Posted in Programming by Brian Russell on November 9, 2006

The first post I described a lot about the tool and what its intended purpose was.  In this post I will finish up by talking about some more features of the tool as well as some limitations.

The VSTS Database Edition project helps with schema tracking of a single database.  Because of this, it does not version server level entities such as logins and server roles.  This is an important point to remember, but not something that is a show stopper.  The tool solves references to global SQL Server objects by caching an included XML file that represents SQL 2000 or SQL 2005.  Other dependencies such as cross database calls are presented as warnings to the user when the schema is built.  The warnings basically say we can’t guarantee this will work during runtime.

The tool uses two part file extensions in order to figure out what icons to show in the windows, and it will be used in the future for designer features they are going to add.  It is best to use them.  An example of a two part file extension would be “Person.Table.SQL”, or “Person.View.SQL”.  If you use the two part file extension, you can’t store a different type inside the file.  The tool will validate that you only have table objects scripted in .Table.SQL files.  It allows you to add new scripts just as you would expect, and the new scripts are generated from templates.  So, you can modify the way the default code is generated, just like you can do in VS for code files.

Since this tool is treating your scripts just like source control, small changes to formatting will be viewed as changes.  It helps if everyone formats their SQL the same to help to not have too much merge conflicts.

You can have pre-deployment as well as post-deployment scripts that get added to the build script.  These are just files that the tool will take the text inside and append to the generated build script.  Be careful, these are added every time. This includes updating the database with the new schema.  So, if in your pre-deployment script you drop the database, the update script that is generated will fail.  Or, if you insert data post deployment, it will be ran every time you update which may lead to multiple records being inserted into the DB when that wasn’t what you intended to do.  You have to cover your butt on this stuff by using things like “if not exists()” in your scripts.

The tool includes a way to generate test data.  It is able to generate repeatable data in order to test against.  This is important because your unit tests will count on certain data being there, and this tool will put it there.  It can purge the tables before putting the data in, as well as resetting identities so when the new data is inserted it will be the same as last time.  The data that is generated is created by some algorithm they created that involves deltas and what not.  That isn’t too important to know, but you can be assured it is smart random data that is generated. 

You can control how the random data is generated.  So, normally we store phone numbers in a varchar field.  Well, this tool may generate “sdfjaspodiy” as sample data in that field.  Instead, you want more meaningful data to be generated.  To do this, you give the generator a regular expression matching the data format you want generated.  So, you could build a regular expression that validates a phone number field, and the random data generator will generate data that fits that regular expression.  There is a databound generator included that will pull data from a data source to use as the random data.  Let’s say you want to fill a state column with only valid states you have in a lookup table somewhere.  Well, point the generator at that data and it will randomly pick data from that list and put it in your test data.  You can even write your own custom data generators which I expect the community to come up with over time. 

Continuing on with test data, you can apply rules as to how much test data is to be generated.  You can even apply ratios for foreign key references.  So, if a person has many addresses, you can tell the generator to generate 5 addresses for every one person. 

One downside to test data generation is that it can’t generate data for circular references (tables that reference themselves).  They are looking to fix this, but not in V1.
The tool also includes some unit testing functionality.  I think it is limited in scope, but may prove useful in certain circumstances.  For instance, it can generate test stubs for stored procedures, user defined functions, and triggers.  You can use T-SQL assertions or client side assertions.  To be honest, I didn’t capture a whole lot of information about this.  It seemed too limited and didn’t support the correct process for unit testing: “write test – fail – write code – test pass – refactor”.  It does support pre and post test scripts just like setup and teardown methods used by popular unit testing frameworks.

Last, but not least, the tool supports rename refactoring.  Since the tool tracks references, it is able to allow you to refactor a table name, and it will tell you everywhere that table is referenced.  Same with column names, views, constraints, etc.  The BIG downside to this is that because the tool only does comparisons to create build scripts, it doesn’t remember what was refactored, only what was changed.  So the change will be changed correctly, but may result in data loss if deployed (with warnings of course).  The reason for this is pretty simple.  If you change a column name and run the refactoring, it works great on the schema.  Now you go to deploy the change to the database, and it does a comparison.  The tool sees that the column you renamed doesn’t exist in the script anymore, but does in the live database, so it generates a drop of that column.  Then, it sees the newly renamed column in the script, but doesn’t see it in the live database.  It then generates a create for that column.

Well, that is all I remember or wrote down.  I hope this post helps anyone looking into the Visual Studio Team System Database Edition tool.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: