データベースプロジェクトを使用した SQL Server ベースの環境の CI/CD at SE の雑記

SQL Server ベースの環境向けの Visual Studio のプロジェクトとして、データベースプロジェクトがあります。

このプロジェクトは当初はオフラインデータベース開発向けの機能として実装が行われていましたが、昨今は SQL Server ベースの環境の CI/CDでも活用が行われています。

データベースプロジェクトについては Visual Studio (or SSDT) だけでなく、Azure Data Studio (ADS) の SQL Database プロジェクトの拡張機能でも作成することができます。

SQL Database プロジェクトの拡張機能

最近、データベースプロジェクトを活用した CI/CD について調査を行っていましたので、分かった内容をまとめておきたいと思います。

Contents

1 Azure Data Studio のデータベースプロジェクトの前提となる知識
2 Azure Data Studio のデータベースプロジェクトを使用した DACPAC の作成と活用
3 GitHub Actions を使用した CI/CD

Azure Data Studio のデータベースプロジェクトの前提となる知識

Azure Data Studio のデータベースプロジェクト

データベースプロジェクトについては、前述の次のリンクで情報を確認することができます。

プロジェクト指向のオフラインデータベース開発: Visual Studio を使用したデータベースプロジェクト開発
SQL Database プロジェクトの拡張機能: Azure Data Studio を使用したデータベースプロジェクト開発

Visual Studio を使用した、データベースプロジェクトではクエリのデバッグ等も行うことができますが、Azure Data Studio を使用したデータベースプロジェクト開発については DACPAC を作成するためのツールとして考えたほうが良いかと思います。

SDK スタイルのデータベースプロジェクト

Azure Data Studio でデータベースプロジェクトを作成する場合には、従来型のデータベースプロジェクトと SDK スタイルのデータベースプロジェクトのどちらを使用するかを選択することができます。(デフォルトは SDK スタイル)

SDK スタイルのプロジェクトについては、次のドキュメントで解説が行われています。

SQL Database プロジェクトの拡張機能と SDK スタイルの SQL プロジェクトを併用する (プレビュー)

従来型のデータベースプロジェクトは「msbuild」を使用したビルドを主なターゲットとして考えられたもの (適切なオプションを指定すれば dotnet build でもビルドできます) となり、SDK スタイルのプロジェクトは「dotnet build」を使用したビルドを主なターゲットとして考えられたものとなるのかと思っています。

SDK スタイルのデータベースプロジェクトのほうがプロジェクトファイルもシンプルになっており、「dotnet build」を Linux 上で実行してビルドするということもできますので、CI/CD で使用する汎用的なホステッドランナーの選択性も増えるのではないでしょうか。(ビルドするための Windows が必須というわけではなくなる)

Microsoft.Build.Sql (Preview) に記載されていますが、現状 SDK スタイルのプロジェクトは Azure Data Studio (または Visual Studio Code) でサポートされており、Visual Studio については将来的な追加予定となっており 2024/3 時点では追加は行われていません。(Visual Studio で作成できるのは従来型のデータベースプロジェクト)

DACPAC

DACPAC は、データ層アプリケーション (DAC) の成果物となり「.dacpac」の拡張子を持つ ZIP 圧縮されたファイルとなります。

データ層アプリケーションフレームワーク (DacFx) により作成が行われており、ツールとしては SqlPackage が提供されています。
SSMS から操作する場合は「データ層アプリケーションの抽出」から作成することができます。

DACPAC はデータベースのスキーマ情報となり、実データを含まない構成情報のみを含んだファイルとなります。(.bacpac であればデータが含まれたものとなります)

最新のドキュメントでは一覧化されたものがないのですが、古いドキュメントではサポート対象のオブジェクトが一覧化されています。

このようなオブジェクトの定義を含んだファイルが DACPAC となります。

DACPAC を使用した代表的な操作

DACPAC を使用して実行できる操作については、SqlPackage の内容が参考となります。

代表的な操作としては次の二種類となるかと思います

どちらも処理対象とした DACPAC / SQL Server ベースの環境との差分を確認し、増分更新を行うための操作となります。

Publish は実際にデータベースに対して増分更新を実行し、Script は増分更新スクリプトの生成のみを実行し実際の反映は行わない処理となります。

データベースのスキーマ更新は動作に対しての影響が大きい処理となりますので、実運用では「Script」操作によるスクリプト生成までを実行し、実際の反映については手動で実施してもよいかと思います。

今回は次のような処理の実現を行っています。

データベースプロジェクトで、データベースの各オブジェクトが「.sql」ファイルとして管理されたプロジェクトにより、データベーススキーマを管理
データベースプロジェクトで管理するファイルに変更があった場合はプロジェクトをビルドし、DACPAC を作成
作成された DACPAC と実際のデータベースのスキーマを比較して増分更新用のスクリプトを生成

Azure Data Studio のデータベースプロジェクトを使用する前に必要となる作業

Azure Data Studio でデータベースプロジェクトを使用する際には、拡張機能として「SQL Database Projects」をインストールする必要がありますが、それ以外にもいくつかの作業が必要となります。

dotnet のパッケージソースとして Nuget を追加

一般的な問題に記載されていますが、初期状態でデータベースプロジェクトをビルドしようとするとビルドでエラーが発生します。

dotnet nuget add source https://api.nuget.org/v3/index.json -n nuget.org

このコマンドを実行し、Nuget をパッケージソースとして追加しておくことでビルドエラーを解消することができます。

使用する .NET SDK のバージョン

英語環境で使用している場合は問題ないと思うのですが、日本語環境で使用している場合、デフォルトでインストールを求められる .NET SDK 6.x だと、ビルドしたときの「出力」タブのメッセージが文字化けしていました。

.NET SDK 7.0 / 8.0 で試したところ文字化けしませんでしたので、文字化けが発生した場合は .NET のダウンロードから、7.0 か 8.0 をダウンロードしてインストールしてみるとよいかもしれません。

Azure Data Studio のデータベースプロジェクトは .NET 6.0 を想定しているようですが、私が試した範囲では 7.0 / 8.0 でも動作しました。

Azure Data Studio のデータベースプロジェクトを使用した DACPAC の作成と活用

データベースプロジェクトへのスキーマの取り込み

データベースプロジェクトへのスキーマの取り込みはいくつかの方法があります。

データベースプロジェクト内に新規にファイルを作成し、プロジェクトでいちからスキーマを作成する
既存データベースに接続し、データベースの内容をプロジェクトに取り込む (既存データベースからの直接のコード化)
出力済みの DACPAC をプロジェクトに取り込む

Visual Studio であればすべてのパターンに対応していますが、Azure Data Studio については「1」「2」のサポートとなり、「3」はサポートされていません。

DACPAC の作成

Azure Data Studio を使用した DACPAC の作成については SQL Database プロジェクトの拡張機能のドキュメントを見ればよいかと。

主に使用する操作としてはプロジェクトを右クリックした際に表示されるメニューの「ビルド」「公開」となります。

「ビルド」については DACPAC の作成を行い、「公開」については、Publish または Script のいずれかの操作を行うことができます。

公開から遷移した UI で「スクリプトの生成」を行うことで増分スクリプトの生成のみを行うことが可能です。

実際に作成されたスクリプトが以下となります。新しく追加された列の認識 / 追加されたインデックスの作成 / ストアドプロシージャの変更といった内容が考慮されスクリプトの生成が行われています。

現状、GitHub Actions / Azure DevOps のランナーでは「DacFx 162.2.111.2」が使用されていますので、インデックスの作成についてはオンライン実行としてスクリプトを生成することができるようになっています。

/*
ProjectDB の配置スクリプト

このコードはツールによって生成されました。
このファイルへの変更は、正しくない動作の原因になる可能性があると共に、
コードが再生成された場合に失われます。
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "ProjectDB"
:setvar DefaultFilePrefix "ProjectDB"
:setvar DefaultDataPath "D:\Database\"
:setvar DefaultLogPath "D:\Database\"

GO
:on error exit
GO
/*
SQLCMD モードを検出して、SQLCMD モードがサポートされていない場合にスクリプトの実行を無効にします。
SQLCMD モードを有効化した後でスクリプトを再度有効にするには、次を実行します:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'このスクリプトを正常に実行するには SQLCMD モードを有効にする必要があります。';
        SET NOEXEC ON;
    END


GO
USE [$(DatabaseName)];


GO
PRINT N'インデックス [dbo].[T01].[NCIX_T01_IDX02] を削除しています...';


GO
DROP INDEX [NCIX_T01_IDX02]
    ON [dbo].[T01];


GO
PRINT N'テーブル [dbo].[T01] を変更しています...';


GO
ALTER TABLE [dbo].[T01]
    ADD [C4] INT NULL;


GO
PRINT N'インデックス [dbo].[T01].[NCIX_T01_IDX02] を作成しています...';


GO
CREATE NONCLUSTERED INDEX [NCIX_T01_IDX02]
    ON [dbo].[T01]([C2] ASC) WITH (ONLINE = ON);


GO
PRINT N'インデックス [dbo].[T01].[NCIX_T01_IDX03] を作成しています...';


GO
CREATE NONCLUSTERED INDEX [NCIX_T01_IDX03]
    ON [dbo].[T01]([C3] ASC)
    INCLUDE([C2]) WITH (ONLINE = ON);


GO
PRINT N'プロシージャ [dbo].[usp_Q01] を変更しています...';


GO
ALTER PROCEDURE [dbo].[usp_Q01]
  @param1 int = 0,
  @param2 int
AS
  SELECT @param1, @param2
RETURN 0
GO
PRINT N'更新が完了しました。';


GO

このように、実際のデータベースの状態とデータベースプロジェクトのスクリプトの比較を行い、差分を解消するスクリプトを作成 / 公開するのがデータベースプロジェクトを使用した CI/CD の特徴となるのではないでしょうか。

以下は実際に試した際に気づいた内容となります。

データベースプロパティの設定を GUI で行うことができなかった

Visual Studio でデータベースプロジェクトを作成する場合、プロジェクトのプロパティからデータベースの設定を変更することができます。

現状、このようなインタフェースは Azure Data Studio では提供されていないため、データベースのプロパティ設定を変更する場合には、手動で「.sqlproj」を変更する必要がありそうでした。

「PropertyGroup」に次のような設定を行うことで、Publish 先として指定した環境で増分更新ができていましたので、設定としては想定通り動作するかと思います。

 <PropertyGroup>
    <ModelCollation>1041,CI</ModelCollation>
    <TargetDatabaseSet>True</TargetDatabaseSet>
    <DefaultCollation>Japanese_CI_AS</DefaultCollation>
    <QueryStoreDesiredState>ReadWrite</QueryStoreDesiredState>
    <QueryStoreCaptureMode>Auto</QueryStoreCaptureMode>
  </PropertyGroup>

ディレクトリとしてファイルは管理するがデータベースプロジェクトのビルド対象からは除外

データベースプロジェクトでは、次のようなオブジェクトの追加を行うことができます。

プロジェクトで管理しているディレクトリには運用のスクリプトなどを含めておき、この状態でソース管理を行いたいケースもあるかと思います。

データベースプロジェクトのペインで表示されている内容は、ビルドをした時の対象となるファイルとなるため、運用用の SQL スクリプトがこのペインで確認できるファイルとして含まれている場合はビルドエラーが発生する可能性があります。

「データベースプロジェクトとしてソース管理をしておきたいがビルド対象からは除外したい」という場合は、データベースプロジェクトで「プロジェクトから除外する」をクリックしておくことでビルド対象外とすることができます。

私は「Scripts」というディレクトリを作成し、そのディレクトリは以下はビルド対象外とするような設定としています。

発行プロファイルの設定

公開時に発行プロファイルを指定することで、DACPAC と比較対象となる SQL Server ベースの環境で差分があった場合にどのように処理を行うかを設定することができます。

設定は、GUI から発行プロファイルの XML を次のような情報を参考にしながらカスタマイズすることで実施できます。

検証で使用した発行プロファイルは次のような内容となります。

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="15.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <TargetDatabaseName>ProjectDB</TargetDatabaseName>
    <AllowIncompatiblePlatform>True</AllowIncompatiblePlatform>
    <DropObjectsNotInSource>False</DropObjectsNotInSource>
    <DropPermissionsNotInSource>False</DropPermissionsNotInSource>
    <PerformIndexOperationsOnline>True</PerformIndexOperationsOnline>
    <IgnoreKeywordCasing>False</IgnoreKeywordCasing>
    <IgnoreSemicolonBetweenStatements>False</IgnoreSemicolonBetweenStatements>
    <AllowDropBlockingAssemblies>True</AllowDropBlockingAssemblies>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

GitHub Actions を使用した CI/CD

データベースプロジェクトを使用することで、データベースのスキーマを .sql ファイルとして管理することができます。

データベースのスキーマをファイルとして管理をすることができるようになるとソース管理システム上でデータベースのスキーマの変更を管理することができるようになります。

私が触れる機会があるのが GitHub と Azure DevOps になるのですが、今回は GitHub Actions でデータベースプロジェクトのビルドと増分更新スキーマの生成を行ってみたいと思います。

CI/CD で活用できるタスク

どちらのソースコード管理でもデータベースプロジェクトに活用できるタスクが提供されています。

GitHub

sql-action

Azure DevOps

DotNetCoreCLI@2 – .NET Core v2 タスク
- MSBuild@1 – MSBuild v1 タスク: プロジェクトの種類によってはこちらを使用
SqlAzureDacpacDeployment@1 – Azure SQL Database deployment v1 タスク

GitHub Actions による増分更新スクリプトの作成

GitHub Actions のワークフローとしては、次のような設定を行うことで増分更新スクリプトを作成できます。

name: SQL Server CI Sample
on:
  push:
    branches: [ "master" ]
    paths: 
    - "ProjectDB/**"
  workflow_dispatch:
 
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Build DACPAC
        run: dotnet build ./ProjectDB/ProjectDB.sqlproj -c Release
      - name: Create SQL Script
        uses: azure/sql-action@v2.2.1
        with:
          action: "script"
          connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
          path: "./ProjectDB/bin/Release/ProjectDB.dacpac"
          arguments: "/OutputPath:./script.sql /p:DropObjectsNotInSource=False /p:DropPermissionsNotInSource=False /p:PerformIndexOperationsOnline=False"
      - name: Upload Build Artifact
        uses: actions/upload-artifact@v4
        with:
          path: "./script.sql"
          overwrite: true

「ProjectDB」というディレクトリ配下にデータベースプロジェクトのファイル一式を配置しており、そのディレクトリ内 (データベースプロジェクトで管理されているファイル) のファイルの変更をトリガーとしています。

ワークフローではデータベースプロジェクトをビルドしてDACPAC を作成し、作成された DACPAC とシークレットに設定した SQL Database の接続文字列の接続先を比較して、増分スクリプトの生成を行っています。

ワークフローの実行が完了すると、アーティファクトとして増分更新用のスクリプトがアップロードされますので、このスクリプトを使用して増分の反映を行うことができます。

Azure DevOps のパイイプラインによる増分スクリプトの作成

類似の実装は Azure DevOps でも次のようなパイプラインを使用して実現できるかと。

trigger:
  branches:
    include:
      - master
  paths:
    exclude:
      - pipelines/*

pool:
  vmImage: windows-2022

steps:
- task: DotNetCoreCLI@2
  inputs:
    command: 'build'
    arguments: '--configuration Release'

- task: SqlAzureDacpacDeployment@1
  name: DacPacDeploy
  inputs:
    deployType: DacpacTask
    DeploymentAction: Script
    DacpacFile: '$(Build.Repository.LocalPath)/bin/Release/$(BACPACName)'
    azureSubscription: $(AzureSubscription)
    AuthenticationType: server
    ServerName: $(ServerName)
    DatabaseName: $(Databasename)
    SqlUsername: $(LoginName)
    SqlPassword: $(LoginPassword)
    AdditionalArguments: '/p:ExcludeObjectTypes="Users" /p:CommentOutSetVarDeclarations=true /p:PerformIndexOperationsOnline=true'

- task: PublishBuildArtifacts@1
  inputs:
    PathtoPublish: '$(Build.Repository.LocalPath)/bin/Release/$(BACPACName)'
    ArtifactName: 'DACPAC'
    publishLocation: Container
- task: PublishBuildArtifacts@1
  inputs:
    PathtoPublish: '$(DacPacDeploy.SqlDeploymentOutputFile)'
    ArtifactName: 'SQLScript'
    publishLocation: Container

月	火	水	木	金	土	日
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

SE の雑記