In my previous look at Visual Studio Team System 2008 Database Edition (VSDB), I gave an overview of VSDB and its major features. In this follow-up, I will focus on how you can use it to effectively manage your database development lifecycle.
First, let's look at what happens behind the scenes while working on a project. Once you import a database into your project, you work in an "offline mode." Each database object is saved into a single file, including sub-objects. Each table trigger or index, for example, will have its own file rather than be stored in the same file as the table. You have to use the T-SQL editor to modify database objects because there is still no user interface (UI) editor, even in this release. While this may be difficult for less experienced database developers, it is also a great way to improve your Data Definition Language (DDL) skills.
I...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
n the project, you can make changes to existing objects or add new objects. These changes are not made to the original database unless you reconnect again and either explicitly deploy the changes or run the scripts generated by the deployment process. VSDB instead uses a validation model stored in a single file behind the scenes. This is a change from the previous version, in which VSDB generated a validation database to check your scripts. When you save a changed script in the database project, VSDB checks the script against the model and applies the valid changes. But if your script does not end up being valid, due to a syntax error, name conflict or any other error, VSDB marks the file in the project and notifies you about the error. If you try to build a project when one or more objects fail validation, your build will fail and you will have to resolve all issues before you can create a database build.
When you work with database objects, you can choose between two different views. The Solution Explorer view shows the objects in folders categorized by object type (tables, indexes, views, etc.). This view is also the physical representation of the file structure in the project: What you see here is how the files are saved on the disk. Database Schema View, the other view, is hierarchical in the manner of SQL Server Management Studio. There is a list of top-level objects, such as tables or stored procedures, with the ability to drill down into lower-level objects such as indexes, primary keys or constraints.
I find the Database Schema View much easier to work with and less likely to compromise the project's organization. When you call up the New Item dialog in Solution Explorer, for example, it chooses the last object type that was selected. This means that if you right-click on the Indexes node and create a new object after creating a stored procedure, VSDB is set to create a file with the *.proc.sql extension. You can still save the file with your CREATE INDEX code inside and build the project, but the file will now have the .proc.sql extension rather than the .index.sql extension. The Database Schema View, however, offers the choice of Full Text Index, Index and XML Index when you attempt to add a new item. This keeps the user from accidentally mismatching object types with expected file extensions.
After you modify, delete or add new objects, it's time to perform a database build and generate the files needed for deployment. When you hit the "Build" button, VSDB validates the whole project, resolves all dependencies and generates a schema file containing a model of the database in XML format, along with pre and post deployment scripts. The Build process does not, however, do any comparison with the target database. Once your project is built, you can proceed with deployment. When you run the Deploy command, VSDB compares the schema generated by the build with the target database and generates a deployment script. The generated script file contains T-SQL scripts meant to be run against your target database to deploy all your changes. You can configure the project to either generate this deployment script or to generate and deploy it in the target database.
Over the years, one of my rules has been never to trust generated scripts enough to run them in a production database. While it might be acceptable to deploy directly from VSDB to nonproduction target databases, you should not do it with ones used for production. I recommend that you examine the script and ensure that nothing in it will cause a problem in your database. The generated script is actually an SQLCMD script, and VSDB executes the script directly when you deploy while the deploy setting is on. If you decide to run the script manually through SQL Server Management Studio, make sure you have switched the execution mode to SQLCMD.
Another option for executing is to run sqlcmd.exe from the command prompt or a batch file, but this method poses an issue: The generated script contains the following code to check both the SQL Server name and the database name, confirming that they match the names in the project:
IF (@@servername != 'DEVBOX\YUKON')
BEGIN
RAISERROR(N'The server name in the build script %s does not match the name of the target server %s. Verify whether your database project settings are correct and whether your build script is up to date.', 16, 127,N'DEVBOX\YUKON',@@servername) WITH NOWAIT
RETURN
END
IF NOT EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE [name] = N'AWTest')
BEGIN
RAISERROR(N'You cannot deploy this update script to target DEVBOX\YUKON. The database for which this script was built, AWTest, does not exist on this server.', 16, 127) WITH NOWAIT
RETURN
END
The script above would fail, unless the name of the server is DEVBOX\YUKON and the name of the database is AWTest. This means you cannot take this script and execute it on a number of servers with different database names. You will have to manually delete these two sections to make it run against any database with the right schema. You could somehow mitigate this, though, by using Configuration Manager in your project and creating multiple project configurations. Each configuration allows you to define unique settings within the project, including SQL Server connection, database name and script output location. If you then generate a build for each configuration, each generated script will contain the code needed to synchronize the project with the target database. But if, for whatever reason, you cannot connect from VSDB to the production (or another) server, you still have to delete the server name check code or modify the server name.
Once you modify these scripts, you should save them or check them into SourceSafe. If you do not, the next deployment process will overwrite them. From the design perspective, it would make more sense if each project configuration provided an option to generate or not generate the code that checks the server name and the database name.
Conclusion
VSDB is a powerful and useful tool for managing your database development lifecycle, along with providing a set of tools to make database development easier. While some areas of the product are still rough around the edges, even in this second version, its main set of features is appealing enough to consider including VSDB in your set of tools.
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 cChair for the SQL Server track at the annual DevTeach conferences in Canada and is president of the Vermont SQL Server User Group.
