Downloaded 12 times
Uploaded byRed Gate Software
Database Deployment Pipeline - SQL In The City Workshop 2014
The document discusses building an automated database deployment pipeline to reduce friction between development and operations through continuous delivery practices. It provides examples of how companies like Boxon, Move with Us, and Practice Fusion improved their processes by implementing version control of database changes, continuous integration testing of builds, and automated deployment of packages to environments. The goals are to understand how to automate the pipeline step-by-step, learn necessary organizational changes, and appreciate why these changes support modern development methodologies.
Database Deployment Pipeline - SQL In The City Workshop 2014
- 1. Building an Automated DatabaseDeployment Pipeline Continuous delivery for databases Grant Fritchey Red Gate Software
- 2. Goals Understand thetechnology and process requirements to work towards automation step-by-step in your release pipeline. Learn about the organizational changes necessary to support process modifications. Appreciate why these changes are necessary in support of modern development and deployment methodologies.
- 3. scarydba.com grant@scarydba.com @gfritchey Grant Fritchey Product Evangelist,Red Gate Software /in/scarydba
- 4. ALM – andwhere the database fits in Three core processes in Application Lifecycle Management: Governance Development Operations
- 5. Natural friction acrosspipeline Development → Operations
- 7. Natural friction acrosspipeline Development → Operations Operations → Development Why?
- 8. Development focus ison speed
- 9. Agile Scrum Lean Feature-driven Development Iterative
- 10. Operations focus ison production protectionprotection
- 11. Monitoring Deployment Integrity Data Management
- 12. Databases as abottleneck Odd languages SQL Cubes X-Query 3 reasons why databases have traditionally slowed down deployments: Data persistence Data outlives applications Data can’t be replaced DBA paranoia Frankly… 1 2 3
- 13. A quick primeron continuous delivery Development Test Production Continuous integration Continuous deployment Continuous delivery Release pipeline Approval gate
- 14. The goals ofcontinuous delivery for databases • Faster feedback on changes made – Continuously integrate team changes – Automated testing – Releases rehearsed in testing environments before deployed to Production = repeatability, repeatability and a strong team process for changes
- 15. Focus on thepipeline Think of the Toyota production system… Start with Lean o Focus on the customer, eliminate waste o Continuously Improve o Empower the team o Optimize the whole o Plan for change o Automate processes o Build quality in
- 16. SOURCE CONTROL CONTINUOUS INTEGRATION AUTOMATED TESTING AUTOMATED DEPLOYMENT Four key stagesof the deployment pipeline
- 17. Create development environment forautomation Empower Development Environment should let them work at their speed Take part in Development Instead of stopping bad deployments, stop bad development Automate your build process Supply clean production data or supply good sample data Get started on writing tests Build your library
- 18. Different types oftesting for different stages of the pipeline: Development Integration Testing QA Pre-Production/ Staging Production Automate testing Unit tests Integration tests Automated tests Deployment validation Behaviour validation Other validations
- 19. B C D A AlwaysBe Continuously Delivering Deliver
- 20. Every delivery ispractice
- 21. DBAs must workwith Devs… Yes, you must protect the data for the business, but that must be tempered with helping deliver functionality. Changes to philosophy
- 22. Devs must workwith DBAs… Yes, you may know more about business needs, so educate rather than isolate. Changes to philosophy
- 23. Project Management mustthink of operations as part of development… Yes: Deployment is part of development Release 1.1 and on are part of development o And planning for 1.1 is not premature optimization Data retention is part of development Changes to philosophy
- 24.
- 25. Changes to workplacefor continuous database delivery • Empower developers to do more • Bring DBAs into development teams • Management must buy-in • Management must get out of the way
- 26. How are othercompanies getting started?
- 27. Case Study: Boxon •Global packaging and labelling company • Based in Sweden; sub-division in China • Three business areas: profitable packing solutions; intelligent marking; customized big- bags solutions for bulk handling • One developer responsible for ASP.NET application with SQL Server backend, enabling customers to control consistent printing of labels wherever the print shop is located in the world
- 28. Boxon – InitialProcess • Deploying to production was scary… • Database changes not version- controlled • 2-hour window - at night - to complete deployments to production – difficult to find quiet period with a global customer base PAIN POINTS
- 29. Boxon – ImprovedProcess • Deployments are less scary now… • Tickets are logged and prioritized in Unfuddle (unfuddle.com) • Ticket numbers are logged in SVN and TeamCity to track items • Now use SQL Source Control and Subversion to version DB changes – Source of authority on database build • TeamCity (CI build server) is triggered on check-in of changes – Fast response – changes checked in frequently • Notification if build fails • Build packaged into a Nuget file for deployment BUILDING A PIPELINE
- 30. Case Study: Movewith Us • Risk management solution and sales and marketing channel provider for residential property businesses in the UK • Located near Cambridge, UK • Customers put a heavy load on databases, so updates need to be efficient
- 31. Move with Us– Initial Process • Database code often not checked in to VCS due to risk of conflicts – changes were communicated by email instead • Hot fixes sometimes done directly on integration server • 1 day needed to release updates – a lot of work in building and testing • Problems with merging code led to a lot of firefighting PAIN POINTS
- 32. Move with Us– Improved Process • Each developer has a local copy of database • SQL Source Control and Subversion used to check in database changes to version control • TeamCity (CI build server) automatically compiles changes on check-in • Successful builds packaged into a Nuget file automatically – Fast getting here – multiple database changes checked in per day • Nuget package deployed to Test on demand • If problems detected, return to Dev to fix BUILDING A PIPELINE
- 33. Case Study: PracticeFusion • Web-based electronic health record (EHR) company • Founded in 2005 • Based in San Francisco • Hosts over 50 million patient records • Used in all 50 states and by 150k+ physicians • 3 DBA engineers; 1 data architect; 10+ developers
- 34. Practice Fusion –Initial Process • Hand-crafted SQL scripts - inconsistent • Database changes not always version controlled • Changes released by opening dozens of files in SSMS • Long-winded team comms if further changes needed • Smoke-testing deployments showed some objects had been deployed straight to Prod and weren’t in deployment script PAIN POINTS
- 35. Practice Fusion –Improved Process • SQL Source Control and Subversion to version DB changes – Source of authority on database build • Jenkins (CI build server) is triggered on check-in of changes – More time to develop; less time managing scripts • Build failed by Jenkins if problems detected – Fewer issues deploying to production • Jenkins, SQL Compare and SQL Data Compare used to deploy changes to environments • Custom scripts for replication BUILDING A PIPELINE
- 36. Summary • Eliminate ormitigate friction • Adopt lean methodologies and focus • A-B-C-D • Change your philosophy • Change your workplace
- 37. Goals Understand thetechnology and process requirements to work towards automation step-by-step in your release pipeline. Learn about the organizational changes necessary to support process modifications. Appreciate why these changes are necessary in support of modern development and deployment methodologies.
- 38. Documentation and resources Continuous Delivery by Jez Humble and David Farley (Addison Wesley) The Phoenix Project: A Novel About IT, DevOps, and Helping Your Business Win by Gene Kim, Kevin Behr and George Spafford (IT Revolution Press) The Goal: A Process of Ongoing Improvement by Eliyahu M. Goldratt and Jeff Cox (Gower Publishing Ltd.) Agile Organization by the agile admin (theagileadmin.com) Further resources: Database Delivery Learning program: www.red-gate.com/delivery – Patterns and practices on Simple-Talk – Tutorials in Red Gate training academy www.youtube.com/user/RedGateVideos - for recorded seminars
- 39. Want to trythe tools you’ve just seen? Come to our Hands-on Labs
Editor's Notes
- #14 See where continuous delivery fits in on the continuum of a release pipeline – and continuous processes. Important to emphasise the quality check at the approval gate stage for continuous delivery – this is a chance for DBAs to review scripts before they enter the production environment.
- #28 Boxon, a global packaging and labelling company serving over 7,000 customers. Founded over 80 years ago, Boxon builds complete lines for packaging solutions and enables companies to better manage their global supply chain.
- #29 While Mattias, the developer, had the application code in Subversion, the database was in an uncontrolled environment. It was difficult to know what version the database was on, which version of the app it was tied to or what the last changes were. Started to put deployments into buckets based on level of risk: the most risky ones would be deployed to TEST for additional testing, less risky ones would be tested on DEV. This process could be subjective.
- #36 Now - Changes are committed to Subversion using SQL Source Control. Jenkins polls Subversion for changes and triggers a build. Jenkins creates a brand new database and rebuilds everything from scratch. If there are any problems (e.g. Dependent tables/columns/procedures/functions are missing), then the build will fail. We also use the Jenkins "Promotions" plug-in to deploy database builds to our various environments. Red Gate SQL Compare is used to deploy the schema. Red Gate SQL Data Compare is used to synchronize the contents of lookup tables. We have custom scripts that we use to turn Replication on and off during this process. When we are preparing deployments to our Staging and Production environments, we do not blindly deploy the changes. Instead, we use Red Gate Compare to generate the alter script, which is inspected manually by our DBA team to ensure we are making appropriate changes (e.g. make sure we are not needlessly creating a large index).
- #38 Not sold on these, but I wanted to make a first pass. Understand the process requirements that are necessary to streamline database deployments.