SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

Azure Stack の SQL リソースプロバイダーの DB 作成で実行されているクエリを確認する

leave a comment

Azure Stack の SQL リソースプロバイダーで作成されている DB とログインのクエリをまとめてみたいと思います。


ログインと DB の内容ですが、スタンドアロンと AlwaysOn 可用性グループでは大きく構成が異なっています。

ログイン

スタンドアロンの場合、次のようなクエリでログインが作成されます。

USE [master];
SELECT 1 FROM master.sys.sql_logins WHERE name = @loginname
USE [master];
CREATE LOGIN [@logonname] WITH PASSWORD = @logonpassword
USE [master];
IF NOT EXISTS(SELECT name FROM sys.database_principals WHERE name = @logonname)
CREATE USER [@logonname] FOR LOGIN [@logonname ]
USE [master];
IF EXISTS(SELECT name FROM sys.syslogins WHERE name = @loginname)
DROP USER [@loginname]
USE [@databasename];
IF EXISTS(SELECT name FROM sys.database_principals WHERE name = @logonname)
DROP USER [@logonname];
EXEC dbo.sp_changedbowner @loginame = @logonname , @map = false

 
インスタンスに通常のログインとして作成が行われ、DB オーナーの権限が付与された状態となりますので、インスタンスに対しての操作権限はありませんが、作成した DB に対してはすべての権限を持つログインとして利用することが可能です。
また、インスタンスに対してのログインとして作成されますので、同一のログインを付与した DB であれば、複数の DB を跨いで操作することが可能な環境となります。
image
AlwaysOn 可用性グループの場合は、次のクエリでログインが作成されます。

USE [master];
SELECT 1 FROM master.sys.sql_logins WHERE name = @loginname
USE [master];
SELECT 1 FROM master.sys.sql_logins WHERE name = @loginname
USE [@databasename]
CREATE USER [@logonname] WITH PASSWORD = @logonpassword
sp_addrolemember N'db_owner', @logonname;
USE [@databasename];
DENY BACKUP DATABASE TO [@logonname];
DENY CHECKPOINT TO [@logonname];

権限系については、バックアップとチェックポイントの明示的な実行が拒否されています。

AlwaysOn 可用性グループの場合は、通常のログインではなく、DB にパスワードを持つユーザーとして作成が行われます。

そのため master には接続ができず、接続をする際には、DB 名を明示的に指定した形で接続をする必要があります。
また、同一のログインを指定して、異なる DB を作成したとしても、そのユーザー間は異なる SID で作成されており、DB に対して、「SET TRUSTWORTHY ON」が設定されていない状態となりますので、DB を跨いだクエリの実行を行うことができません。
image
 
ログインについては、次のような違いがありますので、この辺は注意しておいた方がよいかと。

  • スタンドアロン : DB を跨いで接続をすることが可能
  • AlwaysOn 可用性グループ : DB を跨いで接続することは不可

 

データベース

スタンドアロンの場合は次のようなクエリで DB が作成されます。

USE [master];
SELECT 1 FROM sys.databases WHERE name = @databasename
CREATE DATABASE [@databasename] COLLATE  @collation
ALTER DATABASE [@databasename] MODIFY FILE (NAME = @databasename, MAXSIZE = UNLIMITED, FILEGROWTH = @databasesize MB)
ALTER DATABASE [@databasename] MODIFY FILE (NAME = @databasename , MAXSIZE = @databasesize MB)

 
DB の作成については作成時に指定した照合順序と、DB サイズが使用されて作成されることが特徴的ですね。
AlwaysOn 可用性グループについては次のようなクエリで DB が作成されます。

USE [master];
SELECT 1 FROM sys.databases WHERE name = @databasename
CREATE DATABASE [@databasename ] CONTAINMENT = PARTIAL COLLATE @collation
ALTER DATABASE [@databasename] MODIFY FILE (NAME = @databasename, MAXSIZE = UNLIMITED, FILEGROWTH = @databasesize MB)
ALTER DATABASE [@databasename] MODIFY FILE (NAME = @databasename , MAXSIZE = @databasesize MB)
BACKUP DATABASE @databasename] TO DISK = 'NUL' WITH NOFORMAT, INIT, NAME = @databasename, SKIP;
ALTER AVAILABILITY GROUP [@availabilitygroupname] ADD DATABASE [@databasename];

 
可用性グループの場合は、DB 作成後に初回のバックアップを NULL デバイスに取得して、その後リスナーが設定されている可用性グループに対して DB の追加が行われ、自動的に可用性グループに組み込まれるような設定となっています。
DB を作成するだけで可用性グループに組み込まれ冗長性が担保されるというのは、SQL Database と同じ思想ですね。
実際に作成されるリソースがどのようなクエリが使用され、作成されているかを把握することは、「何ができるか」を判断するうえでポイントとなりますので、この辺の情報は持っていた方が良いのではないでしょうか。

Share

Written by Masayuki.Ozawa

7月 9th, 2018 at 12:01 am

Posted in Azure Stack

Tagged with

Leave a Reply