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

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

python3.6.1で週報を作ってみる

親会社様のご命令で週報を送る事になった。
しかもアウトプットはEXCEL指定・・・

メンバーのタスクはJIRAで行っていて、毎週毎週JIRAから重要なタスクをEXCELにコピペって作成するのが面倒なので、スクリプト書いてみた。
※APIアクセスでは無いので、スクリプト内にログイン情報をベタ書きしています

jira.py


#!/usr/bin/env python3
# coding:UTF-8

import sys
import codecs
from jira import JIRA
import xlwt
import datetime

# JIRA 設定
JIRA_HOST = 'https://xxxxx'
USER = 'xxxxxxxx'
PASS = 'xxxxxxxx'
QUERY = 'project=xxxxxxx and status != "クローズ" and priority != "低" AND issuetype = Task order by priority desc , duedate asc'
# output(Excel)
today = datetime.date.today()
OUT_PATH  = "/root/work/"
OUT_FILE = u"today.isoformat() + ".xls"

book = xlwt.Workbook()
newsheet_1 = book.add_sheet('task')

# JIRA認証
options = {
    'server': JIRA_HOST,
    'verify': False}

jira = JIRA(options , basic_auth=(USER, PASS))

# my top 5 issues due by the end of the week, ordered by priority
oh_crap = jira.search_issues(QUERY)

NO = 1
TASK_LIST = {}

for item in oh_crap:
    if (item.fields.issuetype.subtask) != 'True' :
        LIST = []
        LIST.append((item.fields.priority.name).encode('utf-8'))
        LIST.append((','.join(item.fields.labels)).encode('utf-8'))
        LIST.append((item.fields.summary).encode('utf-8'))
        LIST.append((item.fields.status.name).encode('utf-8'))
        LIST.append((item.fields.assignee.displayName).encode('utf-8'))
        LIST.append((item.fields.updated).encode('utf-8'))
        LIST.append((item.fields.duedate).encode('utf-8'))

        TASK_LIST.update({NO:LIST})
        NO += 1

# Excelに書き込む

# border
borders        = xlwt.Borders()
borders.left   = xlwt.Borders.THIN
borders.right  = xlwt.Borders.THIN
borders.top    = xlwt.Borders.THIN
borders.bottom = xlwt.Borders.THIN

# header color
color_header          = xlwt.Pattern()
color_header.pattern  = xlwt.Pattern.SOLID_PATTERN
color_header.pattern_fore_colour = 0x1F

# header set
style_header          = xlwt.XFStyle()
style_header.borders  = borders
style_header.pattern  = color_header

# body high color
color_body          = xlwt.Pattern()
color_body.pattern  = xlwt.Pattern.SOLID_PATTERN
color_body.pattern_fore_colour = 0x35

# body high set
style_body_high          = xlwt.XFStyle()
style_body_high.borders  = borders
style_body_high.pattern  = color_body

# body nomal set
style_body          = xlwt.XFStyle()
style_body.borders  = borders



# ヘッダー追加
newsheet_1_row_1 = newsheet_1.row(0)
newsheet_1_row_1.write(0,'優先度',style_header)
newsheet_1_row_1.write(1,'カテゴリ',style_header)
newsheet_1_row_1.write(2,'タスク',style_header)
newsheet_1_row_1.write(3,'ステータス',style_header)
newsheet_1_row_1.write(4,'担当者',style_header)
newsheet_1_row_1.write(5,'更新日',style_header)
newsheet_1_row_1.write(6,'完了予定日',style_header)

# セルの横幅変更
newsheet_1_column_1 = newsheet_1.col(1)  # カテゴリ
newsheet_1_column_1.width = 4000

newsheet_1_column_2 = newsheet_1.col(2)  # Task
newsheet_1_column_2.width = 20000

newsheet_1_column_3 = newsheet_1.col(3)  # Status
newsheet_1_column_3.width = 4000

newsheet_1_column_4 = newsheet_1.col(4)  # Member
newsheet_1_column_4.width = 4000

newsheet_1_column_5 = newsheet_1.col(5)  # UpDate
newsheet_1_column_5.width = 4000

newsheet_1_column_6 = newsheet_1.col(6)  # Deadline
newsheet_1_column_6.width = 4000

NO = 1

for task_no in TASK_LIST:
    newsheet_1_row_NO = newsheet_1.row(NO)
    if (TASK_LIST[task_no][0].decode('utf-8')) == '高' :
        newsheet_1_row_NO.write(0,TASK_LIST[task_no][0].decode('utf-8'),style_body_high)
        newsheet_1_row_NO.write(1,TASK_LIST[task_no][1].decode('utf-8'),style_body_high)
        newsheet_1_row_NO.write(2,TASK_LIST[task_no][2].decode('utf-8'),style_body_high)
        newsheet_1_row_NO.write(3,TASK_LIST[task_no][3].decode('utf-8'),style_body_high)
        newsheet_1_row_NO.write(4,TASK_LIST[task_no][4].decode('utf-8'),style_body_high)

        # UpDateのみフォーマットが違うので合わせる
        up_d = datetime.datetime.strptime(TASK_LIST[task_no][5].decode('utf-8'),"%Y-%m-%dT%H:%M:%S.000+0900")
        update_day = "{0:%Y-%m-%d}".format(up_d)
        newsheet_1_row_NO.write(5,update_day,style_body_high)
        newsheet_1_row_NO.write(6,TASK_LIST[task_no][6].decode('utf-8'),style_body_high)

    else :
        newsheet_1_row_NO.write(0,TASK_LIST[task_no][0].decode('utf-8'),style_body)
        newsheet_1_row_NO.write(1,TASK_LIST[task_no][1].decode('utf-8'),style_body)
        newsheet_1_row_NO.write(2,TASK_LIST[task_no][2].decode('utf-8'),style_body)
        newsheet_1_row_NO.write(3,TASK_LIST[task_no][3].decode('utf-8'),style_body)
        newsheet_1_row_NO.write(4,TASK_LIST[task_no][4].decode('utf-8'),style_body)

        # UpDateのみフォーマットが違うので合わせる
        up_d = datetime.datetime.strptime(TASK_LIST[task_no][5].decode('utf-8'),"%Y-%m-%dT%H:%M:%S.000+0900")
        update_day = "{0:%Y-%m-%d}".format(up_d)
        newsheet_1_row_NO.write(5,update_day,style_body)
        newsheet_1_row_NO.write(6,TASK_LIST[task_no][6].decode('utf-8'),style_body)
    NO += 1

book.save(OUT_FILE)

sendmail.py

#!/usr/bin/env python3
# coding:UTF-8

import os.path
import datetime
import smtplib

from email import encoders
from email.mime.multipart import MIMEMultipart
from email.mime.text      import MIMEText
from email.utils          import formatdate
from email.mime.base      import MIMEBase

today = datetime.date.today()
OUT_PATH  = "/root/work/"
FILE_NAME = today.isoformat() + ".xls"
OUT_FILE = u"gloops_infra_" + today.isoformat() + ".xls"

SMTP_SERVER = 'mail.xxx.jp'
TO_ADDR   = 'xxx@xxxxx.jp'
FROM_ADDR = 'xxx@xxxxx.jp'

body = """
お疲れ様です。
JIRAさんです。

先週分のインフラチームの週報を添付します( ・_・)ノΞ●~*
確認して関係者に送付してね(´ ω `)ノシ
"""

# CREATE MASSAGE
msg = MIMEMultipart()
msg['Subject'] = '親会社様に送りつける週報_'+str(today)
msg['From'] = FROM_ADDR
msg['To'] = TO_ADDR
msg['Date'] = formatdate()

# 本文
body = MIMEText(body, 'plain')
msg.attach(body)

# 添付ファイル
attachment = MIMEBase('application', 'vnd.ms-excel')
file = open(OUT_FILE, 'rb')
attachment.set_payload(file.read())
file.close()

encoders.encode_base64(attachment)
msg.attach(attachment)
attachment.add_header("Content-Disposition", "attachment", filename=FILE_NAME)


# 送信
s = smtplib.SMTP(SMTP_SERVER)
s.send_message(msg)
s.close()

上記2個のスクリプトをcronで毎週月曜日に送りつけます。

# 週報
30 50 8 * * 1 cd /root/work/jira ; /usr/local/python-3.6.1/bin/python3 jira.py
31 0 9 * * 1 cd /root/work/jira ; /usr/local/python-3.6.1/bin/python3 sendmail.py

これで確認者は内容を見て、必要ならちょこっと修正して親会社様に送るだけ。

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

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

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

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

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

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

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

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

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

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

dirnameが無い!?

centosでdirnameを実行すると、そんなコマンド無いよ!と怒られたのでメモ
※他のメンバーがOSインストールしたんだけど、baseだけしか入れてないのかな?

# dirname
dirname: missing operand
詳しくは `dirname –help’ を実行して下さい.

yum -y install coreutils.x86_64

SQLServer on Linuxを触ってみる

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

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

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

# リポジトリ追加
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

インストールPATHは/opt配下。

ls -1 /opt/
microsoft   → odbcドライバ
mssql       → SQLServer
mssql-tools → bcpとsqlcmd

続いて、初期セットアップを行う。

/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.

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

プロセスも動いている!
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))

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

powershellでCPU情報を取得するアレコレ

# cpu情報を表示
Get-WmiObject -Class Win32_Processor

# 物理コア数を表示
Get-WmiObject -Class Win32_Processor | Format-List NumberOfCores

# 論理コア数を表示(THH)
Get-WmiObject -Class Win32_Processor | Format-List NumberOfLogicalProcessors

と、色々と調べたけど、

Get-WmiObject -Class Win32_Processor | Format-List NumberOfCores, NumberOfLogicalProcessor

が一番すっきりする

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で変更しておきましょう。

コマンドからWindowsFilewallを操作する

上手く動かねないじゃん!と思ってググった備忘録

一般的な事かもしれないけど、書き方のルールは以下の通り

  1. rule= や name=はダブルクォテーションで囲う
  2. profile=で複数プロファイルを指定する時もダブルクォテーションで囲う
  3. 既存のルールには同じ名前でプロファイルが異なる物が多い。
  4. その場合は設定変更の場合は、対象をユニークになるように指定する必要がある。
    netsh advfirewall firewall show rule name=”Windows リモート管理 (HTTP 受信)” dir=in
    → 2つある
    netsh advfirewall firewall show rule name=”Windows リモート管理 (HTTP 受信)” dir=in profile=”domain,private”
    → ユニーク
    netsh advfirewall firewall show rule name=”Windows リモート管理 (HTTP 受信)” dir=in profile=”pblic”
    → ユニーク

  5. 変更の場合は、set name=<対象の名前> <対象をユニークにする条件> new <変更後の設定> と書く
  6. netsh advfirewall firewall set rule name=”Windows リモート管理 (HTTP 受信)” dir=in profile=”domain,private” new profile=any