Transcript T-SQL Deployment and Continuous Integration Best Practices

T-SQL Deployment and
Continuous Integration
Best Practices
Grant Fritchey – Red Gate Software
www.ScaryDBA.com
Grant Fritchey | www.ScaryDBA.com
Goals
 Learn the importance of source control for
databases in a database team development
environment
 Show how database development can comply with
code development best practices
 Learn how to incorporate testing and support of a
continuous integration environment into database
development processes
Grant Fritchey | www.ScaryDBA.com
2
Grant Fritchey
 Product Evangelist for Red Gate Software
 Email: [email protected]
 Twitter: @gfritchey
 Blog: scarydba.com
Grant Fritchey | www.ScaryDBA.com
3
The Database “Problem”
 Information persistence layer
 Businesses like their data
 Keeping that data in place can be work
» Which is the start of the “problem”
 Object Relational Impedance Mismatch
» Which is the rest of the “problem”
Grant Fritchey | www.ScaryDBA.com
4
The Database “Solution”
 Treat it as much like code as possible
 Do the following
» Work directly with the team
» Define a repeatable process
» Put the database into source control
» Put tests in place to validate the database
» Automate as much of the process as possible
 Follow the local development paradigm as much as
possible
Grant Fritchey | www.ScaryDBA.com
5
The Team
 Roles
» Application Developer
» Database Developer
» Database Administrator
» Report Writer
»?
 Communication
 Collaboration
» Remember, it’s a common goal
 Contention
Grant Fritchey | www.ScaryDBA.com
6
Process: Team Glue
 You must define a process
» It’s your process, you define it
 Management buy-in
 Write the process down
» And publish it
 Review process regularly
 Follow the process
» Exceptions have to be exceptional
 The process applies to everyone
» No, DBAs, you’re not that special
Grant Fritchey | www.ScaryDBA.com
7
Source Control
 Known states
» Production
» Source control
 Tighter integration with Developers
 History of changes
 Auditing
 But, requires discipline
» Always work through source control
» Always deploy from source control
» Only deploy from source control
Grant Fritchey | www.ScaryDBA.com
8
Testing
 Test Driven development
 Improved quality
 Continuous integration
 Testing needs
» Automation
» Isolation
» Repeatability
Grant Fritchey | www.ScaryDBA.com
9
Automation
 Because you have work to do
 Multiple developers
 Multiple servers
 Tools you know
» TSQL
» Powershell
» Command line
» 3rd Party
Grant Fritchey | www.ScaryDBA.com
10
Continuous Integration
 Quality control
 Identify issues early
 Build from source control
 Automatic process
 Scheduled or Triggered
 Make the build self-testing
 Publish the results
Grant Fritchey | www.ScaryDBA.com
11
Continuous Integration runs
• each
Unittime
test you
database
code
commit
a
• Generate
database
upgrade
• change
Validate
upgrade scripts
to database
source control
scripts• Validate database creation scripts
• Generate creation scripts
We
also buildbuild
a database
that dev
can and
be
Optionally
and
update
If any part of the CI process fails, the
used
by
the application for its own
test
environments
‘build’
is
deemed
broken
and
Each time
the
build
is
successful,
publish
unit tests
developers
are alerted
immediately
updated
database
documentation
Grant Fritchey | www.ScaryDBA.com
Tool Set
 Source Control
» Flavors don’t matter
» Make it work for you
» Use labeling (or equivalent)
» Use branching (or equivalent)
 Build Tool
» SSMS
» Visual Studio
» 3rd Party
 CI Monitoring Tool
» TeamCity
» CruiseControl
» Custom
» Other
Grant Fritchey | www.ScaryDBA.com
13
Demo
 More than one way to do this
Grant Fritchey | www.ScaryDBA.com
14
Goals
 Learn the importance of source control for
databases in a database team development
environment
 Show how database development can comply with
code development best practices
 Learn how to incorporate testing and support of a
continuous integration environment into database
development processes
Grant Fritchey | www.ScaryDBA.com
15
Resources
 Scarydba.com/resources
 http://knowscrum.com/benefits-of-continuous-integration-inscrum-best-practices-in-scrum/ - Benefits of CI in Scrum
 http://www.codinghorror.com/blog/2006/10/the-build-serveryour-projects-heart-monitor.html - The Build Server, Your Projects
Heart Monitor
 http://martinfowler.com/articles/continuousIntegration.html Martin Fowler on CI
 http://en.wikipedia.org/wiki/Continuous_integration - Wikipedia CI
 http://www.real-sql-guy.com/search/label/Source%20Control –
Automated Source Control
 http://www.simple-talk.com/sql/sql-tools/sql-test-seeing-redchange-to-green/ - Rob Sullivan – Seeing Red Change to Green
 http://www.simple-talk.com/content/article.aspx?article=1247 –
Troy Hunt – Continuous Integration for SQL Server Databases
Grant Fritchey | www.ScaryDBA.com
16
Grant Fritchey
 Product Evangelist for Red Gate Software
 Email: [email protected]
 Twitter: @gfritchey
 Blog: scarydba.com
Grant Fritchey | www.ScaryDBA.com
17
Questions?
Grant Fritchey | www.ScaryDBA.com
18