Microsoft's Visual Studio for database professionals was originally designed to fill a gap in database development by providing features for managing lifecycles as well as tools for collaboration and testing. The company recently released Visual Studio Team System 2008 Database Edition (VSDB), and many of its features can usually only be found only in third-party programs -- or database developers, including myself, have created them. Let's take a look at what's new in the most recent version of Visual Studio Team System.
Feature overview
The features in Visual Studio Team System 2008 Database Edition are designed around typical database development tasks. The first step is to import your new schema into the project from a script file or target database. VSDB then parses the script file or reverse-engineers the database and saves the database schema in multiple SQL files. These files are saved at a granular level: Each table, index, primary key, foreign key, constraint, etc., is saved in a separate file.
By adding files or manually modifying existing script files, you can add tables, stored procedures and other database projects. This paradigm takes time to get used to because object modification in VSDB works differently than it does in SQL Server Management Studio, where you get a modification dialog that allows you either to deploy your change right away or to save the change script. With Visual Studio 2008 for database professionals, all your changes are made offline and only in the database model. When you are ready to deploy changes to another database, you first need to build a project using the Build command. Creating a project build is similar to compiling an application: VSDB checks all files, examines changes, verifies that everything is structurally sound and ensures that there are no syntax errors.
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
p>
Once the build is done, you can run the Deploy command. This command generates a script file with all the changes and optionally runs it in the target database. The other option is to just save the script and run it manually. This option is a setting in the project properties.
Schema Compare
At some point during development, you may need to synchronize the project with another database to update the model with changes someone has made or perhaps to ensure that no one has made changes. To do so, you can use the Schema Compare feature, which allows you to select a target and source database or project. The output of schema comparison is a list of objects in the source and target, plus suggested actions such as Create, Delete, Update or Skip.
You can also filter the list by selecting Different Objects, Missing Objects or New Objects in the schema filter (Data → Schema Compare → Filter on the menu). This filter enables you to see quickly which changes need to be made to the target. You can then save the change script to a file, open the script in T-SQL editor or -- if you're brave enough -- write schema changes directly to the target.
You can also use this feature on its own in Visual Studio Team System 2008 Database Edition --without creating a project -- to quickly compare any two databases and potentially synchronize them, or you can simply save the script. I recommend that developers do the latter and also ensure that the generated script is correct.
The screenshot below shows the Schema Compare dialog. It is filtered to present only new or changed objects in the model compared with a database. As you can see, it identifies that the ErrorLog table has a new column and that the model contains a new table:
[IMAGE]
Click on image for larger version
Data Compare
A similar feature to Schema Compare is Data Compare. This feature allows you to compare data on selected tables and views in two databases. You can define the level of comparison by selecting the following options: Only in Source, Only in Target, Different Records and Identical Records. The data tables can be compared only if the tables in the source and target database have the same name, schema and owner. In addition, they must have the same primary key, unique index or unique constraint. Once the comparison is done, you get the same options as you do with Schema Compare: You can either write changes to the target database or save the script. This feature is useful for deploying reference data from development to production or for bringing data from a production server to development databases.
Data Generation Plans
One of the common challenges in database development involves the lack of data in the development database, especially if it's a new project. VSDB includes a feature to ease this problem: Data Generation Plans. This feature allows you to create one or more plans to generate sample data into a target database. In each plan, you define which tables to include, how many rows per table should be generated and the type of data that should be inserted.
Numerical fields are pretty straightforward to configure, and in most cases the suggested defaults are just fine. For string-based columns, you can specify the minimum and maximum length. VSDB generates random strings for these columns, so the downside is that generated data can be hard to read. Moreover, if you use such data in an application, it may not pass data validations on application screens. Luckily, you can also define Regular Expression output for string-based columns and then use RegEx to help generate data in a desired format, like a random ZIP code or phone number.
Refactor
Another useful feature for database development in VSDB is the Refactor option, which is just a fancy name for re-naming database objects. When you decide to re-name an object, you can select the Refactor feature, and VSDB finds all dependencies and generates scripts for updating not only the object you want to rename but all dependencies as well. If you rename a table column, for example, VSDB updates all stored procedures, views, indexes, constraints and other objects that use the column. This feature can be a huge timesaver with objects that have many dependencies.
Things to consider
Most database development shops typically have more than one version of the same database, such as one or more development databases or quality assurance, staging, testing and integration databases, and so on. In my experience, as hard as you try to keep them in sync, you ultimately have to chase down differences in the schema. This can be the result of imperfect schema management, developers with cowboy mentalities and too many privileges, or a failed update that hasn't been rolled back.
The Visual Studio Team System 2008 Database Edition development team devised an interesting notion: The truth lies in the production database. This means that its schema should be considered your "golden database," because somewhere out there you also have one or more applications working with that database. While I agree with this principle, it doesn't always work. I once worked on a large application for an independent software vendor that had about 50 production databases, for example. How do you handle this scenario? You certainly don't want to maintain 50 database models that fundamentally should be the same.
The answer depends on how your organization works, the number of production databases, access to them over the Internet (not always possible) and so on. My recommendation is to create a single VSDB model using the "true" database selected or your ideal golden database candidate. Once you finalize and test the model with your applications, you can use the Schema Compare features to synchronize your model with the remaining databases.
Hopefully this article provides you a good useful overview of the features included in Visual Studio Team System 2008 Database Edition. In a follow-up article next month, I will explore how to manage database development lifecycle using VSDB.
ABOUT THE AUTHOR:
[IMAGE]
Roman Rehak is a senior database architect at MyWebLink.com in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools. He regularly contributes SQL Server articles to Visual Studio Magazine, SQL Server Magazine and other technical publications and presents at user groups and conferences in the U.S. and Canada. He is an active member and volunteer of the Professional Association for SQL Server. Rehak also serves as the Technical chair for the SQL Server track at the annual DevTeach conferences in Canada and is president of the Vermont SQL Server User Group.
![[IMAGE]](http://media.techtarget.com/digitalguide/images/Misc/sql_VS2008_figure1.JPG){kind=link}