概要

Vagrant 上に建てたCentOS7.6にansibleをインストールし、それを使って
SqlServer2017をセットアップした。
日本語の記事がそれほど多くなかったので手順をメモしておく。

環境

Windows 10
Vagrant 2.2.4
- vagrant-vbguest (0.17.2, global)
Virtualbox 6.0.6r130049
CentOS Linux release 7.6.1810 (Core)

ディレクトリ構造

[vagrant@localhost vagrant]$ tree
.
├── Vagrantfile
└── provision
    ├── bash
    │   └── install_ansible.sh
    └── playbooks
        ├── inventory
        │   └── hosts
        ├── library
        │   ├── mssql_conf.py
        │   └── mssql_login.py
        ├── play.yml
        ├── roles
        │   └── mssql-server
        │       └── tasks
        │           └── main.yml
        └── vault.yml

ソース

最終的な作成結果 → SqlServer使用可能時点

python3.7の準備

pythonの3.7をインストールしている
virtualenvをインストールしている

Vagrantfile

# ansibleインストール用shell
$ansible_install = <<SHELL
  if ! type /usr/local/bin/virtualenv > /dev/null 2>&1; then
    # rootユーザとして実行されるためsudo不要
    # yum install -y https://centos7.iuscommunity.org/ius-release.rpm
    # yum search python37
    # 3.7はまだepelにいないのでソースからビルド
    yum install -y zlib-devel libffi-devel bzip2-devel openssl-devel ncurses-devel sqlite-devel readline-devel tk-devel gdbm-devel libuuid-devel xz-devel 
    cd /usr/local/src \
    && curl -O https://www.python.org/ftp/python/3.7.3/Python-3.7.3.tgz \
    && tar xf Python-3.7.3.tgz \
    && cd Python-3.7.3 \
    && ./configure \
    && make \
    && make altinstall 
    
    # echo $PATHは以下の結果となり、/usr/local/binは含まれていない。
    # /usr/local/sbin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
    /usr/local/bin/python3.7 -m pip install --upgrade pip
    /usr/local/bin/python3.7 -m pip install virtualenv  

    # vagrantユーザとしてvirtualenvとansibleをインストール
    su -c "source /vagrant/provision/bash/install_ansible.sh" vagrant
  fi
SHELL

Vagrant.configure("2") do |config|
  config.vm.box = "bento/centos-7.6"
  config.vm.box_version = "201812.27.0"

  config.vm.network "private_network", ip: "192.168.50.11"

  config.vm.provider "virtualbox" do |vm|
    # メモリを設定
    vm.memory = 4092
    # Vagrant1.8から利用出来るLinked Cloneをオンにする。
    vm.linked_clone = true

    vm.customize [ "modifyvm", :id, "--cpus", "2", "--ioapic", "on"]

    # 普段はtrue。Vagrant assumes that this means the command failed! setup となったときに、falseにしてみる。解決した場合、vagrant-vbguestが悪さをしている
    config.vbguest.auto_update = true
  end
  # ansible vaultのためにパーミッションを指定
  config.vm.synced_folder "./provision", "/provision", id: "ansible", owner: "vagrant", group: "vagrant", mount_options: ["dmode=775,fmode=664"]
  # ansibleをインストール
  config.vm.provision "shell", inline: $ansible_install
  # ansibleを実行
  config.vm.provision "shell", inline: <<-SHELL
    # timezoneを日本に変更
    timedatectl set-timezone Asia/Tokyo
    # localeを日本に変更
    localectl set-locale LANG=ja_JP.UTF-8
    # virtualenv起動
    source /home/vagrant/venv/bin/activate
    # provision 実行
    DEFAULT_VAULT_PASSWORD_FILE=ANSIBLE_CONFIG=/provision/.ansible.cfg ansible-playbook -i /provision/playbooks/inventory/hosts /provision/playbooks/play.yml -c local -v --vault-password-file /provision/playbooks/.passwd
  SHELL
end

virtualenvで作成した仮想環境上にansibleをインストール

provision/bash/install_ansible.sh

# !/bin/bash

curl -kL "https://bootstrap.pypa.io/get-pip.py" | sudo /usr/local/bin/python3.7
cd $HOME
/usr/local/bin/virtualenv venv
source $HOME/venv/bin/activate
pip install ansible
ansible --version

ansible playbookの準備

ansible-vault create vault.ymlで作成。
その時開かれるviには以下を入力。
作成されるときに聞かれるパスワードは今回は「password」に設定（セキュリティ低）。

---
# The password for the sa user. Only used if mssql-server needs to be installed.
sa_password: '!sa_password001'
test_user_name: 'test_db_user'
test_user_pass: '!test_password001'

これによって以下のようなvault.ymlファイルが作成される。

provision/playbooks/vault.yml

$ANSIBLE_VAULT;1.1;AES256
66646134376565333433313332366265663764326633323436643638616238626433356434343436
6532626430326233613235306533333730373833343631370a383134613537326263376330653866
34373066373463376332613135613133386336343231653461306131306265366636623639626637
3463353261633032380a626232653036343830306435346438653734656562636662346137363332
33316534366536363336393335666366306535623236386435613465306535323639303862633733
35363835346663376338653034363531383131373261626564393363353466346634656331326537
30366564326465316564656433386663353133656431393939653164383937393735333365663035
31336332373935636232646537313434363534333064383366646433396665643736633638383738
38633237366536333962336536663765343035336463313539653961383561373664336136383364
39303564666534656364613833626164613935636131386564653936616332323336316162393634
30383431383838353230353163303138633738633335353361313635636361633330656661623938
35646436633839343463616266336462393965666264373132623331613939393534626232663437
61623531366164626661653465373964373737646462333763373034613862373933

.passwdにパスワードを記述。git管理にはいれない。

provision/playbooks/.passwd

password

SqlServerのインストール

microsoft/sql-server-samplesを参考にして設定。

provision\playbooks\roles\mssql-server\tasks\main.yml

---

# install mssql-server package

- name: install mssql-server repo (CentOS, RedHat)
  get_url:
    url: "{{ centos_server_repo_url }}"
    dest: /etc/yum.repos.d/mssql-server.repo
  when: ansible_distribution in ['CentOS', 'RedHat']

- name: install mssql-server repo (Ubuntu)
  get_url:
    url: "{{ ubuntu_server_repo_url }}"
    dest: /etc/apt/sources.list.d/mssql-server.list
  when: ansible_distribution == 'Ubuntu'

- name: refresh apt-get cache for server repo (Ubuntu)
  command: apt-get update
  when: ansible_distribution == 'Ubuntu'

- name: install mssql-server package
  package:
    name: mssql-server
    state: latest


# setup

- name: mssql-server setup
  mssql_conf:
    setup_sa_password: "{{ sa_password }}"
    setup_pid: "{{ pid }}"
    login_name: 'sa'
    login_password: "{{ sa_password }}"


# TSQL endpoint

- name: set TSQL endpoint port
  mssql_conf:
    name: network.tcpport
    value: "{{ tsql_endpoint_port }}"
    login_name: 'sa'
    login_password: "{{ sa_password }}"

- name: check if firewalld is installed (CentOS, RedHat)
  command: rpm -q firewalld
  register: firewalld_installed
  failed_when: false
  when: ansible_distribution in ['CentOS', 'RedHat']

- name: open TSQL endpoint in firewall (CentOS, RedHat)
  firewalld:
    port: "{{ tsql_endpoint_port }}/tcp"
    state: enabled
    permanent: true
  when: (ansible_distribution in ['CentOS', 'RedHat']) and firewalld_installed.rc == 0


- name: reload firewall (CentOS, RedHat)
  command: firewall-cmd --reload
  # vagrantでは"FirewallD is not running"と言われて失敗する
  failed_when: false
  when: (ansible_distribution in ['CentOS', 'RedHat']) and firewalld_installed.rc == 0

- name: open TSQL endpoint in firewall (Ubuntu)
  ufw:
    port: "{{ tsql_endpoint_port }}"
    proto: tcp
    rule: allow
  when: ansible_distribution == 'Ubuntu'


# mssql-tools package

- name: install mssql-tools repo (CentOS, RedHat)
  get_url:
    url: "{{ centos_tools_repo_url }}"
    dest: /etc/yum.repos.d/mssql-tools.repo
  when: ansible_distribution in ['CentOS', 'RedHat']

- name: install mssql-tools repo (Ubuntu)
  get_url:
    url: "{{ ubuntu_tools_repo_url }}"
    dest: /etc/apt/sources.list.d/mssql-tools.list
  when: ansible_distribution == 'Ubuntu'

- name: refresh apt-get cache for tools repo (Ubuntu)
  command: apt-get update
  when: ansible_distribution == 'Ubuntu'

- name: install mssql-tools package
  package:
    name: mssql-tools
    state: latest
  environment:
    ACCEPT_EULA: 'y'


# Start mssql-server service

- name: start sqlservr
  service:
    name: mssql-server
    state: started

# テスト用のユーザ作成
- name: add user
  mssql_login:
    name: "{{ test_user_name }}"
    password: "{{ test_user_pass }}"
    roles:
    # DBの追加・削除ができる権限
      - dbcreator
    login_name: 'sa'
    login_password: "{{ sa_password }}"

- name: パスを通す 接続したとき
  lineinfile:
    path: /home/vagrant/.bash_profile
    line: 'export PATH="$PATH:/opt/mssql-tools/bin"'

- name: パスを通す 各bashごと毎回
  lineinfile:
    path: /home/vagrant/.bash_profile
    line: 'export PATH="$PATH:/opt/mssql-tools/bin"'

- name: 初期DBの作成。大文字小文字を区別する設定に変更
  shell: /opt/mssql-tools/bin/sqlcmd -S localhost -U test_db_user -P '!test_password001' -Q "IF DB_ID (N'TestDB') IS NULL create database [TestDB] collate Japanese_CS_AS"
  register: result
  changed_when: result.rc != 0

- name: 初期テーブルの作成。
  shell: /opt/mssql-tools/bin/sqlcmd -S localhost -U test_db_user -P '!test_password001' -d TestDB -Q "if object_id('TestTable') is null CREATE TABLE TestTable (id INT IDENTITY(1,1) NOT NULL,name NVARCHAR(128), CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED ([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]"
  register: result
  changed_when: result.rc != 0


- name: 初期データの挿入
  shell: /opt/mssql-tools/bin/sqlcmd -S localhost -U test_db_user -P '!test_password001' -d TestDB -Q "INSERT INTO TestTable(name) SELECT 'ばなな' name WHERE NOT EXISTS (select 1 from TestTable where name = 'ばなな')"
  register: result
  changed_when: result.rc != 0

ansible実行用のファイルを作成

microsoft/sql-server-samplesからprovision/playbooks/library内のmssql_conf.pyとmssql_login.pyライブラリは取得。

[localhost]
127.0.0.1

provision/playbooks/play.yml

---
# Usage:
#    ansible-playbook ./play.yml -i ./inventory --ask-vault-pass -e 'ansible_user=<>'
- hosts: localhost

  vars:
    # The URL of the repo to fetch the mssql-server and mssql-server-ha packages from
    centos_server_repo_url: 'https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo'
    ubuntu_server_repo_url: 'https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list'

    # The URL of the repo to fetch the mssql-tools package from
    centos_tools_repo_url: 'https://packages.microsoft.com/config/rhel/7/prod.repo'
    ubuntu_tools_repo_url: 'https://packages.microsoft.com/config/ubuntu/16.04/prod.list'

    # The sqlservr PID. Only used if mssql-server needs to be installed.
    pid: 'Developer'

    # The port for the TSQL endpoint
    tsql_endpoint_port: 1433


  vars_files:
  - 'vault.yml'

  become: yes
  become_user: root
  become_method: sudo
  any_errors_fatal: true
  max_fail_percentage: 0

  roles:
  - mssql-server

接続確認

vagrant upで立ち上げ、準備として vagrant provisionでfailedが0のことを確認したら、
サーバに接続してみる。

作成中におきたエラー

pythonインストール時

    default: Traceback (most recent call last):
    default:   File "/usr/local/src/Python-3.7.3/Lib/runpy.py", line 193, in _run_module_as_main
    default:     "__main__", mod_spec)
    default:   File "/usr/local/src/Python-3.7.3/Lib/runpy.py", line 85, in _run_code
    default:     exec(code, run_globals)
    default:   File "/usr/local/src/Python-3.7.3/Lib/ensurepip/__main__.py", line 5, in <module>
    default:     sys.exit(ensurepip._main())
    default:   File "/usr/local/src/Python-3.7.3/Lib/ensurepip/__init__.py", line 204, in _main
    default:     default_pip=args.default_pip,
    default:   File "/usr/local/src/Python-3.7.3/Lib/ensurepip/__init__.py", line 117, in _bootstrap
    default:     return _run_pip(args + [p[0] for p in _PROJECTS], additional_paths)
    default:   File "/usr/local/src/Python-3.7.3/Lib/ensurepip/__init__.py", line 27, in _run_pip
    default:     import pip._internal
    default: zipimport.ZipImportError: can't decompress data; zlib not available
    default: make: *** [altinstall] Error 1

パーミション設定をディレクトリに行わずにansible vaultを使ったとき

    default:  [WARNING]: Error in vault password file loading (default): Problem running
    default: vault password script /vagrant/provision/playbooks/.passwd ([Errno 8] Exec
    default: format error: '/vagrant/provision/playbooks/.passwd'). If this is not a script,
    default: remove the executable bit from the file.
    default: ERROR! Problem running vault password script /vagrant/provision/playbooks/.passwd ([Errno 8] Exec format error: '/vagrant/provision/playbooks/.passwd'). If this is
not a script, remove the executable bit from the file.
The SSH command responded with a non-zero exit status. Vagrant
assumes that this means the command failed. The output for this command
should be in the log above. Please read the output to determine what
went wrong.

vaultのパスワードを間違えた時

    default: ERROR! Decryption failed (no vault secrets were found that could decrypt) on /provision/playbooks/vault.yml

ansibleのtasks/main.ymlにタブ文字を入れてしまったとき

    default: ERROR! Syntax Error while loading YAML.
    default:   found character '\t' that cannot start any token

参考

Centos7.6
virtualenv
virtualenv
memo docker
memo centos
memo ansible
ansible centos
python3
python3.7
how to install python3.7 ... インストールの参考
python3.8 ... 3.8は20191020がリリースらしい
python -v
python.py
ansible python3.7
microsoft/sql-server-samples
ansible vault
ansible vault windows
sqlserver サーバレベルのロール
 sqlserver データベースレベルのロール
 sql server tool
sqlcmd
sqlcmd
DB初期化
 照合順序
 SqlServer文字化け
 ないときは作る