1 / 64

Visual Studio Team Edition for Database Professionals

Explore the features of Visual Studio Team Edition for Database Professionals including database unit testing, refactoring, change management, and more. Learn how to enhance your database development lifecycle.

edarren
Download Presentation

Visual Studio Team Edition for Database Professionals

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Visual Studio Team Editionfor Database Professionals Mario Szpuszta Software Architect Developer & Platform Group Microsoft Österreich GmbH. marioszp@microsoft.com http://blogs.msdn.com/mszCool Microsoft Confidential

  2. MSDN Briefings – Organisation • Monthly technical briefings • Currently released technology • Your current needs • Invitation / Registration / Feedback • http://blogs.msdn.com/msdnat • http://blogs.msdn.com/talk • Well, what I am doing here?

  3. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

  4. Static Code Analysis

  5. Unit Testing

  6. The Development Engine IT Governance Operational Excellence • Deployment • Impact analysis • Updates and Maintenance • App Health Monitoring • Security • Automated failover and recovery plans • Workflow customization • Business process re-engineering • Demand generation • Business Value Capture • Outsourcing • Resource planning Test Arch PM Dev IT Solution Lifecycle Value through “Better Together” integration across the Solution Lifecycle

  7. Process and Architecture Guidance Code Profiler Change Management Work Item Tracking Reporting Project Site Integration Services Project Management Dynamic Code Analyzer Build Server Unit Testing Static Code Analyzer Team Foundation Client Code Coverage Deployment Modeling Visio and UML Modeling Application Modeling Test Case Management Logical Infra. Modeling Load Testing Class Modeling VS Pro Manual Testing Visual Studio Industry Partners Visual Studio Team System Visual StudioTeam Architect Visual StudioTeam Developer Visual StudioTeam Test Visual StudioTeam Foundation

  8. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

  9. Product Overview (1) • Database Project System • Schema and Script Versioning • SCC Integration • T-SQL Editor with Query Execution • Database Schema Management • Build & Deploy • Schema Compare • Data Compare

  10. Product Overview (2) • Database Unit Testing • (Test) Data Generator • Stored Procedure Unit Testing • Schema Refactoring • Version 1: Change names, only • Future versions: indexes, constraints, relations • Team Foundation Server Integration • Work Item Tracking • Process Integration

  11. Database Development Life CycleThe cycle of life for database developers Edit Refactor Compare Database Project Deploy Build Data Generation Test Compare

  12. Production Database Management Studio Schema Conceptual Overview • Difficult to Manage Change to the schema • Production Database is one version of the truth for Data and Schema • DBA doesn’t have access to changes until he/she has deploy or reject choice • Changes often made to production database and not rolled back into test Tuning Monitoring Schema Changes “One Version of the Truth” for Data and Schema

  13. Production Database Management Studio Schema Conceptual Overview • Schema Change now managed in VSTS and TFS • Production Database is now “One version of the truth” only for Data • DBA doesn’t have access to changes until he/she has deploy or reject choice • “One Version of the truth for Schema” is Under Source Control Tuning Monitoring “One Version of the Truth” for Data Changes can be rolled out in a scheduled, managed way Scripts allow administrators to mange change updates “One Version of the Truth” for Schema • Offline • Under Source Control Schema Changes

  14. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

  15. Project ModelThe center of gravity Collection of .SQL file containing T-SQL DDL fragments Import database schema SQLServerDatabase Database Project Template Database Project Create New Project SQL Script Reverse engineer existing .SQL script files (*)

  16. Offline Model • Project model • Schema Objects representation • Collection of T-SQL DDL fragments • Objects are Parsed and Interpreted at: • Project Load Time • Object Change (save) • Source Control Sync (external change)

  17. Production Database Test Database Offline Model • Import database schema to populate project from existing database • Changes to schema traditionally have immediate affect • With off-line project nothing changes until you deploy the change Create table AUCTION ( id int not null, title varchar(25) not null, startDate DateTime not null, length in not null)

  18. Reverse Engineering a Schema DEMO

  19. Shredding in to SQL Fragments • Loading, importing or reverse engineering • Shreds the schema definition • Smallest possible DDL fragments • Example: • Table • CREATE TABLE [dbo].[Territories]([TerritoryID] [nvarchar] (20) NOT NULL,[TerritoryDescription] [nchar] (50) NOT NULL,[RegionID] [int] NOT NULL) ON [PRIMARY] • Primary Key • ALTER TABLE [dbo].[Territories] ADD CONSTRAINT [PK_Territories] PRIMARY KEY NONCLUSTERED ([TerritoryID]) ON [PRIMARY] • FK • ALTER TABLE [dbo].[Territories] ADDCONSTRAINT [FK_Territories_Region] FOREIGN KEY ([RegionID]) REFERENCES [dbo].[Region] ([RegionID])

  20. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

  21. Managed Change • Changes are local • Comparison between databases • Test database • Production database • Elements under source control • Any SCCI compliant version system • Template driven • Version specific SQL 2000 or SQL 2005

  22. Working With the Project • Make changes • Add new elements • Modify existing elements • Delete Items • Compare databases • Build update script • Deploy new or incremental update • Visual Studio • MSBuild action

  23. Build/Deploy Standard VS build task Configurations New vs. Update builds Project properties for build Schema compare used for build Pre/Post Deployment scripts Build results in SQL script file Deploy Deploy via SQL query tool Deploy via MSBuild task RTM: SQLCMD command support

  24. Changing the database DEMO

  25. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

  26. What you need for Testing? Updated schema Test drivers (unit tests) Tons of data (realistic)

  27. Data GenerationDesign Time • Data generator component • Strategy for generation • Distribution for generator • Range of values • Relation between values • Settings for generator & distribution • Number of rows • Row-count ratios between tables

  28. Data GenerationDesign Time – Default Behavior • Per column generator • Matching data type • Aware of CHECK constraints • Special attributes • Foreign Keys  Foreign Key generator • Uniqueness  PK, UC, indexes • Default distribution • Uniform distribution when not unique

  29. Data Generation – Value Generators • Simple generators for each data type • Strings (char, varchar, nvarchar…) • Numbers (smallint, int, bigint, float…) • Binary (varbinary, image…) • Date and Time • UUID and Bit • Complex generators • Foreign Key • Regular Expression • Data Bound

  30. Data GenerationDistributions @ Work

  31. Data GenerationDesign Time • Understand domain constraints • Check constraints (min/max) • Table cardinality • Enforce table ratios • Column value distribution

  32. Generate Test-Data DEMO

  33. Database Unit TestingDesign Time • Automatically generate unit tests: • Stored Procedures, Functions, Triggers • Test Validation (assertions) • T-SQL  RAISERROR • Client Assertions • None Empty ResultSet • Row Count • Execution Time, … • Pre & Post Test Scripts

  34. Database Unit Testing Test Execution • Automatic Deployment Integration • Automatically deploy database project prior to running tests • Data Generation Integration • Automatically generate data based on generation plan prior to running tests • Execution & Validation connections • Validation connection can be higher privileged account

  35. Create a Unit Test DEMO

  36. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

  37. Database Schema RefactoringWhat is refactoring? • “A database refactoring is a small change to your database schema which improves its design without changing its semantics.” • Agile Database Development, Scott Ambler

  38. Database Schema Refactoring Rename Refactoring… • Rename any SQL 2000/2005 schema object • Updates all references in… • Schema Objects • Data Generation Plans • Scripts • Database Unit Tests • Preview changes prior to commit • Global undo to reverse all changes

  39. Database Schema Refactoring Refactoring Safety Net • Unit Testing • Generate tests after refactoring • Version Control • Store all previous versions before refactoring • Schema Compare • Analyze the exact differences between the project and live database to understand the impact of the update

  40. Refactoring an Object DEMO

  41. Agenda • Team System Review • VSTS for Database Professionals • Project System • Change Management • Database Unit Testing • Database Refactoring • Advanced Topics, Extensibility • Summary

  42. Command Line Building • Using devenv.exe • Visual Studio shell in command line mode • Using MSBuild.exe • Important note: • In CTP3 the project needs to be opened inside Visual Studio!

  43. Project Properties • SET options • Only override when different • Collations • Only override when different • Difference between New and Update

  44. Building Using MSBuild • Build – New database script • msbuild NorthwindOnline.dbproj /t:build • msbuild NorthwindOnline.dbproj /t:build /p:Configuration="New Deployment" • Build – Update for defined target server • msbuild NorthwindOnline.dbproj /t:build /p:Configuration="Update Deployment" /p:TargetConnectionString="Data Source=(local)\sql80;Integrated Security=True;Pooling=False;" /p:TargetDatabase="NorthwindOnlineTestRun"

  45. Deploying Using MSBuild • Deploy – New database • msbuild NorthwindOnline.dbproj /t:deploy /p:Configuration="New Deployment" • Deploy – Update Database • msbuild NorthwindOnline.dbproj /t:deploy /p:Configuration="Update Deployment" /p:TargetConnectionString="Data Source=(local)\sql80;Integrated Security=True;Pooling=False;"

  46. Misc. Actions Using MSBuild • All (Build + Deploy) • msbuild NorthwindOnline.dbproj /t:all • Clean • msbuild NorthwindOnline.dbproj /t:clean • msbuild NorthwindOnline.dbproj /t:clean /p:Configuration="New Deployment" • msbuild NorthwindOnline.dbproj /t:clean /p:Configuration="Update Deployment"

More Related