It can be said that there are two schools of thought about database programming. On one hand, some database developers connect to the live database to do all their work. On the other hand, many developers program offline and connect to the database only when it is time to check in new code.
Visual Studio 2005 Team Edition for Database Professionals, hereafter DB Pro, embraces the latter approach, placing both data and schema in the same place.
DB Pro is the fourth edition of Microsoft's Visual Studio 2005 Team System, or VSTS, a tool for boosting collaboration throughout the development cycle. Now in trial edition, DB Pro joins editions for architects, developers and testers.
Additional Team System editions, addressing other roles in the software development life cycle, are in the works, Sajee Mathew, VSTS technical specialist, said at a recent Microsoft DB Pro launch event in the Boston area.
With DBPro, there is an offline, source control-protected version of a database development project. So far, the product supports projects and templates for SQL Server 2000 and SQL Server 2005. Sometime in the second half of 2007, Microsoft will release a Service Pack for VSTS that includes APIs for working with databases like Oracle and DB2, Mathew said.
The recommended tool for managing projects under source control is Microsoft's Team Foundation Server, which is the server-side version of Visual Studio 2005 Team System. However, any SCCI-compliant providers will work, Benjamin Day, a .NET development consultant, said at the launch event.
In addition to source control, each developer has a representation of the project, or a "sandbox," where his or her work occurs. Microsoft's free SQL Server Express is a good place to store a sandbox, officials said. With the sandbox, "No one's stomping on each other's changes. You can keep working on what you're working on until it's done," Day said.
When a task in the sandbox is finished, there are several things a prudent database developer should do before logging on and checking in his code with source control.
One step is to run GetLatest on source control and test her new code before checking it in. "You always want to make sure you're not breaking the build," Day said. DB Pro does include some error-checking tools, he added; for example, it will recognize that "restaurant asdfasdf" is not the right name for a table.
DB Pro offers other tools for reducing code errors. For example, if a developer decides to rename a table, a refactoring tool will search through a project's schema objects and update that table name accordingly, said Robert Rae, technology solutions professional, Microsoft. DB Pro also has a schema compare tool, which can pinpoint the differences between projects and databases and between databases and databases.
Unit testing is another procedure that database developers should get in the habit of doing, Mathew said. There is a bit of overhead involved, since one must create new code to test existing code, but the benefits are worth it.
"Unit testing has gained a lot of credence and following in the last few years due to agile development," Mathew said. "Adding unit testing to database development will improve the quality of applications even further."
DB Pro's unit test capabilities include automatic generation of T-SQL unit test stub for stored procedures, functions and triggers as well as test validation on T-SQL assertions and client-side assertions. While the stubs are generated as T-SQL code, they can also be brought up in C# or VB code, Mathew said.
One final DB Pro feature introduced at the launch event was the product's data generating tools. These allow developers to plug randomly generated bits of data -- everything from birthdays to names and ID numbers -- into a table without compromising customer data. "Using customer data in a test environment is at best unethical and at worst illegal," Mathew said. "You wouldn't want your [personal] bank data being used in a test."