【保存版】SQL Server でよく遭遇する問題 (Q&A)

SQL Server のインストールは簡単で、すべて既定のパラメータのままインストールを実施したとしても、問題なく動作するデータベース初心者にも優しいDBMS製品となります。

一方で、特に初めて SQL Server インストールして利用する人より、同じような質問を受けることがあります。

今回は、よく受ける質問の内容の一部を紹介したいと思います。

【Q1】SQL Server をインストールしたサーバーからは接続できるが、クライアントにインストールした SQL Server Management Studio (以下 SSMS) から リモート接続することができない。

【A1】

クライアントから SQL Server へのリモート接続ができない要因として、以下の点をチェックします。

1) SQL Server がインストールされたサーバー上の Windows Defender ファイアウォールの「受信の規則」で、SQL Server への通信が許可されていることを確認。

 参考URL

Windows ファイアウォールの構成 - SQL Server | Microsoft Docs

【第3回】基本から始める SQL Server【インスタンス】

2) SQL Server 構成マネージャー (SQL Server Configuration Manager) より、SQL Server ネットワークの構成 - 該当 SQL Server インスタンスの TCP/IP プロトコルが「有効」になっていることを確認。

※ 各プロトコルの有効／無効の状態を変更した場合、SQL Server プロセスの再起動が必要。

参考URL

サーバー ネットワークの構成 - SQL Server | Microsoft Docs

サーバー ネットワーク プロトコルの有効化または無効化 - SQL Server | Microsoft Docs

【Q2】Windows 認証による SQL Server への接続はできるが、SQL Server 認証 (SQL Server ログイン) による SQL Server への認証が失敗し、接続することができない。

【A2】

SQL Server 認証による認証が失敗する要因として、以下の点をチェックします。

1) SSMS から 該当インスタンス - 右クリック - プロパティ - ページの選択：セキュリティ の「サーバー認証」で「SQL Server認証モードと Windows認証モード」が選択されていることを確認。

2) 1) を設定済みの場合、「サーバー認証」設定の変更後、SQL Server プロセスの再起動を実施しているか確認。実施していない場合は、SQL Server プロセスの再起動を実施。

※ 「サーバー認証」設定の変更後、設定変更を反映されるため、SQL Server プロセスの再起動が必要。

3) SQL Server 認証に使用する SQL Server ログインの状態が「有効」になっているか、SSMS から 該当インスタンス - セキュリティ - ログイン - 該当SQL Serverログイン - 右クリック - プロパティ - ページの選択 : 状態 -「設定：ログイン」の設定を確認。

【Q3】SQL Server Always On 可用性グループ (もしくは ミラーリング) を構築している環境で、ノード Ａ 上が プライマリ ノードの場合、SQL Server ログイン test (SQL Server 認証) で接続が可能だが、フェールオーバーが発生し、ノード Ｂ 上が プライマリ ノードに切り替わると、SQL Server ログイン test (SQL Server 認証) で接続ができなくなる。

【A3】

ノードA、ノードB 上に同じ名前で SQL Server ログイン「test」を作成したとしても、各ログインの識別に利用される「SID」が異なると、全く別の SQL Server ログインとして扱われます。

そのため、本現象を解消するためには、以下の何れかの方法を検討します。

[考えられる対処方法]

1) 包含可用性グループとして構成

※ ミラーリングの場合は、包含可用性グループを構成できないため、2) の対処方法を実施。

参考URL

包含可用性グループとは - SQL Server Always On | Microsoft Docs

2) ノードA (SQL Server Always On 可用性グループのプライマリ (もしく ミラーリング  プリンシパル) 上に作成された SQL Server ログイン test の SID の値を確認し、ノードB 上で 確認した SID の値を指定して、新しい SQL Server ログイン test を作成

[対処例]

2-1)  ノードA 上にインストールされた SQL Server インスタンス に接続し、対象の SQL Server ログイン (今回は「test」) の「SID」値を確認するコマンドを実行し、「SID」値を確認。

select sid, name from sys.sql_logins where name = 'test'

2-2) ノードA 上で該当ログイン (今回は「test」) を、SSMS - セキュリティ - ログイン - 該当のログイン - ログインをスクリプト化 - 新規作成 - 新しい クエリ エディター ウィンドウ を利用してスクリプト化

[コマンド出力例]

2-3) 2-1) で確認した SID値 および 2-2) でエクスポートしたログインのスクリプトを利用し、SID値を明示的に指定して、SQL Server ログイン(今回は「test」)を作成するコマンドを実行

CREATE LOGIN [test] WITH PASSWORD=N'*****', SID=0x3DE02*****

参考URL

包含可用性グループとは - SQL Server Always On | Microsoft Docs

CREATE LOGIN (Transact-SQL) - SQL Server | Microsoft Docs

[補足]

SQL Server ログインのパスワードも移行したい場合は、以下 URL に記載がある ストアドプロシージャ sp_help_revlogin を利用し、ログインのスクリプト化を実施します。

【Q4】該当データベースの復旧モデルを「単純」から「完全」に変更したが、トランザクションログにトランザクション情報が蓄積されない。

【A4】

データベースの復旧モデルを変更したデータベースに対して、「完全バックアップ」を実行します。

初めてデータベースの場合、データベースの復旧モデルを「単純」から「完全」に変更するだけでは、トランザクションログにトランザクション情報は蓄積されず、トランザクションのコミット後にログレコードの削除が行われます。

データベースの復旧モデルを「単純」から「完全」変更後、データベースの復旧モデルを変更したデータベースに対して「完全バックアップ」を実施した後より、トランザクションログにログレコードの蓄積が行われるようになります。

【Q5】該当SQL Serverの管理者アカウントを誤って削除 (sa ログインのバスワードの失念) により、該当SQL Serverインスタンスに管理者権限をもったログインで接続することができなくなった。

【A5】

該当 SQL Server インスタンスを シングル ユーザー モードで起動することで、コンピュータのローカル管理グループに所属するメンバーに対して、管理者権限 (sysadmin) が付与されます。この状態で 該当 SQL Server インスタンスに接続し、管理者権限が付与されたログインの作成、もしくは、既存の管理者権限が付与された SQL Server アカウント(sa など) のパスワードを変更します。

参考URL

シングル ユーザー モードでの SQL Server の起動 - SQL Server | Microsoft Docs

システム管理者がロックアウトされた場合の SQL Server への接続 - SQL Server | Microsoft Docs

【Q6】仮想マシン上にSQL Serverにインストールする場合の必要となるライセンスが分からない。

【A6】

クラウド上の仮想マシンに SQL Server を持ち込みライセンスとしてインストールする場合は、ソフトウェア アシュアランス (SA) が付いた SQL Server ライセンス (Standard/Enterprise など) を購入する必要がある。

オンプレミス上に構築した仮想ホストサーバー (VMWare、Citrix など) のゲストOSに SQL Server をインストールする場合は、ソフトウェア アシュアランスが付いていない SQL Server ライセンスを適用可能。 

[注意点]

仮想マシン上に SQL Server をインストールする場合、最小コアライセンスが定義されています。2022年6月時点では、最小コアが 4 コア と定義されており、最小構成において 4コア分の SQL Server ライセンスの購入が必要。

例えば、2CPU の仮想マシンに SQL Server をインストールする場合、最小コアライセンスが 4コアであるため、4コア分の SQL Server ライセンスの購入が必要。

※ 2022年6月時点。ライセンスの考え方は変わる可能性があるため、ライセンス購入に関する質問は、ライセンス購入先に確認が必要。

まとめ

今回は、SQL Server でよく遭遇する問題について、Q＆A 形式でまとめてみました。

今後、必要に応じて、Q＆A 項目を増やしていこうと思います。

※ 2022年6月時点