Chris Dahlberg DATABASE DEVELOPMENT WITH VISUAL STUDIO 2010

Chris Dahlberg DATABASE DEVELOPMENT WITH VISUAL STUDIO 2010 1

“Legacy” Database Development �Change management can be difficult ◦ Full database backups ◦ Numerous CREATE/ALTER scripts �Difficult for offline development �Difficult to integrate into ALM ◦ Lack of proper versioning tools ◦ Lack of formal testing tools 2

Version Control Challenge class Auction { { { Id get; { get; set; } } Int 32 Id {Int 32 get; set; } String Name { get; set; } Date. Time Start. Time { get; set; } } Int 32 Duration { get; set; } } V 1 V 2 V 3 App Revision History CREATE TABLE dbo. Auction ALTER TABLE Auctions ( ADD start. Time ALTER COLUMN DATETIME start. Time NULL DATETIME NOT id INTNULL NOT NULL, name VARCHAR(25) NOT NULL ALTER TABLE Auctions ) ADD duration int NOT NULL Database

Manual Versioning -- Version 1: Add table dbo. Auction IF OBJECT_ID (N'dbo. Auction', N'U') IS NULL BEGIN CREATE TABLE dbo. Auction ( id INT NOT NULL, name VARCHAR(25) NOT NULL ) END -- Version 2 Add start. Time column IF NOT EXISTS (SELECT * FROM sys. columns WHERE name = ‘start. Time') BEGIN ALTER TABLE Auctions ADD start. Time DATETIME NULL END -- Version 3: Add duration column IF NOT EXISTS (SELECT * FROM sys. columns WHERE name = ‘duration’) BEGIN ALTER TABLE Auctions ALTER COLUMN start. Time DATETIME NOT NULL ALTER TABLE Auctions ADD duration int NOT NULL END

The Solution �Database development in Visual Studio �Source Code Control �Simplified Deployment �Refactoring �Code Analysis �Unit Testing �Build automation

Version Control The “Visual Studio” Approach class Auction { { { Id get; { get; set; } } Int 32 Id {Int 32 get; set; } String Name { get; set; } Date. Time Start. Time { get; set; } } Int 32 Duration { get; set; } } V 1 V 2 V 3 CREATE TABLE dbo. Auction ( ( ( id INT NULL, NOT NULL, id INT id NOT name VARCHAR(25) NOT NULL, name VARCHAR(25) NOT NULL start. Time DATETIME NULL NOT NULL, ) ) duration INT NOT NULL ) App Revision History Logical Database

Deployment The “Visual Studio” Approach CREATE TABLE dbo. Auction ( ( ( id INT NULL, NOT NULL, id INT id NOT name VARCHAR(25) NOT NULL, name VARCHAR(25) NOT NULL start. Time DATETIME NULL NOT NULL, ) ) duration INT NOT NULL ) V 1 V 2 V 3 New Deployment Logical Database Revision History CREATE TABLE dbo. Auction ( ALTER TABLE Auctions id NOT start. Time NULL PRIMARY KEY, NOT NULL ALTER INT COLUMN DATETIME name VARCHAR(25) NOT NULL, start. Time NOT NULL, ALTER DATETIME TABLE Auctions duration INT NOT int NULLNOT NULL ADD duration ) Incremental Deployment

Database project types Database Project Server Project Data-tier Application Component • Available for SQL Server 2005 and SQL Server 2008 • Defines a user database • Supports all objects implemented by the respective SQL Server version • Available for SQL Server 2005 and SQL Server 2008 • Defines server-level objects and modifications to the SQL master database • Examples of server-level objects are logins and custom error messages • Defines databases that will be deployed to SQL Server Utilities • A self-contained unit of management that defines and bundles database objects and SQL Server instance objects that are associated with the database and deployment requirements

Database Tools Roadmap • • • Offline Change management SQL 2000/2005 ALM support for databases • • • Data-tier Application Component Projects introduced Data-tier Application Component projects and Database projects operate Sx. S • • • SQL 2008 support Separated Build and Deploy No “Design. DB” required SQL 2008 R 2 support SQL Intelli. Sense Support for 3 rd party database platforms SQL Azure support Visual Designers Single deployment engine Mission critical app support Full support for SQL objects Project system enhancements ALM feature enhancements v. Next Vision Combine the best of Datatier Application Component and Database Projects © 2009 Aspect Software, Inc. All rights reserved. 9