KEMBAR78
DevOps CI and Data Warehouse | PDF | Microsoft Sql Server | Microsoft Visual Studio
Scribd
Upload
230 views30 pages

DevOps CI and Data Warehouse

This document discusses applying DevOps principles and practices to data warehousing. It describes using SQL Server Data Tools to model databases declaratively and generate deployment scripts, as well as tools for continuous integration of SSIS, SSAS, and SSRS artifacts. PowerShell and the psake build tool are recommended for automating builds and deployments. Octopus Deploy can then deploy packaged artifacts to environments. Applying these techniques helps shorten development cycles, improve quality and enable more frequent releases for data warehouses.

Uploaded by

maghnus
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
230 views30 pages

DevOps CI and Data Warehouse

This document discusses applying DevOps principles and practices to data warehousing. It describes using SQL Server Data Tools to model databases declaratively and generate deployment scripts, as well as tools for continuous integration of SSIS, SSAS, and SSRS artifacts. PowerShell and the psake build tool are recommended for automating builds and deployments. Octopus Deploy can then deploy packaged artifacts to environments. Applying these techniques helps shorten development cycles, improve quality and enable more frequent releases for data warehouses.

Uploaded by

maghnus
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 30

Dr.

John Tunnicliffe

devops-your-dwh.com
john@bovi.co.uk
M: +447771818770

DevOps, Continuous Integration


and the

Data Warehouse
Agenda
What is DevOps?
SQL Server Data Tools (SSDT)
SSDT Unit Tests
PowerShell and psake build tools
Azure DevOps
Octopus Deploy
What is Continuous Integration?
Continuous Integration is a development practice that requires developers to
integrate code into a shared repository several times a day.

Each check-in is verified by an automated build, deploy and test cycle


which allows errors to be detected and thereby corrected quickly.
What is DevOps?
DevOps extends the concept of Continuous Integration (CI) by packaging each
successful build and automating the deployment of the software into
target environments (i.e. test / pre-prod / prod)

DevOps also covers application usage and performance monitoring


What is DevOps?
The DevOps approach strongly advocates the automation and monitoring of
all steps of software construction, from integration, testing, releasing to
deployment and infrastructure management
DevOps aims to
 Shorten the development cycle

 Increase deployment frequency

 Make releases more dependable

 Improve stability

 Better collaboration and alignment


Key Considerations for DevOps
 Deployable artifacts
 What is generated from the code that I can deploy?
 Packaging artifacts
 How do I package all the artifacts together that make one release?
 Deployment
 How do I deploy the artifacts?
 Configuration
 How can I set up each environment so that everything works post deploy?
Why is DevOps so difficult?
 BI developers are not familiar with DevOps, CI or CD
 Never seen it in action in a data warehouse context
 Not familiar with the tools and techniques
 Cannot comprehend how it can be applied
 Data is a BIG barrier
 PROD data is different to DEV
 Problems often only manifest themselves in PROD data
 Time taken to load data
Data Warehouse
 Project started 2010
 SQL 2008 R2 – tools & techniques have remained static
 Various “initiatives” have resulted in a confused data architecture
 Three competing data streams and lots of data marts
 Overly complicated and complex!
 100,000 files in the code-base!
 14 databases, 200 SSIS packages, 4 multidimensional cubes
 7 developers, 4 testers, 3 BAs, 1 S2T, 1 DBA, 1 PM
 Each database had 2 projects – split “storage” and “code”
 Configuration scattered across code base
 Manual build and very manual deploy – 1 guy, 4 days per month!
 Each production release was getting longer, complex and error prone!
Data Warehouse
 Imported all databases into SQL Server Data Tools
 New SCC repository with simplified structure: only 10% files
 PowerShell script written to do local build & deployment
 Development tools
 Visual Studio 2013 for SQL Server Data Tools (SSDT)
 Visual Studio 2008 for SSIS and SSAS (servers still SQL 2008R2)
 TeamCity build server
 Creates single Nuget package containing all 14 databases, 200+ SSIS package and
SSAS cubes plus deployment scripts
 Automatically deploys and tests everything
 Automatic deployment to test, UAT and other environments!
DevOps in Practice
Two approaches to
SQL database development
Migration Scripts Declarative Model
 Manually prepare upgrade scripts  Define what we want
 Must be run in correct order  Tool creates migration scripts

 Scripts are combination of DDL and  Still need data fixes


data fixes
 Automated deployments
 Manual deployments
SQL Server Data Tools
 Declarative model design environment for SQL Server
 Requires Visual Studio 2012+
 Supports SQL Server 2005+
 Very easy to import an existing database
 Must replace hard-coded database names with variables
 Add references to other databases and setup SQLCmd variables
 Don’t import existing code or upgrade VSDT projects
 Instead start a new SSDT project and import a deployed database instance
 Then transfer over pre- and post-deploy scripts
SQL Server Data Tools
 Build generates a DACPAC => “deployable artifact”
 Contains entire database model
 Conforms to Open Packaging Convention = ZIP file!
 Publish deploys the database to the server
 Compares content of DACPAC to target server and generates a deployment script
 Tailor how publish works using a DAC Publish Profile
 To automate build / deployment:
 Use MsBuild to create DACPAC from Visual Studio solutions
 Use SQLPackage.exe to deploy each DACPAC using a DAC Publish Profile
SQL Server Data Tools
DAC Publish Profile
SQL Server Data Tools
Tools and techniques for DevOps
Using SSDT for Unit Testing
 Add a Unit Test C# project to your Visual Studio solution
Tools and techniques for DevOps
SSDT Artifacts
 Build
 MsBuild generates a DACPAC => “deployable artifact”

 Deploy
 Use SQLPackage.exe to deploy each DACPAC using a DAC Publish Profile
Tools and techniques for DevOps
SSIS Artifacts
 Build
 MsBuild generates an ISPAC => “deployable artifact”

 Deploy
 Use IsDeploymentWizard.exe to deploy the ISPAC
 Finish configuration by running SQLCmd scripts from PowerShell that use SSISDB
catalog stored procedures to:
▪ Create Projects & Folders
▪ Create Environments & Variables
▪ Associate Environment with Project
 SSISDB deployment script from https://www.hansmichiels.com/2016/11/04/
Tools and techniques for DevOps
SSAS Artifacts
 Build
 MsBuild generates a .AsDatabase file => “deployable artifact”

 Deploy
 Microsoft.AnalysisServices.Deployment.exe /s
 Use PowerShell to alter XML config files defining target server etc.
▪ Top Tip: Windows EXE => so pipe to Out-Null to force PowerShell to wait
Tools and techniques for DevOps
SSRS Artifacts
 Build
 Does nothing useful! => simply copies RDL files to a sub-directory

 Deploy
1. SSRS web service – ReportService2005.asmx?WSDL
 Problematic and unreliable
2. Use Visual Studio command-line (devenv.exe)
 Problematic and unreliable
3. REST APIs for SQL Server 2017 Reporting Services
 No examples and I have not had chance to try yet
Tools and techniques for DevOps
Source Code Control (SCC)
 ALL code MUST be under Source Code Control
 SQL Server, SSIS, BIML, SSAS, SSRS, Test Suites etc. etc.
 Recommend Using Three Branches
1. Development branch
2. Release branch
3. Prod branch
 SCC Locking strategy:
 Use optimistic locking for SQL, C#, BIML
 Use pessimistic locking for SSIS, SSAS, SSRS and PowerBI
PowerShell
 PowerShell is the “glue” for your CI project
 Part of Windows Management Framework
 Advanced scripting
 Proper error handling Try-Catch
 Easy to read/write XML
 Ideal for reading and writing config files
 Easy to read/write CSV
 Great for iteration
 SQL specific CmdLets
 Invoke-SQLCmd
 Invoke-ASCmd & SQLAS
psake
 Task-orientated build tool written in PowerShell
 Uses a dependency pattern similar to MSBuild or Make
 Download from https://github.com/psake/psake
 Build script consist of Tasks
 Each task can depend on other tasks
Tools and techniques for CI
psake
Octopus Deploy
 Octopus Server
 Database and Web App
 Octopus Tentacle
on each target server

 Build pipeline pushes


versioned Nuget package to
Octopus Library
 Octopus deploys to each
environment in LifeCycle
Download my PowerShell scripts

https://github.com/DrJohnT/devops-your-dwh

Blog: https://devops-your-dwh.com
Agenda Recap
What is DevOps?
SQL Server Data Tools (SSDT)
SSDT Unit Tests
PowerShell and psake build tools
Azure DevOps
Octopus Deploy
Dr. John Tunnicliffe

devops-your-dwh.com
john@bovi.co.uk
M: +447771818770

DevOps, Continuous Integration


and the

Data Warehouse

You might also like