This week's commentary isn't about a new version of Microsoft SQL Server that's Microsoft .NET specific. Rather, this commentary looks at how common development practices must evolve as Microsoft development tools evolve. Take, for example, the .NET Framework. One of the .NET Framework's main goals is to let developers create software that meets enterprise requirements—that is, create software that moves beyond the Win32 desktop and simple Active Server Pages (ASP) site to scalable multi-tiered applications.
Almost every application that developers create using Microsoft development tools includes a connection to a database. Unlike everything else related to this type of project, the structures, stored procedures, and other objects that make up the database aren't treated as source code. Hence, when new tables or stored procedures are necessary, the application's developer will often add them directly to the development database. The project's development team members might use SQL Server Enterprise Manager to create and save these items without any review. Project development team members often demand and receive access to the database so that they can create whatever structures and procedures they need. This development practice results in a mishmash of styles and nearly duplicate objects at the end of the development cycle. This practice doesn't lead to successful enterprise applications.
Most .NET projects for enterprise applications start with three tiers: display (aka front end or UI), middle-tier (aka business logic), and database. These projects have two sets of developers: front end and middle tier. The front-end developers work on the UI; the middle-tier developers work on the business logic and create the database. Because the .NET Framework introduces numerous data-bound controls, the front-end developers often access the database directly by binding controls. Thus, even though developers discuss having multiple tiers, they often completely bypass using them and undermine the middle tier's ability to abstract data.
A chaotic development approach to multi-tiered development is one reason why developers new to the .NET Framework invariably step on their own feet. These developers typically don't think in terms of having a middle-tier component returning data sets. Instead, they think in traditional terms—that is, having the database return the data set directly to the UI, which means the UI and the database have dependencies. Although the current release of the application or that part of the development project might work fine initially, any change to the UI will directly affect the database. For that reason, even though a middle-tier component might initially just pass-through an unaltered data set, developers should adjust the UI to work with that component. That way, if the developers need to change a UI that's bound to a data set, the change remains within the middle tier. In other words, the developers don't need to change and test the database that all the applications use.
The multi-tiered development approach relies on effectively managing the .NET database. Managing a database can be as challenging as creating it. The first step is to make someone responsible for the database's health. This person can be a DBA or simply a middle-tier developer whom you've designated as the "database duke." (This responsibility doesn't need to be the developer's only task.) Make the DBA responsible for all the objects in the database. Everyone should go through the DBA, whether he or she wants to add a new table or create a new stored procedure.
To facilitate managing the development of your database, Visual Studio .NET provides a database project. This project holds the scripts that create the database and its objects. The DBA can place these files in Visual SourceSafe (VSS) for versioning. The DBA can also create stored procedures that automatically start with a common set of comments and provide a central way to manage and maintain updates to both tables and stored procedures as the application matures. That's the good news. The bad news is that this database project is still a somewhat immature project type. In most cases, creating new tables outside of Visual Studio .NET is easier. More important, the project doesn't provide an effective way to package a database. Using Enterprise Manager to create the package is still easier but riskier.
In spite of these challenges, the Visual Studio .NET database project is an excellent start to providing developers the tools and techniques that support enterprise development. Click here for more information about creating a database project.
