データベースサーバー

Microsoft SQL Server

概要

Microsoft SQL Serverは、マイクロソフトが開発・提供する企業向けリレーショナルデータベース管理システム（RDBMS）です。Windowsプラットフォームを中心に、Linux環境でも動作する高性能なデータベースソリューションとして、世界中の企業で広く採用されています。1989年に初版がリリースされて以来、継続的な機能強化が行われ、現在では高可用性、ビジネスインテリジェンス、機械学習機能まで統合した包括的なデータプラットフォームへと進化しています。

詳細

SQL Serverは、エンタープライズレベルのデータ管理要件に対応する包括的なデータベースプラットフォームです。標準SQLに加えて、独自拡張のT-SQL（Transact-SQL）を提供し、ストアドプロシージャ、トリガー、ユーザー定義関数などの高度なプログラミング機能をサポートしています。

主要な特徴として、Always On可用性グループによる高可用性とディザスタリカバリ、クエリストアによる自動パフォーマンスチューニング、列ストアインデックスによる分析クエリの高速化、In-Memory OLTPによるトランザクション処理の高速化などがあります。

SQL Serverは複数のエディションを提供しており、Express（無料版）、Standard、Enterprise、Developerエディションがあります。Enterpriseエディションでは、最大8つのセカンダリレプリカをサポートするAlways On可用性グループ、自動チューニング、高度な暗号化機能など、ミッションクリティカルなワークロードに必要な機能を提供します。

近年では、Azure SQL DatabaseやAzure SQL Managed Instanceとして、クラウドネイティブなマネージドサービスも提供され、オンプレミスとクラウドのハイブリッド展開が可能です。また、SQL Server 2017以降ではLinuxサポートが追加され、Docker コンテナでの実行も可能になりました。

メリット・デメリット

メリット

• Windowsとの高い統合性: Active Directory、.NET Framework、PowerShellとのシームレスな統合
• 包括的なツールセット: SQL Server Management Studio（SSMS）、SQL Server Data Tools（SSDT）など充実した管理・開発ツール
• 優れたパフォーマンス: クエリストア、自動チューニング、列ストアインデックス、In-Memory OLTPによる高速処理
• 高可用性ソリューション: Always On可用性グループ、フェールオーバークラスタリング、データベースミラーリング
• 統合されたBI機能: SQL Server Reporting Services（SSRS）、SQL Server Analysis Services（SSAS）、SQL Server Integration Services（SSIS）
• セキュリティ機能: 透過的データ暗号化（TDE）、Always Encrypted、行レベルセキュリティ、動的データマスキング
• クロスプラットフォーム: Windows、Linux、Docker環境での実行をサポート
• Azureとの統合: Azure SQL Database、Azure Synapse Analyticsとのシームレスな連携

デメリット

• ライセンスコスト: Enterprise版は高額で、コア単位のライセンスモデルが複雑
• リソース消費: 大量のメモリとCPUを必要とし、適切なサイジングが重要
• Windows中心の設計: Linux版は機能制限があり、一部の機能はWindows版でのみ利用可能
• 複雑な管理: 高度な機能を活用するには専門的な知識と経験が必要
• アップグレードの複雑さ: メジャーバージョンアップグレードには慎重な計画と検証が必要
• ベンダーロックイン: T-SQL固有の機能への依存により、他のデータベースへの移行が困難
• サイズ制限: Express版には10GBのデータベースサイズ制限がある

参考ページ

• Microsoft SQL Server 公式サイト
• SQL Server ドキュメント
• SQL Server Management Studio (SSMS)
• Azure SQL Database
• SQL Server Data Tools (SSDT)
• SQL Server Express Edition（無料版）
• SQL Server サンプルデータベース
• SQL Server Community

書き方の例

基本的な接続とクエリ

-- SQL Server Management Studio または sqlcmd での接続
-- 統合認証
sqlcmd -S localhost\SQLEXPRESS -E
-- SQL Server認証
sqlcmd -S localhost\SQLEXPRESS -U sa -P password

-- 基本的なSELECT文
SELECT 
    ProductID,
    Name,
    ProductNumber,
    StandardCost,
    ListPrice,
    ListPrice - StandardCost AS Profit
FROM Production.Product
WHERE ListPrice > 0
ORDER BY Profit DESC;

-- テーブルの作成
CREATE TABLE Sales.Customer (
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE,
    CreatedDate DATETIME2 DEFAULT SYSDATETIME(),
    ModifiedDate DATETIME2 DEFAULT SYSDATETIME()
);

T-SQLストアドプロシージャ

-- ストアドプロシージャの作成
CREATE OR ALTER PROCEDURE Sales.usp_GetCustomerOrders
    @CustomerID INT,
    @StartDate DATE = NULL,
    @EndDate DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;
    
    -- パラメータのデフォルト値設定
    IF @StartDate IS NULL
        SET @StartDate = DATEADD(YEAR, -1, GETDATE());
    
    IF @EndDate IS NULL
        SET @EndDate = GETDATE();
    
    -- エラーハンドリングを含むクエリ
    BEGIN TRY
        SELECT 
            o.OrderID,
            o.OrderDate,
            o.TotalAmount,
            od.ProductID,
            p.Name AS ProductName,
            od.Quantity,
            od.UnitPrice
        FROM Sales.Orders o
        INNER JOIN Sales.OrderDetails od ON o.OrderID = od.OrderID
        INNER JOIN Production.Product p ON od.ProductID = p.ProductID
        WHERE o.CustomerID = @CustomerID
            AND o.OrderDate BETWEEN @StartDate AND @EndDate
        ORDER BY o.OrderDate DESC;
        
        -- 注文サマリーも返す
        SELECT 
            COUNT(DISTINCT OrderID) AS TotalOrders,
            SUM(TotalAmount) AS TotalSpent,
            AVG(TotalAmount) AS AverageOrderValue
        FROM Sales.Orders
        WHERE CustomerID = @CustomerID
            AND OrderDate BETWEEN @StartDate AND @EndDate;
            
    END TRY
    BEGIN CATCH
        -- エラー情報を取得
        SELECT 
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_MESSAGE() AS ErrorMessage,
            ERROR_LINE() AS ErrorLine,
            ERROR_PROCEDURE() AS ErrorProcedure;
        
        -- エラーを再発生
        THROW;
    END CATCH
END;
GO

-- ストアドプロシージャの実行
EXEC Sales.usp_GetCustomerOrders 
    @CustomerID = 123,
    @StartDate = '2024-01-01',
    @EndDate = '2024-12-31';

Always On可用性グループ

-- 可用性グループの作成（プライマリレプリカで実行）
CREATE AVAILABILITY GROUP [AG_Production]
WITH (
    AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
    DB_FAILOVER = ON,
    DTC_SUPPORT = PER_DB
)
FOR DATABASE [AdventureWorks], [Sales]
REPLICA ON 
    N'SQL-SERVER-01' WITH (
        ENDPOINT_URL = N'TCP://SQL-SERVER-01.domain.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY,
                      READ_ONLY_ROUTING_URL = N'TCP://SQL-SERVER-01.domain.com:1433')
    ),
    N'SQL-SERVER-02' WITH (
        ENDPOINT_URL = N'TCP://SQL-SERVER-02.domain.com:5022',
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        FAILOVER_MODE = AUTOMATIC,
        BACKUP_PRIORITY = 50,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY,
                      READ_ONLY_ROUTING_URL = N'TCP://SQL-SERVER-02.domain.com:1433')
    );

-- リスナーの作成
ALTER AVAILABILITY GROUP [AG_Production]
ADD LISTENER N'AG-LISTENER' (
    WITH IP ((N'192.168.1.100', N'255.255.255.0')),
    PORT = 1433
);

-- 読み取り専用ルーティングの設定
ALTER AVAILABILITY GROUP [AG_Production]
MODIFY REPLICA ON N'SQL-SERVER-01'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SQL-SERVER-02','SQL-SERVER-01')));

パフォーマンスチューニング

-- クエリストアの有効化
ALTER DATABASE [AdventureWorks] 
SET QUERY_STORE = ON (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1000,
    INTERVAL_LENGTH_MINUTES = 60,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 200
);

-- 実行プランの分析
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT c.CustomerID, c.FirstName, c.LastName,
       COUNT(o.OrderID) AS OrderCount,
       SUM(o.TotalAmount) AS TotalSpent
FROM Sales.Customer c
LEFT JOIN Sales.Orders o ON c.CustomerID = o.CustomerID
WHERE o.OrderDate >= DATEADD(MONTH, -6, GETDATE())
GROUP BY c.CustomerID, c.FirstName, c.LastName
HAVING COUNT(o.OrderID) > 5
ORDER BY TotalSpent DESC;

-- 列ストアインデックスの作成
CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactSales
ON Sales.FactSales
WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0);

-- 自動チューニングの有効化
ALTER DATABASE [AdventureWorks] 
SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);

In-Memory OLTP

-- メモリ最適化ファイルグループの追加
ALTER DATABASE [AdventureWorks] 
ADD FILEGROUP [InMemory_FG] CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE [AdventureWorks] 
ADD FILE (
    NAME = N'InMemory_File',
    FILENAME = N'C:\Data\AdventureWorks_InMemory.ndf'
) TO FILEGROUP [InMemory_FG];

-- メモリ最適化テーブルの作成
CREATE TABLE Sales.OrdersInMemory (
    OrderID INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
    CustomerID INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    Status TINYINT NOT NULL,
    
    INDEX IX_CustomerID NONCLUSTERED (CustomerID),
    INDEX IX_OrderDate NONCLUSTERED (OrderDate)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- ネイティブコンパイルストアドプロシージャ
CREATE PROCEDURE Sales.usp_InsertOrderInMemory
    @CustomerID INT,
    @TotalAmount DECIMAL(18,2)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'日本語'
)
    INSERT INTO Sales.OrdersInMemory (CustomerID, OrderDate, TotalAmount, Status)
    VALUES (@CustomerID, SYSDATETIME(), @TotalAmount, 1);
END;

セキュリティ機能

-- 透過的データ暗号化（TDE）の有効化
-- マスターキーの作成
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password_123!';

-- 証明書の作成
CREATE CERTIFICATE TDE_Certificate 
WITH SUBJECT = 'TDE Certificate for AdventureWorks';

-- データベース暗号化キーの作成
USE [AdventureWorks];
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;

-- TDEの有効化
ALTER DATABASE [AdventureWorks]
SET ENCRYPTION ON;

-- Always Encryptedの設定
CREATE COLUMN MASTER KEY [CMK_AlwaysEncrypted]
WITH (
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/My/Certificate_Thumbprint'
);

CREATE COLUMN ENCRYPTION KEY [CEK_AlwaysEncrypted]
WITH VALUES (
    COLUMN_MASTER_KEY = [CMK_AlwaysEncrypted],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x...
);

-- 行レベルセキュリティ
CREATE FUNCTION Security.fn_SecurityPredicate(@UserID INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
    SELECT 1 AS fn_SecurityPredicate_Result
    WHERE @UserID = USER_ID() OR IS_ROLEMEMBER('db_owner') = 1
);

CREATE SECURITY POLICY Sales.CustomerSecurityPolicy
ADD FILTER PREDICATE Security.fn_SecurityPredicate(UserID) 
ON Sales.Customer
WITH (STATE = ON);