Application Security Testing: An Integral Part of DevOps
Most developers I've talked to agree that the database is the most important component in a typical business application. Yet, for years SQL Server database developers have used Administration tools shipping with SQL Server or a variety of 3rd party utilities. Visual Studio, the Microsoft flagship development environment, was the domain of the VB.NET or C# developer. TSQL support in Visual Studio was rudimentary at best.
That story changed last year with the advent of Visual Studio Team Services Database Edition. VSTS Database Edition was a late addition to VSTS 2005 and was enhanced with the VSTS 2008 release. I'm going to show you how you can incorporate VSTS Database Edition into your database development process.
Important, but not Sexy
Databases are not visually engaging. Unless a database is not working, most application users don't see any part of a database. Generally, databases don't interact with the outside world like a Web Service or an Application Control Library would.
Compared to an Object Oriented-based application, database components are tightly coupled. Tables are coupled to each other via foreign keys. Views, functions, and stored procedures are tightly coupled to tables and each other. Broken database code means a broken database or—even worse—corrupted data.
A database is not like an assembly. There is little you can do to version a SQL Server database like you would version an assembly. Tight coupling and lack of versioning have other ramifications. It's difficult for multiple developers to work on a database. Aside from TSQL, there are other things to manage like Roles and object permissions. Finally, you don't compile a database like you do an assembly.
Simple and Specialized
Alternately, Transact SQL code is pretty compact and specialized. TSQL only does data. There are no delegates, class hierarchies, or multi-threading features to support. TSQL really serves only two roles: Data Definition (DDL), statements like ALTER and CREATE; and Data Manipulation (DML), statements like INSERT, UPDATE, and DELETE. Because database code generally works in the database, there are fewer execution dependencies and test construction is easier than the many levels of testing you typically undergo with, for example, a Desktop or Web Application.
VSTS Database Edition addresses the issues above and leverages the structure of TSQL, turning Visual Studio into a capable database development environment. Now, I'll take you through a sample project so you can see VSTS Database Edition in action.
Creating a Project
First, the Database Edition only works with Visual Studio Team Suite. To showcase the tools and simplify this article, I'm going to build the project from an existing database, rather than building a database from scratch.
Like all Visual Studio development, database development is managed by using projects and solutions. A project typically corresponds to a single database. You can create SQL 2005 projects or SQL 2000 projects. I've installed some additional Power Tools, so your install may look slightly different. Figure 1 displays the SQL Server projects.
Figure 1: New database project
After creating the project, it's important (but not required) to match project collation with the database collation. Collation defines things such as ordering for text fields and case sensitivity. Neglecting to set collation can lead to confusing warnings and errors. Figure 2 shows where you set project collation on the project properties.
Figure 2: Project collation property
At this point, I could have begun building a database in VSTS. Like other Visual Studio projects, right-clicking the project and selecting "Add Item" displays the dialog of components you can create in the project.
Instead, as stated earlier, I'm going to use an existing database, the ubiquitous AdventureWorks database. There are two ways to import TSQL into a project: importing directly from a database or importing from a file. Importing from a database will include all the TSQL in the database along with items like Roles and Users.
I prefer to develop in SQL Server Management Studio or to use a tool like ERWIN to generate the DDL, so typically I choose the file import initially and then use the Schema Compare on moving forward from there (I'll explain Schema Compare shortly.) For demonstration purposes, I'm going to import using the database import option. Figure 3 shows the database import steps.
Figure 3: Import database schema
With a working project and imported database, it's time to start adding things to the database and showcase some of the other development features.