Better Support for Application Developers

Over the past months, I've covered many of the features of Visual Studio 2005 Team Edition for Database Professionals (Team Data's). This month, I want to step back and discuss what Team Data means to development team members whose jobs aren't directly related to developing the database. As I mentioned in previous articles, database development and administration are often not considered application development roles; however, most applications have a database of some form, and a number of application team members work with it.

Imagine that you're an application developer working on some client or middle-tier application that needs to access a database. Although you're not directly responsible for writing application code on the database server or defining the database structure, you must understand the database schema in order to select the best way to access the database. You must know if any stored procedures or views can simplify access, which table structures and relationships you need to access, and whether creating a query or requesting a stored procedure or view is more efficient. Writing database-access code brings up more questions. Do you have the latest version of the database on your development machine? Do you have a representative sample of test data to ensure your code performs correctly?

Team Data can answer these questions for application developers. By storing the database schema in a version-control system, such as Team Foundation Server (TFS), you can easily access the most recent version of the database and create a representative database on your development machine. You can review the exact structure of that database and populate it by using pre-defined data generation plans to ensure that, as you develop the data-access code, it's working correctly against the application database. You can create new or use existing unit tests to ensure that as you develop or modify the code, you aren’t breaking any other applications or databases that you’re testing. In addition, if you are using TFS, you can become an even more integral part of the application development team. You can create basic versions of database objects that you require to implement your application then shelve this code for a database expert to review and potentially alter. (For more information about shelving, see http://msdn2.microsoft.com/en-us/vstudio/aa718531.aspx and “Source Control Basics: Shelving, Branching, and Merging,” http://www.sqlmag.com, InstantDoc ID 50216.) This functionality lets an application developer who has even a minimal knowledge of T-SQL effectively design the required database objects. Then, an expert can use that design and adjust the implementation to meet the database requirements. This process is potentially more efficient than formally submitting a specification for a new object, which would be developed by a different team.

As a database's structure changes, it can be difficult to ensure that application code that accesses the database isn't broken. A basic form of test-driven development can bring huge productivity gains to the application development team and can somewhat alleviate this problem. If the database is changed and that change breaks an area of code during the application test runs, the testers can simply assign a work item to fix any code affected by the database change. You as the developer receive the work item, pull the most recent version of the database, refer to unit tests and data generation plans to find the problem, and make a fix according to the correct version of the database. This process can significantly reduce the impact of database changes on the application by allowing identification and resolution of any potential effects related to application change. This scenario also shows why it's important for the testers to have access to the latest versions of the database, database unit tests, and data generation plans. The ability to integrate all of these pieces into the overall test architecture for an application reduces the possibility of breaking changes slipping through the cracks and can help simplify the identification and resolution of any database-related problems in an application.

Because the database can often be the one single point of failure for an application, it's important to ensure that all development team members work against the database in the most efficient way possible. Tools such as Team Data and TFS can help make the process easier to implement and can help you be consistent in your process. Never underestimate the effect of the database on the entire application development process. If there's a database in your application, you can be assured that at some time or another you will come into contact with it.