仕事で SQL Server のことを調べる機会があったので、その時調べたことの簡単なメモです。

環境

データベース、スキーマ、テーブル

SQL Server ではインスタンスにデータベースを作成し、データベースの中に “スキーマ” を定義し、スキーマの中にテーブルや Stored Procedure、Function 等を作成します。

スキーマはテーブルや Stored Procedure などをグループ化するためのもので、どのユーザーがどのような操作をできるかといった認可をまとめて設定するためのものです。

スキーマを使わず、各テーブルや Stored Procedure 等に直接権限を設定することもできますが、スキーマを使って権限を管理するのが推奨されています。

スキーマを何も指定しないでテーブル等を作成すると、dbo スキーマというデフォルトで用意されているスキーマにテーブル等が作成されます。

dbo やその他デフォルトで用意されているスキーマでは、db_owner や data_reader といったデフォルトで用意されているロールに対して可能な操作が定義されているので、ユーザーをロールに割り当てるだけですぐに使用できるようになります。

本番環境では権限を厳密に管理するために、自前でスキーマやロールを定義した方が良いと思います。

LOGIN と USER

SQL Server では、”LOGIN” は認証に使われるユーザーアカウントを表し、”USER” は認可に使われるアカウントを表します。

LOGIN はサーバーインスタンスのレベルで作成されます。Windows 認証と SQL Server 認証を選択でき、SQL Server 認証の場合はパスワードを指定して作成します。

USER はデータベースレベルで作成され、各 LOGIN に対して権限を付与する目的で使います。

LOGIN や USER は SQL Server Management Studio (SSMS) を使って UI からも作成できますが、SQL で作成する場合は CREATE LOGIN または CREATE USER を使って作成できます。

SQL

-- LOGIN を作成
USE master;
GO
-- Windows 認証の LOGIN を作成
CREATE LOGIN 'my_domain\my_user' FROM WINDOWS;

-- SQL Server 認証の LOGIN を作成
CREATE LOGIN 'my_user' WITH PASSWORD = 'password';

-- USER を作成。USER はデータベース毎に作成します。
USE my_database;  -- USER を作成するデータベースを指定
GO

-- Windows 認証 LOGIN から USER を作成
CREATE USER 'my_domain\my_user' FOR LOGIN 'my_domain\my_user';

-- SQL Server 認証 LOGIN から USER を作成
CREATE USER 'my_user' FOR LOGIN 'my_user';

-- USER に db_owner ロールを付与する例
ALTER ROLE db_owner ADD MEMBER 'my_domain\my_user';
ALTER ROLE db_owner ADD MEMBER 'my_user';

リモート接続設定

リモートホストから SQL Server にアクセスする場合、通常は TCP が使用されますが、SQL Server ではデフォルトで TCP 接続が有効になっていません。

また Windows Firewall でも SQL Server が使用するポートの受信許可を設定する必要があります。

(デフォルトインスタンスを選択して SQL Server をインストールした場合、使用されるポートは 1433 です)

UI から設定する場合は、SQL Server 構成マネージャーを使って TCP を有効化し、Windows Firewall で新しい受信ルールを追加して TCP ポート 1433 を許可します。

コマンドで設定する場合は、以下のようにリモート接続を有効化できます。

cmd

# SQL Server で TCP 接続を有効化
> sqlps
PS> $wmi = New-Object 'Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer' localhost
PS> $tcp = $wmi.ServerInstances['MSSQLSERVER'].ServerProtocols['Tcp']
PS> $tcp.IsEnabled = $true
PS> $tcp.Alter()
PS> Restart-Service -Name MSSQLSERVER -Force
PS> exit

# Firewall で SQL Server のポート 1433 を許可
> netsh advfirewall firewall add rule name="allow-sql-server" dir=in action=allow enable=yes profile=any protocol=tcp localport=1433 remoteport=any

メモリとデータファイル

メモリ使用量について

SQL Server では、使用するメモリの容量は動的に拡張され、デフォルトでは最大 “サーバーに搭載されているメモリ – 5MB” まで拡張されます。

基本的には SQL Server が自動で管理するのに任せるのが推奨されていますが、ユーザーが使用されるメモリの上限を設定することもできます。上限を設定するには以下の SQL を実行します。

SQL

EXEC sp_configure 'max server memory', 1024  -- メモリ上限を MB 単位で設定
GO
RECONFIGURE
GO

メモリの使用量を確認するには、動的管理ビューを確認するのが簡単だと思います。他に Windows Server に用意されているパフォーマンスカウンターでも確認することもできます。

SQL

-- 動的管理ビューで用途毎のメモリ使用量を確認する例
SELECT TOP 10 type, sum(pages_kb) AS memory_allocated_to_memory_clerk
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY sum(pages_kb) DESC;

データファイルについて

SQL Server では、テーブルやインデックスに保存されるデータは .mdf ファイルに保存され、トランザクションログは .ldf ファイルに保存されます。

これらのファイルの容量も自動で拡張されます。

ファイルサイズを拡張する際のタイムアウトは 30秒と固定で、SQL Server 2000 までは “ファイルサイズの何% を拡張する” という動作だったため、ファイルサイズが大きくなった場合に拡張幅も大きくなり、サイズを拡張する時にタイムアウトしてエラーとなる、という問題がありました。

SQL Server 2005 からは、デフォルトの拡張幅が 10MB に変更されたことでこのような問題が発生することは無くなりましたが、それでも自動拡張が頻発する場合はパフォーマンスに影響するため、予め余裕を持ったサイズでデータファイルを作成するのが推奨されています。

(なお SQL Server 2019 では、デフォルトの拡張幅が 64MB になっていました)

自動拡張されるサイズを確認するには sys.database_files というシステムビューを参照します。

このシステムビューでは、データサイズは “ページ数” で表示されます。”ページ” とは SQL Server がデータを読み書きする時の単位となる 8KB の領域を指します。1ページ = 8KB となるため、ページ数を 128 で割ると MB 単位のデータサイズを計算できます。

SQL

/* my_db データベースの自動拡張サイズを 100MB に変更する例 */
USE my_db;
GO

-- データファイルの論理名を確認 
SELECT
  name,  -- データファイルの論理名
  type_desc,
  growth / 128 AS growth_mb,  -- ページ数を 128 で割り MB 単位に変換
  physical_name
FROM sys.database_files;
/*
name		type_desc	growth_mb	physical_name
my_db		ROWS		64		C:\...\DATA\my_db.mdf
my_db_log	LOG		64		C:\...\DATA\my_db_log.ldf
*/

-- 自動拡張サイズを変更
ALTER DATABASE my_db MODIFY FILE 
  (NAME = 'my_db',  -- データファイルの論理名を指定します
   FILEGROUTH = 100MB);

-- 結果を確認
SELECT
  name,
  type_desc,
  growth / 128 AS growth_mb,  -- ページ数を 128 で割り MB 単位に変換
  physical_name
FROM sys.database_files;
/*
name		type_desc	growth_mb	physical_name
my_db		ROWS		100		C:\...\DATA\my_db.mdf
my_db_log	LOG		64		C:\...\DATA\my_db_log.ldf
*/

トランザクションログ

SQL Server のトランザクションログは、.ldf ファイルに保存されます。

SQL Server で何かデータ変更が行われると、その操作がトランザクションログに記録されます。トランザクションの開始や、コミット/ロールバックした時にもログに記録され、サーバーが障害等で急に停止した時、SQL Server はこのトランザクションログの内容を使ってデータファイルを最新の状態に復旧します。

.ldf ファイルは、内部的には複数の “仮想ログファイル” に分割されて管理されています。

トランザクションログは、何もしないと溜まる一方のため定期的にクリーンアップされたり、または逆にファイルサイズが足りなくなると自動で拡張したりしますが、このクリーンアップや自動拡張は仮想ログファイルの単位で行われます。

仮想ログファイルのサイズは動的に決定され、ユーザーがコントロールできる部分ではありません。

ただ、小さい .ldf ファイルには小さい仮想ログファイルが作成される傾向があり、また大きいログファイルには大きい仮想ログファイルが作成される傾向があります。

仮想ログファイルの数が多いと管理にオーバーヘッドがかかるため、小さい仮想ログファイルが大量に作成されるよりは、少数の大きい仮想ログファイルが作成される方が良いと考えられています。

そのため .ldf ファイルを作成する際は、初めからなるべく大きいファイルサイズで作成するのが推奨されています。

まとめ

SQL Server は自動でリソース使用量を調整するため、基本的にはデフォルトの設定のままで良いと思います。

データファイルやトランザクションログファイルのサイズについては、運用開始後の負担を軽減するためにも、初めにデータ量を見積もった上で適切なサイズに設定するのを推奨します。