Transcript Automate

Avoiding the Pitfalls of Database
Change Automation
Cindy Bean
Sr. Software Consultant
DBmaestro
SQLSaturday #514 – Houston, May 14, 2016
Agenda
 Why Automate?
 Database Automation Options
 Scripts
 Compare and Sync Tools
 Database Enforced Source Control
 Q&A
Why Automate?
 Agile World…




Doing better with less
Reacting quickly to market needs
Getting ahead of competition
Just can’t wait 6 months for that next release…
 Agile Development
 Process Automation
 Continuous Delivery
Dealing with Risk
 Smaller, more focused changes are easier to manage
(Agile…)
 Automation of repeating tasks lowers risk of (human) error
 Development and Operations should work in synergy
(DevOps)
Automation is the Name of the Game…
Continuous Integration
Continuous Delivery
Continuous Deployment
 DevOps (a clipped compound of
"development" and
"operations") is a culture,
movement or practice that
emphasizes the collaboration
and communication of both
software developers and other
information-technology (IT)
professionals while automating
the process of software delivery
and infrastructure changes.
What is Continuous Integration?
 Principles and practices of software
development





Focus on streamlining development
Developers integrate code into shared repository
Each check-in is verified
Automated builds
Automated tests
 Easier & quicker to prevent and find problems,
less back tracks => short integrations
And Continuous Delivery?
 Next step after continuous integration
 Becoming lean, and even more Agile
 Make sure each change is releasable
 Develop-> build-> test-> move to staging-> acceptance test
 Build a process to release with a push of a button
 Deploy to production-> test production
 Actual deployment to production is manually
actuated
=> Ensure risk mitigation and high efficiency
Why Continuous Delivery?
 Rapid changes
 Reacting quickly to market
needs
 Getting ahead of
competition
 Fewer changes backed out
 Better collaboration
 Fewer defects
 Ultimately better service
 Happy customers
 Profitability
Continuous Delivery Focus points




Team and process
Version everything
Automate your tests
Fix it, properly (no out of
process changes!)
 Automate your deployments
 Create the deployment
pipeline
Database is a Key Component




Database holds your essential information
Changes can impact the entire system
Need to be synchronized with other changes
Often overlooked
Reaching Inside the Database
 There is more to a database than SQL scripts
 Schema structure
 Code
 Content and meta-content
 Internal dependencies
…
 Ensure that changes are not made without
authorization
 Ensure no out-of-process changes
Real Database Issues
DevOps for Microsoft SQL Server
 Goals for Continuous Delivery




Database change process
Version control
No “Out of Process” changes
Automate your deployments
Database Automation Options
 Scripts
 Changelog Activities Tracking
 Compare and Sync Tools
 Database Enforced Source Control
SCRIPTS
Scripts
 Requires organization
 One script per object or script per change
 Where to store, filesystem or version control
 How to name files
 Scripts are not connected to actual database
objects
 No red-flags if the script cannot execute
properly until it’s run
 No auditing of the database changes
Two Isolated Processes
Scripts
Pit Falls
Scripts & Version Control
 Challenges…
 Code-overrides
 Working on the wrong revisions
 Scripts not always checked-in to the version
control solution
 Out of process updates go unnoticed
 Hard to locate outdated update scripts
 Order of script execution (data dependencies)
Scripts are Static…
 Scripts, unless super sophisticated:
 Unaware of changes made in the target
environment
 Time passed from their creation until their
execution
 Potential to override production hot-fixes & work
done by parallel teams
 Content changes are very hard to manage
 Metadata & lookup content does not fit practically
into the version control
 In most cases they are simply not managed
Scripts… Build Once Deploy Many
Out of
Process
Change
Model
Dev
Dev
Dev
Int
QA
Stage
Prod
1.5
1.7
1.6
1.4
1.3
1.2
1.1
1.7
1.1
1.4
1.1.1
1.1
?
1.1
1.1.1
1.1
1.2
1.1
1.2
1.1
1.2
1.3
1.2
1.3
1.2
1.3
1.4
1.3
1.4
1.3
1.4
1.5
1.4
1.5
1.4
1.5
1.6
1.5
1.6
1.5
1.6
1.7
1.6
1.7
1.6
Database Deploy Script
X
X
X
1.2
X
X
X
1.5
1.3
1.4
1.6
1.7
X
Re-Base (due to defects)
Environment
Changelog Activities Tracking
 Process similar to
scripts (ex.
Liquibase)
 Create a changelog
file in XML
 Add a changeset
(unique ID and
author)
Changelog Activities Tracking
 Process similar to scripts
(continued)
 Run the changeset
 Check your database
 Two other tables are
created
 databasechangelog
 databasechangeloglock
Changelog Activities Tracking
 Benefits
 Easy if you know XML
 Supports many database types
 Challenges
 Organize changelog files
 Master changelog to order releases
 More files to manage
 Recommended to keep stored procedures separate
 Recommended to avoid multiple changes per
changeset
COMPARE AND SYNC TOOLS
Compare & Sync tools
 Compares the differences between
databases and generates script to implement
 Challenges…
 Out of process changes
 Parallel development
 Conflicts
Compare & Sync tools
Safe to automate?
Sure…
Deployment Automation
 An index exists in Target (Production) but not
in source (QA)
 What should we do? Drop the index or not?
Compare & Sync tools
Safe to automate?
No. Requires manual inspection…
Safe?
Simple, right?
NO! we are going to BREAK production
without even knowing…
How Can It Break Production???
 A compare & sync tool:
 Is unaware of any changes that occurred before
the time it ran
 Has no knowledge of changes that took place at
the target environment
 Does not leverage version control for more
information
 Unable to deal with conflicts & merges between
different teams
How Can It Break Production???
 A compare & sync tool:
 … Requires manual inspection
 Requires detailed knowledge regarding each
change as part of the process
So… no automation… as automating
problems into production is a major risk!!!
DATABASE ENFORCED
SOURCE CONTROL
Manage DB Code Like Application Code?
In Case of Defects: Return to Dev or Fix Locally and Handle Conflicts
Development
Databases
• Enforced Source Control
• Check-In Database
Changes
Build Server
• Impact Analysis
• Generate Delta Script
• Merge Conflicts
Test Servers
• Execute SQL Scripts
• Run Auto Tests
UAT Servers
• Execute SQL Scripts
• Run Auto Tests
Database Deployment Safety Net
Database Source
Control Repository
Binaries – Source Control
Repository
Prod Servers
• Execute SQL Scripts
Database Enforced Source Control
 Integrated Version Control process
 Leverage proven version control best practices
 Force check in & out for database changes
 Labels
 etc..
 No code-overrides
 Always working with the correct revision
 All changes are documented
Database Enforced Source Control
 Integrated Version Control process
 Always know who did what, when, why and from
where
 No out-of-process changes
 Supporting structure and code
 No time spent on manual coding of the
change scripts
Version Control – 1 Enforced Process
Database IDE
Deployment Automation - Safety Net
Simple Compare & Sync
Source
vs. Target
=
≠
Action
No Action
?
You do not have all
of the information
Baseline aware Analysis
Source vs.
Baseline
Target vs.
Baseline
Action
=
=
No Action
≠
=
Deploy Changes
=
≠
Protect Target
≠
≠
Merge Changes
Including the Baseline in the
impact analysis identifies
conflicts to prevent code
overrides
Protecting Target Environment
Development
Baseline
Production
Previous Label /
Production Golden Copy
No index in baseline =>
we should protect the NEW index on production!!!
(Protect Target)
Protecting Target Environment
Development
Baseline
Previous Label /
Production Golden Copy
BUT… If we had the index in the baseline =>
we should take it down from production…
(Deploy Change)
Production
Deployment Automation
And Merge???
Impact Analysis
Safety Net For Deployment Automation
 Database Safe Automation:
 Leverages version control
 Can be run as a batch process
 Integrates to ALM (labels, Continuous Integration
& Delivery)
 Deals with conflicts & merges to match code
agility
Can raise red flags to stop the line…
if requires human intervention
Comparison Chart
Thank You Sponsors!
Visit the Sponsor
tables to enter their
end of day raffles.
Turn in your completed Event
Evaluation form at the end of the
day in the Registration area to be
entered in additional drawings.
Want more free training?
Check out the Houston Area
SQL Server User Group
which meets on the 2nd
Tuesday of each month.
Details at
http://houston.sqlpass.org
Deployment Automation
Q&A
Thanks…
Cindy Bean
[email protected]
www.dbmaestro.com
47