Azure SQL Databaseを使ったPower Platformの検討


**Power Platform**はデータベースに**Dataverse**を利用することが多いですが、**Azure SQL Database**を使うことも可能です。**Azure SQL Database**を使った場合を検討してみます。


**Azure SQL Database**を使う場合、**SQL Server**コネクタを利用する方法と**仮想テーブル**を利用する方法があります。

https://powerautomate.microsoft.com/ja-jp/connectors/details/shared_sql/sql-server/

https://learn.microsoft.com/ja-jp/power-apps/maker/data-platform/create-edit-virtual-entities

**SQL Server**コネクターを利用して**Azure SQL Database**を使う場合。

```plantuml
"Power Apps"->"SQL Serverコネクター" 
"SQL Serverコネクター"->"Azure SQL Database" 
"Azure SQL Database"->"SQL Serverコネクター" 
"SQL Serverコネクター"->"Power Apps" 
```

**仮想テーブル**を利用する場合は、**Dataverse**を経由して**Azure SQL Server**へ接続します。

```plantuml
"Power Apps"->"Dataverseコネクター" 
"Dataverseコネクター"->"Dataverse" : 仮想テーブル
"Dataverse"->"SQL Serverコネクター"
"SQL Serverコネクター"->"Azure SQL Database"
"Azure SQL Database"->"SQL Serverコネクター"
"SQL Serverコネクター"->"Dataverse" 
"Dataverse"->"Dataverseコネクター" : 仮想テーブル
"Dataverseコネクター"->"Power Apps" 
```

## Azure SQL Databaseの準備

Azure SQL Databaseの無料プランを使って準備します。

https://learn.microsoft.com/ja-jp/azure/azure-sql/database/free-offer?view=azuresql

- Azure ポータルでAzure SQL Database for freeを有効にします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/346eb61a-89e1-43ee-75d3-ccebd3206004.png)
- SQL Database サーバーを作成します。今回の認証方法は ``Microsoft Entraのみの認証を使用する`` を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/6bbff349-c710-3e21-8946-fbea980d8c32.png)
- SQL データベースを作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/6a29a4ff-8b7a-d171-4c57-23323ed0eec7.png)
- 接続方法は、``パブリック エンドポイント`` を選択して、``Azure サービスおよびリソースにこのサーバへのアクセスを許可する`` を ``はい``にします。これでPower Platformの**SQL Server**コネクタからアクセスできます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/e08eb094-ab20-88e1-1d8d-74bb7cd892d9.png)

- クエリ エディターを使用してテーブルを作成します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/72745d02-f2d3-a175-bcba-c845020ea7f4.png)

```SQL
CREATE TABLE Member
(
	MemberId INT IDENTITY PRIMARY KEY,
	FirstName NVARCHAR(128) NOT NULL,
	LastName NVARCHAR(128) NOT NULL,
	DateOfBirth DATE
);

CREATE TABLE Team
(
	TeamId INT IDENTITY PRIMARY KEY,
	GroupName NVARCHAR(128) NOT NULL
);

CREATE TABLE TeamMember
(
	TeamMemberId INT IDENTITY PRIMARY KEY,
TeamMemberName NVARCHAR(128),
	MemberId INT REFERENCES Member (MemberId),
	TeamId INT REFERENCES Team (TeamId),
	TeamPoint DECIMAL(5,2) CHECK (TeamPoint >= 0.00 AND TeamPoint <= 100.00)
	CONSTRAINT [UQ_TeamMembers] UNIQUE CLUSTERED
	(
		MemberId, TeamId
	)
);

```
- テーブルを作成します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/409334fa-2afb-111f-4f2e-85eba8463ce4.png)


# SQL Serverコネクタを利用する場合

**SQL Server**コネクターを利用して**Azure SQL Database**を使う場合。

```plantuml
"Power Apps"->"SQL Serverコネクター" 
"SQL Serverコネクター"->"Azure SQL Database" 
"Azure SQL Database"->"SQL Serverコネクター" 
"SQL Serverコネクター"->"Power Apps" 
```

- make.powerapps.comから**SQL Server**コネクタを利用してAzure SQL Serverへ接続します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/9e0b5dab-d0b5-e1d1-1cd2-eda8db7e525d.png)

- **外部データを選択**を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/db524adf-6297-9776-c06a-c9d9c5805639.png)

- ``Authentication type`` は ``Azure AD Integrated``を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/556eb52c-d7c9-1363-e6ef-8b990971a44a.png)

- Azure SQL ServerのDatabaseを選択して先ほど作成したテーブルMemberを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/43f29706-3d0a-ffba-f1a5-fdf58965ea17.png)

- テーブルMemberを閲覧、編集、削除できるアプリが作成されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/de56578c-b98b-a7e4-b86f-a9692e639801.png)

# 仮想テーブルを利用する場合
**仮想テーブル**を利用する場合は、**Dataverse**を経由して**Azure SQL Server**へ接続します。

```plantuml
"Power Apps"->"Dataverseコネクター" 
"Dataverseコネクター"->"Dataverse" : 仮想テーブル
"Dataverse"->"SQL Serverコネクター"
"SQL Serverコネクター"->"Azure SQL Database"
"Azure SQL Database"->"SQL Serverコネクター"
"SQL Serverコネクター"->"Dataverse" 
"Dataverse"->"Dataverseコネクター" : 仮想テーブル
"Dataverseコネクター"->"Power Apps" 
```
- **仮想テーブルを作成**を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/cafec7e7-1988-abe8-848b-042316fff5c1.png)
- **SQL Serverコネクタ**を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/b9ba77ab-d8e3-6296-f9ac-2833f8356a95.png)
- **Azure SQL Server**を入力します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/c2426771-fb65-6fe0-bf88-557f642822df.png)
- テーブルMemberを選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/a5d673ab-9277-b5b6-6e01-c7011b51ac55.png)
- 自動的にマッピング情報が生成されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/5dd1cc7d-6f6d-9c83-0eda-79e3cf3fc19b.png)
- DataverseのテーブルMemberが作成されました。実体はAzure SQL DatabaseのテーブルMemberになります。通常のDataverseのテーブルと同様に利用できます。
例えば、Dataverseの通常テーブルへ仮想テーブルの検索列を追加すれば２つのデータベースを連携することもできます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/f8715170-0183-906c-d328-ee2194228027.png)

:::note warn
注意点としては仮想テーブルを利用する場合、テーブルに文字列型のカラムが１つ以上ないとプライマリーフィールドが設定できないエラーが表示されます。
:::
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/248463/19cd85d5-100a-4e94-0310-de9c030e0a1e.png)

上記の方法で、Azure SQL Databaseを使ったPower Platformが利用できるようになります。

Azure SQL DatabaseであればAzureを利用して大量データのバッチ処理も作成することができるため、Dataverseのプラグインが2分以上実行できない問題も回避しやすくなります。

一方、Dataverseであれば標準で利用できる、行レベルによるアクセス制御、列レベルのセキュリティルール設定などが利用できなくなります。

DataverseとAzure SQL Databaseを組み合わせて、お互いのメリットを利用するのがいいのではないかと思います。

```mermaid
sequenceDiagram

  actor U1 as コンシューマ
  participant M as .NET MAUI
  participant AF as Azure Function
  participant ASD as Azure SQL Database

  participant DV as Dataverse
  participant PA as Power Apps
  actor U2 as エンタープライズ

  U1->>M: アプリ
  M->>AF: API
  AF->>ASD: データ処理
  ASD->>DV: 仮想テーブル
  U2->>PA: Power Apps
  PA->>DV: Dataverse
```