Change Is Easy! Managing Change is Hard

A Walk Around SQL Server Data Tools

Presented by

James McAuliffeBI Solution Architect, MCITP

Convergence Consulting Group

BUSINESS INTELLIGENCE: It’s Who We Are and What We Do.Business Value – Improve business processes via analytic solutionsSpecialized Expertise – Data & Analytics: From initiative to adoptionPartner Approach – Trusted advisor and evangelist, change cultures Our People are the Best – Experienced, passionate, professionals

Convergence Consulting Group – Who We Are

An award-winning consulting services firm that specializes in helping customers increase business performance through the transformation of data into actionable, intuitive analytic solutions.

Ask Me About Opportunities

Agenda – Walk Arounds

Change Is Easy - Managing Change is Hard

• A Walk Around Database Change Management (Processes, Principles)

Managing Change Is Hard - So Make It Easier

• A Walk Around SQL Server Data Tools (SSDT)

40 TRILLION GB

Why Do You Care About Change Management?

Change Is Going to Happen:

Inevitable

Easy To Introduce

Hard to Manage

Expensive

Sometimes TOO expensive

Data Is Valuable

Your Cell Phone Data

• Marketing Contacts• Operations• Payroll• Financial Reporting

Your Digital Pictures

Your Company’s Data

Data Is Expensive

Expensive to Acquire

Expensive to Retain

Expensive to LOSE

Database Development Lifecycle

Analysis

Logical Design

Physical

Develop

Deliver

Maintain

CREATE TABLE dbo.Auction( id INT NOT NULL, name VARCHAR(25) NOT NULL, start DATETIME NULL, len INT NULL)

class AuctionApplication( int id; void MethodA();)

Data Change Delivery Is Challenging

Database

class AuctionApplication( int id; void MethodA(); void MethodB();)

class AuctionApplication( int id; string cacheTitle; void MethodA(); void MethodB();)

Revision History

App

V 1 V 2 V 3

ALTER TABLE dbo.Auction WITH CHECK ADD CONSTRAINT Au_PK PRIMARY KEY (id)

ALTER TABLE dbo.Auction WITH CHECK ADD CONSTRAINT Au_SK UNIQUE (name)

T-SQL Change Management DEMO

Create a table named AUCTION Populate the table. Make some changes.

Change Management Is Hard

Next: Make It Easy

Change WILL Happen Data Is Valuable Data Is Expensive

Data Loss is Even More Expensive

Data Change Delivery Is Challenging

Change Management Approach

Single Source Of Truth

• People and Processes to determine change (Governance)• Structural Organization in the Library

Organizational Support

• Compare• Delta Scripts

Development Support

• Compare• Delta

Deployment

Maintenance

Change Management is Hard – Make it Easy

SQL Server Data Tools (SSDT) NOT BIDS (not BI Dev Environment)

SSDT - BI

Free with SQL Server 2012 Free add-in for Visual Studio 2010/2012 Uhh – FREE?

One Source Of Truth

Multiple Database Targets

Multiple Developers

Multiple Versions

One Ring To Rule Them All - SSDT

Organize

Having Lots of Stuff is Useless

• Object• Schema• Schema/Object

SSDT Uses Same Organization Model As SSMS

• If it’s all the same to you…

Solution: Organize By

SQL Server Object Explorer

SSMS vs SSDT – which is which?

Develop

VS Developers and Database Developers Share Common Construct

• Offline• Online

Developers can work either

• Multiple Sources, One Destination

When Done, Check Changes Into the DB Project

Deploy

Handoff is continual

When time for a release, all roles can see all changes consistently• Database Developer OR• Application Developer OR • DBA

Generate one script

Life is good.

DEMO

For those that missed the live demo and have questions, feel free to email me at jmcauliffe@ccgbi.com

Visit http://www.sqlsaturday.com/ to enjoy the next live SQL Saturday coming to a city near you.

Review: Change Management ApproachSingle Source Of Truth

Organizational Support• People and Processes to determine change (Governance)• Structural Organization in the Library (Consistency)

Development Support

Deployment• Compare• Delta Scripts

Maintenance• Compare• Delta

SQL Server Data Toolshttp://msdn.microsoft.com/en-us/library/hh272686(v=VS.103).aspx

Data Developer Center -> Microsoft SQL Server Data Tools ...https://msdn.microsoft.com/en-us/data/tools.aspx

Learn More About Microsoft SQL Server Data Tools http://msdn.microsoft.com/en-us/data/hh297029

SQL Server Data Tools Team Bloghttp://blogs.msdn.com/b/ssdt/

Home of the Data Dude (Gert Drapers: Father of Database Tools)http://blogs.msdn.com/b/gertd/

Resources

James McAuliffeConvergence Consulting Group jmcauliffe@ccgbi.com www.ccgBI.com www.linkedin.com/pub/james-mcauliffe/3/b09/b/

Thank You!!

Change Is Easy! Managing Change is Hard