カテゴリー: SQLServer

ログシッピングの実行状態を確認する

SQLServerのログシッピングは、トランザクションバックアップの出力、コピー、リストアが別々のジョブで実行される。

デフォルトでログシッピングを作成した場合、15分間隔でジョブが作成されるが、MasterとSlaveのデータ差異が１５分とは限らない為、Slave機で以下のクエリを流して同期状態を確認する。

[code]

SET NOCOUNT ON
USE master

/* DB名を入れる変数 */
DECLARE @dbname varchar(80);

/* DB一覧 */
DECLARE DBnameList CURSOR FOR
select name from sys.databases

/* sp_help_log_shipping_secondary_databaseの結果を入れる */
CREATE TABLE #TMP (
secondary_id nchar(128)
,primary_server nchar(128)
,primary_database nchar(128)
,backup_source_directory nchar(128)
,backup_destination_directory nchar(128)
,file_retention_period int
,copy_job_id nchar(128)
,restore_job_id nchar(128)
,monitor_server nchar(128)
,monitor_server_security_mode nchar(128)
,secondary_database nchar(128)
,restore_delay int
,restore_all int
,restore_mode int
,disconnect_users int
,block_size int
,buffer_count int
,max_transfer_size int
,restore_threshold int
,threshold_alert int
,threshold_alert_enabled int
,last_copied_file nchar(128)
,last_copied_date datetime2
,last_copied_date_utc datetime2
,last_restored_file nchar(128)
,last_restored_date datetime2
,last_restored_date_utc datetime2
,history_retention_period int
,last_restored_latency int
)

/* ループ処理開始 */
OPEN DBnameList;
FETCH NEXT FROM DBnameList INTO @dbname

WHILE (@@FETCH_STATUS = 0)
BEGIN
print @dbname
INSERT INTO #TMP EXEC sp_help_log_shipping_secondary_database @dbname
FETCH NEXT FROM DBnameList INTO @dbname
END

CLOSE DBnameList
DEALLOCATE DBnameList

/* 一覧出力 */

select
secondary_database as ‘DB名’
,last_copied_date as ‘最終コピー時刻’
,last_restored_date as ‘最終リストア時刻’
,last_restored_file as ‘最終リストアファイル名’
from #TMP

/* TMPDB削除 */
DROP TABLE #TMP

[/code]

リストア時に対象DBへのアクセスを強制的に落とす

Slave側のDBをログシッピングのStandbyモードで動かしている場合、
SSMSのユーザーセッションが残っているとリストアが失敗する事がある。

会社のとあるシステムでは、15分毎にLSCOPYを行い、深夜に1回、LSRESTOREジョブが動いているが、その良く失敗する。
酷い時は週5回は失敗して、「データが更新されてない！」と利用部門からクレームが来て手動でジョブを実行していた。

接続を切って帰ってくれればいいのだが、何度言ってもダメなので、
LSRESTOREジョブにステップを追加して、当該DBへのアクセスを全てkillする事にした。

その時に動いている処理があるのかもしれないが、ムシャクシャして書いた。

[code]
SET NOCOUNT ON
CREATE TABLE #sp_who (
spid smallint
,ecid smallint
,status nchar(30)
,loginame nchar(128)
,hostname nchar(128)
,blk char(5)
,dbname nchar(128)
,cmd nchar(16)
,request_id int)

INSERT INTO #sp_who EXEC sp_who

DECLARE CUR_SPID CURSOR FOR select spid from #sp_who where dbname = ‘DB名’

DECLARE @SPID nchar(5)

OPEN CUR_SPID

FETCH NEXT FROM CUR_SPID INTO @SPID

WHILE (@@FETCH_STATUS = 0)

BEGIN
DECLARE @command nchar(128)
SET @command = N’kill ‘ + @SPID

EXEC sp_executesql @command

FETCH NEXT FROM CUR_SPID INTO @SPID

END

CLOSE CUR_SPID
DEALLOCATE CUR_SPID
DROP TABLE #sp_who
[/code]

これで、翌朝にドタバタ＆イライラしなくても済みそう。

一時テーブルにストアド結果を入れる

SQLServerで一時テーブルの作成とストアド結果をINSERTするやり方。
いつも忘れてしまうからメモ。

[code]
CREATE TABLE #sp_who (
spid smallint
,ecid smallint
,status nchar(30)
,loginame nchar(128)
,hostname nchar(128)
,blk char(5)
,dbname nchar(128)
,cmd nchar(16)
,request_id int)

INSERT INTO #sp_who EXEC sp_who

select * from #sp_who

DROP TABLE #sp_who
[/code]

SQLServer on Linuxを触ってみる

先週、SQLServer on Linuxのpublic Public Previewが開始されたので、さっそく触ってみた。

[環境]
VirtualBOX上の CentOS7
CPU ：2コア
メモリ: 4096MB ※3250MB以下は、インストール時にエラーが出ます。

インストールは、公開されているリポジトリを追加してyumコマンド叩くだけ！
非常に簡単！

[code]
# リポジトリ追加
curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo

# インストール
yum -y install mssql-server mssql-tools

# 途中、ライセンス確認のチェックが入るので、大文字でYESと入力
Do you accept the license terms? (Enter YES or NO) YES

[/code]

インストールPATHは/opt配下。
[code]
ls -1 /opt/
microsoft → odbcドライバ
mssql → SQLServer
mssql-tools → bcpとsqlcmd
[/code]

続いて、初期セットアップを行う。
[code]
/opt/mssql/bin/sqlservr-setup
（中略）
Setting system administrator (SA) account password…

Do you wish to start the SQL Server service now? [y/n]: y
Do you wish to enable SQL Server to start on boot? [y/n]: y
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service to /usr/lib/systemd/system/mssql-server-telemetry.service.

Setup completed successfully.
[/code]

セットアップスクリプトが動いたっぽい。

プロセスも動いている！
mssql 2257 1 2 18:37 ? 00:00:00 /opt/mssql/bin/sqlservr
mssql 2263 1 0 18:37 ? 00:00:00 /opt/mssql/bin/sqlservr-telemetry /var/opt/mssql/.system
mssql 2270 2257 43 18:37 ? 00:00:04 /opt/mssql/bin/sqlservr

ポートも空いている！
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN
tcp 0 0 0.0.0.0:1433 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:1434 0.0.0.0:* LISTEN
tcp6 0 0 :::22 :::* LISTEN

最後にSSMSで接続してみます。
sqlserver_on_linux
当然、Windows認証は使えませんが、セットアップ時に決めたSAのパスワードで接続する事が出来ました。

Versionは、
Microsoft SQL Server vNext (CTP1) – 14.0.1.246 (X64) Nov 1 2016 23:24:39 Copyright (c) Microsoft Corporation on Linux (CentOS Linux 7 (Core))

と出ているので、インストール自体は成功したようです。

SQL Server から AWS Auroraにマイグレーションする時の注意事項

オンプレ環境のSQL Server開発機からAWS Auroraにマイグレーションした際、DMS（Database Migration Service）を使って見たので、ハマった処をメモ。
https://aws.amazon.com/jp/dms/

eyecatch_dms-200x200
※仕様をよく読んだら書いてあるのかもしれない。

【移設方法】
オンプレのSQLServerは、DMSからアクセスが出来ないネットワークにある為、セキュリティを弄らずに移設をする方法を考えました。
結果的に、[開発機] – [RDS for SQL Server] – [DMS] – [RDS for Aurora]
と余計なSQLServerを挟んでいます。

20161108_1

【移行時の注意点】
・DMSはスキーマ単位になりますが、Auroraはスキーマの概念がありません。
　SQLServerで
　[db_name1].[dbo].[table_name]
　[db_name2].[dbo].[table_name]
　は別テーブルとして扱われますが、Auroraでは[dbo].[table_name]と単一になります。

　その為、事前にスキーマを[ALTER SCHEMA db_name1 TRANSFER dbo.table_name]等のコマンドで変更しておく必要があります。
　対象は、一般テーブル、システムテーブルです

　また、ストアドもスキーマを変更する必要があります。
　こちらは変更のコマンドが解らなかったので、SSMSでストアドのCreate文作成　→　dboの部分をdb_nameに変更して新規作成を行いました。

・LOBサイズ
　DMSのLOBサイズの上限は、デフォルトで24KBになっています。
　大きめのデータが格納されている場合、この上限に引っかかる場合がありますので、適時変更を行ってください。
　分からなくても、1024KBぐらいあれば十分かと。

・移行元がSQLServerの場合、継続的なレプリケートが出来ない。
　DMSの強みである継続的レプリケート。
　これにより、大容量のデータベースでもダウンタイムを低減させる事が出来ます。
　が、現時点でSQLServerからAuroraへは、レプリケートが行いません。

・IDENTITIYはAutoIncrimentにならない
・datetimeoffset(7)　→　varchar(34)
この辺はエンジンの違いが出てきます。

追加のデータが入らないうちに、Alter Tableで変更しておきましょう。

SQL Server 2016 StandardのAlwaysONを試す

今までEnterprise版でのみ利用可能だったSQLServerのAlwaysONについて2016から一定の制限はありますが、高可用性グループの作成が出来るようになりましたので早速構築してみました。

【制限】
・1つの高可用性データベースに所属できるスキーマは1つのみ

複数のリスナーは作成は出来るが、Masterを片寄出来ないのが難点。
もちろんjoin句も利用出来ないのでアプリケーション側で結合するか、Linkサーバーとやらを使うしかない（？）

サーバーA：

  • リスナー 192.168.x.10　データベース名1（Primary）　
  • リスナー 192.168.x.11　データベース名2（Replica）

    サーバーB：

  • リスナー 192.168.x.10　データベース名1（Replica）　
  • リスナー 192.168.x.11　データベース名1（Primary）

    構築方法は、SQLServer2012と基本的に同じ。
    ただし、SSMSの日本語ローカライズ版はにはバグがあり、現時点では以下の事が出来ません。

  • 対象DBの完全同期（ウィザードからフルバックアップ、リストア）が出来ない。
  • レプリカサーバーがAGに自動参加しない
  • レプリカサーバーにNORECOVERYでリストアし、可用性DBに参加させようとすると、データ差異があると言われて可用性データベースに参加できない。

    20161017_1

    マイクロソフトに確認したが、修正リクエスト中でいつ直るか解らないとの事だったので、英語版のSSMSを使って構築するとすんなり行く。

    • シングルクォート内で変数を展開する

    T-SQLで、シングルクォート内で変数を展開するに上手いやり方が解らない・・・
    例えば以下のようなクエリ。

    [sql]
    DECLARE @d nvarchar(50)
    DECLARE @cmd nvarchar(100)

    SET @d = N’C:\database\backup’
    SET @cmd = N’dir ‘ + @d + ‘ /b’
    exec master..xp_cmdshell @cmd
    [/sql]

    効率悪いと思うけど、1時間ほどググってこれで落ち着いたが正しいのかな？
    個人的には、以下のように書きたい。書いたらスッキリすると思う。
    [sql]
    DECLARE @d nvarchar(50)
    SET @d = N’C:\database\backup’
    exec master..xp_cmdshell ‘dir @{d} /b’
    — とか
    exec master..xp_cmdshell ”’dir’ + @d + ‘ /b”’
    [/sql]

    RDS for SQL Serverのフルバック機能を検証する

    先日、AWSのRDS for SQLServerのフルバックアップがS3のバケット上に置けるようになったので、触ってみる。
    現時点ではトランザクションログバックアップが出来ないが、S3を使うことによりオンプレ環境から取得したフルバックアップのリストアや、解析用にフルバックアップを別サーバーにリストアするなど、
    利用用途が増える。

    参考：
    Amazon RDS for SQL Server – Amazon S3 でネイティブバックアップと復元をサポート
    Importing and Exporting SQL Server Databases

    事前準備：
    リンク先に記載の通り、S3のバケット作成とIAMロールの作成を行う。
    S3のバケット作成とIAMロールの作成は、RDSのオプショングループの作成画面からも行える。
    ちなみに、default:sqlserver-xx グループには、オプションを追加することが出来ないので新規でオプショングループを準備する。

    rds_s3_1

    rds_s3_2

    作成したオプショングループをインスタンスに割り当て、インスタンスの再起動を行うとRDS for SQLServerからS3へのアクセスが可能になる。

    S3操作：
    S3への操作は、AWSが準備したストアドプロシージャを実行する。

    【フルバックアップ取得】
    [sql]
    exec msdb.dbo.rds_backup_database
    @source_db_name=’DBスキーマ名’,
    @s3_arn_to_backup_to=’arn:aws:s3:::S3バケット名/ファイル名.bk’,
    @overwrite_S3_backup_file=1;
    [/sql]

    【DBリストア】
    [sql]
    exec msdb.dbo.rds_restore_database
    @restore_db_name=’DBスキーマ名’,
    @s3_arn_to_restore_from=’arn:aws:s3:::S3バケット名/ファイル名.bk’;
    [/sql]

    【タスク状態確認】
    [sql]
    exec msdb.dbo.rds_task_status @db_name=’DBスキーマ名’
    [/sql]

    RDS for SQLServerではメンテナンスプランが使えない為、以下のような処理をエージェントジョブで回すとフルバックアップ取得の代わりになる。
    ※保持期間を過ぎたバックアップファイルの削除を、lambdaなりAWSCLIなりで行う事。

    [sql]
    USE msdb

    declare @Backet nvarchar(20)
    declare @DATE nvarchar(10)
    declare @DBNAME nvarchar(30)
    declare @BackupFile nvarchar(50)

    SET @Backet = ‘s3バケット名’
    SET @DATE = (SELECT CONVERT(date, getdate()))

    –データベース一覧を取得(master(ID=1),tempdb(ID=2),model(ID=3),msdb(ID=4) 以上)
    DECLARE C1_DBNAME CURSOR FOR
    SELECT NAME FROM SYS.DATABASES WHERE DATABASE_ID > ‘5’

    OPEN C1_DBNAME

    FETCH NEXT FROM C1_DBNAME INTO @DBNAME

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    SET @BackupFile = N’arn:aws:s3:::’ + @Backet + ‘/’ + @DBNAME + ‘_’ + @DATE + ‘.bak’

    IF @BackupFile IS NOT NULL
    exec msdb.dbo.rds_backup_database
    @source_db_name = @DBNAME,
    @s3_arn_to_backup_to = @BackupFile,
    @overwrite_S3_backup_file = 1;

    FETCH NEXT FROM C1_DBNAME INTO @DBNAME
    END

    CLOSE C1_DBNAME
    DEALLOCATE C1_DBNAME

    GO
    [/sql]

    コマンドラインからSQL Server2012をインストールする

    覚書

    以下のファイルを、ConfigurationFile.iniという名前で保存する。
    [code]
    ;SQL Server 2012 Configuration File
    [OPTIONS]

    ; INSTALL、UNINSTALL、UPGRADE などのセットアップ ワークフローを指定します。これは必須のパラメーターです。

    ACTION="Install"
    IACCEPTSQLSERVERLICENSETERMS="True"
    ; コマンド ライン引数 ENU の詳細なヘルプが定義されていません。

    ENU="False"

    ; ユーザー インターフェイスの動作を制御するパラメーターです。
    ; 有効な値は Normal (完全な UI の場合)、AutoAdvance (簡易 UI の場合)、および EnableUIOnServerCore (GUI ブロックをバイパスする Server Core セットアップの場合) です。

    ; UIMODE="NOMAL"

    ; ユーザー インターフェイスは表示されません。

    QUIET="False"

    ; ユーザー操作はなく、進行状況だけが表示されます。

    QUIETSIMPLE="True"

    ; SQL Server セットアップで更新プログラムを検出して含める必要があるかどうかを指定します。
    ; 有効な値は、True と False または 1 と 0 です。既定では、SQL Server セットアップには、検出された更新プログラムが含まれます。

    UpdateEnabled="True"

    ; インストール、アンインストール、またはアップグレードを行う機能を指定します。
    ; 最上位の機能には、SQL、AS、RS、IS、MDS、ツールなどがあります。
    ; SQL 機能では、データベース エンジン、レプリケーション、フルテキスト、および Data Quality Services (DQS) サーバーがインストールされます。
    ; ツール機能では、管理ツール、オンライン ブック コンポーネント、SQL Server Data Tools、その他の共有コンポーネントがインストールされます。

    FEATURES=SQLENGINE,BIDS,CONN,BC,SDK,SSMS,ADV_SSMS,SNAC_SDK

    ; SQL Server セットアップが更新プログラムを取得する場所を指定します。
    ; 有効な値は、"MU" (Microsoft Update を検索)、有効なフォルダー パス、.\MyUpdates のような相対パス、または UNC 共有です。
    ; 既定では、SQL Server セットアップは Microsoft Update を検索するか、Window Server Update Services を通じて Windows Update サービスを検索します。

    UpdateSource="MU"

    ; コマンド ライン パラメーターの使用方法を表示します

    HELP="False"

    ; 詳細なセットアップ ログをコンソールにパイプすることを指定します。

    INDICATEPROGRESS="False"

    ; セットアップで WOW64 にインストールするよう指定します。このコマンド ライン引数は、IA64 や 32 ビット システム上ではサポートされません。

    X86="False"

    ; 共有コンポーネントのルート インストール ディレクトリを指定します。このディレクトリは、共有コンポーネントのインストール後は変更されません。

    INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"

    ; WOW64 共有コンポーネントのルート インストール ディレクトリを指定します。このディレクトリは、WOW64 共有コンポーネントのインストール後は変更されません。

    INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"

    ; 既定のインスタンスまたは名前付きインスタンスを指定します。既定のインスタンスは、Express 以外のエディションの場合は MSSQLSERVER、Express エディションの場合は SQLExpress です。SQL Server データベース エンジン (SQL)、Analysis Services (AS)、または Reporting Services (RS) のインストール時には、このパラメーターが必須です。

    INSTANCENAME="MSSQLSERVER"

    ; 指定した SQL Server 機能のインスタンス ID を指定してください。SQL Server のディレクトリ構造、レジストリ構造、およびサービス名には、SQL Server インスタンスのインスタンス ID が組み込まれます。

    INSTANCEID="MSSQLSERVER"

    ; SQL Server 機能の使用状況データを収集して Microsoft へ送信できるようにするかどうかを指定します。この機能を有効にするには 1 または True を指定し、無効にするには 0 または False を指定します。

    SQMREPORTING="False"

    ; 今後のリリースの SQL Server の改善のため、Microsoft にエラーを報告できるようにするかどうかを指定します。この機能を有効にするには 1 または True を指定し、無効にするには 0 または False を指定します。

    ERRORREPORTING="False"

    ; インストール ディレクトリを指定します。

    INSTANCEDIR="C:\Program Files\Microsoft SQL Server"

    ; エージェント アカウント名

    AGTSVCACCOUNT="gree-rogue\Administrator"
    AGTSVCPASSWORD="パスワード"
    ; インストール後にサービスを自動的に開始します。

    AGTSVCSTARTUPTYPE="Automatic"

    ; CM ブリックの TCP 通信ポート

    COMMFABRICPORT="0"

    ; マトリックスでのプライベート ネットワークの使用方法

    COMMFABRICNETWORKLEVEL="0"

    ; ブリック間の通信を保護する方法

    COMMFABRICENCRYPTION="0"

    ; CM ブリックで使用される TCP ポート

    MATRIXCMBRICKCOMMPORT="0"

    ; SQL Server サービスのスタートアップの種類。

    SQLSVCSTARTUPTYPE="Automatic"

    ; FILESTREAM 機能を有効にするレベル (0、1、2、または 3)。

    FILESTREAMLEVEL="0"

    ; SQL Server Express で RANU を有効にするには、"1" に設定します。

    ENABLERANU="False"

    ; データベース エンジンに使用する Windows 照合順序または SQL 照合順序を指定します。

    SQLCOLLATION="Japanese_CI_AS"

    ; SQL Server サービスのアカウント: ドメイン\ユーザーまたはシステム アカウント。

    SQLSVCACCOUNT="administrator"
    SQLSVCPASSWORD="パスワード"

    ; SQL Server のシステム管理者として準備する Windows アカウント。

    SQLSYSADMINACCOUNTS="administrator"

    ; 既定値は Windows 認証です。混合モード認証の場合は "SQL" を使用してください。

    SECURITYMODE="SQL"

    ; データベース エンジンのバックアップ ファイルの既定のディレクトリ。

    SQLBACKUPDIR="C:\database\backup"

    ; データベース エンジンのユーザー データベースの既定のディレクトリ。

    SQLUSERDBDIR="F:\database\db"

    ; データベース エンジンのユーザー データベース ログの既定のディレクトリ。

    SQLUSERDBLOGDIR="C:\database\log"

    ; データベース エンジンの TempDB ファイルのディレクトリ。

    SQLTEMPDBDIR="F:\database\db"

    ; データベース エンジンの TempDB ログ ファイルのディレクトリ。

    SQLTEMPDBLOGDIR="F:\database\tmp"

    ; 現在のユーザーを SQL Server 2012 Express のデータベース エンジン システム管理者として準備します。

    ADDCURRENTUSERASSQLADMIN="False"

    ; TCP/IP プロトコルを無効にするには 0 を、有効にするには 1 を指定します。

    TCPENABLED="1"

    ; Named Pipes プロトコルを無効にするには 0 を、有効にするには 1 を指定します。

    NPENABLED="0"

    ; Browser サービスのスタートアップの種類。

    BROWSERSVCSTARTUPTYPE="Disabled"

    [/code]

    コマンドプロンプトを開き、
    [code]
    インストールメディア内setup.exe /SAPWD="SQL認証時のsaのパスワード" /ConfigurationFile="保存したConfigurationFile.ini"
    [/code]

    SQLServerに大量のテストデータを入れる

    SQL Serverのパーティション分割の検証で、大量に日付をを含むテストデータが欲しかったので、
    http://d.hatena.ne.jp/dotnetmemo/20111015/1318663995
    http://d.hatena.ne.jp/qaz76/20110512/1305251400
    を参考にしてみた。

    先輩方に感謝！

    testdata

    [code]
    # テーブル作成
    CREATE TABLE [dbo].[TestTable](
    [id] [int] NULL,
    [data] [varchar](50) NULL,
    [add_date] [datetime2](7) NULL
    )

    GO
    [/code]

    [code]
    — ランダムな日付返す
    CREATE VIEW
    V_RAND
    AS
    SELECT RAND() RND
    GO
    –UDF
    CREATE FUNCTION
    RANDNUM
    (
    @START AS INT,
    @END AS INT
    )
    RETURNS INT
    AS
    BEGIN
    RETURN (SELECT FLOOR(@START + (RND * (@END – @START + 1))) FROM V_RAND)
    END
    GO

    –UDF
    CREATE FUNCTION
    RANDDATE
    (
    @START AS DATETIME,
    @END AS DATETIME
    )
    RETURNS DATETIME
    AS
    BEGIN
    RETURN DATEADD(DAY, DBO.RANDNUM(0, DATEDIFF(DAY, @START, @END)), @START)
    END
    GO

    [/code]

    [code]
    — 100万件レコード作る
    — add_dateには、2015/01/01から2016/07/31までの日付をランダムでくっつける（重複あり）
    Declare @p_NumberOfRows Bigint
    Select @p_NumberOfRows=1000000;
    With Base As
    (
    Select 1 as n
    Union All
    Select n+1 From Base Where n < Ceiling(SQRT(@p_NumberOfRows))
    ),
    Expand As
    (
    Select 1 as C From Base as B1, Base as B2
    ),
    Nums As
    (
    Select Row_Number() OVER(ORDER BY C) As n From Expand
    )
    INSERT INTO TestTable
    Select n, ‘DATA’ + right(‘0000000000’ + convert(varchar, n), 10),dbo.RANDDATE(‘2015/01/01’, ‘2016/07/31’)
    from Nums Where n<=@p_NumberOfRows
    OPTION (MaxRecursion 0);
    [/code]