DevOps, Continuous Integration & Database Lifecycle Management: Rule them all

Do you want to make your deployments risk free? Do you want to deliver business values to your customers faster? Do you want to increase the efficiency of your Team? Are your application and database deployment separate processes? Do you encounter issues while deploying your database changes? Do you want to automate your testing process? Is your database slowing you down?

If the answer to any of the above questions is a Yes, then this presentation is for YOU.
Come join me in this session to understand the problems with traditional database development, why organizations are moving towards achieving Continuous Integration and Database DevOps, the problems it tries to solve and learn about the toolsets which will assist you in this journey towards painless database deployments.

Transcript

1. Samir Behara, Senior Developer, EBSCO @samirbehara www.dotnetvibes.com DevOps, Continuous Integration & Database Lifecycle Management: Rule them all
2. Samir Behara builds software solutions using cutting edge Microsoft technologies, primarily C# and SQL Server. Has a Bachelor Degree in Computer Science with 12 years of IT experience. Co-Chapter Lead of Steel City SQL Server User Group , Birmingham, AL SAMIR BEHARA Senior Developer, EBSCO
3. DATABASE RELEASE? EMAIL THE SCRIPTS & I WILL EXECUTE IT
4. DATABASE LIFECYCLE MANAGEMENT OVERVIEW Coding Unit Testing Static Code Analysis Source Control Continuous Integration Continuous Deployment SHIFT LEFT – Catch issues as early as possible
5. COST OF DEVELOPING SOFTWARE Project Phase Analysis Design Development Testing Maintenance
6. TESTING PYRAMID
7. TEST DRIVEN DEVELOPMENT
8. TECHNICAL DEBT
9. STATIC CODE ANALYSIS SQL Cop 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
10. STATIC CODE ANALYSIS SonarQube 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
11. CODE BUILD INTEGRATE RELEASE DEPLOY TEST Continuous Integration Continuous Delivery Continuous Deployment RELEASE PIPELINE
12. SOURCE CONTROL DEVELOPMENT CONTINUOUS INTEGRATION BUILD TEST Feedback Mechanism Trigger
13. CODE STAGING PRODUCTION CONTINUOUS DELIVERY CONTINUOUS DEPLOYMENT BUILD CODE STAGING PRODUCTION BUILD Auto Auto Auto Auto Auto Manual
14. STATE BASED APPROACH Source Control is the system of truth COMPARE SSDT SCHEMA COMPARE GENERATE EXECUTE DIFFERENCE SCRIPT DATABASE PROJECT/ DACPAC
15. MIGRATION BASED APPROACH Database is the system of truth STATE 1 STATE 2 STATE 3 STATE N Migration 1 Migration 2 Migration N
16. STATE BASED APPROACH • System of Truth is the Source Code • Suited for frequent database changes • Suited for large sized teams • Source Code contains the current state of the database • Less control on the migration script • Complex refactoring might take multiple steps MIGRATION BASED APPROACH • System of Truth is the Database • Suited for infrequent database changes • Suited for small sized teams • Have to maintain a long list of migration scripts within the source control • More fine grain control on the migration script • Complex refactoring can be handled by a single script
17. 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.
18. BACKWARDS COMPATIBILITY  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
19. Expand Transition Phase Contract Initial Refactoring is applied Perform Cleanup to complete the refactoring Data is migrated EXPAND AND CONTRACT PATTERN
20. VERSION CONTROL DISCONNECTED DATABASE DEVELOPMENT DECLARATIVE APPROACH DACPAC DEPLOPYMENTS CODE EDITING EXPERIENCE SCHEMA & DATA COMPARE SQL SERVER DATA TOOLS
21. SSDT INSTALLATION IN VISUAL STUDIO 2017
22. REDGATE READYROLL INCREMENTAL CHANGE SHIFT LEFT MORE CONTROL ON MIGRATION SCRIPT HYBRID APPROACH PROGRAMMABLE OBJECTS OFFLINE SCHEMA MODEL
23. READYROLL INSTALLATION IN VISUAL STUDIO 2017
24. Flyway is free and open source. Flyway facilitates the Automatic Deployment of database changes. Fly ay reates a ta le a e ‘s he a_ ersio ‘ i your data ase. Flyway supports number of databases – Oracle, SQL Server, MySQL, PostgreSQL, MariaDB, SQLite, Redshift and more.
25. 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
26. 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.
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
28. Thank You samirbehara@gmail.com @samirbehara dotnetvibes.com