{"id":127,"date":"2020-05-20T10:42:47","date_gmt":"2020-05-20T17:42:47","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=127"},"modified":"2020-06-19T07:57:02","modified_gmt":"2020-06-19T14:57:02","slug":"devops-for-azure-sql","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/devops-for-azure-sql\/","title":{"rendered":"DevOps for Azure SQL"},"content":{"rendered":"<p>\u201cDevOps\u201d is a broad term which encompasses various disciplines like CI \/ CD, testing, monitoring <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/architecture\/framework\/devops\/overview\">and more<\/a>. Donavan Brown\u2019s <a href=\"https:\/\/www.donovanbrown.com\/post\/what-is-devops\">classic post<\/a> states: &#8220;<em>DevOps is the union of people, process, and products to enable continuous delivery of value to our end users\u2026<\/em> <em>You cannot buy DevOps and install it.\u00a0 DevOps is not just automation or infrastructure as code.\u00a0 DevOps is people following a process enabled by products to deliver value to our end users&#8221;.<\/em>\u00a0In this post, we take a non-opinionated look at what DevOps for Azure SQL looks like, by reviewing common concepts and providing some options for implementing DevOps for Azure SQL.<\/p>\n<p><img decoding=\"async\" class=\"wp-image-120 aligncenter\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image001.png\" alt=\"Elements of DevOps\" width=\"550\" height=\"326\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image001.png 847w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image001-300x178.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image001-768x455.png 768w\" sizes=\"(max-width: 550px) 100vw, 550px\" \/><\/p>\n<h2>Personas<\/h2>\n<p>If you consider DevOps in a typical organization, several personas emerge:<\/p>\n<ul>\n<li>Security and Compliance \u2013 typically responsible to define security controls and patterns<\/li>\n<li>Central IT \u2013 responsible for defining environments (dev \/ pre-prod \/ prod) and acceptable infrastructure patterns (including networking)<\/li>\n<li>Operations \u2013 manages pipelines and software releases<\/li>\n<li>Development \u2013 implements line-of-business applications<\/li>\n<\/ul>\n<p>All these personas have a role to play when it comes to DevOps for Azure SQL. For example, what we classically refer to as \u201cDBA\u201d or \u201cData Engineering\u201d roles tend to span the Operations and Development personas mentioned above. In the sections below, you will see correlations of technology and process to these personas.<\/p>\n<h2>Database Lifecycle<\/h2>\n<p>Let\u2019s quickly consider the lifecycle of a database:<\/p>\n<ul>\n<li>A database is created as a collection of schema objects (tables, views, indexes etc.) and programmability objects like stored procedures, functions etc.) It is possible to represent all of these as code which can then be version controlled and subjected to \u201coperations\u201d conceptually like any other application code<\/li>\n<li>In the past, it was common for database development to be done in a \u201cconnected\u201d fashion where database management tools would be used to create database objects. The database itself would serve as the system of truth<\/li>\n<li>Over time, the availability of database design tools and \/ or object-relational mapping libraries have caused most of the database development to happen inside a development environment of some sort. Accompanying this has been the tighter integration of database development practices with \u201ctraditional\u201d app dev practices \u2013 source control, automated testing, code reviews, release management etc. This is where most of this blog post will focus on.<\/li>\n<li>When talking about changes and releases to databases, it is important to also consider what makes database operations slightly different from \u201cregular\u201d application code: in addition to the \u201ccode\u201d (schema and programmability objects), a database also has state in the form of data stored in it and as the data stored conforms to the schema of the database objects, any changes to those objects typically impacts data. This is what makes operations on a database more challenging than the usual.<\/li>\n<li>Lastly, the broader database lifecycle (out of scope for this blog post) encompasses critical activities like performance monitoring, iterative tuning of queries and much more. There\u2019s excellent coverage of this broader view of Database Lifecycle Management in this <a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/database-delivery\/what-is-database-lifecycle-management-dlm\/\">series of community posts<\/a> from Redgate.<\/li>\n<\/ul>\n<h2>Developer Experience<\/h2>\n<p>There are two common approaches when it comes to implementing changes in a database:<\/p>\n<ul>\n<li>Declarative or state-based approach: the developer specifies the end state of the database, and lets the tooling figure out how to incrementally update a given target database to bring it to that end state<\/li>\n<li>Imperative or migration-based approach: the developer (sometimes with help from some tooling) creates an incremental script (or set of scripts) which implements the change<\/li>\n<\/ul>\n<h3>State-based approach<\/h3>\n<p><a href=\"https:\/\/visualstudio.microsoft.com\/vs\/features\/ssdt\/\">SSDT<\/a>, now integrated into Microsoft Visual Studio, is a popular tool of choice for most Microsoft SQL database developers. SSDT provides the full power of the regular Visual Studio Source Control features like editing code, git pull \/ push, View History, etc. and also provides a first-class \u201cstate-based\u201d (or declarative) development experience on Microsoft Windows.<\/p>\n<p>The build output (artifact) produced by SSDT is in the form of a <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/data-tier-applications\/data-tier-applications\">DACPAC file<\/a>. DACPACs are the preferred means of deployment in a state-based approach, and they can be used for both greenfield and incremental deployments of databases. The underlying framework on which SSDT is based upon is a library called DAC Framework (DACFx). While the DACPAC is a model of your database schema and code, DACFx implements very powerful \u201cschema comparison\u201d capability which allows the production of a \u201cpublish script\u201d based on the difference between the state of the target database and the desired state (as defined in the DACPAC \/ model). SSDT also leverages and exposes this powerful schema comparison as graphical tool in the IDE.<\/p>\n<p>DACPAC deployment is enabled by a set of libraries and tools shipped under the name \u201c<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/tools\/sqlpackage-download\">SQLPackage<\/a>\u201d. SQLPackage is technically the name of the main tool, which in turn is powered by DACFx. These tools and library are now available for Windows, Linux and macOS. DACFx is also available as a <a href=\"https:\/\/www.nuget.org\/packages\/Microsoft.SqlServer.DacFx\">NuGet package<\/a> (for .NET Framework and .NET Core) and allows interested developers to optionally write their own custom deployment tooling. See the Practical Considerations section of this post for examples of customizing DACFx.<\/p>\n<p>SSDT also allows code sharing and reuse through advanced features like <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssdt\/add-database-reference-dialog-box\">Composite Projects<\/a> where a large project can be composed almost entirely out of references to smaller, building block projects, each of which builds its own DACPAC:<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-121\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image003.png\" alt=\"Composite Database project\" width=\"329\" height=\"348\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image003.png 620w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image003-284x300.png 284w\" sizes=\"(max-width: 329px) 100vw, 329px\" \/><\/p>\n<h3>Migration-based approach<\/h3>\n<p>Instead of the state-based approach used by SSDT, some customers opt to use the migration-based approach with third-party tools like <a href=\"https:\/\/www.red-gate.com\/products\/sql-development\/sql-change-automation\/\">SQL Change Automation<\/a>, <a href=\"https:\/\/dbup.readthedocs.io\/en\/latest\/\">DbUp<\/a>, <a href=\"https:\/\/flywaydb.org\/\">Flyway<\/a> etc. These third-party tools are mentioned here on an \u201cas-is\u201d basis. Please review the capabilities, applicability, and accuracy of these before using them in your projects and workflows. A simple example of using DbUp for implementing database migrations using .NET Core (as a part of an application) is available in <a href=\"https:\/\/github.com\/amthomas46\/data-migration-app\/\">this repo<\/a>. A screenshot of the development experience on Ubuntu using VSCode is shown below:<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-122\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image005.png\" alt=\"DbUp migration in Visual Studio Code\" width=\"720\" height=\"405\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image005.png 1049w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image005-300x169.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image005-1024x576.png 1024w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image005-768x432.png 768w\" sizes=\"(max-width: 720px) 100vw, 720px\" \/><\/p>\n<p>If you want to know more about the differences between state-based vs. migration-based approaches and tooling, the \u201cDevOps for Databases\u201d content within the <a href=\"http:\/\/microsoft.github.io\/PartsUnlimited\/\">PartsUnlimited<\/a> lab exercises can be useful.<\/p>\n<h3>Code Analysis<\/h3>\n<p>SSDT also ships with built-in Code Analysis capabilities which can be used to detect common anti-patterns in T-SQL code. In addition to the built-in rules, there are some open-source projects (listed in the Practical Considerations section of this post) which provide additional code analysis rules. There is a comprehensive <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssdt\/overview-of-extensibility-for-database-code-analysis-rules\">Extensibility model<\/a> provided by SSDT to add your own custom rules. While most rules are flagged as warnings, the SSDT project can optionally be set to cause the build to fail if specific (or any) rules are flagged during project build. Having Code Analysis built-in to the project greatly helps avoid common errors from creeping into production environments. Code Analysis surfaces warnings \/ errors which generally pin-point the source of the issue, making it easy for the developer to fix the issue:<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-124\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image009.png\" alt=\"T-SQL Code Analysis output in SSDT\" width=\"927\" height=\"295\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image009.png 1050w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image009-300x95.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image009-1024x326.png 1024w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image009-768x244.png 768w\" sizes=\"(max-width: 927px) 100vw, 927px\" \/><\/p>\n<p>Third party tools like <a href=\"https:\/\/documentation.red-gate.com\/scg\/sql-code-analysis-documentation\">SQL Code Guard<\/a> provide similar code quality features for migration-based projects. There are some open-source command-line SQL code analyzers like <a href=\"https:\/\/github.com\/jarulraj\/sqlcheck\">sqlcheck<\/a> as well, which can be integrated into CI pipelines to drive code quality.<\/p>\n<h3>Unit Tests<\/h3>\n<p>Unit Testing is another common requirement which can be accomplished by using Unit Tests within the Database project as shown <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssdt\/walkthrough-creating-and-running-a-sql-server-unit-test\">here<\/a>, or by using third-party tools like <a href=\"https:\/\/tsqlt.org\/user-guide\/quick-start\/\">tSQLt<\/a>.<\/p>\n<h2>DevOps Tooling<\/h2>\n<p>There are multiple options for this. <a href=\"https:\/\/azure.microsoft.com\/en-us\/solutions\/devops\/\">Azure DevOps<\/a> (Azure DevOps) provides an integrated solution through the entire software lifecycle: Planning, Development, Deployment and Operations. <a href=\"https:\/\/github.com\/enterprise\">GitHub Enterprise<\/a> is also very popular with customers. There are many other options which support integrated planning, development, delivery, and operations functionality.<\/p>\n<h3>Software Process<\/h3>\n<p>Development teams can define, track, and lay out work with Kanban boards, backlogs, custom dashboards and reporting capabilities using\u00a0<a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/devops\/boards\/\">Azure Boards<\/a>. Azure Boards fully supports common <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/boards\/work-items\/guidance\/choose-process\">process models<\/a> like Scrum, Agile or CMMI, and allows <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/reference\/customize-work\">customization<\/a> as needed. Azure DevOps also supports <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/organizations\/projects\/about-projects\">multiple projects<\/a>, multiple teams within an organization, and multiple organizations can exist as needed. Usually, the overall Azure DevOps organization is managed by Central IT.<\/p>\n<h3>Code workflows<\/h3>\n<p>Development teams should follow appropriate Git branching strategy \u2013 as an example refer to <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/repos\/git\/git-branching-guidance\">this article<\/a>. Use feature, release, and hotfix branches to independently track ongoing work. Port changes back to master from these branches.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-116\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image011.png\" alt=\"Typical branching strategy\" width=\"298\" height=\"177\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image011.png 370w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image011-300x178.png 300w\" sizes=\"(max-width: 298px) 100vw, 298px\" \/><\/p>\n<p>Azure DevOps provides version control and code collaboration workflows which should be very familiar to most developers. Here is a screenshot of a Pull Request for a database schema change, in Azure DevOps:<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-117\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image013.png\" alt=\"Pull Request in Azure DevOps\" width=\"692\" height=\"336\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image013.png 1197w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image013-300x146.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image013-1024x497.png 1024w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/05\/image013-768x373.png 768w\" sizes=\"(max-width: 692px) 100vw, 692px\" \/><\/p>\n<h3>Creating Azure SQL resources<\/h3>\n<p>Typically, Central IT will create the Azure SQL resources (server, database, elastic pool, managed instance etc.) ahead of time or as a distinct separate step in a DevOps pipeline. A good way to do this is to use an <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/sql-database\/sql-database-resource-manager-samples\">ARM template<\/a>, because it allows great control over the deployment and allows the implementation to comply with Security and Compliance standards that are defined by the Security teams. For example, security requirements might dictate that the Azure SQL DB Logical Server only allow connections over a private endpoint using <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/sql-database\/sql-database-private-endpoint-overview\">Private Link<\/a>.<\/p>\n<p>As an alternative to using ARM templates, if you use a T-SQL command to create a new Azure SQL DB then the T-SQL script must loop and check for completion of the database creation. See the Practical Considerations section of this post for an example of this. Note that certain resources, like Azure SQL DB Logical Server cannot be created via T-SQL. Use ARM templates or equivalent <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/sql-database\/sql-database-powershell-samples\">PowerShell<\/a> \/ <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/sql-database\/sql-database-cli-samples\">Azure CLI<\/a> commands for that.<\/p>\n<h3>Build and Release pipelines<\/h3>\n<p>Development teams can also automate testing and practice continuous integration in the cloud with\u00a0<a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/devops\/pipelines\/\">Azure Pipelines<\/a>, create automatic workflows from idea to production with\u00a0<a href=\"https:\/\/github.com\/features\/actions\">GitHub Actions<\/a>, and even bring their\u00a0<a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/jenkins\/overview\/\">Jenkins<\/a>\u00a0workloads to the Azure.<\/p>\n<p>Azure DevOps also provides Build and Release pipelines which can be used to implement a typical CI\/CD workflow. Typically, the Build pipeline will produce a DACPAC artifact. Please refer to the Practical Considerations section of this post for a note on how to efficiently do this in Azure DevOps. You can also use the <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/targets\/azure-sqldb\">built-in deployment task<\/a> to deploy to Azure SQL DB as part of the same pipeline. More information and source code for that task is available <a href=\"https:\/\/github.com\/microsoft\/azure-pipelines-tasks\/tree\/master\/Tasks\/SqlAzureDacpacDeploymentV1\">here<\/a>. We can implement the entire pipeline using YAML which can then be stored and versioned as part of a repo. Here is a sample Azure DevOps pipeline:<\/p>\n<pre class=\"prettyprint\">pr:\r\n  - master\r\n\r\npool: \r\n  vmImage: 'windows-latest'\r\n\r\nvariables:\r\n - group: 'Secrets'\r\n - name: solution\r\n   value: '**\/*.sln'\r\n - name: buildPlatform\r\n   value: 'Any CPU'\r\n - name: buildConfiguration\r\n   value: 'Release'\r\n\r\nsteps:\r\n\r\n- task: VSBuild@1\r\n  inputs:\r\n    solution: '$(solution)'\r\n    platform: '$(buildPlatform)'\r\n    configuration: '$(buildConfiguration)'\r\n\r\n- task: PublishPipelineArtifact@1\r\n  displayName: 'Publish DACPAC as artifact for current run'\r\n  inputs:\r\n    targetPath: $(Build.SourcesDirectory)\r\n    artifactName: 'sqlproj_artifacts_$(System.JobAttempt)'\r\n  condition: always()\r\n\r\n- task: SqlAzureDacpacDeployment@1\r\n  displayName: 'Deploy Azure SQL DB'\r\n  inputs:\r\n    azureSubscription: '$(azureSubscription)'\r\n    ServerName: '$(azureSqlServerName)'\r\n    DatabaseName: '$(azureSqlDBName)'\r\n    SqlUsername: '$(azureSqlUser)'\r\n    SqlPassword: '$(azureSqlPassword)'\r\n    DacpacFile: '$(System.DefaultWorkingDirectory)\/WideWorldImporters\/bin\/Release\/WideWorldImporters.dacpac'\r\n    PublishProfile: '$(System.DefaultWorkingDirectory)\/WideWorldImporters\/WideWorldImporters.publish.xml'\r\n    DeleteFirewallRule: false<\/pre>\n<p>For those who prefer to work with GitHub Actions, there is a <a href=\"https:\/\/github.com\/Azure\/sql-action\">deploy Azure SQL Database Action<\/a> which allows the user to deploy either a DACPAC or a SQL script to an Azure SQL DB. An example of how to use this with migration-based SQL script approach is in this <a href=\"https:\/\/github.com\/yorek\/azure-sql-action-test\/blob\/master\/.github\/workflows\/main.yml\">sample project.<\/a><\/p>\n<h3>Other Tooling Options<\/h3>\n<p>In addition to the above first-party tools, customers also have a choice of third-party solutions like <a href=\"https:\/\/www.red-gate.com\/products\/sql-development\/sql-change-automation\/\">Redgate&#8217;s SQL Change Automation<\/a> from Red-Gate, <a href=\"https:\/\/octopus.com\/\">Octopus Deploy<\/a> and many others to choose from. Some customers have hybrid solutions leveraging (for example) GitHub Enterprise for source code control, Azure DevOps Pipelines for CI and CD and a third-party solution for Release Management.<\/p>\n<h2>Practical Considerations<\/h2>\n<h3>SSDT: Open-source Code Analysis tools<\/h3>\n<p>These projects are maintained by the community and are mentioned here on an \u201cas-is\u201d basis. Please review the capabilities, applicability, and accuracy of these before using them in your projects and workflows:<\/p>\n<ul>\n<li><a href=\"https:\/\/github.com\/davebally\/TSQL-Smells\">TSQL-Smells<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/tcartwright\/SqlServer.Rules\">Rules<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/arvindshmicrosoft\/DACExtensions\/tree\/master\/RuleSamples\">DACExtension Rule Samples<\/a><\/li>\n<li><a href=\"https:\/\/github.com\/colindooley11\/SSDT-Code-Analysis\/tree\/Branch-Test\/Carnage.Core.Rules.Data\/Carnage.Core.Rules.Data\/Rules\">SSDT-Code-Analysis<\/a><\/li>\n<\/ul>\n<h3>SSDT: Specify Azure SQL DB Service Objective<\/h3>\n<p>Depending on your CI process, you may need to specify the Service Objective (SLO) as part of the Deployment settings \/ <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssdt\/how-to-change-target-platform-and-publish-a-database-project\" target=\"_blank\" rel=\"noopener noreferrer\">publish profile<\/a>. If the target database does not exist, then DACFx will create a database with the default SLO (which is currently \u201cGeneral Purpose Gen 5, 2 vCore\u201d) database. We can override this default SLO and have DACFx create the target database with (for example) a Hyperscale 16 vCore SLO by the adding the following lines to the XML publish profile file:<\/p>\n<pre class=\"prettyprint\">&lt;DatabaseEdition&gt;Hyperscale&lt;\/DatabaseEdition&gt;\r\n&lt;DatabaseServiceObjective&gt;HS_Gen5_16&lt;\/DatabaseServiceObjective&gt;<\/pre>\n<p>While the above will create the database with the correct SLO (which controls the resource limits \/ compute size for each DB), note that these settings are ignored if the database already exists. In other words, the target database will not be modified to a new SLO if the publish profile is changed to specify the new SLO. Instead, you should script the SLO change using T-SQL or use an ARM template to specify the new SLO before deploying the DACPAC. A reference for the various SLO names for the vCore model is <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/sql-database\/sql-database-vcore-resource-limits-single-databases\">here<\/a> and the list for the older DTU model is <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/sql-database\/sql-database-dtu-resource-limits-single-databases\">here<\/a>.<\/p>\n<h3>Self-hosted Azure DevOps agent<\/h3>\n<p>Previously, we described how certain scenarios may enforce specific infrastructure and security patterns when deploying Azure SQL DB. For example, security requirements might dictate that the Azure SQL server only allow connections over a private endpoint using <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/sql-database\/sql-database-private-endpoint-overview\">Private Link<\/a>. In such a case it may be necessary to deploy a <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/agents\/agents?view=azure-devops&amp;tabs=browser#install\">self-hosted Azure DevOps agent<\/a> (typically inside a VM deployed by Central IT) directly connected to the Azure Virtual Network corresponding to the Azure SQL DB private endpoint. In such cases all that you will need on the self-hosted agent is the SQLPackage tools.<\/p>\n<h3>Creating a new Azure SQL DB through T-SQL<\/h3>\n<p>If you choose to create the Azure SQL DB as part of your pipeline by executing T-SQL scripts, keep in mind that the CREATE DATABASE T-SQL statement runs as an asynchronous operation for Azure SQL DB. So, it is necessary to query the status of the operation before proceeding. Here is a sample script showing you how to do that:<\/p>\n<pre class=\"prettyprint\">PRINT N'Creating $(DatabaseName)...'\r\nGO\r\nCREATE DATABASE [$(DatabaseName)] COLLATE SQL_Latin1_General_CP1_CI_AS\r\nGO\r\nDECLARE  @job_state INT = 0;\r\nDECLARE  @index INT = 0;\r\nDECLARE @EscapedDBNameLiteral sysname = N'$(DatabaseName)'\r\nWAITFOR DELAY '00:00:05';\r\nWHILE (@index &lt; 60) \r\nBEGIN\r\n\tSET @job_state = ISNULL( (SELECT SUM (result)  FROM (\r\n\t\tSELECT TOP 1 [state] AS result\r\n\t\tFROM sys.dm_operation_status WHERE resource_type = 0 \r\n\t\tAND operation = 'CREATE DATABASE' AND major_resource_id = @EscapedDBNameLiteral AND [state] = 2\r\n\t\tORDER BY start_time DESC\r\n\t\t) r), -1);\r\n\r\n\tSET @index = @index + 1;\r\n\r\n\tIF @job_state = 0 \/* pending *\/ OR @job_state = 1 \/* in progress *\/ OR @job_state = -1 \/* job not found *\/ OR (SELECT [state] FROM sys.databases WHERE name = @EscapedDBNameLiteral) &lt;&gt; 0\r\n\t\tWAITFOR DELAY '00:00:05';\r\n\tELSE \r\n    \tBREAK;\r\nEND\r\nGO<\/pre>\n<h3>Customizing DACFx using contributors<\/h3>\n<p>Sometimes you may want to override the default behavior and scripts generated by DACFx. For example, you may want to leverage online operations when altering a table, something that is not enabled in DACFx. To do this, DACFx provides a rich extensibility mechanism called Contributors. Examples of these are available <a href=\"https:\/\/github.com\/arvindshmicrosoft\/DACExtensions\/tree\/master\/Samples\/Contributors\">here<\/a>. A community presentation about this topic is also available <a href=\"https:\/\/www.youtube.com\/watch?v=dROcCOvVE9Y\">here<\/a>.<\/p>\n<h2>Conclusion<\/h2>\n<p>DevOps is a broad term with multiple technology and process options at hand. There are many options to implement DevOps for Azure SQL &#8211; as an example, SSDT or other tools to perform state-based or migration-based development and release. You can also select from solutions like GitHub Actions or Azure DevOps pipelines (or even a combination thereof). Use what is most appropriate and efficient for your scenario. In upcoming posts, we will show you more examples of these approaches and tools in action. Stay tuned!<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>DevOps is a broad term with multiple technology and process options at hand. Read this post for an overview of what DevOps for Azure SQL means, and review some options for tools and processes to use.<\/p>\n","protected":false},"author":32627,"featured_media":81,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,92],"tags":[95,30,93,94],"class_list":["post-127","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-devops","tag-azure-devops","tag-developers","tag-devops","tag-github"],"acf":[],"blog_post_summary":"<p>DevOps is a broad term with multiple technology and process options at hand. Read this post for an overview of what DevOps for Azure SQL means, and review some options for tools and processes to use.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/127","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/32627"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=127"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/127\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/81"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}