The Data Dude Meets Team Build “Integrate the data tier developer into the core development life cycle and process.” That is one of the main objectives of Visual Studio Team Edition for Database Professionals, also known under its project name “Data Dude.” Bringing the data tier developer into Visual Studio is the first step in enabling closer integration between the application and data tier developer. Having both environments leverage the same Team Foundation Build (Team Build) system enables daily and automatic integration of changes into the build process, enforcing closer integration and shorter feedback cycles between the two originally disjoint disciplines. Visual Studio Team Edition for Database Professionals (VSDBPro) enables you to manage your SQL Server database schema (definition) the same way as you handle your application projects and source code. It starts by representing your database schema inside the newly added Database Project (.dbproj) (see Figure 1). It does so through a collection of T-SQL DDL fragments. Each fragment is stored in a single .SQL file and represents a single schema artifact, for example a table, queue, constraint or procedure definition. Because fragments only represent a single schema object, tracking changes and versioning these schema objects is a lot simpler and precise. 
Figure 1: Adding a New Project to SCC. After you have established the Database Project you have two views: The Solution Explorer view presents the physical layout of all the directories and files used to store the objects inside the project (Figure 2). The Schema View provides a logical representation of the complete schema organized by schema and/or object type (Figure 3). 
Figure 2: Solution Explorer-AdventureWorksDB.
Figure 3: Schema View-AdventureWorksDB.Build & Deploy Now that you have a collection of .SQL fragments, how do you go about deploying your schema? This is where “build” comes in. When you build a Database Project, the build engine takes all the fragments inside the project and compares them with the schema inside the target database. Build is based on a difference-based build engine (Figure 4). The build engine takes two inputs: the project state; which is “what you want” and the target database; which is “what you have.” The comparison results in a set of schema objects that are different, which are sorted to reflect the correct dependency order. The build engine determined this dependency information during the load of the project by parsing and interpreting all DDL fragments for the lifetime of the project. Finally, the build engine generates a deployment script in the form of a .SQL script which contains a set of T-SQL DDL statements to incrementally update the schema on the target server/database. This alleviates the need to manually create and/or maintain incremental update scripts to keep your database schema up-to-date. 
Figure 4: Difference Based Build.Using the incremental deployment script you can update the target database, using the Deploy command inside Visual Studio. The Truth Is in the Project The big change is that the Database Project is now the authoritative source of the schema definition, not the database instance(s) the schema is deployed to. Since the Database Project has become the center of truth for our database schema, placing the project under source code control enables the next step in our crusade of integrating the data tier into the development life cycle: versioning of the schema objects (Figure 5). 
Figure 5: Initial Check-in.This enables placing the project under source code control (SCC), the versioning of the individual schema objects, and the tracking of the changes of these objects. It also enables alignment in versioning between the application and the data tier. Both can use the same labels to indicate versions at a given point in time and use branching when projects are derived or merged. So when a developer syncs his or her sources to a certain label inside source code control, this contains the definition of the state of the database schema as it was used at that point in time when the label was created. Now that you understand how the individual projects work, let me switch gears and focus on how you can integrate this into the team environment. |