Download as PDF, PPTX
Uploaded byOri Donner
SQL Server DevOps Jumpstart
The document provides an overview of SQL Server Database Application Lifecycle Management (ALM) aimed at developers, DBAs, and DevOps professionals, emphasizing agile methodologies and DevOps practices. It discusses version control, branching strategies, continuous integration, continuous delivery, and database management tools such as SSDT and SQLPackage. Key concepts include feedback loops, software performance monitoring, and metadata management in a collaborative development environment.
More Related Content
Similar to SQL Server DevOps Jumpstart
SQL Server DevOps Jumpstart
- 1. SQL SERVER DEVOPS JUMPSTARTDON’T FEAR THESHELL Ori Donner SQL Server DBA & DevOps at Crow Electronic Engineering LTD Dec 2016 Data platform Meetup Israel
- 2. MEETING SUBJECT : SQLServer Database Application Lifecycle Management TARGET AUDIENCE : SQL Server developers. DBA’s. DevOps.
- 3. AGENDA • Agile • DevOps •Version Control • Branching & Versioning strategies • SQL Server database ALM tools • Q/A
- 4.
- 5. AGILE The shift fromplan driven software to adaptive planning approach : Success == Project is advancing according to plan (prediction). What happens if the requirements are not stable? 1. Stabilize the requirements. 2. Break the equation. Come up with a software development process that is tolerant towards changes (competitive advantage).
- 6. AGILE Feedback : Settingup fast feedback loop • From developer perspective : • How fast you can drive the Mars rover? Speed of feedback effects your software development performance . • From user perspective : • Get fast feedback from the user and develop what is necessary for your user.
- 7. AGILE Feedback : Measurements Constantfeedback loop of software development process : • Does our software integrate? • Does it built and perform well? • What stage of readiness to production is it? Feedback loop for the basic health of the software.
- 8. AGILE Feedback : PairProgramming You can only use half of your brain at a time. The only way to apply one full brain to software development is to use two half brains at one time. Not two people working in one project, but one brain working on one project.
- 9.
- 10. DEVOPS • At theAgile 2008 conference, Andrew Clay Shafer and Patrick Debois discussed "Agile Infrastructure". The term "DevOps" was popularized through a series of DevOpsdays starting in 2009 in Belgium. • DevOps main issue is to constantly improve software delivery. • Improve the software. • Improve the environment to which you are delivering. • Improve the delivery process.
- 11. DEVOPS PIPELINE BUILD Source code TESTDEPLOY Software Process
- 12. DEVOPS PIPELINE • Code- Code development and review, Version control tools, code merging. • Build - continuous integration tools, build status. • Test - Test and results determine performance. • Package - Artifact repository, application pre-deployment staging. • Release - Change management, release approvals, release automation. • Configure - Infrastructure configuration and management, Infrastructure as Code tools (Vagrant - virtualization, Docker - containers). • Monitor - Applications performance monitoring, end user experience.
- 13.
- 14. DEVOPS PROCESS &TOOLS BUILD Source code TEST DEPLOY Software Git GitLab-CI Process CMD Tools SSDT Developer IDE Tool MSBuild MSTest sqlpackage DevOps IDE Tool
- 15. CONTINUOUS INTEGRATION • Frequentsmall check ins instead of waiting until the whole piece is done. Every developer checks his code multiple times a day. • Every time someone checks in the code is verified by an automated build system (Max respond time of 10 min). • Fix the broken build right away.
- 16. CONTINUOUS DELIVERY • Thecapability to get software deployed to any particular environment , in the delivery pipeline, at any given time : • In case of existing environment you set the environments configuration. • In case of where the environment does not exist you need to provision the environment. • Keep the product releasable. • Build the quality inside the process (speed + quality) • Test in an environment closest to production.
- 17. CONTINUOUS DEPLOYMENT • ContinuousDeployment is the process of checking into master, running all the tests and if everything goes green it is automatically pushed to production (usually for .com project).
- 18.
- 19. VERSION CONTROL BASICS •Tracking changes that happen within directories or files : modifying a file, deleting a directory, adding a new file, moving files . • The set of files or directories that are under version control are more commonly called a repository. • The version control system will wait for you to submit your changes as a single collection of actions. In version control, this collection of actions is known as a commit. • When a commit is made, the changes are recorded as a changeset and given a unique hash (like 846eee7d92415cfd3f8a936d9ba5c3ad345831e5).
- 20. VERSION CONTROL BASICS •The version control system will provide a way to view the difference between the conflicting versions, allowing you to make a choice to edit the files manually , to merge the options, or allow one revision to win over the other. • Since each commit is recorded as a change to a file or set of files and directories, it is sometimes useful to view what changed between revisions (Diffing) . • A branch allows you to create a copy (or snapshot) of the repository that you can modify in parallel without altering the main set. You can continue to commit new changes to the branch as you work, while others commit to the trunk or master without the changes affecting each other.
- 21. VERSION CONTROL BASICS •By merging the branch with the trunk or master (or even another branch), your version control tool will attempt to seamlessly merge each file and line of code automatically. Once a branch is merged it then updates the trunk or master with the latest files.
- 22.
- 23. CENTRALIZED Benefits • It iseasy to understand. • You have more control over users and access . • Simple to get started. Drawbacks • Dependent on access to the server. • It can be slower because every command connects to the server. • Branching and merging tools are difficult to use.
- 24.
- 25. DISTRIBUTED Benefits • More powerfuland detailed change tracking, which means less conflicts. • No server necessary • Branching and merging is more reliable. • It’s fast. Drawbacks • The distributed model is harder to understand. • The revisions are not incremental numbers, which make them harder to reference.
- 26. GIT • Distributed versioncontrol system, developed by Junio Hamano & Linus Torvalds. • Runs on major operating systems : • Runs on Windows (download : https://git-scm.com/downloads). • Embedded in VS 2013, VS code, TFS & VS Online. • posh-git for powershell (installation : https://github.com/dahlbyk/posh-git). With chocolatey package manager : choco install git choco install poshgit
- 27. GITLAB • GitLab isa web-based repository manager that lets teams collaborate on code, duplicate code to safely create and edit new projects, then merge finished code into existing projects.. • GitLab Community Edition (CE) - free, self hosted application, support from Community • GitLab has everything GitHub has, and more - giving teams increased control over their repositories.
- 28.
- 29. BRANCHING CONCEPTS • Trunk-based: one master branch. • Feature driven : feature branches with one master brunch. • Release branches : install software. • Git flow : 2 main branches. application life cycle for installed apps. • Open source : fork , squash and pull request.
- 30. GITFLOW • GitFlow isbranching and release management strategy that helps developers keep track of features, hotfixes and releases in bigger software projects.
- 31. MAIN BRANCHES • Thecentral repo holds two main branches with an infinite lifetime: • master : reflects a production- ready state source code. • develop : reflects development changes for the next release.
- 32. SUPPORTING BRANCHES • Unlikethe main branches, these branches always have a limited life time, since they will be removed eventually. • Feature branches • Release branches • Hotfix branches
- 33. FEATURE BRANCHES • Areused to develop new features for the upcoming or a distant future release. The branch will exist as long as the feature is in development, but will eventually be merged back into develop.
- 34. RELEASE • supports preparationof a new production release. They allow for minor bug fixes and preparing meta-data for a release. By doing all of this work on a release branch, the develop branch is cleared to receive features for the next big release. * major/minor version number bumping
- 35. HOTFIX • HotFix :Team members (on the develop branch) can continue, while another person is preparing a quick production fix. * only patch version number bumping
- 36.
- 37. WHAT TO VERSION •Your product. • Libraries, builds. • Public and private(internal versioning) • DB schemas. • Infrastructure configuration : Containers (Docker) • Public and private API : DreamFactory (reusable REST API)
- 38. VERSIONING MODELS • Numeric: Major.Minor > Major.Minor.Patch > Major.Minor.Patch.Build • Date based : Major.Minor.Date • Marketing versions : Whatever PM or marketing decide • Semantic versioning : Major.Minor.Patch-prerelease+metadata
- 39. SEMANTIC VERSIONING • Exactrules how you change the version. • 0.1.4-b45+d3e760 • 0.1.5-46 • 0.2.1-prerelease+b538 • 1.0.0-alpha • 1.3.9-beta • 2.0.0
- 40. SEMANTIC VERSIONING Given aversion number MAJOR.MINOR.PATCH, increment the: • MAJOR version when you make incompatible API changes, • MINOR version when you add functionality in a backwards-compatible manner, and • PATCH version when you make backwards-compatible bug fixes. • Additional labels for pre-release and build metadata are available as extensions to the MAJOR.MINOR.PATCH format.
- 41. GITVERSION • GitVersion isa tool to help you achieve Semantic Versioning on your project. • GitVersion looks at your git history and works out the semantic version (semver.org) of the commit being built. • By default GitVersion returns a json object to stdout containing all the variables which GitVersion generates.
- 42.
- 43. THE MAIN CHALLENGE •In application versioning, old binaries are being replaced with new binaries after each deployment. • In database versioning a database cannot be overwritten because it contains data. The concept is to create incremental SQL scripts to modify the existing database (tools like Liquibase).
- 44. DATABASE SCHEMA MANAGEMENT Transformationalprocess • Physical schema : Connected database development mode (SSMS). • Seed script, starts initial version of the database. • Change scripts are kept in release folder . • In order to recreate the database in its current version, you have to execute the seed script with all the changes in the right order. • May fail in case of server drift or unrecorded changes made to the database (patches, hot fixes).
- 45. DATABASE SCHEMA MANAGEMENT Statebased methodology • Virtual schema : Data Tier Application Framework (DACFx). • Keep your SQL Server Database in a code form, where you can compile validate and source control. • The tool creates the change scripts for you. • Focus on the schema model not on how to get there. • Need to ensure sufficient log space is available for upgrade process.
- 46. MICROSOFT TOOLS • SSMS: Connected database development. • SMO (Server Management Object) : .net object oriented programming (abstraction) : , C# VS project. • SSDT : Data Tier Application Framework (DACFx). ssdtsmossms ++-Virtual schema +--State based +-+Native (T-SQL)
- 47. SQL SERVER DATATOOL • Project system for database development (like C#). • Build time validation : validation of T-SQL code. • Online/offline editing : object explorer for online editing. • Database publishing with extensibility : deploying from VS. • Schema comparison : between project/dacpac/database. • Database Unit Testing. • SSDT in Visual Studio 2015 (not as a feature in SQL Server installation).
- 48.
- 49. MSBUILD.EXE MSBuild is abuild platform for Microsoft Visual Studio, included in VS since visual studio 2013 . • MSBuild project file is being created behind the scenes while writing code in VS IDE (e.g. Project_name.sqlproj ) • The build process output is a Data Tier Application Package file (e.g. Project_name.dacpac).
- 50. MSBUILD FILE SCHEMA <?xmlversion="1.0" encoding="utf-8"?> <Project ToolsVersion="3.5“ xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> <Import Project="$(MSBuildExtensionsPath)..Microsoft.Data.Tools.Schema.SqlTasks.targets“/> <Target Name="Default"> </Target> <PropertyGroup> </PropertyGroup> <ItemGroup> </ItemGroup> </Project> Root element (req) Attribute Default Name Space (req) Import element : imports tasks,targets and properties for SSDT
- 51. .DACPAC • DAC :A data-tier application is a logical database management entity that defines all of the SQL Server objects - like tables, views, and instance objects, including logins - associated with a user’s database. • DACPAC : A DAC is a self-contained unit of SQL Server database deployment that enables data-tier developers and DBA’s to package SQL Server objects into a portable artifact called a DAC package (OPC zipped file) . • BACPAC is a related artifact that encapsulates the database schema as well as the data stored in the database.
- 52. SQLPACKAGE.EXE SqlPackage is acommand line utility that automates the following database development tasks: • Extract: Creates a database snapshot (.dacpac) file from a live SQL Server. • Publish: Incrementally updates a database schema to match the schema of a source .dacpac file. • Script: Creates a Transact-SQL incremental update script that updates the schema of a target to match the schema of a source.
- 53. SQLCMD.EXE SQLCMD lets youenter Transact-SQL statements, system procedures, and script files : • At the command prompt. • SSMS Query editor in SQLCMD mode. • Windows script file. • Operating system (Cmd.exe) job step of a SQL Server Agent job. * SSMS uses the Microsoft.NET FrameworkSqlClient for execution in regular and SQLCMD mode in Query Editor. When sqlcmd is run from the command line, sqlcmd uses the ODBC driver.
- 54. BCP.EXE • Use bcp.exebulk copy program to move data from two database versions. • Easiest and fastest way to move data. • Can be automated through the command line. • Can handle schema changes (query and format file).
- 55. MORE REFERENCES • Videos: • Understanding DevOps • Using SSDT to manage Database Lifecycle • ALM for SQL Server Database Development • Blogs : • Automation Planet • The agile SQL Club • Automate Database Builds • MSDN : • Automation Command Reference for Database Features of Visual Studio
- 56. MORE REFERENCES • SSDT: • Clearing up the confusion between these two versions • SSDT on MSDN • Official team blog for SSDT • SSDT Download
- 57.