概要

前回、ansibleでcentosにSqlServerをインストールをした。
今度は、ansibleでdockerをインストールして、docker上でSqlServerを動かしてみる。

環境

Windows 10
Vagrant 2.2.4
- vagrant-vbguest (0.17.2, global)
Virtualbox 6.0.6r130049
CentOS Linux release 7.6.1810 (Core)
Docker version 18.09.6, build 481bc77156
docker-compose version 1.24.0, build 0aa59064

構成

[vagrant@localhost vagrant]$ tree
.
├── Vagrantfile
└── provision
    ├── bash
    │   └── install_ansible.sh
    ├── docker
    │   └── docker-compose.yml
    └── playbooks
        ├── inventory
        │   └── hosts
        ├── roles
        │   ├── docker
        │   │   ├── tasks
        │   │   │   └── main.yml
        │   │   └── vars
        │   │       └── main.yml
        │   └── mssql-server
        │       ├── tasks
        │       │   └── main.yml
        │       └── vars
        │           └── main.yml
        └── vault.yml

ソース

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

Vagrantfile

# ansibleインストール用shell
$ansible_install = <<SHELL
  if ! type /usr/local/bin/virtualenv > /dev/null 2>&1; then
    # ansibleでdockerを扱うために以下が必要。
    yum install -y python-setuptools
    
    # 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 = false
  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

  # 起動するたびにDBを立ち上げる
  config.vm.provision "shell", run: "always", inline: <<-SHELL
    cd /vagrant/provision/docker && /usr/local/bin/docker-compose up -d
  SHELL
end

provision/play.yml

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

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

  roles:
  - docker
  - mssql-server

provision/playbooks/roles/docker/tasks/main.yml

---
# docker 
# Installation ubuntu 
# https://docs.docker.com/engine/installation/linux/ubuntulinux/

- block:
  - name: dockerがインストールされていればバージョンを確認
    shell: docker --version | grep {{ docker_version }}
    register: ver_check
    ignore_errors: True
    check_mode: no
    failed_when: no
    changed_when: ver_check.rc != 0

  - name: debug docker version
    debug: var=ver_check

  - name: バージョンがvarsで指定されたものと異なれば、Remove "docker" package
    yum:
      name: "{{ packages }}"
      state: absent
    vars:
      packages:
        - docker-ce
        - docker 
        - docker-client
        - docker-client-latest
        - docker-common
        - docker-latest
        - docker-latest-logrotate
        - docker-logrotate
        - docker-engine
    when: ver_check.rc != 0
    ignore_errors: True

  - name: dockerがインストールされていなければインストール
    command: docker
    register: result
    ignore_errors: True
    check_mode: no
    failed_when: no
    changed_when: result.rc != 0

- block:
  - name: debug dockerインストール
    debug: var=result

  - name: install yum-utils
    yum: name=yum-utils state=present

  - name: add docker repo
    shell: "yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo"
    args:
      chdir: "/etc/yum.repos.d"
      creates: docker-ce.repo

  - name: install docker-ce
    yum: name=docker-ce state=present

  - name: add group
    user: name=vagrant groups=docker append=yes
    tags: dockerhost
    become: True

  - name: restart docker
    systemd:
      name: docker.service
      state: restarted
      daemon_reload: yes
      enabled: yes
  when: result.rc != 0

- block:
  - name: docker-composeがインストールされていればバージョンを確認
    shell: /usr/local/bin/docker-compose --version | grep {{ docker_compose_version }}
    register: ver_check_compose
    ignore_errors: True
    check_mode: no
    failed_when: no
    changed_when: ver_check_compose.rc != 0

  - name: debug docker-compose
    debug: var=ver_check_compose

  - name: バージョンがvarsで指定されたものと異なれば、Remove "docker-compose" package
    shell: rm /usr/local/bin/docker-compose
    ignore_errors: True
    when: ver_check_compose.rc != 0

  - name: check docker-compose
    command: /usr/local/bin/docker-compose -v
    register: result_compose
    ignore_errors: True
    check_mode: no
    failed_when: no
    changed_when: result_compose.rc != 0

  - name: debug docker-compose
    debug: var=result_compose

# 現在のバージョンに合わせてURLを変えること
- block:
  - name: debug docker-compose result
    debug: var=result_compose
  - name: step1 install docker-compose
    shell: curl -L "https://github.com/docker/compose/releases/download/{{ docker_compose_version }}/docker-compose-$(uname -s)-$(uname -m)" > /usr/local/bin/docker-compose &&  chmod +x /usr/local/bin/docker-compose warn=no
  when: result_compose.rc != 0

provision/docker/docker-compose.yml

version: '3'
services:
  db:
    image: microsoft/mssql-server-linux:2017-latest
    environment:
      ACCEPT_EULA: Y
      MSSQL_SA_PASSWORD: YourStrong!Passw0rd # ※のちに再設定するパスワード
    volumes:
      - db-volume:/var/opt/mssql/
    ports:
      - "1433:1433"

volumes:
  db-volume:
    driver: local

docker-compose execは-Tオプションがないと the input device is not a TTY のエラー

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

---
- name: dockerの開始
  shell: cd /vagrant/provision/docker  && /usr/local/bin/docker-compose up -d
  register: result
  changed_when: result.rc != 0

- name: パスワードが変更されているか確認
  shell:  cd /vagrant/provision/docker && /usr/local/bin/docker-compose exec -T {{ docker_container_name}} /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '{{ sa_password }}' -Q 'SELECT @@VERSION' 
  register: ver_check
  ignore_errors: True
  check_mode: no
  failed_when: no
  changed_when: ver_check.rc != 0

- name: debug docker version
  debug: var=ver_check

- name: パスワードが変更されていない場合、初期設定から変更
  shell: cd /vagrant/provision/docker && /usr/local/bin/docker-compose exec -T {{ docker_container_name}} /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '{{ sa_first_password }}' -Q 'ALTER LOGIN SA WITH PASSWORD="{{ sa_password }}"' 
  register: result
  changed_when: result.rc != 0
  when: ver_check.rc != 0

- name: テスト用のユーザ作成
  shell: cd /vagrant/provision/docker && /usr/local/bin/docker-compose exec -T {{ docker_container_name}} /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '{{ sa_password }}' -Q "IF EXISTS(SELECT * FROM sys.sql_logins WHERE name = '{{ test_user_name }}') ALTER LOGIN  {{ test_user_name }} WITH PASSWORD =  '{{ test_user_pass }}' ELSE CREATE LOGIN {{ test_user_name }} WITH PASSWORD =  '{{ test_user_pass }}', DEFAULT_DATABASE = [master], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF" 
  register: result
  changed_when: result.rc != 0

- name: ユーザに権限付与
  shell: cd /vagrant/provision/docker && /usr/local/bin/docker-compose exec -T {{ docker_container_name}} /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '{{ sa_password }}' -Q "ALTER SERVER ROLE dbcreator ADD MEMBER {{ test_user_name }}" 
  register: result
  changed_when: result.rc != 0


- name: 初期DBの作成。大文字小文字を区別する設定に変更
  shell: cd /vagrant/provision/docker && /usr/local/bin/docker-compose exec -T {{ docker_container_name}} /opt/mssql-tools/bin/sqlcmd -S localhost -U {{test_user_name}} -P '{{ test_user_pass }}' -Q "IF DB_ID (N'TestDB') IS NULL create database [TestDB] collate Japanese_CS_AS" 
  register: result
  changed_when: result.rc != 0


- name: 初期テーブルの作成。
  shell: cd /vagrant/provision/docker && /usr/local/bin/docker-compose exec -T {{ docker_container_name}} /opt/mssql-tools/bin/sqlcmd -S localhost -U {{test_user_name}} -P '{{ test_user_pass }}' -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: cd /vagrant/provision/docker && /usr/local/bin/docker-compose exec -T {{ docker_container_name}} /opt/mssql-tools/bin/sqlcmd -S localhost -U {{test_user_name}} -P '{{ test_user_pass }}' -d TestDB  -Q  "INSERT INTO TestTable(name) SELECT N'ばなな' name WHERE NOT EXISTS (select 1 from TestTable where name = 'ばなな')" 
  register: result
  changed_when: result.rc != 0

参考

vagrant上のcentosにansibleでdocker-ceをインストールする
 ansible で docker
AnsibleでDockerとかしたいのにdocker-pyに苦しめられた話
 Rails+SQL Server環境をDocker Composeで構築する
 test
sqlserver docker 実行と接続
 dockerコマンドをcronで実行させたら「TTYが無いよ」と怒られた件
 Excec
【解決】SQLEXPRESSインストールエラー　e:\sql11_main_t.obj.x86release？
vagrant provision