LoginSignup
0
0

More than 3 years have passed since last update.

@hibohiboo(ひぼ)

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

Last updated at Posted at 2019-05-13

概要

前回、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

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0