windows + vagrant + virtualbox + centos7.6+ ansibleで SqlServer2017 linux版を試してみたメモ

## 概要

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使用可能時点](https://github.com/hibohiboo/develop/tree/0f908e923db4abcc30ac7a70da289e27fdb684a2/tutorial/lesson/sqlserver/ansible) 



### python3.7の準備

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

```ruby: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をインストール

```bash: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](https://github.com/microsoft/sql-server-samples/tree/master/samples/features/high%20availability/Linux/Ansible%20Playbook)を参考にして設定。

```yaml: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](https://github.com/microsoft/sql-server-samples/tree/master/samples/features/high%20availability/Linux/Ansible%20Playbook)からprovision/playbooks/library内のmssql_conf.pyとmssql_login.pyライブラリは取得。

```privision/playbooks/inventory/hosts
[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のことを確認したら、
サーバに接続してみる。

![sqlserver.sa.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/99876/9dc6f23e-2da7-4222-5f5b-788a80d43c9c.png)

### 作成中におきたエラー

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](https://www.rem-system.com/centos76-install/)
[virtualenv](https://docs.python.org/ja/3/library/venv.html)
[virtualenv](https://mycodingjp.blogspot.com/2018/12/python-venv-virtualenv.html)
[memo docker](https://qiita.com/hibohiboo/items/00f8c5e172d00c15a269)
[memo centos](https://qiita.com/hibohiboo/items/0768b594cb55346faccf)
[memo ansible](https://qiita.com/hibohiboo/items/04edd4748db266f717b2)
[ansible centos](https://ytooyama.hatenadiary.jp/entry/2017/04/19/223514)
[python3](https://weblabo.oscasierra.net/python3-centos7-yum-install/)
[python3.7](https://narito.ninja/blog/detail/20/)
[how to install python3.7](https://tecadmin.net/install-python-3-7-on-centos/) ... インストールの参考
[python3.8](https://qiita.com/ksato9700/items/3846e8db573a07c71c33) ... 3.8は20191020がリリースらしい
[python -v](https://ja.stackoverflow.com/questions/41096/python-v%E3%82%92%E5%AE%9F%E8%A1%8C%E3%81%97%E3%81%9F%E6%99%82%E3%81%AE%E3%82%A8%E3%83%A9%E3%83%BC%E3%81%A3%E3%81%BD%E3%81%84%E6%A8%99%E6%BA%96%E5%87%BA%E5%8A%9B%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6)
[python.py](https://qiita.com/hirohiro77/items/377dfc0a264acb3db222)
[ansible python3.7](https://dev.classmethod.jp/server-side/python/ansible-python3/)
[microsoft/sql-server-samples](https://github.com/microsoft/sql-server-samples/tree/master/samples/features/high%20availability/Linux/Ansible%20Playbook)
[ansible vault](http://jimaoka.hatenablog.jp/entry/ansible-vault)
[ansible vault windows](https://mseeeen.msen.jp/vagrant-ansible-local-provisioner-with-vault-password/)
[sqlserver サーバレベルのロール](https://docs.microsoft.com/ja-jp/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-2017)
[sqlserver データベースレベルのロール](https://docs.microsoft.com/ja-jp/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-2017)
[sql server tool](https://docs.microsoft.com/ja-jp/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017)
[sqlcmd](https://docs.microsoft.com/ja-jp/sql/tools/sqlcmd-utility?view=sql-server-2017)
[sqlcmd](https://qiita.com/zaburo/items/6edf7c05c5d4f5e039eb)
[DB初期化](https://sakapon.wordpress.com/2010/07/13/sqlserverscript/)
[照合順序](https://www.ksakae1216.com/entry/2017/05/18/063000)
[SqlServer文字化け](https://kitigai.hatenablog.com/entry/2018/05/27/010440)
[ないときは作る](http://fla-moo.blogspot.com/2013/05/sqlserversqlite.html)