SlideShare a Scribd company logo
1 of 23
Download to read offline
SQL Server Continuous
Integration
Using Jenkins and Red Gate
Ernest Hwang
Principal Software Engineer, Practice Fusion
Presented to the
Silicon Valley SQL Server User Group
May 2013
Who am I?
• Ernest Hwang, Principal Software Engineer
at Practice Fusion in San Francisco
• C#, .NET, SQL Server Developer
• Working with SQL Server since 1999 (SQL Server 6.x)
• Using Red Gate for Continuous Integration since June
2011
• Someone who got tired of maintaining a folder of
database scripts for every release.
Why am I here?
• To show how easy it is to apply CI principals to
Database Development
What’s this?
A “How To” guide for automating your day-to-day
maintenance tasks by…
• *Easily* Versioning your database using Red Gate SQL Source
Control
• Using Continuous Integration (via Jenkins) to validate
Database builds
• Using Jenkins to automate database deployments
• Using CI / tSQLt / SQL Test to run unit tests
Prerequisites
• Experience with source control management systems
(svn, TFS, git, Hg)
• Familiarity with Continuous Integration Products
(Jenkins, Team City, Cruise Control)
• Awareness of build scripting languages (ant,
MSBuild)
What software is used?
• SQL Server 2008 / 2012
• Red Gate SQL Source Control
• Red Gate SQL Compare
• Red Gate SQL Data Compare
• Jenkins Continuous Integration Server
http://jenkins-ci.org/
– Promoted Build Plugin
– Copy Artifacts Plugin
– Version Plugin
– Git Plugin
• MSBuild
What is SQL Source Control?
• Source Control Plug in for SQL Server Management
Studio
• Creates a “snapshot” of the database schema defined by
CREATE scripts
• Snapshot is consumable by SQL Compare and may be
used to compare against the schema of an actual
database
What is SQL Source Control?
SQL Source Control is to SSMS
as
TortoiseSVN is to Windows Explorer
Demo Environment
GitHub
Local Copy of
RGDemo database
Dev, QA, & Prod
Databases
Developer Workstation
Build and Database Server
(Windows Azure VM)
Committing Changes to Source
Control
Demo
Local Database
Changes
Commit Changes
via SQL Source
Control
Changes committed to
SCM repository
How does Continuous Integration fit
in?
• The CI server polls the repository for changes
• When changes are checked in, the CI job kicks off
– Verifies that the database can be built
• Builds a brand new database from scratch using SQL
Compare and SQL Data Compare
– Runs unit tests
• Build should fail if unit tests do not succeed
– Archives the artifacts (for deployments)
• Artifacts can include build/test reports
– Emails engineers if there are problems
Breaking the Build
Demo
Commit Changes
via SQL Source
Control
CI Server Detects Changes
Kicks off Build Process
Build can pass or
fail validation
Deploying Changes to Different
Environments
• The Promoted Builds plug in can be used to deploy
changes to Integration/QA/Staging/Production
environments
• SQL Compare and SQL Data Compare are used to
deploy changes between the sourced controlled
database and your development environments
• Deploying to Production and/or Staging can be
configured to just create the scripts as opposed to
forcing the synchronization
Deploying to Dev / QA
Demo
SQL Source Control
Repository
SQL Compare Development Environment
Database
Creating a Database “Version”
• Make sure the Jenkins Versioning plugin is installed
• Create a User Defined Function called
dbo.DATABASE_VERSION()
• Create a build step that updates the UDF with the
version number
• The updated UDF will be archived and used with
deployments
Versioning your Database
Demo
Commit Changes
via SQL Source
Control
CI Server Detects
Changes
Kicks off Build
Process
Build Task updates
dbo.DATABASE_VERSION()
Function
Updated UDF
Archived
Unit Testing with tSQLt and SQL Test
• tSQLt is an open source set of stored procedures and
functions to facilitate Unit Testing in SQL databases
• SQL Test is a wrapper around this framework that
integrates with SSMS
• Your build server can enforce that your unit tests pass
and generate reports
Unit Testing with tSQLt / SQL Test
Demo
Commit Changes
via SQL Source
Control
CI Server Detects
Changes
Kicks off Build
Process
Build Task executes
Unit Test procedures
Tests can pass or
fail the build
What did SQL Source Control do for us?
• Saves developers time (yay!)
– No more maintaining update scripts
– Don’t need to deploy scripts when QA needs changes
• Identifies holes in your deployment process
– Are developers making changes directly to
production?
– Are indexes/constraints missing from your
Dev/QA/Prod environments?
• Creates a definitive database build that can be easily
deployed and redeployed
Considerations
• Environments must be pristine
• Process must be changed and understood
• Production change scripts should be scrutinized
(especially for the first few releases)
• Migration Scripts can be used to massage data
• DB Replication requires more work for promotions
Appendix 1: Other CI Servers
• Jenkins (open source)
• Team City (JetBrains)
• Bamboo (Atlassian)
• CruiseControl / CruiseControl.NET (open source)
Appendix 2: Other Source Control
Systems
• Subversion (SVN)
• Git
• Team Foundation Server
• Mercurial (Hg)
• AccuRev
• Surround SCM
Links / Contact Info
• ehwang@practicefusion.com
• @ernestedcode
• https://github.com/CF9/Databases.RGDemo
• Practice Fusion is Hiring (email me)
http://practicefusion.com/careers/
• facebook.com/practicefusion
• @practicefusion

More Related Content

What's hot

What is Jenkins | Jenkins Tutorial for Beginners | Edureka
What is Jenkins | Jenkins Tutorial for Beginners | EdurekaWhat is Jenkins | Jenkins Tutorial for Beginners | Edureka
What is Jenkins | Jenkins Tutorial for Beginners | EdurekaEdureka!
 
DBmaestro's State of the Database Continuous Delivery Survey- Findings Revealed
DBmaestro's State of the Database Continuous Delivery Survey- Findings RevealedDBmaestro's State of the Database Continuous Delivery Survey- Findings Revealed
DBmaestro's State of the Database Continuous Delivery Survey- Findings RevealedDBmaestro - Database DevOps
 
Best Practices for Database Deployments
Best Practices for Database DeploymentsBest Practices for Database Deployments
Best Practices for Database DeploymentsRed Gate Software
 
The Challenges & Pitfalls of Database Continuous Delivery
The Challenges & Pitfalls of Database Continuous DeliveryThe Challenges & Pitfalls of Database Continuous Delivery
The Challenges & Pitfalls of Database Continuous DeliveryPerforce
 
Continuous Integration, Build Pipelines and Continuous Deployment
Continuous Integration, Build Pipelines and Continuous DeploymentContinuous Integration, Build Pipelines and Continuous Deployment
Continuous Integration, Build Pipelines and Continuous DeploymentChristopher Read
 
Why source control your Oracle Database?
Why source control your Oracle Database?Why source control your Oracle Database?
Why source control your Oracle Database?Red Gate Software
 
Why retail companies can't afford database downtime
Why retail companies can't afford database downtimeWhy retail companies can't afford database downtime
Why retail companies can't afford database downtimeDBmaestro - Database DevOps
 
Continuous integration
Continuous integrationContinuous integration
Continuous integrationhugo lu
 
Challenges and Best Practices of Database Continuous Delivery
Challenges and Best Practices of Database Continuous DeliveryChallenges and Best Practices of Database Continuous Delivery
Challenges and Best Practices of Database Continuous DeliveryDBmaestro - Database DevOps
 
Continuous Delivery & the Database- The Final Frontier
Continuous Delivery & the Database- The Final FrontierContinuous Delivery & the Database- The Final Frontier
Continuous Delivery & the Database- The Final FrontierDBmaestro - Database DevOps
 
Continuous Delivery & the Database - the Final Frontier
Continuous Delivery & the Database - the Final FrontierContinuous Delivery & the Database - the Final Frontier
Continuous Delivery & the Database - the Final FrontierXebiaLabs
 
Challenges and best practices of database continuous delivery
Challenges and best practices of database continuous deliveryChallenges and best practices of database continuous delivery
Challenges and best practices of database continuous deliveryDBmaestro - Database DevOps
 
Continuous integration
Continuous integrationContinuous integration
Continuous integrationamscanne
 
Introduction to CICD
Introduction to CICDIntroduction to CICD
Introduction to CICDKnoldus Inc.
 
Delphix and DBmaestro
Delphix and DBmaestroDelphix and DBmaestro
Delphix and DBmaestroKyle Hailey
 
Jenkins - From Continuous Integration to Continuous Delivery
Jenkins - From Continuous Integration to Continuous DeliveryJenkins - From Continuous Integration to Continuous Delivery
Jenkins - From Continuous Integration to Continuous DeliveryVirendra Bhalothia
 
Continuous Delivery Distilled
Continuous Delivery DistilledContinuous Delivery Distilled
Continuous Delivery DistilledMatt Callanan
 
Continuous Integration, Continuous Quality, Continuous Delivery
Continuous Integration, Continuous Quality, Continuous DeliveryContinuous Integration, Continuous Quality, Continuous Delivery
Continuous Integration, Continuous Quality, Continuous DeliveryJohn Ferguson Smart Limited
 
The future of DevOps: fully left-shifted deployments with version control and...
The future of DevOps: fully left-shifted deployments with version control and...The future of DevOps: fully left-shifted deployments with version control and...
The future of DevOps: fully left-shifted deployments with version control and...Red Gate Software
 

What's hot (20)

What is Jenkins | Jenkins Tutorial for Beginners | Edureka
What is Jenkins | Jenkins Tutorial for Beginners | EdurekaWhat is Jenkins | Jenkins Tutorial for Beginners | Edureka
What is Jenkins | Jenkins Tutorial for Beginners | Edureka
 
DBmaestro's State of the Database Continuous Delivery Survey- Findings Revealed
DBmaestro's State of the Database Continuous Delivery Survey- Findings RevealedDBmaestro's State of the Database Continuous Delivery Survey- Findings Revealed
DBmaestro's State of the Database Continuous Delivery Survey- Findings Revealed
 
Best Practices for Database Deployments
Best Practices for Database DeploymentsBest Practices for Database Deployments
Best Practices for Database Deployments
 
The Challenges & Pitfalls of Database Continuous Delivery
The Challenges & Pitfalls of Database Continuous DeliveryThe Challenges & Pitfalls of Database Continuous Delivery
The Challenges & Pitfalls of Database Continuous Delivery
 
In (database) automation we trust
In (database) automation we trustIn (database) automation we trust
In (database) automation we trust
 
Continuous Integration, Build Pipelines and Continuous Deployment
Continuous Integration, Build Pipelines and Continuous DeploymentContinuous Integration, Build Pipelines and Continuous Deployment
Continuous Integration, Build Pipelines and Continuous Deployment
 
Why source control your Oracle Database?
Why source control your Oracle Database?Why source control your Oracle Database?
Why source control your Oracle Database?
 
Why retail companies can't afford database downtime
Why retail companies can't afford database downtimeWhy retail companies can't afford database downtime
Why retail companies can't afford database downtime
 
Continuous integration
Continuous integrationContinuous integration
Continuous integration
 
Challenges and Best Practices of Database Continuous Delivery
Challenges and Best Practices of Database Continuous DeliveryChallenges and Best Practices of Database Continuous Delivery
Challenges and Best Practices of Database Continuous Delivery
 
Continuous Delivery & the Database- The Final Frontier
Continuous Delivery & the Database- The Final FrontierContinuous Delivery & the Database- The Final Frontier
Continuous Delivery & the Database- The Final Frontier
 
Continuous Delivery & the Database - the Final Frontier
Continuous Delivery & the Database - the Final FrontierContinuous Delivery & the Database - the Final Frontier
Continuous Delivery & the Database - the Final Frontier
 
Challenges and best practices of database continuous delivery
Challenges and best practices of database continuous deliveryChallenges and best practices of database continuous delivery
Challenges and best practices of database continuous delivery
 
Continuous integration
Continuous integrationContinuous integration
Continuous integration
 
Introduction to CICD
Introduction to CICDIntroduction to CICD
Introduction to CICD
 
Delphix and DBmaestro
Delphix and DBmaestroDelphix and DBmaestro
Delphix and DBmaestro
 
Jenkins - From Continuous Integration to Continuous Delivery
Jenkins - From Continuous Integration to Continuous DeliveryJenkins - From Continuous Integration to Continuous Delivery
Jenkins - From Continuous Integration to Continuous Delivery
 
Continuous Delivery Distilled
Continuous Delivery DistilledContinuous Delivery Distilled
Continuous Delivery Distilled
 
Continuous Integration, Continuous Quality, Continuous Delivery
Continuous Integration, Continuous Quality, Continuous DeliveryContinuous Integration, Continuous Quality, Continuous Delivery
Continuous Integration, Continuous Quality, Continuous Delivery
 
The future of DevOps: fully left-shifted deployments with version control and...
The future of DevOps: fully left-shifted deployments with version control and...The future of DevOps: fully left-shifted deployments with version control and...
The future of DevOps: fully left-shifted deployments with version control and...
 

Viewers also liked

Best Practices for Database Deployments - Grant Fritchey, Justin Caldicott - ...
Best Practices for Database Deployments - Grant Fritchey, Justin Caldicott - ...Best Practices for Database Deployments - Grant Fritchey, Justin Caldicott - ...
Best Practices for Database Deployments - Grant Fritchey, Justin Caldicott - ...Red Gate Software
 
Meetup_Warsaw_Lean_Startup&Innovations_in_Corpo_1
Meetup_Warsaw_Lean_Startup&Innovations_in_Corpo_1Meetup_Warsaw_Lean_Startup&Innovations_in_Corpo_1
Meetup_Warsaw_Lean_Startup&Innovations_in_Corpo_1Bartek Janowicz
 
Lean Startup Machine Warsaw - first edition
Lean Startup Machine Warsaw - first editionLean Startup Machine Warsaw - first edition
Lean Startup Machine Warsaw - first editionBartek Janowicz
 
Evolution of Agile world with Lean Startup Concepts
 Evolution of Agile world with Lean Startup Concepts Evolution of Agile world with Lean Startup Concepts
Evolution of Agile world with Lean Startup ConceptsShriKant Vashishtha
 
Lean startup conference '2014_corporate_innovations
Lean startup conference '2014_corporate_innovationsLean startup conference '2014_corporate_innovations
Lean startup conference '2014_corporate_innovationsBartek Janowicz
 
Lean leadership w google campus 16062016
Lean leadership w google campus 16062016Lean leadership w google campus 16062016
Lean leadership w google campus 16062016Bartek Janowicz
 
Large scale automation with jenkins
Large scale automation with jenkinsLarge scale automation with jenkins
Large scale automation with jenkinsKohsuke Kawaguchi
 
Introduction to jenkins
Introduction to jenkinsIntroduction to jenkins
Introduction to jenkinsAbe Diaz
 
Apply the Lean Startup in B2B to Build Products Businesses Want (Course Slides)
Apply the Lean Startup in B2B to Build Products Businesses Want (Course Slides)Apply the Lean Startup in B2B to Build Products Businesses Want (Course Slides)
Apply the Lean Startup in B2B to Build Products Businesses Want (Course Slides)Étienne Garbugli
 

Viewers also liked (10)

Best Practices for Database Deployments - Grant Fritchey, Justin Caldicott - ...
Best Practices for Database Deployments - Grant Fritchey, Justin Caldicott - ...Best Practices for Database Deployments - Grant Fritchey, Justin Caldicott - ...
Best Practices for Database Deployments - Grant Fritchey, Justin Caldicott - ...
 
Meetup_Warsaw_Lean_Startup&Innovations_in_Corpo_1
Meetup_Warsaw_Lean_Startup&Innovations_in_Corpo_1Meetup_Warsaw_Lean_Startup&Innovations_in_Corpo_1
Meetup_Warsaw_Lean_Startup&Innovations_in_Corpo_1
 
Lean Startup Machine Warsaw - first edition
Lean Startup Machine Warsaw - first editionLean Startup Machine Warsaw - first edition
Lean Startup Machine Warsaw - first edition
 
Evolution of Agile world with Lean Startup Concepts
 Evolution of Agile world with Lean Startup Concepts Evolution of Agile world with Lean Startup Concepts
Evolution of Agile world with Lean Startup Concepts
 
Lean startup conference '2014_corporate_innovations
Lean startup conference '2014_corporate_innovationsLean startup conference '2014_corporate_innovations
Lean startup conference '2014_corporate_innovations
 
Lean leadership w google campus 16062016
Lean leadership w google campus 16062016Lean leadership w google campus 16062016
Lean leadership w google campus 16062016
 
Large scale automation with jenkins
Large scale automation with jenkinsLarge scale automation with jenkins
Large scale automation with jenkins
 
Introduction to jenkins
Introduction to jenkinsIntroduction to jenkins
Introduction to jenkins
 
There is no snapshot
There is no snapshotThere is no snapshot
There is no snapshot
 
Apply the Lean Startup in B2B to Build Products Businesses Want (Course Slides)
Apply the Lean Startup in B2B to Build Products Businesses Want (Course Slides)Apply the Lean Startup in B2B to Build Products Businesses Want (Course Slides)
Apply the Lean Startup in B2B to Build Products Businesses Want (Course Slides)
 

Similar to Microsoft SQL Server Continuous Integration

Database Build and Release - SQL In The City - Ernest Hwang
Database Build and Release - SQL In The City - Ernest HwangDatabase Build and Release - SQL In The City - Ernest Hwang
Database Build and Release - SQL In The City - Ernest HwangRed Gate Software
 
KoprowskiT_Session2_SDNEvent_SourceControlForDBA
KoprowskiT_Session2_SDNEvent_SourceControlForDBAKoprowskiT_Session2_SDNEvent_SourceControlForDBA
KoprowskiT_Session2_SDNEvent_SourceControlForDBATobias Koprowski
 
Database CI Demo Using Sql Server
Database CI  Demo Using Sql ServerDatabase CI  Demo Using Sql Server
Database CI Demo Using Sql ServerUmesh Kumar
 
Getting to Walk with DevOps
Getting to Walk with DevOpsGetting to Walk with DevOps
Getting to Walk with DevOpsEklove Mohan
 
Continuous Integration for OpenVMS with Jenkins
Continuous Integration for OpenVMS with JenkinsContinuous Integration for OpenVMS with Jenkins
Continuous Integration for OpenVMS with Jenkinsecubemarketing
 
Jenkins Introduction
Jenkins IntroductionJenkins Introduction
Jenkins IntroductionPavan Gupta
 
Dev/Test scenarios in DevOps world
Dev/Test scenarios in DevOps worldDev/Test scenarios in DevOps world
Dev/Test scenarios in DevOps worldDavide Benvegnù
 
Bringing DevOps to the Database
Bringing DevOps to the DatabaseBringing DevOps to the Database
Bringing DevOps to the DatabaseMichaela Murray
 
Continous integration and delivery for single page applications
Continous integration and delivery for single page applicationsContinous integration and delivery for single page applications
Continous integration and delivery for single page applicationsSunil Dalal
 
Achieving Full Stack DevOps at Colonial Life
Achieving Full Stack DevOps at Colonial Life Achieving Full Stack DevOps at Colonial Life
Achieving Full Stack DevOps at Colonial Life DevOps.com
 
Liquibase få kontroll på dina databasförändringar
Liquibase   få kontroll på dina databasförändringarLiquibase   få kontroll på dina databasförändringar
Liquibase få kontroll på dina databasförändringarSqueed
 
Continuous DB Changes Delivery With Liquibase
Continuous DB Changes Delivery With LiquibaseContinuous DB Changes Delivery With Liquibase
Continuous DB Changes Delivery With LiquibaseAidas Dragūnas
 
Accelerating time to delivery: Modernizing Application Development
Accelerating time to delivery: Modernizing Application DevelopmentAccelerating time to delivery: Modernizing Application Development
Accelerating time to delivery: Modernizing Application DevelopmentMicro Focus
 
Ci for force dot com
Ci for force dot comCi for force dot com
Ci for force dot comDon McIntosh
 
SQL Server DevOps Jumpstart
SQL Server DevOps JumpstartSQL Server DevOps Jumpstart
SQL Server DevOps JumpstartOri Donner
 
Database Change Management
Database Change Management Database Change Management
Database Change Management headspringlabs
 
Continuous Deployment of your Application @SpringOne
Continuous Deployment of your Application @SpringOneContinuous Deployment of your Application @SpringOne
Continuous Deployment of your Application @SpringOneciberkleid
 
OUG Ireland Meet-up 12th January
OUG Ireland Meet-up 12th JanuaryOUG Ireland Meet-up 12th January
OUG Ireland Meet-up 12th JanuaryBrendan Tierney
 
Continuous Integration
Continuous IntegrationContinuous Integration
Continuous IntegrationXPDays
 

Similar to Microsoft SQL Server Continuous Integration (20)

Database Build and Release - SQL In The City - Ernest Hwang
Database Build and Release - SQL In The City - Ernest HwangDatabase Build and Release - SQL In The City - Ernest Hwang
Database Build and Release - SQL In The City - Ernest Hwang
 
KoprowskiT_Session2_SDNEvent_SourceControlForDBA
KoprowskiT_Session2_SDNEvent_SourceControlForDBAKoprowskiT_Session2_SDNEvent_SourceControlForDBA
KoprowskiT_Session2_SDNEvent_SourceControlForDBA
 
Database CI Demo Using Sql Server
Database CI  Demo Using Sql ServerDatabase CI  Demo Using Sql Server
Database CI Demo Using Sql Server
 
Dev ops using Jenkins
Dev ops using JenkinsDev ops using Jenkins
Dev ops using Jenkins
 
Getting to Walk with DevOps
Getting to Walk with DevOpsGetting to Walk with DevOps
Getting to Walk with DevOps
 
Continuous Integration for OpenVMS with Jenkins
Continuous Integration for OpenVMS with JenkinsContinuous Integration for OpenVMS with Jenkins
Continuous Integration for OpenVMS with Jenkins
 
Jenkins Introduction
Jenkins IntroductionJenkins Introduction
Jenkins Introduction
 
Dev/Test scenarios in DevOps world
Dev/Test scenarios in DevOps worldDev/Test scenarios in DevOps world
Dev/Test scenarios in DevOps world
 
Bringing DevOps to the Database
Bringing DevOps to the DatabaseBringing DevOps to the Database
Bringing DevOps to the Database
 
Continous integration and delivery for single page applications
Continous integration and delivery for single page applicationsContinous integration and delivery for single page applications
Continous integration and delivery for single page applications
 
Achieving Full Stack DevOps at Colonial Life
Achieving Full Stack DevOps at Colonial Life Achieving Full Stack DevOps at Colonial Life
Achieving Full Stack DevOps at Colonial Life
 
Liquibase få kontroll på dina databasförändringar
Liquibase   få kontroll på dina databasförändringarLiquibase   få kontroll på dina databasförändringar
Liquibase få kontroll på dina databasförändringar
 
Continuous DB Changes Delivery With Liquibase
Continuous DB Changes Delivery With LiquibaseContinuous DB Changes Delivery With Liquibase
Continuous DB Changes Delivery With Liquibase
 
Accelerating time to delivery: Modernizing Application Development
Accelerating time to delivery: Modernizing Application DevelopmentAccelerating time to delivery: Modernizing Application Development
Accelerating time to delivery: Modernizing Application Development
 
Ci for force dot com
Ci for force dot comCi for force dot com
Ci for force dot com
 
SQL Server DevOps Jumpstart
SQL Server DevOps JumpstartSQL Server DevOps Jumpstart
SQL Server DevOps Jumpstart
 
Database Change Management
Database Change Management Database Change Management
Database Change Management
 
Continuous Deployment of your Application @SpringOne
Continuous Deployment of your Application @SpringOneContinuous Deployment of your Application @SpringOne
Continuous Deployment of your Application @SpringOne
 
OUG Ireland Meet-up 12th January
OUG Ireland Meet-up 12th JanuaryOUG Ireland Meet-up 12th January
OUG Ireland Meet-up 12th January
 
Continuous Integration
Continuous IntegrationContinuous Integration
Continuous Integration
 

More from Mark Ginnebaugh

Automating Microsoft Power BI Creations 2015
Automating Microsoft Power BI Creations 2015Automating Microsoft Power BI Creations 2015
Automating Microsoft Power BI Creations 2015Mark Ginnebaugh
 
Microsoft SQL Server Analysis Services (SSAS) - A Practical Introduction
Microsoft SQL Server Analysis Services (SSAS) - A Practical Introduction Microsoft SQL Server Analysis Services (SSAS) - A Practical Introduction
Microsoft SQL Server Analysis Services (SSAS) - A Practical Introduction Mark Ginnebaugh
 
Platfora - An Analytics Sandbox In A World Of Big Data
Platfora - An Analytics Sandbox In A World Of Big DataPlatfora - An Analytics Sandbox In A World Of Big Data
Platfora - An Analytics Sandbox In A World Of Big DataMark Ginnebaugh
 
Microsoft SQL Server Relational Databases and Primary Keys
Microsoft SQL Server Relational Databases and Primary KeysMicrosoft SQL Server Relational Databases and Primary Keys
Microsoft SQL Server Relational Databases and Primary KeysMark Ginnebaugh
 
DesignMind Microsoft Business Intelligence SQL Server
DesignMind Microsoft Business Intelligence SQL ServerDesignMind Microsoft Business Intelligence SQL Server
DesignMind Microsoft Business Intelligence SQL ServerMark Ginnebaugh
 
San Francisco Bay Area SQL Server July 2013 meetings
San Francisco Bay Area SQL Server July 2013 meetingsSan Francisco Bay Area SQL Server July 2013 meetings
San Francisco Bay Area SQL Server July 2013 meetingsMark Ginnebaugh
 
Silicon Valley SQL Server User Group June 2013
Silicon Valley SQL Server User Group June 2013Silicon Valley SQL Server User Group June 2013
Silicon Valley SQL Server User Group June 2013Mark Ginnebaugh
 
Hortonworks Big Data & Hadoop
Hortonworks Big Data & HadoopHortonworks Big Data & Hadoop
Hortonworks Big Data & HadoopMark Ginnebaugh
 
Microsoft SQL Server Physical Join Operators
Microsoft SQL Server Physical Join OperatorsMicrosoft SQL Server Physical Join Operators
Microsoft SQL Server Physical Join OperatorsMark Ginnebaugh
 
Microsoft PowerPivot & Power View in Excel 2013
Microsoft PowerPivot & Power View in Excel 2013Microsoft PowerPivot & Power View in Excel 2013
Microsoft PowerPivot & Power View in Excel 2013Mark Ginnebaugh
 
Microsoft Data Warehouse Business Intelligence Lifecycle - The Kimball Approach
Microsoft Data Warehouse Business Intelligence Lifecycle - The Kimball ApproachMicrosoft Data Warehouse Business Intelligence Lifecycle - The Kimball Approach
Microsoft Data Warehouse Business Intelligence Lifecycle - The Kimball ApproachMark Ginnebaugh
 
Fusion-io Memory Flash for Microsoft SQL Server 2012
Fusion-io Memory Flash for Microsoft SQL Server 2012Fusion-io Memory Flash for Microsoft SQL Server 2012
Fusion-io Memory Flash for Microsoft SQL Server 2012Mark Ginnebaugh
 
Microsoft Data Mining 2012
Microsoft Data Mining 2012Microsoft Data Mining 2012
Microsoft Data Mining 2012Mark Ginnebaugh
 
Microsoft SQL Server PASS News August 2012
Microsoft SQL Server PASS News August 2012Microsoft SQL Server PASS News August 2012
Microsoft SQL Server PASS News August 2012Mark Ginnebaugh
 
Business Intelligence Dashboard Design Best Practices
Business Intelligence Dashboard Design Best PracticesBusiness Intelligence Dashboard Design Best Practices
Business Intelligence Dashboard Design Best PracticesMark Ginnebaugh
 
Microsoft Mobile Business Intelligence
Microsoft Mobile Business Intelligence Microsoft Mobile Business Intelligence
Microsoft Mobile Business Intelligence Mark Ginnebaugh
 
Microsoft SQL Server 2012 Cloud Ready
Microsoft SQL Server 2012 Cloud ReadyMicrosoft SQL Server 2012 Cloud Ready
Microsoft SQL Server 2012 Cloud ReadyMark Ginnebaugh
 
Microsoft SQL Server 2012 Master Data Services
Microsoft SQL Server 2012 Master Data ServicesMicrosoft SQL Server 2012 Master Data Services
Microsoft SQL Server 2012 Master Data ServicesMark Ginnebaugh
 
Microsoft SQL Server PowerPivot
Microsoft SQL Server PowerPivotMicrosoft SQL Server PowerPivot
Microsoft SQL Server PowerPivotMark Ginnebaugh
 
Microsoft SQL Server Testing Frameworks
Microsoft SQL Server Testing FrameworksMicrosoft SQL Server Testing Frameworks
Microsoft SQL Server Testing FrameworksMark Ginnebaugh
 

More from Mark Ginnebaugh (20)

Automating Microsoft Power BI Creations 2015
Automating Microsoft Power BI Creations 2015Automating Microsoft Power BI Creations 2015
Automating Microsoft Power BI Creations 2015
 
Microsoft SQL Server Analysis Services (SSAS) - A Practical Introduction
Microsoft SQL Server Analysis Services (SSAS) - A Practical Introduction Microsoft SQL Server Analysis Services (SSAS) - A Practical Introduction
Microsoft SQL Server Analysis Services (SSAS) - A Practical Introduction
 
Platfora - An Analytics Sandbox In A World Of Big Data
Platfora - An Analytics Sandbox In A World Of Big DataPlatfora - An Analytics Sandbox In A World Of Big Data
Platfora - An Analytics Sandbox In A World Of Big Data
 
Microsoft SQL Server Relational Databases and Primary Keys
Microsoft SQL Server Relational Databases and Primary KeysMicrosoft SQL Server Relational Databases and Primary Keys
Microsoft SQL Server Relational Databases and Primary Keys
 
DesignMind Microsoft Business Intelligence SQL Server
DesignMind Microsoft Business Intelligence SQL ServerDesignMind Microsoft Business Intelligence SQL Server
DesignMind Microsoft Business Intelligence SQL Server
 
San Francisco Bay Area SQL Server July 2013 meetings
San Francisco Bay Area SQL Server July 2013 meetingsSan Francisco Bay Area SQL Server July 2013 meetings
San Francisco Bay Area SQL Server July 2013 meetings
 
Silicon Valley SQL Server User Group June 2013
Silicon Valley SQL Server User Group June 2013Silicon Valley SQL Server User Group June 2013
Silicon Valley SQL Server User Group June 2013
 
Hortonworks Big Data & Hadoop
Hortonworks Big Data & HadoopHortonworks Big Data & Hadoop
Hortonworks Big Data & Hadoop
 
Microsoft SQL Server Physical Join Operators
Microsoft SQL Server Physical Join OperatorsMicrosoft SQL Server Physical Join Operators
Microsoft SQL Server Physical Join Operators
 
Microsoft PowerPivot & Power View in Excel 2013
Microsoft PowerPivot & Power View in Excel 2013Microsoft PowerPivot & Power View in Excel 2013
Microsoft PowerPivot & Power View in Excel 2013
 
Microsoft Data Warehouse Business Intelligence Lifecycle - The Kimball Approach
Microsoft Data Warehouse Business Intelligence Lifecycle - The Kimball ApproachMicrosoft Data Warehouse Business Intelligence Lifecycle - The Kimball Approach
Microsoft Data Warehouse Business Intelligence Lifecycle - The Kimball Approach
 
Fusion-io Memory Flash for Microsoft SQL Server 2012
Fusion-io Memory Flash for Microsoft SQL Server 2012Fusion-io Memory Flash for Microsoft SQL Server 2012
Fusion-io Memory Flash for Microsoft SQL Server 2012
 
Microsoft Data Mining 2012
Microsoft Data Mining 2012Microsoft Data Mining 2012
Microsoft Data Mining 2012
 
Microsoft SQL Server PASS News August 2012
Microsoft SQL Server PASS News August 2012Microsoft SQL Server PASS News August 2012
Microsoft SQL Server PASS News August 2012
 
Business Intelligence Dashboard Design Best Practices
Business Intelligence Dashboard Design Best PracticesBusiness Intelligence Dashboard Design Best Practices
Business Intelligence Dashboard Design Best Practices
 
Microsoft Mobile Business Intelligence
Microsoft Mobile Business Intelligence Microsoft Mobile Business Intelligence
Microsoft Mobile Business Intelligence
 
Microsoft SQL Server 2012 Cloud Ready
Microsoft SQL Server 2012 Cloud ReadyMicrosoft SQL Server 2012 Cloud Ready
Microsoft SQL Server 2012 Cloud Ready
 
Microsoft SQL Server 2012 Master Data Services
Microsoft SQL Server 2012 Master Data ServicesMicrosoft SQL Server 2012 Master Data Services
Microsoft SQL Server 2012 Master Data Services
 
Microsoft SQL Server PowerPivot
Microsoft SQL Server PowerPivotMicrosoft SQL Server PowerPivot
Microsoft SQL Server PowerPivot
 
Microsoft SQL Server Testing Frameworks
Microsoft SQL Server Testing FrameworksMicrosoft SQL Server Testing Frameworks
Microsoft SQL Server Testing Frameworks
 

Recently uploaded

Project Work on Consumer Behavior in Fast Food Restaurants. Their behavior to...
Project Work on Consumer Behavior in Fast Food Restaurants. Their behavior to...Project Work on Consumer Behavior in Fast Food Restaurants. Their behavior to...
Project Work on Consumer Behavior in Fast Food Restaurants. Their behavior to...BilalAhmed717
 
Green Innovations: Wristbands Ireland's Eco-Friendly Products
Green Innovations: Wristbands Ireland's Eco-Friendly ProductsGreen Innovations: Wristbands Ireland's Eco-Friendly Products
Green Innovations: Wristbands Ireland's Eco-Friendly ProductsWristbands Ireland
 
Presented by Sabri international .......
Presented by Sabri international .......Presented by Sabri international .......
Presented by Sabri international .......SABRI INTERNATIONAL
 
Strategic Resources Corporate Presentation - March 2024 Update
Strategic Resources Corporate Presentation - March 2024 UpdateStrategic Resources Corporate Presentation - March 2024 Update
Strategic Resources Corporate Presentation - March 2024 UpdateAdnet Communications
 
Record of Module Forensic photography in
Record of Module Forensic photography inRecord of Module Forensic photography in
Record of Module Forensic photography inalexademileighpacal
 
Business Models and Business Model Innovation
Business Models and Business Model InnovationBusiness Models and Business Model Innovation
Business Models and Business Model InnovationMichal Hron
 
Bus Eth ch3 ppt.ppt business ethics and corporate social responsibilities ppt
Bus Eth ch3 ppt.ppt business ethics and corporate social responsibilities pptBus Eth ch3 ppt.ppt business ethics and corporate social responsibilities ppt
Bus Eth ch3 ppt.ppt business ethics and corporate social responsibilities pptendeworku
 
Wallet Pitch for startup fintech and loan
Wallet Pitch for startup fintech and loanWallet Pitch for startup fintech and loan
Wallet Pitch for startup fintech and loansujat8807
 
3BBE: THE FUTURE OF ECOMMERCE PRESENTATION - LOUIS MALAYBALAY
3BBE: THE FUTURE OF ECOMMERCE PRESENTATION - LOUIS MALAYBALAY3BBE: THE FUTURE OF ECOMMERCE PRESENTATION - LOUIS MALAYBALAY
3BBE: THE FUTURE OF ECOMMERCE PRESENTATION - LOUIS MALAYBALAYLouis Malaybalay
 
10 Tips for Great Teams CSUN Conference 2024
10 Tips for Great Teams CSUN Conference 202410 Tips for Great Teams CSUN Conference 2024
10 Tips for Great Teams CSUN Conference 2024Nate Evans
 
14 march 2024-capital-markets-update eni.pdf
14 march 2024-capital-markets-update eni.pdf14 march 2024-capital-markets-update eni.pdf
14 march 2024-capital-markets-update eni.pdfEni
 
Dashboards y paneles - CP Home - Area de Operaciones
Dashboards y paneles - CP Home - Area de OperacionesDashboards y paneles - CP Home - Area de Operaciones
Dashboards y paneles - CP Home - Area de OperacionesLPI ONG
 
NVIDIA's overall business overview Presentation.pptx
NVIDIA's overall business overview Presentation.pptxNVIDIA's overall business overview Presentation.pptx
NVIDIA's overall business overview Presentation.pptxKrutik Rakade
 
The Vietnam Believer_Newsletter_Vol.001_Mar12 2024
The Vietnam Believer_Newsletter_Vol.001_Mar12 2024The Vietnam Believer_Newsletter_Vol.001_Mar12 2024
The Vietnam Believer_Newsletter_Vol.001_Mar12 2024believeminhh
 
Reframing Requirements: A Strategic Approach to Requirement Definition, with ...
Reframing Requirements: A Strategic Approach to Requirement Definition, with ...Reframing Requirements: A Strategic Approach to Requirement Definition, with ...
Reframing Requirements: A Strategic Approach to Requirement Definition, with ...Jake Truemper
 
"InShorts: A Game-Changer in the Digital News Age"
"InShorts: A Game-Changer in the Digital News Age""InShorts: A Game-Changer in the Digital News Age"
"InShorts: A Game-Changer in the Digital News Age"Adharsh45
 
We are inviting you on board, to move forward together in the Right Direction
We are inviting you on board, to move forward together in the Right DirectionWe are inviting you on board, to move forward together in the Right Direction
We are inviting you on board, to move forward together in the Right DirectionRight Direction Aero
 
The Smart Bridge Interview now Veranda Learning
The Smart Bridge Interview now Veranda LearningThe Smart Bridge Interview now Veranda Learning
The Smart Bridge Interview now Veranda LearningNaval Singh
 
0311 National Accounts Online Giving Trends.pdf
0311 National Accounts Online Giving Trends.pdf0311 National Accounts Online Giving Trends.pdf
0311 National Accounts Online Giving Trends.pdfBloomerang
 
How The Hustle Milestone Referral Program Got 300K Subscribers
How The Hustle Milestone Referral Program Got 300K SubscribersHow The Hustle Milestone Referral Program Got 300K Subscribers
How The Hustle Milestone Referral Program Got 300K SubscribersFlyyx Tech
 

Recently uploaded (20)

Project Work on Consumer Behavior in Fast Food Restaurants. Their behavior to...
Project Work on Consumer Behavior in Fast Food Restaurants. Their behavior to...Project Work on Consumer Behavior in Fast Food Restaurants. Their behavior to...
Project Work on Consumer Behavior in Fast Food Restaurants. Their behavior to...
 
Green Innovations: Wristbands Ireland's Eco-Friendly Products
Green Innovations: Wristbands Ireland's Eco-Friendly ProductsGreen Innovations: Wristbands Ireland's Eco-Friendly Products
Green Innovations: Wristbands Ireland's Eco-Friendly Products
 
Presented by Sabri international .......
Presented by Sabri international .......Presented by Sabri international .......
Presented by Sabri international .......
 
Strategic Resources Corporate Presentation - March 2024 Update
Strategic Resources Corporate Presentation - March 2024 UpdateStrategic Resources Corporate Presentation - March 2024 Update
Strategic Resources Corporate Presentation - March 2024 Update
 
Record of Module Forensic photography in
Record of Module Forensic photography inRecord of Module Forensic photography in
Record of Module Forensic photography in
 
Business Models and Business Model Innovation
Business Models and Business Model InnovationBusiness Models and Business Model Innovation
Business Models and Business Model Innovation
 
Bus Eth ch3 ppt.ppt business ethics and corporate social responsibilities ppt
Bus Eth ch3 ppt.ppt business ethics and corporate social responsibilities pptBus Eth ch3 ppt.ppt business ethics and corporate social responsibilities ppt
Bus Eth ch3 ppt.ppt business ethics and corporate social responsibilities ppt
 
Wallet Pitch for startup fintech and loan
Wallet Pitch for startup fintech and loanWallet Pitch for startup fintech and loan
Wallet Pitch for startup fintech and loan
 
3BBE: THE FUTURE OF ECOMMERCE PRESENTATION - LOUIS MALAYBALAY
3BBE: THE FUTURE OF ECOMMERCE PRESENTATION - LOUIS MALAYBALAY3BBE: THE FUTURE OF ECOMMERCE PRESENTATION - LOUIS MALAYBALAY
3BBE: THE FUTURE OF ECOMMERCE PRESENTATION - LOUIS MALAYBALAY
 
10 Tips for Great Teams CSUN Conference 2024
10 Tips for Great Teams CSUN Conference 202410 Tips for Great Teams CSUN Conference 2024
10 Tips for Great Teams CSUN Conference 2024
 
14 march 2024-capital-markets-update eni.pdf
14 march 2024-capital-markets-update eni.pdf14 march 2024-capital-markets-update eni.pdf
14 march 2024-capital-markets-update eni.pdf
 
Dashboards y paneles - CP Home - Area de Operaciones
Dashboards y paneles - CP Home - Area de OperacionesDashboards y paneles - CP Home - Area de Operaciones
Dashboards y paneles - CP Home - Area de Operaciones
 
NVIDIA's overall business overview Presentation.pptx
NVIDIA's overall business overview Presentation.pptxNVIDIA's overall business overview Presentation.pptx
NVIDIA's overall business overview Presentation.pptx
 
The Vietnam Believer_Newsletter_Vol.001_Mar12 2024
The Vietnam Believer_Newsletter_Vol.001_Mar12 2024The Vietnam Believer_Newsletter_Vol.001_Mar12 2024
The Vietnam Believer_Newsletter_Vol.001_Mar12 2024
 
Reframing Requirements: A Strategic Approach to Requirement Definition, with ...
Reframing Requirements: A Strategic Approach to Requirement Definition, with ...Reframing Requirements: A Strategic Approach to Requirement Definition, with ...
Reframing Requirements: A Strategic Approach to Requirement Definition, with ...
 
"InShorts: A Game-Changer in the Digital News Age"
"InShorts: A Game-Changer in the Digital News Age""InShorts: A Game-Changer in the Digital News Age"
"InShorts: A Game-Changer in the Digital News Age"
 
We are inviting you on board, to move forward together in the Right Direction
We are inviting you on board, to move forward together in the Right DirectionWe are inviting you on board, to move forward together in the Right Direction
We are inviting you on board, to move forward together in the Right Direction
 
The Smart Bridge Interview now Veranda Learning
The Smart Bridge Interview now Veranda LearningThe Smart Bridge Interview now Veranda Learning
The Smart Bridge Interview now Veranda Learning
 
0311 National Accounts Online Giving Trends.pdf
0311 National Accounts Online Giving Trends.pdf0311 National Accounts Online Giving Trends.pdf
0311 National Accounts Online Giving Trends.pdf
 
How The Hustle Milestone Referral Program Got 300K Subscribers
How The Hustle Milestone Referral Program Got 300K SubscribersHow The Hustle Milestone Referral Program Got 300K Subscribers
How The Hustle Milestone Referral Program Got 300K Subscribers
 

Microsoft SQL Server Continuous Integration

  • 1. SQL Server Continuous Integration Using Jenkins and Red Gate Ernest Hwang Principal Software Engineer, Practice Fusion Presented to the Silicon Valley SQL Server User Group May 2013
  • 2. Who am I? • Ernest Hwang, Principal Software Engineer at Practice Fusion in San Francisco • C#, .NET, SQL Server Developer • Working with SQL Server since 1999 (SQL Server 6.x) • Using Red Gate for Continuous Integration since June 2011 • Someone who got tired of maintaining a folder of database scripts for every release.
  • 3. Why am I here? • To show how easy it is to apply CI principals to Database Development
  • 4. What’s this? A “How To” guide for automating your day-to-day maintenance tasks by… • *Easily* Versioning your database using Red Gate SQL Source Control • Using Continuous Integration (via Jenkins) to validate Database builds • Using Jenkins to automate database deployments • Using CI / tSQLt / SQL Test to run unit tests
  • 5. Prerequisites • Experience with source control management systems (svn, TFS, git, Hg) • Familiarity with Continuous Integration Products (Jenkins, Team City, Cruise Control) • Awareness of build scripting languages (ant, MSBuild)
  • 6. What software is used? • SQL Server 2008 / 2012 • Red Gate SQL Source Control • Red Gate SQL Compare • Red Gate SQL Data Compare • Jenkins Continuous Integration Server http://jenkins-ci.org/ – Promoted Build Plugin – Copy Artifacts Plugin – Version Plugin – Git Plugin • MSBuild
  • 7. What is SQL Source Control? • Source Control Plug in for SQL Server Management Studio • Creates a “snapshot” of the database schema defined by CREATE scripts • Snapshot is consumable by SQL Compare and may be used to compare against the schema of an actual database
  • 8. What is SQL Source Control? SQL Source Control is to SSMS as TortoiseSVN is to Windows Explorer
  • 9. Demo Environment GitHub Local Copy of RGDemo database Dev, QA, & Prod Databases Developer Workstation Build and Database Server (Windows Azure VM)
  • 10. Committing Changes to Source Control Demo Local Database Changes Commit Changes via SQL Source Control Changes committed to SCM repository
  • 11. How does Continuous Integration fit in? • The CI server polls the repository for changes • When changes are checked in, the CI job kicks off – Verifies that the database can be built • Builds a brand new database from scratch using SQL Compare and SQL Data Compare – Runs unit tests • Build should fail if unit tests do not succeed – Archives the artifacts (for deployments) • Artifacts can include build/test reports – Emails engineers if there are problems
  • 12. Breaking the Build Demo Commit Changes via SQL Source Control CI Server Detects Changes Kicks off Build Process Build can pass or fail validation
  • 13. Deploying Changes to Different Environments • The Promoted Builds plug in can be used to deploy changes to Integration/QA/Staging/Production environments • SQL Compare and SQL Data Compare are used to deploy changes between the sourced controlled database and your development environments • Deploying to Production and/or Staging can be configured to just create the scripts as opposed to forcing the synchronization
  • 14. Deploying to Dev / QA Demo SQL Source Control Repository SQL Compare Development Environment Database
  • 15. Creating a Database “Version” • Make sure the Jenkins Versioning plugin is installed • Create a User Defined Function called dbo.DATABASE_VERSION() • Create a build step that updates the UDF with the version number • The updated UDF will be archived and used with deployments
  • 16. Versioning your Database Demo Commit Changes via SQL Source Control CI Server Detects Changes Kicks off Build Process Build Task updates dbo.DATABASE_VERSION() Function Updated UDF Archived
  • 17. Unit Testing with tSQLt and SQL Test • tSQLt is an open source set of stored procedures and functions to facilitate Unit Testing in SQL databases • SQL Test is a wrapper around this framework that integrates with SSMS • Your build server can enforce that your unit tests pass and generate reports
  • 18. Unit Testing with tSQLt / SQL Test Demo Commit Changes via SQL Source Control CI Server Detects Changes Kicks off Build Process Build Task executes Unit Test procedures Tests can pass or fail the build
  • 19. What did SQL Source Control do for us? • Saves developers time (yay!) – No more maintaining update scripts – Don’t need to deploy scripts when QA needs changes • Identifies holes in your deployment process – Are developers making changes directly to production? – Are indexes/constraints missing from your Dev/QA/Prod environments? • Creates a definitive database build that can be easily deployed and redeployed
  • 20. Considerations • Environments must be pristine • Process must be changed and understood • Production change scripts should be scrutinized (especially for the first few releases) • Migration Scripts can be used to massage data • DB Replication requires more work for promotions
  • 21. Appendix 1: Other CI Servers • Jenkins (open source) • Team City (JetBrains) • Bamboo (Atlassian) • CruiseControl / CruiseControl.NET (open source)
  • 22. Appendix 2: Other Source Control Systems • Subversion (SVN) • Git • Team Foundation Server • Mercurial (Hg) • AccuRev • Surround SCM
  • 23. Links / Contact Info • ehwang@practicefusion.com • @ernestedcode • https://github.com/CF9/Databases.RGDemo • Practice Fusion is Hiring (email me) http://practicefusion.com/careers/ • facebook.com/practicefusion • @practicefusion