Thursday, March 15, 2007

Tools for working with databases

Mike Zeff
Developer Evangelist
Developer & Platform Strategy Group
Microsoft NZ

http://blogs.msdn.com/mikezeff

Or how to make the database guy part of the development team.

Visual Studio Team System

Team Edition for Database Professionals

  • Expand to database teams
  • Manage database change
  • Database Testing
  • Database Deployment

What we heard from customers

(or “why did we build it…”)

  • Managing data base change is hard…
    • A rollback means a LONG night
    • Development teams can end up working with out-of-data versions
    • Finding errors at the end of the development cycle
  • Disconnect between development and database teams

Conceptual Overview

  • Schema change now managed in VS Team System and TFS
  • Production Database is now “One version of the truth” only for Data
  • “One Version of the truth for Schema” is Under Source Control

Creating a Database Project

  • Core concept: offline database development
  • Simply a series of files collected together into a single logical collection
  • The files represent the truth of your schema
  • Can be included in complete solution
  • Can be stored in source control such as Team Foundation Server for versioning
Offline Development
  • Import database schema to populate project from production
  • Nothing changes until deployment
  • A couple of other points, but Mike was too fast


Demo Summary
  • Import schema from existing database
  • Database project is created as a series of .SQL files.
  • Some more, but he’s too fast.

Isolated Iterative Development

Benefits of this Approach
  • Managed, project oriented evolution of database schema
  • Application code and database
  • Too fast
Managing Change
  • Changes are local to the project
  • Project can be compared with database
  • All elements can be managed under version control
  • Template driven
    • Version specific Microsoft SQL Server 2000 or SQL Server 2005
Working with the Project
  • Add new elements
  • Too fast
Refactoring
  • Bring the power of refactoring to SQL
  • Update all dependent objects in a database project
  • Make an atomic change, see preview
  • Rename
Demo Summary
  • All changes occur offline, local to the project
  • Changes can be compared to database
  • Refactoring can update all dependent objects
A Rollback Means a LONG night
  • Unit testing helps ensure that changes do not break existing code
  • Unit test designer is SQL focused
  • Work in the language of your choice: T-SQL, VB, C#
  • Builds on existing Team Test unit Test functionality

What We Can Test

  • Stored Procedures
  • Functions
  • Triggers
  • Arbitrary SQL
  • Too fast
Test Data
  • To create a solid foundation for testing we support data generation
  • Deterministic – always generate
  • Too fast
Demo Summary
  • Use of Regular Expressions to limit generated test data
  • (he couldn’t get the RegEx to validate an email address)
  • Can specify number of rows to generate (up to 1 million?)
  • Can specify ratio of rows to related tables
Build/Deploy
  • Standard Visual Studio build task
    • New versus update builds
    • Project properties for builds
    • Scheme compare for builds
  • Configurations
  • Pre/Post Deployment scripts
  • Build results in SQL script file
  • Deploy
Build Cycle
  • Can also be used in a “continuous” environment

Deployment Summary

  • Deploy using .SQL file
  • Too fast

Additional Resources

  • Team web forum
    • http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=725
  • Team blogs
Call to action
  • Visit the Database Professional Team Centre
  • Watch the on demand web cast series
  • Visit http://www.microsoft.co.nz/visualstudio for special offers on VSTS and VSTE DB Pro
  • Extra credit: install the beta version
  • Too fast


The Future of Data interaction

Problem

Data != Objects

LINQ
  • Fix the impedance mismatch among different data domains
  • Focus on the needs of the application not bridging disparate data representations
  • Build on general purpose language features in C# 3.0 and ????
.NET Language Integrated Query (LINQ)
  • LINQ enabled data sources
  • LINQ enabled ADO.NET
    • LINQ to SQL
    • LINQ to Datasets
    • LINQ to entities
  • LINQ to Objects
  • LINQ to XML
.NET Language Integrated Query framework
  • Too fast
Querying relational data
  • current way (SqlCommand, SqlConnection, DataReader)
  • LINQ way (Define [Table], and use in code as a first-class object).
Call to Action
Posted by at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)