SQL Server on Linux for Docker でのエラー対策

SQL Server on Linux for Docker を使っていて次のようなエラーに遭遇しました。

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2749.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

悲しい…。
SQL Serverを起動して、初期DBやテーブルを作成したいと思って、次のようなDockerfileを書きました。

FROM mcr.microsoft.com/mssql/server:2017-latest
RUN sqlcmd -Q ‘CREATE Database test’ -E
EXPOSE 1433

これ原因がわからなくて、ムッシュに教えてもらいました。
単純な話で、「RUN」でコマンドを実行するタイミングでは、まだSQL Serverが起動してないのです。
起動してないのに、コマンド実行しようとしても動作しませんよね。

IgniteのBRK0417「Inside SQL Server Containers」でも言及されていましたね。
なので対応方法もセッションで紹介されている方法でOKです。

SQL ServerインポートおよびエクスポートウィザードのSSISパッケージのオプションについて

SQL Server インポートおよびエクスポートウィザードでデータの移行ができます。画面でぽちぽちして、最後にSSISのパッケージとして保存することができます。
保存すると「.dtsx」という拡張子で出力されます。

ウィザードで出力されたSSISパッケージの細かいオプションがどうなっているのかが気になったので調べてみました。

前提:今回の調査では、コピー元もコピー先もSQL Server Native Clientで同じAzure SQL Databaseサーバーの別のデータベースにコピーしています。

制御フローは1つだけです。

image

制御フローにパラメーターで気になるのは次のところ。

  • AutoAdjustBufferSize:False
  • DefaultBufferMaxRows:10000
  • DefaultBufferSize:3145728
  • EngineThreads:10

SSISとしては、シンプルなデータフローが定義されています。

image

データソースはこんな感じです。

出力クエリとそれによって取得できるカラムの情報ですね。

image

image

コピー先の設定はこんな感じ。

image

テーブルロックされて、CHECK制約が有効で、IDは保持しなくて、NULLも保持しない。

データアクセスモードは、「テーブルまたはビュー 高速読み込み」

バッチごとの行数はブランクで、挿入コミットサイズの最大値は2147483647。

あとはデータのマッピング情報がありますね。

image

バッチサイズについて

参考:https://www.mssqltips.com/sqlservertip/1840/sql-server-integration-services-ssis-best-practices/

バッチごとの行数は既定では「-1」で、すべての受信業が単一のバッチとして扱われる。
この値を変更すると受信したすべての行を複数のバッチに分割できる。設定可能なのはバッチ内の最大行数までの整数値。

挿入コミットサイズの最大値の既定値の意味は、受信したすべての行が正常に完了すると一回コミットされることを意味するようです。
この値を変更しないと大量のデータ転送中にトランザクションログとtempdbの負荷が増大するという。

参考:MSDN ONLINE

参考:SSIS Best Practice

SQL Server AlwaysOn でプライマリサーバーかどうかを確認するクエリ

動的管理ビュー (DMV) を使用して、AlwayOn でプライマリサーバーかどうかを確認するクエリ。

select name,replica_server_name,role_desc,dns_name from sys.availability_groups_cluster agc
right join sys.dm_hadr_availability_replica_cluster_states dharc
on agc.group_id = dharc.group_id
left join sys.dm_hadr_availability_replica_states dhars
on dharc.replica_id=dhars.replica_id
left join sys.availability_group_listeners agl
on agc.group_id = agl.group_id

そーすると、次の画像のように、サーバー一覧とSecondaryかPrimaryかが表示される。自身のサーバーのロールしか取れないので、SecondaryかPrimaryかしかわからない。複数サーバーあると、どれがPrimaryかはそれぞれのサーバーで実行してみないとわからない…。

image

SQL Server 2016 での JSON

SQL Server 2016 では、JSONへのサポートが追加されました。
どのような考え方でJSONサポートが追加されていて、どのようにユーザーはJSONをSQL Serverで使用できるのかを理解するのに、SQL Server チームが投稿した全4回の投稿が参考になるので、ざっくりと意訳してみました。

概要

SQL Server でJSON関数は、JSONデータを分析しクエリを実行し、JSONをリレーショナルフォーマットに変換し、SQLクエリ結果をJSONテキストに出力します。

 

 

組み込み関数(JSON_VALUE、JSON_QUERY)を使用して、JSONからデータを取得したり、JSONフォーマットが正しいかを検証できます。OPENJSON関数を使用するとJSONオブジェクト配列を行セットに変換できます。FOR JSONでJSONテキスト形式でクエリ結果を取得できます。

次のTransact-SQLコードは、JSONテキストの格納にテキスト変数を指定しています。

DECLARE @json NVARCHAR(4000)
SET @json =
N'{
    “info”:{ 
      “type”:1,
      “address”:{ 
        “town”:”Bristol”,
        “county”:”Avon”,
        “country”:”England”
      },
      “tags”:[“Sport”, “Water polo”]
   },
   “type”:”Basic”
}’

JSON_VALUE と JSON_QUERY関数を使用して、JSONテキストから値とオブジェクトを取得できます。

SELECT
  JSON_VALUE(@json, ‘$.type’) as type,
  JSON_VALUE(@json, ‘$.info.address.town’) as town,
  JSON_QUERY(@json, ‘$.info.tags’) as tags

このクエリは、「Basic」、「Bristol」、「[“Support”,”Water polo”]」を返します。JSON_VALUE関数は、セカンドパラメーターで指定したJSONパスでJSONテキスト(例えば、文字列、数字、true/false)から値を一つ返します。JSON組み込み関数はセカンドパラメーターで指定するJavaScriptのような文法でJSONの値やオブジェクトを参照します。

OPENJSON関数はJSONテキストの配列を参照でき、配列から項目を返します。

SELECT value
FROM OPENJSON(@json, ‘$.info.tags’)

この例では、タグ配列から文字列を返します。OPENJSON関数は複雑なオブジェクトを返します。

FOR JSONはSQLクエリによって返す結果セットをJSONテキスト形式で整形します。

SELECT object_id, name
FROM sys.tables
FOR JSON PATH

SQL ServerにJSONデータを格納する

SQL ServerではJSONはテキスト形式で格納されます。
JSONデータを格納するために標準的なNVARCHARカラムが使用されます。
次の例は、いくつかの情報をJSON形式で保存したシンプルなテーブルです。

CREATE TABLE Person (
Id int IDENTITY PRIMARY KEY NONCLUSTERED,
FirstName nvarchar(100) NOT NULL,
LastName nvarchar(100) NOT NULL,
InfoJson nvarchar(max) 
) WITH (MEMORY_OPTIMIZED=ON)

伝統的なリレーショナルのみのデータベースやドキュメントのみのシステムと、SQL Server 2016で提供するハイブリッドモデルとの大きな違いがこの例で示されています。SQL Server 2016では、通常の列(この例では、FirstNameやLastName)とJSONを格納した列(この例では、InfoJSON)を混在させることができます。

SQL Serverでは、必要に応じて通常の列やJSON列、Spatial、XML列と1つ以上混在させて構成することができます。同じテーブルで速くアクセスできる標準的なリレーショナル列と柔軟性を提供するJSON列により迅速なアプリケーション開発ができます。

JSONはテキスト列に格納されますが、平文ではありません。SQL Serverは、UNICODE圧縮のような最大50%の圧縮ができる圧縮メカニズムを使用してテキスト列のストレージを最適化する仕組みを組み込んでいます。またカラムストアテーブルにJSONテキストを格納できたり、またはGZipアルゴリズムを使う組み込みのCOMPRESS機能で明示的に圧縮することができます。

JSONはSQL ServerのコンポーネントやNVARCHARデータで動作するテクノロジーと完璧な互換性があります。上の例では、JSONは高パフォーマンスを提供するインメモリOLTP(Hekaton)テーブルに格納しています。JSONを標準的なテーブルや、カラムストアインデックス、FILESTREAMに格納できます。Polybaseを使用してHadoopから読み込んだり、ファイルシステムから読み込んだり、Azure SQLにストレッチデータベースを設定できます。

もし自由なテキストフォーマットでJSONを保持したくない場合は、標準的なCHECK制約とISJSON関数を使用して正しいフォーマットになっているかのJSON検証妥当性確認を追加できます。

ALTER TABLE Person
ADD CONSTRAINT [Content should be formatted as JSON]
 CHECK ( ISJSON( InfoJSON )> 0 )

こては標準的なチェック制約を使用して、正しいフォーマットのJSONカラムにテキストが格納されているかどうかの妥当性確認ができます。

JSONはテキストで表すので、クライアントアプリケーションを変更したり、新しいドライバーをまったり、プロトコルを変更する必要がありません。C#、Java、Node.jsアプリケーションでJSONドキュメントの読み書きができます。JSONはテキストフィールドでORMモデルで読み込んだり、Ajaxリクエストを経由して直接JavaScriptクライアントコードに送信できます。

組み込みのJSON処理用の関数

SQL Server 2016 では、JSONテキストをパースしたり処理する関数が提供されています。

  • ISJSON(json文):NVARCHARテキストがJSON仕様に沿った正しいフォーマットかを確認します。JSONを格納するNVARCHAR列に、この関数を使用してチェック制約を作成できます。
  • JSON_VALUE(json文,パス):JSONをパースして、JavaScriptライクなパスで指定された値を出力します。(JSONパス例は下を参照)
  • JSON_QUERY(json文,パス):JSONをパースして、JavaScriptライクなパスで指定されたオブジェクトまたは配列を出力します。(JSONパス例は下を参照)

これらの関数はJSONパスを使用してJSONテキストで値やオブジェクトを参照します。JSONパスはJSONテキストのプロパティの参照をJavaScriptライクな文法を使用します。いくつかの例を提示します。

  • $ – 入力されたテキストのJSONオブジェクト全体を参照します
  • $.property1 – JSONオブジェクトのproperty1を参照します
  • $[4] – JSON配列の5番目の項目を参照します(インデックスはJavaScriptのように0から数えます)
  • $.property1.property2.array1[5].property3.array2[15].property4 – JSONオブジェクトで複雑にネストしたプロパティを参照します
  • $.info.”first name” – infoオブジェクトの”first name”プロパティを参照します。スペースやダラーなどの特別な記号がキーに含まれている場合、ダブルコーテーションマークで囲む必要があります。

$記号は、入力したJSONオブジェクト(XPATH言語ではルート”/”に該当します)を示します。JSONオブジェクトでプロパティを参照するために、$の後にJavaScriptライクのプロパティか配列を追加できます。次の例は、組み込み関数を使用してシンプルなクエリ例の1つです。

SELECT Id, FirstName, LastName,
     JSON_VALUE(InfoJSON, ‘$.info.”social security number”’) as SSN,

     JSON_QUERY(InfoJSON, ‘$.skills’) as Skills
FROM Person AS t
WHERE ISJSON( InfoJSON ) > 0
AND JSON_VALUE(InfoJSON, ‘$.Type’) = ‘Student’

このクエリは、FirstNameとLastNameを標準的なテーブル列から返し、JSON列からスキルの配列と社会保障番号を返します。結果は、InfoJSON列に正しいJSONが格納されていて、Type値にStudentを含んでる行を結果として返します。クエリの一部でJSONの値を使用できます。order byやgroup byなど。

JSONテキストをリレーショナルテーブルへ変換する – OPENJSON

OPENJSONは、JSONテキストの中を見たり、JSONオブジェクトの配列を示したり、主力結果に各エレメントを1行で戻したりするテーブル値関数(TVF)です。

上の例では、JSON配列のどこを開くか(例では、$.Ordrrs)、どの列を結果で返し、JSONオブジェクトの値をセルのどこに返すかを指定します。

次の例では、@orders変数のJSON配列を行セットにし、標準的なテーブルに挿入します。

INSERT INTO Orders(Number, Date, Customer, Quantity)
SELECT Number, Date, Customer, Quantity
 OPENJSON (@orders)
 WITH (
        Number varchar(200),
        Date datetime,
        Customer varchar(200),
        Quantity int
 ) AS OrdersArray

OPENJSONによって返される結果セットの4列は、WITHで定義されます。OPENJSONはNumber、Date、Customer、Quantityプロパティを探索し、それらの値を結果セットの列に変換します。既定では、プロパティが無ければNullを返します。上のクエリではm、@orders変数に次のJSON配列が含まてれいると仮定しています。

‘[
   {“Number”:1, “Date”: “8/10/2012”, “Customer”: “Adventure works”, “Quantity”: 1200},
   {“Number”:4, “Date”: “5/11/2012”, “Customer”: “Adventure works”, “Quantity”: 100},
   {“Number”:6, “Date”: “1/3/2012”, “Customer”: “Adventure works”, “Quantity”: 250},
   {“Number”:8, “Date”: “12/7/2012”, “Customer”: “Adventure works”, “Quantity”: 2200}
]’

JSONテキストからリレーショナル形式に変換するのはとても簡単なのがわかるかと思います。列名とデータ型と、JSONテキストでマッチしたプロパティを見つけたOPENJSONの列を指定する必要があります。

OPENJSONは、リレーショナルとJSONデータを同じクエリで組み合わせて使用することができます。前掲のJSONテキストがOrder列に格納されていると仮定して、次のクエリはJSONと列を組み合わせたクエリです。

SELECT Id, FirstName, LastName, Number, Date, Customer, Quantity
 FROM Person
    CROSS APPLY OPENJSON (OrdersJson)
                            WITH (
                                        Number varchar(200),
                                        Date datetime,
                                        Customer varchar(200),
                                        Quantity int ) AS OrdersArray

OPENJSONは、各セルに配列を開き、JSONオブジェクトごとに1行を配列で返します。CROSS APPLY OPENJSON文法は、JSONセル内のJSON配列からマテリアライズされる子内部表と表の行を結合するのに使われます。

JSONデータのインデックス

JSON値はテキストでフォーマットされますが、テーブル列にほかの値と同じ方法でインデックスすることができます。標準的な非クラスタ化インデックスまたはフルテキスト検索インデックスを使用できます。

クエリでよく使用されるいくつかのJSONプロパティにインデックスを作成したい場合、値を参照する非永続型の計算列を作成し、その列に標準的なインデックスを作成します。次の例は、InfoJSON列で$.Companyプロパティを使用して行をフィルタリングするクエリを最適化しています。

ALTER TABLE Person
ADD vCompany AS JSON_VALUE(InfoJSON, $.Company’)

CREATE INDEX idx_Person_1
    ON Person(vCompany)

SQL Serverはハイブリッドモデルを提供し、JSONからの値をキーや列に含めることができ、JSON値と標準的な列を同じインデックスで使用できます。

JSONは通常のテキストなので、標準的なフルテキスト検索を使用できます。フルテキストインデックスは、値の配列上で作成されます。フルテキストインデックスをJSON配列を含んだ列上で作成したり、既存の列でいくつかの配列を参照する計算列を作成しそのカラム上でフルテキスト検索インデックスを作成できます。

ALTER TABLE Person
ADD vEmailAddresses AS JSON_QUERY(InfoJSON, ‘$.Contact.Emails’)

CREATE FULLTEXT INDEX ON Person(vEmailAddresses)
    KEY INDEX PK_Person_ID ON jsonFullTextCatalog;

フルテキスト検索インデックスはJSONはいてるに含まれているいくつかの値を含んでいる行を探そうとするクエリを最適化する必要があるときに便利です。

SELECT PersonID, FirstName,LastName,vEmailAddresses
FROM Person
WHERE CONTAINS(vEmailAddresses, ‘john@mail.microsoft.com’)

このクエリは、email配列に「john@mail.microsoft.com」を含んでいるPerson行を返します。フルテキストインデックスは、JSONのパースに関して特別なルールを用意していません。セパレーター(ダブルクォートやコンマ、括弧)を使用して、JSON配列を分割し、配列に値をインデックスします。フルテキストインデックスは、適切に数字やシンプルな文字列値に配列します。JSON配列でより複雑なオブジェクトを持っている場合、システムはKeyとValueの違いを認識できないのでフルテキストインデックスは直接適用できません。

ここまでで、同じインデックスで、JSON値とリレーショナル列両方を使用できることが分かりました。

データをJSONとして出力する – FOR JSON

SQL  SELECT クエリの終わりに、FOR JSONを追加すると、SQL Serverは、JSONにフォーマットした結果をクライアントに返します。全ての行は1つのJSONオブジェクトにフォーマットされ、結果セットのセル内の値はJSONオブジェクトのvalueとして生成され、カラム名またはエイリアス名じゃKye名として生成されます。FOR JSONには2種類あります。

  • FOR JSON PATH:カラム名/エイリアス名を使用してJSONアウトプットの構造を定義できます。列エイリアスでドットで区切られた文字列を指定すると、JSONプロパティはネーミング規則に従います。この機能はスラッシュを使用してパスを分割するFOR XML PATHと似ています。
  • FOR JSON AUTO:クエリで使用したテーブル仮想をもとに、ネストしたJSONサブ配列を自動的に生成します。

FOR JSONで生成されたJSONテキストは、OPENJSONを使用してリレーショナル形式に戻す返還ができます。

まとめ

SQL ServerのJSON関数はJSONをリレーショナルに変換、リレーショナルデータをJSONに返還するのと同様のJSON分析とクエリ発行を可能にします。アプリケーションレイヤーで追加の変更なしにJSONデータを使ったり発行できるようになりました。

SQL ServerはリレーショナルデータとJSONを組み合わせたハイブリッドストレージモデルを提供します。高速データアクセスと柔軟で迅速な開発とのトレードオフを可能にしました。標準的な列とJSONテキストの値の両方で同じインデックス技術を使用できます。

ハイブリッドモデルは、SQL Serverのメリットである、フルパワーのクエリ言語とACIDトランザクションの恩恵を受けられます。SQL Serverと互換性のあるエコシステムであるツールを使用でき、既知の管理モデル、セキュリティモデルが、JSON機能でもそのまま使用できます。

参照記事

SQL Server のクエリヒントによるロックへの影響(SELECT)

SQL Serverで、クエリヒントを使用するとロックにどのような影響が出るのかを調べてみました。

結論

image

標準クエリの場合、ISを取得するので、DDL文とXロック(SELECTだと、XLOCKクエリヒントを使ったクエリ)と競合する。UPDLOCKとは競合しない。

UPDLOCKは、キーにUを取得するので、DDL文と同一キーへのUとXと競合する。ページにIUを取得するので、ページへのUとXと競合する。キーへのUはトランザクション終了時まで保持される。

 

詳細は、ロックの互換性参照

調査方法

今回は単純なテーブルとクエリでの調査です。
4列あるテーブルで、100000行のデータを格納して、Where無しのクエリで処理をしています。

テーブル例

image

クエリ例

SELECT   [Uid]
      ,[AddTime]
      ,[EnemyId]
      ,[StrongType]
FROM [dev_next_dev].[dbo].[AAA]

クエリヒント無し

データベースにSロックを取得・
テーブルにISロックを取得。
PageにISロックを取得しますね。(検証中、まれにSの時もあり)

image

UPDLOCK

データベースにSロック
テーブルにIXロック
PAGEにIUロック
KEYにUロックを取得します。Keyは全レコード(Top1000なので1000行)のロックを保持します。

image

image

これは、トランザクションが終了するまで保持されていて、トランザクションが終了すると、KEYのUロックとPAGEのIUロック、テーブルのIXロックが解除されます。

image

NOLOCK

データベースにSロックを取得します。
テーブルにSCH-Sロックを取得します。

image

SCH-Cロックは、DDL文発行時に取得されるSCH-Mロックとのみ競合します。

TABLOCK

データベースにSロック
テーブルに大量のSロック

image

XLOCK

データベースにSロック
テーブルにISロック
テーブルにIXロック
ページにIXロック
キーごとにXロック

image