今までEnterprise版でのみ利用可能だったSQLServerのAlwaysONについて2016から一定の制限はありますが、高可用性グループの作成が出来るようになりましたので早速構築してみました。
【制限】
・1つの高可用性データベースに所属できるスキーマは1つのみ
複数のリスナーは作成は出来るが、Masterを片寄出来ないのが難点。
もちろんjoin句も利用出来ないのでアプリケーション側で結合するか、Linkサーバーとやらを使うしかない（？）
サーバーA：
サーバーB：
構築方法は、SQLServer2012と基本的に同じ。
ただし、SSMSの日本語ローカライズ版はにはバグがあり、現時点では以下の事が出来ません。
マイクロソフトに確認したが、修正リクエスト中でいつ直るか解らないとの事だったので、英語版の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]
スケール調整の覚書
グラフ枠のプロパティ/グラフ/垂直スケール の最大値を100から1000に上げる
NetworkInterface/NIC名/Bytes Sent/sec
・・・ GigaNicを積んでいる場合、スケールは0.00001 でxxxMB/s
※グラフ赤線
NetworkInterface/NIC名/Packet Sent/sec
・・・ GigaNicを積んでいる場合、スケールは0.001 でxxxMbps
※グラフ黄緑線
先日、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 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]
新規にwindows2012サーバーを準備し、WindowsでポリシーベースのQOSを使ってS3へのアップロードスピードを帯域制御する方法をトレースしてみた。
QoSの設定はこの通りだが、「出力方向のスロットル率を指定する」には注意が必要。
検証では200MBbpsに設定を行ったが、物理1Gの回線環境で800MBps以上トラフィックが流れてしまう。
MSに問い合わせをして調査、検証を行った結果、
windows2012のインストール時点のQoSのバグっぽい事まで突き止める。
（Windowsアップデートしたら動きが改善されているので、どこかでパッチが当たった模様）
Windowsアップデート前： 制限の設定値の10倍が実際の制限値となる。
・ QoSを200MBps（204800 KBps）に制限 → トラフィックが200MBps以上
・ QoSを 20MBps（20480 KBｐｓ） に制限 → トラフィックは200MBps以下になる
Windowsアップデート後： 制限の設定値と同じ値が実際の制限値となる。
・ QoSを200MBps（204800 KBps）に制限 → トラフィックが200MBps以下になる
MSからKB番号の返答が来たら追記します。
覚書
以下のファイルを、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]
以前windows用カスタムインストールメディアを作成するで、
OSインストール後に、アプリケーションをインストールをしたい場合の設定を調べたので記載。
動きとしては、
注意点としては、
会社のOSはWindowsServer2008R2なので、SetupComplete.cmdに以下の処理を追記。
[code]
# ログイン後のセットアップに使うスクリプト群をC:直下に配置
xcopy %windir%\Setup\scripts\custum_setup C:\setup\ /Y
# ドットネットフレームワーク4.5をインストール
%windir%\Setup\scripts\Packages\dotnetfx45_full_x86_x64.exe /q
# Windowsアップデート関連の何かをインストール
%windir%\Setup\scripts\Packages\Windows6.1-KB3138612-x64.msu /quiet /norestart
# Powershell4.0をインストール（再起動あり）
%windir%\Setup\scripts\Packages\Windows6.1-KB2819745-x64-MultiPkg.msu /quiet /forcerestart
[/code]
SQL Serverのパーティション分割の検証で、大量に日付をを含むテストデータが欲しかったので、
http://d.hatena.ne.jp/dotnetmemo/20111015/1318663995
http://d.hatena.ne.jp/qaz76/20110512/1305251400
を参考にしてみた。
先輩方に感謝！
[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]
基本的には上のリンク通りだがメモしておく。
Apache(RPM) + wordpressの構成
[code]
# 実行ファイルを置く場所 ※今回は/usr/bin
cd /usr/bin
# リポジトリ取得
git clone https://github.com/certbot/certbot
# インストール&設定 ※ 事前に80/tcpを落としておく
cd certbot
/usr/bin/certbot-auto certonly -a standalone -m メールアドレス -d www.jhhk-family.net –agree-tos –no-bootstrap
# ssl.conf修正
# 事前にssl.confを設定してたとして、Let’s Encryptに上書きされるので、もう一度修正。
vim /etc/httpd/conf.d/ssl.conf
<VirtualHost _default_:443>
DocumentRoot "/var/www/wordpress"
ErrorLog logs/ssl_error_log
TransferLog logs/ssl_access_log
LogLevel warn
<Directory "/var/www/wordpress/">
Options FollowSymLinks
AllowOverride All
Order allow,deny
Allow from all
</Directory>
</vertualhost>
# 最後にDocumentRoot直下の.htaccessに、rewriteの設定を追加し、HTTP接続をHTTPSにリダイレクトする
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteCond %{HTTPS} off # 追加
RewriteRule ^(.*)$ https://%{HTTP_HOST}%{REQUEST_URI} [R,L] # 追加
RewriteBase /
RewriteRule ^index\.php$ – [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
</IfModule>
[/code]
Let’s Encryptの有効期限が3ヶ月なので、crontagで毎月更新している人が多い。
00 16 * * 2 root /usr/bin/certbot-auto renew –post-hook “service httod restart”
基本的にはAWS 外部からの ElastiCache リソースへのアクセスの通り。
Elastic CacheにはAWS以外からのアクセスが出来ないので、
インターネット-[Elastic IP]–[EC2]–[Elastic Cache]
みたいに、中間にEC2インスタンスを噛ます必要がある。
EC2は、PREROUTINGしかt1.microインスタンスでも十分じゃないかな
[code]
#中間のEC2上で
iptables -t nat -A PREROUTING -i eth0 -p tcp –dport 6379 -j DNAT –to ElasticCacheのIP:6379
iptables -t nat -A POSTROUTING -o eth0 -j MASQUERADE
iptables-save
#/etc/sysctl.conf
net.ipv4.ip_forward = 1
sysctl -a
[/code]