SlideShare a Scribd company logo
DevOps, Continuous
Integration &
Database Lifecycle
Management
By : Muhammad Hashir
Issues with traditional Database
Development
1. Data is hard to manage. You can overwrite and redeploy your code from scratch every time, but
the same cannot be done with data. Databases have existing data which needs to be persisted
and hence it cannot be wiped off and recreated during deployments – just like the code.
2. Having no Source Control for your database objects implies that there is no versioning information
available for your database changes. Production is the version and system of truth. This makes it
almost impossible to unit test your changes and automate your deployments.
3. Developers must spend a lot of time in creating the DML scripts. They also need to account for the
tedious rollback scripts, in case the overall deployment had to be rolled back. The Database
Administrator must spend a lot of time in executing scripts against multiple environments.
4. Removing manual work and inefficiencies in your database development process is one of the
reasons why organizations are moving towards automating their database deployments.
DATABASE Continuous Integration LIFECYCLE MANAGEMENT
OVERVIEW
Coding Unit Testing
Static Code
Analysis
Source Control
Continuous
Integration
Continuous
Deployment
SHIFT LEFT – Catch issues as early as possible
Static Code Analyzer
• Free and No Installation required
• Need to have VIEW SERVER STATE or
VIEW DATABASE STATE permission
• SQL Cop has a set of predefined rules
to identify anti-patterns for database
development
1. SQLCOP
• Continuous Inspection of Code Quality
• Identify Code Smells, Bugs and
Vulnerabilities
• Analyzers for 20+ Programming Languages
• DevOps Integration
• Reject Check-in when Quality Gate not met
2. Sonar Cube
Database Deployment
Approaches
Source Control is the system of truth
COMPARE
SSDT SCHEMA
COMPARE
GENERATE
EXECUTE
DIFFERENCE SCRIPT
DATABASE PROJECT/
DACPAC
1. STATE BASED APPROACH DACPAC is a self-contained
deployment file which is used for
deploying SQL Server objects to an
instance of SQL Server. You can
also think of DACPAC to be like a
database snapshot file, which can
serve as the in-memory
representation of database objects
and can be stored for maintaining
version history.
When the DACPAC is deployed, it
uses the information in the
DACPAC file as the source database
schema. It compares this with the
defined target to generate an
appropriate change script, which is
then executed against the target to
sync up both the environments.
Database is the system of truth
STATE 1 STATE 2 STATE 3 STATE N
Migration 1 Migration 2 Migration N
2. Migration BASED APPROACH
STATE BASED APPROACH
1. System of Truth is the Source Code
2. Suited for frequent database changes
3. Suited for large sized teams
4. Source Code contains the current
state of the database
5. Less control on the migration script
6. Complex refactoring might take
multiple steps
MIGRATION BASED APPROACH
1. System of Truth is the Database
2. Suited for infrequent database changes
3. Suited for small sized teams
4. Must maintain a long list of migration
scripts within the source control.
5. More fine grain control on the
migration script
6. Complex refactoring can be handled by
a single script
DATABASE DRIFTS
Any change to the database schema or reference data
that was made directly in the database environment,
outside of the normal automated delivery pipeline.
Ensure that all migrations are idempotent – meaning that
running a script more than once has no additional impact.
 Renaming a Column/Table/Stored Procedure/ View/ Function
 Dropping a Column/Table
 Remove a Stored Procedure/ View/ Function
 Moving a Column from one table
 Adding a Column/Table/Stored Procedure/ View/ Function
Non-Breaking Database Change
Breaking Database Change
BACKWARDS COMPATIBILITY
Expand
Transition
Phase
Contract
Initial Refactoring
is applied
Perform Cleanup to
complete the refactoring
Data is migrated
EXPAND AND CONTRACT PATTERN
SQL Server Data Tools in
Visual Studio 2017
Red gate Data Tools in Visual Studio 2017
1. Ready Roll Core: allows you to develop, source control, and safely automate deployments of
database changes alongside application changes. Ready Roll Core is available in the
Enterprise edition of Visual Studio 2017.
2. SQL Prompt Core: offers advanced code completion for SQL. SQL Prompt Core is available in
the Enterprise edition of Visual Studio 2017.
3. SQL Search: lets you find SQL objects fast and easily explore across databases. SQL Search is
available in all editions of Visual Studio 2017.
VERSION
CONTROL
DISCONNECTED
DATABASE
DEVELOPMENT
DECLARATIVE
APPROACH
DACPAC
DEPLOPYMENTS
CODE EDITING
EXPERIENCE
SCHEMA & DATA
COMPARE
SQL SERVER
DATA TOOLS
SSDT INSTALLATION IN VISUAL STUDIO 2017
REDGATE
READYROLL
INCREMENTAL
CHANGE
SHIFT LEFT
MORE CONTROL
ON MIGRATION
SCRIPT
HYBRID
APPROACH
PROGRAMMABLE
OBJECTS
OFFLINE
SCHEMA MODEL
READYROLL INSTALLATION IN VISUAL STUDIO 2017
Flyway is free and open source.
Flyway facilitates the Automatic Deployment of database changes.
Flyway creates a table name ‘schema_version‘ in your database.
Flyway supports number of databases – Oracle, SQL Server, MySQL,
PostgreSQL, MariaDB, SQLite, Redshift and more.
• Changes made to same file causes Merge
Conflict.
MAIN
BRANCH 1 BRANCH 2
• Conflicts are caused by merge issues
because of long running feature branches.
Resolving Merge Conflicts is Error Prone and Time Consuming.
• Longer running features has the
potential to create Merge issues.
File1.cs File2.cs
7 Changes 5 Changes
Reverse Integration
BRANCHING STRATEGY
FEATURE FLAGS
• Potential alternative to maintaining multiple feature
branches.
• Reduces the need for constant branching and
merging.
• Enables releases with unfinished features at no risk.
Azure DevOps Release
CODE BUILD INTEGRATE RELEASE DEPLOY
TEST
Continuous Integration
Continuous Delivery
Continuous Deployment
RELEASE PIPELINE
SOURCE CONTROL
DEVELOPMENT CONTINUOUS INTEGRATION
BUILD
TEST
Feedback Mechanism
Trigger
CODE STAGING PRODUCTION
CONTINUOUS DELIVERY
CONTINUOUS DEPLOYMENT
BUILD
CODE STAGING PRODUCTION
BUILD
Auto Auto
Auto Auto Auto
Manual
RESOURCES
• Continuous Integration with SQL Server Data Tools in Visual Studio 2017
• Database Static Code Analysis using SQL Cop
• Managing your Technical Debt using SonarQube
• Continuous Integration, Continuous Delivery and Continuous Deployment
Ad

More Related Content

What's hot (20)

Devops Devops Devops, at Froscon
Devops Devops Devops, at FrosconDevops Devops Devops, at Froscon
Devops Devops Devops, at Froscon
Kris Buytaert
 
Continues Integration and Continuous Delivery with Azure DevOps - Deploy Anyt...
Continues Integration and Continuous Delivery with Azure DevOps - Deploy Anyt...Continues Integration and Continuous Delivery with Azure DevOps - Deploy Anyt...
Continues Integration and Continuous Delivery with Azure DevOps - Deploy Anyt...
Janusz Nowak
 
A Crash Course in Building Site Reliability
A Crash Course in Building Site ReliabilityA Crash Course in Building Site Reliability
A Crash Course in Building Site Reliability
Acquia
 
Introduction to kubernetes
Introduction to kubernetesIntroduction to kubernetes
Introduction to kubernetes
Michal Cwienczek
 
DevSecOps 101
DevSecOps 101DevSecOps 101
DevSecOps 101
Narudom Roongsiriwong, CISSP
 
SRE Demystified - 05 - Toil Elimination
SRE Demystified - 05 - Toil EliminationSRE Demystified - 05 - Toil Elimination
SRE Demystified - 05 - Toil Elimination
Dr Ganesh Iyer
 
DevOps - A Gentle Introduction
DevOps - A Gentle IntroductionDevOps - A Gentle Introduction
DevOps - A Gentle Introduction
CodeOps Technologies LLP
 
DevOps 101 - an Introduction to DevOps
DevOps 101  - an Introduction to DevOpsDevOps 101  - an Introduction to DevOps
DevOps 101 - an Introduction to DevOps
Red Gate Software
 
Microservices, DevOps & SRE
Microservices, DevOps & SREMicroservices, DevOps & SRE
Microservices, DevOps & SRE
Araf Karsh Hamid
 
Scaling DevSecOps Culture for Enterprise
Scaling DevSecOps Culture for EnterpriseScaling DevSecOps Culture for Enterprise
Scaling DevSecOps Culture for Enterprise
Opsta
 
Getting Started with Infrastructure as Code
Getting Started with Infrastructure as CodeGetting Started with Infrastructure as Code
Getting Started with Infrastructure as Code
WinWire Technologies Inc
 
Azure DevOps
Azure DevOpsAzure DevOps
Azure DevOps
Felipe Artur Feltes
 
What is Disaster Recovery as a Service?
What is Disaster Recovery as a Service?What is Disaster Recovery as a Service?
What is Disaster Recovery as a Service?
Infrascale
 
Integration Patterns and Anti-Patterns for Microservices Architectures
Integration Patterns and Anti-Patterns for Microservices ArchitecturesIntegration Patterns and Anti-Patterns for Microservices Architectures
Integration Patterns and Anti-Patterns for Microservices Architectures
Apcera
 
Modern CI/CD Pipeline Using Azure DevOps
Modern CI/CD Pipeline Using Azure DevOpsModern CI/CD Pipeline Using Azure DevOps
Modern CI/CD Pipeline Using Azure DevOps
GlobalLogic Ukraine
 
Introduction to DevOps
Introduction to DevOpsIntroduction to DevOps
Introduction to DevOps
Ravindu Fernando
 
Azure kubernetes service (aks)
Azure kubernetes service (aks)Azure kubernetes service (aks)
Azure kubernetes service (aks)
Akash Agrawal
 
Azure devops
Azure devopsAzure devops
Azure devops
Mohit Chhabra
 
AWS ELB
AWS ELBAWS ELB
AWS ELB
Mahesh Raj
 
Simplify DevOps with Microservices and Mobile Backends.pptx
Simplify DevOps with Microservices and Mobile Backends.pptxSimplify DevOps with Microservices and Mobile Backends.pptx
Simplify DevOps with Microservices and Mobile Backends.pptx
ssuser5faa791
 
Devops Devops Devops, at Froscon
Devops Devops Devops, at FrosconDevops Devops Devops, at Froscon
Devops Devops Devops, at Froscon
Kris Buytaert
 
Continues Integration and Continuous Delivery with Azure DevOps - Deploy Anyt...
Continues Integration and Continuous Delivery with Azure DevOps - Deploy Anyt...Continues Integration and Continuous Delivery with Azure DevOps - Deploy Anyt...
Continues Integration and Continuous Delivery with Azure DevOps - Deploy Anyt...
Janusz Nowak
 
A Crash Course in Building Site Reliability
A Crash Course in Building Site ReliabilityA Crash Course in Building Site Reliability
A Crash Course in Building Site Reliability
Acquia
 
Introduction to kubernetes
Introduction to kubernetesIntroduction to kubernetes
Introduction to kubernetes
Michal Cwienczek
 
SRE Demystified - 05 - Toil Elimination
SRE Demystified - 05 - Toil EliminationSRE Demystified - 05 - Toil Elimination
SRE Demystified - 05 - Toil Elimination
Dr Ganesh Iyer
 
DevOps 101 - an Introduction to DevOps
DevOps 101  - an Introduction to DevOpsDevOps 101  - an Introduction to DevOps
DevOps 101 - an Introduction to DevOps
Red Gate Software
 
Microservices, DevOps & SRE
Microservices, DevOps & SREMicroservices, DevOps & SRE
Microservices, DevOps & SRE
Araf Karsh Hamid
 
Scaling DevSecOps Culture for Enterprise
Scaling DevSecOps Culture for EnterpriseScaling DevSecOps Culture for Enterprise
Scaling DevSecOps Culture for Enterprise
Opsta
 
Getting Started with Infrastructure as Code
Getting Started with Infrastructure as CodeGetting Started with Infrastructure as Code
Getting Started with Infrastructure as Code
WinWire Technologies Inc
 
What is Disaster Recovery as a Service?
What is Disaster Recovery as a Service?What is Disaster Recovery as a Service?
What is Disaster Recovery as a Service?
Infrascale
 
Integration Patterns and Anti-Patterns for Microservices Architectures
Integration Patterns and Anti-Patterns for Microservices ArchitecturesIntegration Patterns and Anti-Patterns for Microservices Architectures
Integration Patterns and Anti-Patterns for Microservices Architectures
Apcera
 
Modern CI/CD Pipeline Using Azure DevOps
Modern CI/CD Pipeline Using Azure DevOpsModern CI/CD Pipeline Using Azure DevOps
Modern CI/CD Pipeline Using Azure DevOps
GlobalLogic Ukraine
 
Azure kubernetes service (aks)
Azure kubernetes service (aks)Azure kubernetes service (aks)
Azure kubernetes service (aks)
Akash Agrawal
 
Simplify DevOps with Microservices and Mobile Backends.pptx
Simplify DevOps with Microservices and Mobile Backends.pptxSimplify DevOps with Microservices and Mobile Backends.pptx
Simplify DevOps with Microservices and Mobile Backends.pptx
ssuser5faa791
 

Similar to Database CI/CD Pipeline (20)

KoprowskiT_Session2_SDNEvent_SourceControlForDBA
KoprowskiT_Session2_SDNEvent_SourceControlForDBAKoprowskiT_Session2_SDNEvent_SourceControlForDBA
KoprowskiT_Session2_SDNEvent_SourceControlForDBA
Tobias Koprowski
 
Azure DevOps for the Data Professional
Azure DevOps for the Data ProfessionalAzure DevOps for the Data Professional
Azure DevOps for the Data Professional
Sarah Dutkiewicz
 
Database CI Demo Using Sql Server
Database CI  Demo Using Sql ServerDatabase CI  Demo Using Sql Server
Database CI Demo Using Sql Server
Umesh Kumar
 
Kelly potvin nosurprises_odtug_oow12
Kelly potvin nosurprises_odtug_oow12Kelly potvin nosurprises_odtug_oow12
Kelly potvin nosurprises_odtug_oow12
Enkitec
 
1 extreme performance - part i
1   extreme performance - part i1   extreme performance - part i
1 extreme performance - part i
sqlserver.co.il
 
Sql server 2019 New Features by Yevhen Nedaskivskyi
Sql server 2019 New Features by Yevhen NedaskivskyiSql server 2019 New Features by Yevhen Nedaskivskyi
Sql server 2019 New Features by Yevhen Nedaskivskyi
Alex Tumanoff
 
Delivering changes for databases and applications
Delivering changes for databases and applicationsDelivering changes for databases and applications
Delivering changes for databases and applications
Eduardo Piairo
 
SSDT unleashed
SSDT unleashedSSDT unleashed
SSDT unleashed
GomathiNayagam S
 
DB Change Manager XE6 Datasheet - The Essential Schema and Data Synchronizati...
DB Change Manager XE6 Datasheet - The Essential Schema and Data Synchronizati...DB Change Manager XE6 Datasheet - The Essential Schema and Data Synchronizati...
DB Change Manager XE6 Datasheet - The Essential Schema and Data Synchronizati...
Embarcadero Technologies
 
Exploring Scalability, Performance And Deployment
Exploring Scalability, Performance And DeploymentExploring Scalability, Performance And Deployment
Exploring Scalability, Performance And Deployment
rsnarayanan
 
DevOps Drive-In: Automate Database Deployments in Your Continuous Delivery Pi...
DevOps Drive-In: Automate Database Deployments in Your Continuous Delivery Pi...DevOps Drive-In: Automate Database Deployments in Your Continuous Delivery Pi...
DevOps Drive-In: Automate Database Deployments in Your Continuous Delivery Pi...
Serena Software
 
Migrate SQL Workloads to Azure
Migrate SQL Workloads to AzureMigrate SQL Workloads to Azure
Migrate SQL Workloads to Azure
Antonios Chatzipavlis
 
Copy Data Management for the DBA
Copy Data Management for the DBACopy Data Management for the DBA
Copy Data Management for the DBA
Kellyn Pot'Vin-Gorman
 
Борис Трофимов. Continuous Database migration-это просто!
Борис Трофимов. Continuous Database migration-это просто!Борис Трофимов. Continuous Database migration-это просто!
Борис Трофимов. Continuous Database migration-это просто!
Volha Banadyseva
 
Continuous DB migration based on carbon5 framework
Continuous DB migration based on carbon5 frameworkContinuous DB migration based on carbon5 framework
Continuous DB migration based on carbon5 framework
b0ris_1
 
Why you should(n't) run your databases in the cloud
Why you should(n't) run your databases in the cloudWhy you should(n't) run your databases in the cloud
Why you should(n't) run your databases in the cloud
Microsoft TechNet - Belgium and Luxembourg
 
Azure DevOps for Developers
Azure DevOps for DevelopersAzure DevOps for Developers
Azure DevOps for Developers
Sarah Dutkiewicz
 
Level up your deployments for SQL Source Control
Level up your deployments for SQL Source Control Level up your deployments for SQL Source Control
Level up your deployments for SQL Source Control
Red Gate Software
 
Using MS-SQL Server with Visual DataFlex
Using MS-SQL Server with Visual DataFlexUsing MS-SQL Server with Visual DataFlex
Using MS-SQL Server with Visual DataFlex
webhostingguy
 
ScaleBase Webinar: Scaling MySQL - Sharding Made Easy!
ScaleBase Webinar: Scaling MySQL - Sharding Made Easy!ScaleBase Webinar: Scaling MySQL - Sharding Made Easy!
ScaleBase Webinar: Scaling MySQL - Sharding Made Easy!
ScaleBase
 
KoprowskiT_Session2_SDNEvent_SourceControlForDBA
KoprowskiT_Session2_SDNEvent_SourceControlForDBAKoprowskiT_Session2_SDNEvent_SourceControlForDBA
KoprowskiT_Session2_SDNEvent_SourceControlForDBA
Tobias Koprowski
 
Azure DevOps for the Data Professional
Azure DevOps for the Data ProfessionalAzure DevOps for the Data Professional
Azure DevOps for the Data Professional
Sarah Dutkiewicz
 
Database CI Demo Using Sql Server
Database CI  Demo Using Sql ServerDatabase CI  Demo Using Sql Server
Database CI Demo Using Sql Server
Umesh Kumar
 
Kelly potvin nosurprises_odtug_oow12
Kelly potvin nosurprises_odtug_oow12Kelly potvin nosurprises_odtug_oow12
Kelly potvin nosurprises_odtug_oow12
Enkitec
 
1 extreme performance - part i
1   extreme performance - part i1   extreme performance - part i
1 extreme performance - part i
sqlserver.co.il
 
Sql server 2019 New Features by Yevhen Nedaskivskyi
Sql server 2019 New Features by Yevhen NedaskivskyiSql server 2019 New Features by Yevhen Nedaskivskyi
Sql server 2019 New Features by Yevhen Nedaskivskyi
Alex Tumanoff
 
Delivering changes for databases and applications
Delivering changes for databases and applicationsDelivering changes for databases and applications
Delivering changes for databases and applications
Eduardo Piairo
 
DB Change Manager XE6 Datasheet - The Essential Schema and Data Synchronizati...
DB Change Manager XE6 Datasheet - The Essential Schema and Data Synchronizati...DB Change Manager XE6 Datasheet - The Essential Schema and Data Synchronizati...
DB Change Manager XE6 Datasheet - The Essential Schema and Data Synchronizati...
Embarcadero Technologies
 
Exploring Scalability, Performance And Deployment
Exploring Scalability, Performance And DeploymentExploring Scalability, Performance And Deployment
Exploring Scalability, Performance And Deployment
rsnarayanan
 
DevOps Drive-In: Automate Database Deployments in Your Continuous Delivery Pi...
DevOps Drive-In: Automate Database Deployments in Your Continuous Delivery Pi...DevOps Drive-In: Automate Database Deployments in Your Continuous Delivery Pi...
DevOps Drive-In: Automate Database Deployments in Your Continuous Delivery Pi...
Serena Software
 
Борис Трофимов. Continuous Database migration-это просто!
Борис Трофимов. Continuous Database migration-это просто!Борис Трофимов. Continuous Database migration-это просто!
Борис Трофимов. Continuous Database migration-это просто!
Volha Banadyseva
 
Continuous DB migration based on carbon5 framework
Continuous DB migration based on carbon5 frameworkContinuous DB migration based on carbon5 framework
Continuous DB migration based on carbon5 framework
b0ris_1
 
Azure DevOps for Developers
Azure DevOps for DevelopersAzure DevOps for Developers
Azure DevOps for Developers
Sarah Dutkiewicz
 
Level up your deployments for SQL Source Control
Level up your deployments for SQL Source Control Level up your deployments for SQL Source Control
Level up your deployments for SQL Source Control
Red Gate Software
 
Using MS-SQL Server with Visual DataFlex
Using MS-SQL Server with Visual DataFlexUsing MS-SQL Server with Visual DataFlex
Using MS-SQL Server with Visual DataFlex
webhostingguy
 
ScaleBase Webinar: Scaling MySQL - Sharding Made Easy!
ScaleBase Webinar: Scaling MySQL - Sharding Made Easy!ScaleBase Webinar: Scaling MySQL - Sharding Made Easy!
ScaleBase Webinar: Scaling MySQL - Sharding Made Easy!
ScaleBase
 
Ad

Recently uploaded (20)

Orion Context Broker introduction 20250509
Orion Context Broker introduction 20250509Orion Context Broker introduction 20250509
Orion Context Broker introduction 20250509
Fermin Galan
 
How to Install and Activate ListGrabber Plugin
How to Install and Activate ListGrabber PluginHow to Install and Activate ListGrabber Plugin
How to Install and Activate ListGrabber Plugin
eGrabber
 
Digital Twins Software Service in Belfast
Digital Twins Software Service in BelfastDigital Twins Software Service in Belfast
Digital Twins Software Service in Belfast
julia smits
 
Wilcom Embroidery Studio Crack Free Latest 2025
Wilcom Embroidery Studio Crack Free Latest 2025Wilcom Embroidery Studio Crack Free Latest 2025
Wilcom Embroidery Studio Crack Free Latest 2025
Web Designer
 
The-Future-is-Hybrid-Exploring-Azure’s-Role-in-Multi-Cloud-Strategies.pptx
The-Future-is-Hybrid-Exploring-Azure’s-Role-in-Multi-Cloud-Strategies.pptxThe-Future-is-Hybrid-Exploring-Azure’s-Role-in-Multi-Cloud-Strategies.pptx
The-Future-is-Hybrid-Exploring-Azure’s-Role-in-Multi-Cloud-Strategies.pptx
james brownuae
 
sequencediagrams.pptx software Engineering
sequencediagrams.pptx software Engineeringsequencediagrams.pptx software Engineering
sequencediagrams.pptx software Engineering
aashrithakondapalli8
 
Download MathType Crack Version 2025???
Download MathType Crack  Version 2025???Download MathType Crack  Version 2025???
Download MathType Crack Version 2025???
Google
 
Passive House Canada Conference 2025 Presentation [Final]_v4.ppt
Passive House Canada Conference 2025 Presentation [Final]_v4.pptPassive House Canada Conference 2025 Presentation [Final]_v4.ppt
Passive House Canada Conference 2025 Presentation [Final]_v4.ppt
IES VE
 
wAIred_LearnWithOutAI_JCON_14052025.pptx
wAIred_LearnWithOutAI_JCON_14052025.pptxwAIred_LearnWithOutAI_JCON_14052025.pptx
wAIred_LearnWithOutAI_JCON_14052025.pptx
SimonedeGijt
 
Exchange Migration Tool- Shoviv Software
Exchange Migration Tool- Shoviv SoftwareExchange Migration Tool- Shoviv Software
Exchange Migration Tool- Shoviv Software
Shoviv Software
 
Robotic Process Automation (RPA) Software Development Services.pptx
Robotic Process Automation (RPA) Software Development Services.pptxRobotic Process Automation (RPA) Software Development Services.pptx
Robotic Process Automation (RPA) Software Development Services.pptx
julia smits
 
Memory Management and Leaks in Postgres from pgext.day 2025
Memory Management and Leaks in Postgres from pgext.day 2025Memory Management and Leaks in Postgres from pgext.day 2025
Memory Management and Leaks in Postgres from pgext.day 2025
Phil Eaton
 
GC Tuning: A Masterpiece in Performance Engineering
GC Tuning: A Masterpiece in Performance EngineeringGC Tuning: A Masterpiece in Performance Engineering
GC Tuning: A Masterpiece in Performance Engineering
Tier1 app
 
Adobe Audition Crack FRESH Version 2025 FREE
Adobe Audition Crack FRESH Version 2025 FREEAdobe Audition Crack FRESH Version 2025 FREE
Adobe Audition Crack FRESH Version 2025 FREE
zafranwaqar90
 
From Vibe Coding to Vibe Testing - Complete PowerPoint Presentation
From Vibe Coding to Vibe Testing - Complete PowerPoint PresentationFrom Vibe Coding to Vibe Testing - Complete PowerPoint Presentation
From Vibe Coding to Vibe Testing - Complete PowerPoint Presentation
Shay Ginsbourg
 
What Do Candidates Really Think About AI-Powered Recruitment Tools?
What Do Candidates Really Think About AI-Powered Recruitment Tools?What Do Candidates Really Think About AI-Powered Recruitment Tools?
What Do Candidates Really Think About AI-Powered Recruitment Tools?
HireME
 
How to Troubleshoot 9 Types of OutOfMemoryError
How to Troubleshoot 9 Types of OutOfMemoryErrorHow to Troubleshoot 9 Types of OutOfMemoryError
How to Troubleshoot 9 Types of OutOfMemoryError
Tier1 app
 
Beyond the code. Complexity - 2025.05 - SwiftCraft
Beyond the code. Complexity - 2025.05 - SwiftCraftBeyond the code. Complexity - 2025.05 - SwiftCraft
Beyond the code. Complexity - 2025.05 - SwiftCraft
Dmitrii Ivanov
 
Best HR and Payroll Software in Bangladesh - accordHRM
Best HR and Payroll Software in Bangladesh - accordHRMBest HR and Payroll Software in Bangladesh - accordHRM
Best HR and Payroll Software in Bangladesh - accordHRM
accordHRM
 
Adobe Media Encoder Crack FREE Download 2025
Adobe Media Encoder  Crack FREE Download 2025Adobe Media Encoder  Crack FREE Download 2025
Adobe Media Encoder Crack FREE Download 2025
zafranwaqar90
 
Orion Context Broker introduction 20250509
Orion Context Broker introduction 20250509Orion Context Broker introduction 20250509
Orion Context Broker introduction 20250509
Fermin Galan
 
How to Install and Activate ListGrabber Plugin
How to Install and Activate ListGrabber PluginHow to Install and Activate ListGrabber Plugin
How to Install and Activate ListGrabber Plugin
eGrabber
 
Digital Twins Software Service in Belfast
Digital Twins Software Service in BelfastDigital Twins Software Service in Belfast
Digital Twins Software Service in Belfast
julia smits
 
Wilcom Embroidery Studio Crack Free Latest 2025
Wilcom Embroidery Studio Crack Free Latest 2025Wilcom Embroidery Studio Crack Free Latest 2025
Wilcom Embroidery Studio Crack Free Latest 2025
Web Designer
 
The-Future-is-Hybrid-Exploring-Azure’s-Role-in-Multi-Cloud-Strategies.pptx
The-Future-is-Hybrid-Exploring-Azure’s-Role-in-Multi-Cloud-Strategies.pptxThe-Future-is-Hybrid-Exploring-Azure’s-Role-in-Multi-Cloud-Strategies.pptx
The-Future-is-Hybrid-Exploring-Azure’s-Role-in-Multi-Cloud-Strategies.pptx
james brownuae
 
sequencediagrams.pptx software Engineering
sequencediagrams.pptx software Engineeringsequencediagrams.pptx software Engineering
sequencediagrams.pptx software Engineering
aashrithakondapalli8
 
Download MathType Crack Version 2025???
Download MathType Crack  Version 2025???Download MathType Crack  Version 2025???
Download MathType Crack Version 2025???
Google
 
Passive House Canada Conference 2025 Presentation [Final]_v4.ppt
Passive House Canada Conference 2025 Presentation [Final]_v4.pptPassive House Canada Conference 2025 Presentation [Final]_v4.ppt
Passive House Canada Conference 2025 Presentation [Final]_v4.ppt
IES VE
 
wAIred_LearnWithOutAI_JCON_14052025.pptx
wAIred_LearnWithOutAI_JCON_14052025.pptxwAIred_LearnWithOutAI_JCON_14052025.pptx
wAIred_LearnWithOutAI_JCON_14052025.pptx
SimonedeGijt
 
Exchange Migration Tool- Shoviv Software
Exchange Migration Tool- Shoviv SoftwareExchange Migration Tool- Shoviv Software
Exchange Migration Tool- Shoviv Software
Shoviv Software
 
Robotic Process Automation (RPA) Software Development Services.pptx
Robotic Process Automation (RPA) Software Development Services.pptxRobotic Process Automation (RPA) Software Development Services.pptx
Robotic Process Automation (RPA) Software Development Services.pptx
julia smits
 
Memory Management and Leaks in Postgres from pgext.day 2025
Memory Management and Leaks in Postgres from pgext.day 2025Memory Management and Leaks in Postgres from pgext.day 2025
Memory Management and Leaks in Postgres from pgext.day 2025
Phil Eaton
 
GC Tuning: A Masterpiece in Performance Engineering
GC Tuning: A Masterpiece in Performance EngineeringGC Tuning: A Masterpiece in Performance Engineering
GC Tuning: A Masterpiece in Performance Engineering
Tier1 app
 
Adobe Audition Crack FRESH Version 2025 FREE
Adobe Audition Crack FRESH Version 2025 FREEAdobe Audition Crack FRESH Version 2025 FREE
Adobe Audition Crack FRESH Version 2025 FREE
zafranwaqar90
 
From Vibe Coding to Vibe Testing - Complete PowerPoint Presentation
From Vibe Coding to Vibe Testing - Complete PowerPoint PresentationFrom Vibe Coding to Vibe Testing - Complete PowerPoint Presentation
From Vibe Coding to Vibe Testing - Complete PowerPoint Presentation
Shay Ginsbourg
 
What Do Candidates Really Think About AI-Powered Recruitment Tools?
What Do Candidates Really Think About AI-Powered Recruitment Tools?What Do Candidates Really Think About AI-Powered Recruitment Tools?
What Do Candidates Really Think About AI-Powered Recruitment Tools?
HireME
 
How to Troubleshoot 9 Types of OutOfMemoryError
How to Troubleshoot 9 Types of OutOfMemoryErrorHow to Troubleshoot 9 Types of OutOfMemoryError
How to Troubleshoot 9 Types of OutOfMemoryError
Tier1 app
 
Beyond the code. Complexity - 2025.05 - SwiftCraft
Beyond the code. Complexity - 2025.05 - SwiftCraftBeyond the code. Complexity - 2025.05 - SwiftCraft
Beyond the code. Complexity - 2025.05 - SwiftCraft
Dmitrii Ivanov
 
Best HR and Payroll Software in Bangladesh - accordHRM
Best HR and Payroll Software in Bangladesh - accordHRMBest HR and Payroll Software in Bangladesh - accordHRM
Best HR and Payroll Software in Bangladesh - accordHRM
accordHRM
 
Adobe Media Encoder Crack FREE Download 2025
Adobe Media Encoder  Crack FREE Download 2025Adobe Media Encoder  Crack FREE Download 2025
Adobe Media Encoder Crack FREE Download 2025
zafranwaqar90
 
Ad

Database CI/CD Pipeline

  • 1. DevOps, Continuous Integration & Database Lifecycle Management By : Muhammad Hashir
  • 2. Issues with traditional Database Development 1. Data is hard to manage. You can overwrite and redeploy your code from scratch every time, but the same cannot be done with data. Databases have existing data which needs to be persisted and hence it cannot be wiped off and recreated during deployments – just like the code. 2. Having no Source Control for your database objects implies that there is no versioning information available for your database changes. Production is the version and system of truth. This makes it almost impossible to unit test your changes and automate your deployments. 3. Developers must spend a lot of time in creating the DML scripts. They also need to account for the tedious rollback scripts, in case the overall deployment had to be rolled back. The Database Administrator must spend a lot of time in executing scripts against multiple environments. 4. Removing manual work and inefficiencies in your database development process is one of the reasons why organizations are moving towards automating their database deployments.
  • 3. DATABASE Continuous Integration LIFECYCLE MANAGEMENT OVERVIEW Coding Unit Testing Static Code Analysis Source Control Continuous Integration Continuous Deployment SHIFT LEFT – Catch issues as early as possible
  • 5. • Free and No Installation required • Need to have VIEW SERVER STATE or VIEW DATABASE STATE permission • SQL Cop has a set of predefined rules to identify anti-patterns for database development 1. SQLCOP
  • 6. • Continuous Inspection of Code Quality • Identify Code Smells, Bugs and Vulnerabilities • Analyzers for 20+ Programming Languages • DevOps Integration • Reject Check-in when Quality Gate not met 2. Sonar Cube
  • 8. Source Control is the system of truth COMPARE SSDT SCHEMA COMPARE GENERATE EXECUTE DIFFERENCE SCRIPT DATABASE PROJECT/ DACPAC 1. STATE BASED APPROACH DACPAC is a self-contained deployment file which is used for deploying SQL Server objects to an instance of SQL Server. You can also think of DACPAC to be like a database snapshot file, which can serve as the in-memory representation of database objects and can be stored for maintaining version history. When the DACPAC is deployed, it uses the information in the DACPAC file as the source database schema. It compares this with the defined target to generate an appropriate change script, which is then executed against the target to sync up both the environments.
  • 9. Database is the system of truth STATE 1 STATE 2 STATE 3 STATE N Migration 1 Migration 2 Migration N 2. Migration BASED APPROACH
  • 10. STATE BASED APPROACH 1. System of Truth is the Source Code 2. Suited for frequent database changes 3. Suited for large sized teams 4. Source Code contains the current state of the database 5. Less control on the migration script 6. Complex refactoring might take multiple steps MIGRATION BASED APPROACH 1. System of Truth is the Database 2. Suited for infrequent database changes 3. Suited for small sized teams 4. Must maintain a long list of migration scripts within the source control. 5. More fine grain control on the migration script 6. Complex refactoring can be handled by a single script
  • 11. DATABASE DRIFTS Any change to the database schema or reference data that was made directly in the database environment, outside of the normal automated delivery pipeline. Ensure that all migrations are idempotent – meaning that running a script more than once has no additional impact.
  • 12.  Renaming a Column/Table/Stored Procedure/ View/ Function  Dropping a Column/Table  Remove a Stored Procedure/ View/ Function  Moving a Column from one table  Adding a Column/Table/Stored Procedure/ View/ Function Non-Breaking Database Change Breaking Database Change BACKWARDS COMPATIBILITY
  • 13. Expand Transition Phase Contract Initial Refactoring is applied Perform Cleanup to complete the refactoring Data is migrated EXPAND AND CONTRACT PATTERN
  • 14. SQL Server Data Tools in Visual Studio 2017
  • 15. Red gate Data Tools in Visual Studio 2017 1. Ready Roll Core: allows you to develop, source control, and safely automate deployments of database changes alongside application changes. Ready Roll Core is available in the Enterprise edition of Visual Studio 2017. 2. SQL Prompt Core: offers advanced code completion for SQL. SQL Prompt Core is available in the Enterprise edition of Visual Studio 2017. 3. SQL Search: lets you find SQL objects fast and easily explore across databases. SQL Search is available in all editions of Visual Studio 2017.
  • 17. SSDT INSTALLATION IN VISUAL STUDIO 2017
  • 18. REDGATE READYROLL INCREMENTAL CHANGE SHIFT LEFT MORE CONTROL ON MIGRATION SCRIPT HYBRID APPROACH PROGRAMMABLE OBJECTS OFFLINE SCHEMA MODEL
  • 19. READYROLL INSTALLATION IN VISUAL STUDIO 2017
  • 20. Flyway is free and open source. Flyway facilitates the Automatic Deployment of database changes. Flyway creates a table name ‘schema_version‘ in your database. Flyway supports number of databases – Oracle, SQL Server, MySQL, PostgreSQL, MariaDB, SQLite, Redshift and more.
  • 21. • Changes made to same file causes Merge Conflict. MAIN BRANCH 1 BRANCH 2 • Conflicts are caused by merge issues because of long running feature branches. Resolving Merge Conflicts is Error Prone and Time Consuming. • Longer running features has the potential to create Merge issues. File1.cs File2.cs 7 Changes 5 Changes Reverse Integration BRANCHING STRATEGY
  • 22. FEATURE FLAGS • Potential alternative to maintaining multiple feature branches. • Reduces the need for constant branching and merging. • Enables releases with unfinished features at no risk.
  • 24. CODE BUILD INTEGRATE RELEASE DEPLOY TEST Continuous Integration Continuous Delivery Continuous Deployment RELEASE PIPELINE
  • 25. SOURCE CONTROL DEVELOPMENT CONTINUOUS INTEGRATION BUILD TEST Feedback Mechanism Trigger
  • 26. CODE STAGING PRODUCTION CONTINUOUS DELIVERY CONTINUOUS DEPLOYMENT BUILD CODE STAGING PRODUCTION BUILD Auto Auto Auto Auto Auto Manual
  • 27. RESOURCES • Continuous Integration with SQL Server Data Tools in Visual Studio 2017 • Database Static Code Analysis using SQL Cop • Managing your Technical Debt using SonarQube • Continuous Integration, Continuous Delivery and Continuous Deployment
  翻译: