Data Dude, Visual Studio 2010 SQL Server Database Projects

An interesting feature covered by last webinar of PragaticsWorks is the new database project in vs2010 power – xtra edition ;-)

 

All you can do and more

The wizard that will guide you to create a new solution to write the db schema, instead of using the traditional way of writing queries in management studio and keeping all the .sql script together in some hierarchical folder structure, here will be part of a solution (on tfs)

 

You can import an existing db schema from accessible SQL server, for example here the AdvetureworksLT has been imported. Note it’s possible to work on the schema of the db even without having the original scripts used to create the db itself.

 

Options to setup the db properties, usually done with DCL statements etc

 

This sound interesting you can setup Code Analysis; this will assure that the script writing style adheres to the company guidance. More here

 

Another useful feature is the automatic deployment process, basically we can check for change done in some deployed db with the actual schema in the solution and match and look out for any inconsistency and action to take, this script then can be deployed to the engineer

 


Together with the dba it’s possible to define a template to define the db with default value for null handling, quoted identifier etc

 

This is the VS scripting environment where to edit the solution elements, note they are arranged in Programmability, Tables and Views etc

 

The schema view together with the solution explorer gives you a double view of how the solution is organized both logically and physically

 

Super useful functionality, you can refactor any element of your schema without (big) headache

 

In this screenshot the ability to compare the schema in the solution with the db schema deployed on the server

 

Some code-generation tools will give the ability to fill columns data with realistic value (you can define some ranges, reg-expression and read random value from tables with realistic value)

 

Observations

It looks like the MS team is working closer with http://www.red-gate.com/ for the refactor/data generation side, but of course having a db project/solution with all the advantage of working in VS/TFS helps in bigger project specially when handling changes and code quality (the data generator can be very useful for unit test….)

 

Some resources

 

http://msmvps.com/blogs/deborahk/archive/2010/05/02/vs-2010-database-project-an-introduction.aspx

http://msdn.microsoft.com/en-us/library/ff678491.aspx

http://pragmaticworks.com/resources/webinars/

 

 

 

 

 

About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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