SQL Serverのサーバー内部を観察してみる データベース構成file&Datafile
システムリソースに加えてデータベース実体となるファイル群へのアクセスを試みる. ファイルの実際の構成, 中身を確認する.
公式: https://docs.microsoft.com/ja-jp/sql/sql-server/install/file-locations-for-default-and-named-instances-of-sql-server?view=sql-server-ver15#specifying-file-paths
--.icon
データベース構成ファイルの, ざっくり全体像
ファイルの種類
Database engine file, program file.
Data file, ( .mdf ), data and index.
Transaction Log file ( .ldf ).
Database engine file, program file.
まとめ
Relational Serverを司るファイルの格納先を確認.
microsoft ... ODBC(Open Database Connectivity)というOSからDBMSへのアクセスAPIが格納
mssql ... SQL Server, Instance自体の管理
mssql-extensibility ... SQL Server上で, RやJavaなどのプログラミング言語の実行実現.
mssql-tools... ユーザーのDBへの操作ツール. sqlcmdといったコマンド本体がいる.
SQLOS Schedulerとか, Memory管理してたり, QueryのOptimizerなどのComponentってどこにいるんだ... Query Optimizerはtools配下なのかな...
実践. 今回はubuntu上にSQL Serverをインストールした. ubuntuのディレクトリ構成は,
Linuxのディレクトリ構造（一覧）を理解する https://www.linuxmaster.jp/linux_skill/2010/02/06linux.html
code:program 格納先
├ /opt 実行時に書き換えられないアプリケーションソフトウェアパッケージ
... Database EngineのProgram格納先 (option)(??)
├ /usr プログラムやカーネルソース
... Database EngineのProgram格納先(??)実行ファイルはbin, ライブラリはlib.
→いや, ここにはなさそう. アプリケーションではなくて, OS上でのツールインストール先
└ /var システムログなどの動的に変化するファイル
... Datafileの格納先(??)
image作成に使ったDocker file https://hub.docker.com/_/microsoft-mssql-server?tab=description
オリジナルはこれ https://github.com/microsoft/mssql-docker/blob/master/linux/preview/examples/mssql-mlservices/Dockerfile
windowsOS上へのインストール時の各Componentの格納先 https://docs.microsoft.com/ja-jp/sql/sql-server/install/file-locations-for-default-and-named-instances-of-sql-server?view=sql-server-ver15#specifying-file-paths
code:1. optの中にわんさか入ってそう
mssql@sqlsvver1:/opt$ ls
microsoft mssql mssql-extensibility mssql-tools
code: 2. microsoftの中
mssql@sqlsvver1:/opt$ cd microsoft/msodbcsql17/
etc/ include/ lib64/ share/
microsoft配下は, msodbcsql17ディレクトリのみ存在しており, odbc(Open Database Connectivity)というDBMSへのアクセスAPIが格納されている. (憶測)
code: 3. mssqlの中　　
mssql@sqlsvver1:/opt$ cd mssql/
bin/ lib/
mssql@sqlsvver1:/opt/mssql$ cd bin/
checkinstallextensibility.sh handle-crash.sh permissions_check.sh
compress-dump.sh launchpadd setnetbr
crash-support-functions.sh mssql-conf sqlservr
generate-sql-dump.sh paldumper
sqlservr ... SQL Serverインスタンスを制御している. ( https://docs.microsoft.com/en-us/sql/tools/sqlservr-application?view=sql-server-ver15 )
code: 3. mssql-tools/の中
mssql@sqlsvver1:/opt/mssql$ cd ../mssql-tools/
bin/ share/
mssql@sqlsvver1:/opt/mssql-tools$ cd bin/
bcp sqlcmd
sqlcmd ... コマンドの本体
The sqlcmd utility lets you enter Transact-SQL statements, system procedures, and script files through a variety of available modes. ( https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15 )
bcp ... bulk copy program utility
The bulk copy program utility (bcp) bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. ( https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15 )
他Utility https://docs.microsoft.com/en-us/sql/tools/command-prompt-utility-reference-database-engine?view=sql-server-ver15
mssql-toolsはユーザーIF提供の位置づけがしっくりくる.
ちなみにshareには設定ファイルが格納されている.
code: 3. mssql-extensibilityの中
mssql@sqlsvver1:/opt/mssql-extensibility$ ls bin/
exthost launchpad
Learn about the extensibility architecture used for SQL Server Language Extensions, which allows you to run external code in SQL Server. In SQL Server 2019, Java, Python and R are supported. The code executes in a language runtime environment as an extension to the core database engine. ( https://docs.microsoft.com/en-us/sql/language-extensions/concepts/extensibility-framework?view=sql-server-ver15 )
要は, SQL Server上で, RやJavaといった他プログラミング言語の実行が可能になる.
Data file, ( .mdf ), data and index.
データベースの実体であるデータファイルやインデックスファイルにアクセス
DBCC PAGE/INDコマンドを利用して, 実際のデータないしインデックスファイルへアクセスすることが可能. P123より確認してください. DBCC(Database Command Check) https://docs.microsoft.com/ja-jp/sql/t-sql/database-console-commands/dbcc-transact-sql?view=sql-server-ver15
SQL Serverのテーブル,オブジェクトの実現方法としてのデータページ構成~行ストアと列ストア~#6052c771ca2de100009f9982
SQL Serverのデータベース実体をファイル,Diskの中身から探る#604ec03aca2de1000093c83d
Data fileの格納先を調べる.
code:検索SQL
USE master;
SELECT
name 'Logical Name',
physical_name 'File Location'
FROM sys.master_files;
( https://database.guide/how-to-find-the-location-of-data-files-and-log-files-in-sql-server/ )
https://gyazo.com/4beaa856d651347367c6e1abb0d9f5f1
実際に作成したデータベースへ実際にアクセスしてみる.
TestDB /var/opt/mssql/data/TestDB.mdf ... Datafile
TestDB_log /var/opt/mssql/data/TestDB_log.ldf ... TransactionLogFile
バイナリファイルなので, 中身が何者なのかわからない.
code:mssql@sqlsvver1:/var/opt/mssql/data$ od TestDB.mdf | more -f
0000000 007401 000000 001000 000000 000000 000000 000000 000000
0000020 000000 000000 000000 000001 000143 000000 015463 002313
0000040 000000 000000 000001 000000 000000 000000 000000 000000
0000060 000001 000000 000000 000000 000000 000000 132071 034357
0000100 000000 000000 000000 000000 000000 000000 000000 000000
*
0000140 000060 000010 000000 000000 000070 000000 000000 000000
なので, SQL Server のデータベース コンソール コマンドであるDBCC(Database Command Check)をつかってみる. https://docs.microsoft.com/ja-jp/sql/t-sql/database-console-commands/dbcc-transact-sql?view=sql-server-ver15
a way of identifying all pages that belong to a table or index, using the DBCC IND statement. ... The DBCC IND statement is considered undocumented and as such it is not supported by Microsoft. テーブル, インデックス所属するページを教えてくれるコマンドで, 非公式コマンドである.( https://sqlity.net/en/2204/dbcc-ind/ )
code:データベースの状況チェック
SELECT Name from sys.Databases; -- 全データベース取得
SELECT * FROM sys.objects; -- 全テーブル取得
SELECT * FROM sys.objects WHERE type = 'U'; -- ユーザー作成のテーブル取得
SELECT * FROM Inventory; -- テスト作成したテーブル出力
今回は, 以下のテーブルにデータを保存している.
https://gyazo.com/5c6991bb152beb23d51b5f5e56be7b0e
code:DBCC IND
DBCC IND('TestDB','Inventory', -1); -- 「-1」はテーブル内の全情報出力を指定するオプション
https://gyazo.com/3871e2d1d727c0c37bfae06459a4fb9d
code:DBCC PAGE
DBCC PAGE('TestDB', 1, 312,3); -- 1は「PageFID」, 312はPagePID, 3はデータを成形出力するオプション
https://gyazo.com/2f37080cb056a00ffa2ca86a8c935589
実データ. ファイルは上部にファイル管理情報が出力され, その配下にデータ実体が出力される.これが実際のファイルの中身ってことですね. 管理情報を読み解きたい...
index file, Transaction Log file ( .ldf ).についても, 同じように見ていくことはできそう!!
#Azure #Database #SQLServer